Cassandra database schema and select IN issue

Question

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? 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 preceding column "event_time" is either not restricted or by no-EQ relation?

-Subodh

Answer 1

You can't make it work like this. IN predicate on lookup indexed columns is not supported. So you won't be able to use IN on "timestamp" column. You could use IN if you also provide both asset_id and event_time -- that's what cqlsh is telling you with the message

Bad Request: PRIMARY KEY column "timestamp" cannot be restricted (preceding column "event_time" is either not restricted or by a non-EQ relation)

The solution for your problem is denormalization. What happens under the hood when creating a index is that Cassandra will create and handle for you a new table having the indexed as partition key -- so Cassandra has already denormalized and duplicated your data. Remove your index and create a new table

CREATE TABLE sensor_info_table_by_timestamp
(
  asset_id text,
  event_time timestamp,
  "timestamp" timeuuid,
  sensor_reading map<text, text>,
  sensor_serial_number text,
  sensor_type int,
  PRIMARY KEY ("timestamp", asset_id, event_time )
);

Now anytime you write data you put in both tables (you can use a batch). And your query will be

SELECT * from mydb.sensor_info_table_by_timestamp where timestamp IN ( bfdfa614-3166-11e4-a61d-b888e30f5d17 , bf4521ac-3166-11e4-87a3-b888e30f5d17) ;

I see you also created a lookup index on event_time -- if you have to perform query using IN predicate you will have to create a third table ...

HTH, Carlo

Answer 2

[ I had posted this query on cassandra users mailing list, and as per suggestion I ended up creating two tables to handle this requirement ]

CREATE TABLE sensor_asset (
  asset_id text,
  event_time timestamp,
  tuuid timeuuid,
  sensor_reading map<text, text>,
  sensor_serial_number text,
  sensor_type int,
  PRIMARY KEY ((asset_id), event_time)
);

CREATE TABLE sensor_tuuid (
  asset_id text,
  event_time timestamp,
  tuuid timeuuid,
  sensor_reading map<text, text>,
  sensor_serial_number text,
  sensor_type int,
  PRIMARY KEY (tuuid)
);