Thanks for your help Michael.
If specifying asset_id would help I can construct queries that can
include asset_id
So I have been "playing" around with PRIMARY KEY definition and
following table definition
CREATE TABLE sensor_info_table (
asset_id text,
event_time timestamp,
"timestamp" timeuuid,
sensor_reading map<text, text>,
sensor_serial_number text,
sensor_type int,
PRIMARY KEY ((asset_id, "timestamp"), event_time)
);
It does what I want to do, and I removed the index for timestamp item
since now it is part of primary key and thus my query like this works.
SELECT * from sigsense.sensor_info_table where asset_id='3' AND
timestamp IN (
17830bb0-316a-11e4-800f-b888e30f5d17,16ddbdfe-316a-11e4-9f50-b888e30f5d17
);
But now this doesn't work it give
SELECT * from sensor_info_table where asset_id='3' ;
Bad Request: Partition key part timestamp must be restricted since
preceding part is
I am keeping index on event_time as I sometime need to query something
"give me all data since time x" i.e. something like this works.
SELECT * from sensor_info_table where event_time > '2014-08-31
16:54:02-0700' ALLOW FILTERING;
However if I do this things then this don't work:
SELECT * from sensor_info_table where asset_id='3' AND event_time >
'2014-08-31 16:54:02-0700';
Bad Request: Partition key part timestamp must be restricted since
preceding part is
Also I am not conformable with fact that I need to specify ALLOW FILTERING.
I guess cassandra schema design task asks designer to write down
queries before designing schema.
For the above table definition I want to do following queries:
- Give me all sensor data for given asset.
- Give me sensor data that matches given set of timeuuids
- Give me all sendor data for a given asset, that were collected after
| before | between certain event_time.
Given these query criteria how should I construct my schema? One
thought has occurred to me is make three tables with each item
asset_id , event_time, timeuuid as primary keys and depending on type
of query choose the table to do query upon. That seems like a waste of
resources (disk, cpu ), also increasing insert times(!) but thats the
way things need to happen in cassandra world its okay. ( I am
two-three weeks into learning about cassandra).
-Subodh
On Sun, Aug 31, 2014 at 6:44 PM, Laing, Michael
<michael.la...@nytimes.com> wrote:
> Oh it must be late - I missed the fact that you didn't want to specify
> asset_id. The above queries will still work but you have to use 'allow
> filtering' - generally not a good idea. I'll look again in the morning.
>
>
> On Sun, Aug 31, 2014 at 9:41 PM, Laing, Michael <michael.la...@nytimes.com>
> wrote:
>>
>> Hmm. Because the clustering key is (event_time, "timestamp"), event_time
>> must be specified as well - hopefully that info is available to the ux.
>>
>> Unfortunately you will then hit another problem with your query: you are
>> selecting a collection field... this will not work with IN on "timestamp".
>>
>> So you could select all the "timestamp"s for an asset_id/event_time:
>>>
>>> SELECT * from sensor_info_table where asset_id = 'a' and event_time =
>>> 1231234;
>>
>>
>> Or you could apply a range of "timestamp"s:
>>>
>>> SELECT * from sensor_info_table where asset_id = 'a' and event_time =
>>> 1231234 and "timestamp" > 1d934af3-3178-11e4-ba8d-406c8f1838fa and
>>> "timestamp" < 20b82021-3178-11e4-abc2-406c8f1838fa;
>>
>>
>> BTW the secondary indices are not a good idea: high cardinality and of no
>> use in this query that I can see.
>>
>> ml
>>
>>
>> On Sun, Aug 31, 2014 at 8:40 PM, Subodh Nijsure <subodh.nijs...@gmail.com>
>> wrote:
>>>
>>> Not really event time stamp is created by the sensor when it reads data
>>> and timestamp is something server creates when inserting data into
>>> cassandra db. At later point in time my django ux allows users to browse
>>> this data and reference interesting data points via the timestamp field. The
>>> timestamp field is my bridge between Sal and nosql world.
>>>
>>> Subodh
>>>
>>> On Aug 31, 2014 5:33 PM, "Laing, Michael" <michael.la...@nytimes.com>
>>> wrote:
>>>>
>>>> Are event_time and timestamp essentially representing the same datetime?
>>>>
>>>> On Sunday, August 31, 2014, Subodh Nijsure <subodh.nijs...@gmail.com>
>>>> wrote:
>>>>>
>>>>> I have following database schema
>>>>>
>>>>> CREATE TABLE sensor_info_table (
>>>>> asset_id text,
>>>>> event_time timestamp,
>>>>> "timestamp" timeuuid,
>>>>> sensor_reading map<text, text>,
>>>>> sensor_serial_number text,
>>>>> sensor_type int,
>>>>> PRIMARY KEY ((asset_id), event_time, "timestamp")
>>>>> );
>>>>>
>>>>> CREATE INDEX event_time_index ON sensor_info_table (event_time);
>>>>>
>>>>> CREATE INDEX timestamp_index ON sensor_info_table ("timestamp");
>>>>>
>>>>> Now I am able to insert the data into this table, however I am unable
>>>>> to do following query where I want to select items with specific
>>>>> timeuuid values.
>>>>>
>>>>> It gives me following error.
>>>>>
>>>>> SELECT * from mydb.sensor_info_table where timestamp IN (
>>>>> bfdfa614-3166-11e4-a61d-b888e30f5d17 ,
>>>>> bf4521ac-3166-11e4-87a3-b888e30f5d17) ;
>>>>>
>>>>> Bad Request: PRIMARY KEY column "timestamp" cannot be restricted
>>>>> (preceding column "event_time" is either not restricted or by a non-EQ
>>>>> relation)
>>>>>
>>>>> What do I have to do to make this work?
>>>>>
>>>>> For what its worth I am using django for my front end development and
>>>>> I am using "timestamp timeuuid" field as unique indentifier to
>>>>> reference specific sensor reading from django framework -- since
>>>>> cassandra doesn't have way to generate unique id upon insert (like
>>>>> old-style rdms's auto-fields).
>>>>>
>>>>>
>>>>> Below is software version info.
>>>>>
>>>>> show VERSION ; [cqlsh 4.1.1 | Cassandra 2.0.9 | CQL spec 3.1.1 |
>>>>> Thrift protocol 19.39.0]
>>>>>
>>>>> I really don't understand what the error message preceeding column
>>>>> "event_time" is either not restricted or by no-EQ relation?
>>>>>
>>>>> -Subodh Nijsure
>>
>>
>
