CODEWITHSUNDEEP
cassandracql
Balachandar
Balachandar

Reputation: 1628

cassandra cql query with in condition in rowkey and in clustering columns

I am a newbie to cassandra. And i have a table with composite primary key. The description of the table is 

CREATE TABLE testtable (
  foid bigint,
  id bigint,
  severity int,
  category int,
  ack boolean,
  PRIMARY KEY (foid, id, severity, category)
) WITH
  bloom_filter_fp_chance=0.010000 AND
  caching='KEYS_ONLY' AND
  comment='' AND
  dclocal_read_repair_chance=0.000000 AND
  gc_grace_seconds=864000 AND
  index_interval=128 AND
  read_repair_chance=0.100000 AND
  replicate_on_write='true' AND
  populate_io_cache_on_flush='false' AND
  default_time_to_live=0 AND
  speculative_retry='NONE' AND
  memtable_flush_period_in_ms=0 AND
  compaction={'class': 'SizeTieredCompactionStrategy'} AND
  compression={'sstable_compression': 'LZ4Compressor'};

My requirement is that i need to query the table with foid with in condition and id with range condition and severity with in condition

so when i tried the following query 

select * from testtable where foid in (5,6) and id>10 and severity in (5);

i got the error message as

select * from testtable where foid in (5,6) and id>10 and severity in (5);

or even the equal condition on the severity column is suffice for me which won't work either.

Is there any way that the same can be accomplished

I tried with secondary indexes for severity and category too and that did not give me anything positive.

Upvotes: 0

Views: 1130

Answers (1)

lorcan
lorcan

Reputation: 3300

You need to successively restrict the primary keys, so the following will work:

select * from testtable where foid in (1) and id=2 and severity<20 ;

but this won't:

select * from testtable where foid in (1) and id>10 and severity=3;

What about making the query less restrictive (as you suggested in your question) as follows

select * from testtable where foid in (5,6) and id>10

And sorting through the results at the client side?

An alternative (and probably more attractive) solution would be to order your keys according to how you are going to perform the query, e.g.,

CREATE TABLE testtable2 (
  foid bigint,
  severity int,
  id bigint,
  category int,
  ack boolean,
  PRIMARY KEY (foid, severity, id, category)
)

allowing you to make queries like this (note the equality operation on severity, an IN operation on severity won't work):

select * from testtable2 where foid in (5,6) and severity=5 and id>10;

(tested with cql [cqlsh 4.0.1 | Cassandra 2.0.1 | CQL spec 3.1.1 | Thrift protocol 19.37.0])

Upvotes: 1

Related Questions