cassandracqlcassandra-2.0pycassa

how to do the query in cassandra If i have two cluster key in column family


I have a column family and syntax like this:

CREATE TABLE sr_number_callrecord ( 
  id int, 
  callerph text, 
  sr_number text, 
  callid text, 
  start_time text, 
  plan_id int, 
  PRIMARY KEY((sr_number), start_time, callerph) 
);

I want to do the query like :

  a) select * from dummy where sr_number='+919xxxx8383' 
                   and start_time >='2014-12-02 08:23:18' limit 10;

  b)  select * from dummy where sr_number='+919xxxxxx83' 
                          and start_time >='2014-12-02 08:23:18' 
                          and callerph='+9120xxxxxxxx0' limit 10;

First query works fine but second query is giving error like

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

If I get the result in first query, In second query I am just adding one
more cluster key to get filter result and the row will be less


Solution

  • Just like you cannot skip PRIMARY KEY components, you may only use a non-equals operator on the last component that you query (which is why your 1st query works).

    If you do need to serve both of the queries you have listed above, then you will need to have separate query tables for each. To serve the second query, a query table (with the same columns) will work if you define it with a PRIMARY KEY like this:

    PRIMARY KEY((sr_number), callerph, start_time)
    

    That way you are still specifying the parts of your PRIMARY KEY in order, and your non-equals condition is on the last PRIMARY KEY component.