Cassandra Select query

Question

In this table los.configuration is having a partition key consisting of 3 columns (1. groupname 2. class 3.yearofjoining). There may be 2 class BPL and APL. I want to select those two categories from database.

So I have to use the query:

SELECT * FROM CONFIGURATION WHERE GROUPNAME = 332 
AND CLASS IN ('APL','BPL') AND YEAROFJOINING IN (2014,2015);

When I am trying this query it is returning an error as

Partition KEY part class cannot be restricted by IN relation (only the last part of the partition key can).

Any idea what the problem is?

`This is not working` is not a good question. How is it not working? And your problem is very possibly because you're not adding the keyspace to your column family. Try it with `select * from keyspace.table where condition1 = something` and see if that works. — kha, Mar 19 '15 at 10:38
Are you trying to use an OR condition in your where clause? http://stackoverflow.com/questions/10139390/alternative-for-or-condition-after-where-clause-in-select-statement-cassandra — Stefan Podkowinski, Mar 19 '15 at 10:40
SELECT * from TABLE where condition1 = something or condition1 = something — Getz, Mar 19 '15 at 10:40
@Getz While that may work in SQL, it won't work in CQL (Cassandra Query Language). — Aaron, Mar 19 '15 at 11:55
In this table los.configuration is having a partition key consisting of 3 columns. (1. groupname 2. class 3.yearofjoining). there may be 2 class BPL and APL. I want to select those two categories from database. and the yearofjoining is a list of int. So i have to use the query SELECT * FROM CONFIGURATION WHERE GROUPNAME = 332 AND CLASS IN ('APL','BPL') AND YEAROFJOINING IN (2014,2015); and when i am trying this query it is returning an error as Partition KEY part class cannot be restricted by IN relation (only the last part of the partition key can). Any idea what the problem is? — Praveen, Mar 20 '15 at 05:56

score 2 · Answer 1 · edited May 23 '17 at 12:12

The main problem here, is that you are trying to use an IN relation on two parts of a composite partition key. IN can only operate on the last part of a partition or clustering key.

To get your query to work, your PRIMARY KEY will need to partition on GROUPNAME and CLASS, while clustering on YEAROFJOINING:

PRIMARY KEY ((groupname, class), yearofjoining))

When I query your table with that PRIMARY KEY, using your original query:

aploetz@cqlsh:stackoverflow> SELECT * FROM CONFIGURATION 
    WHERE GROUPNAME = 332 AND CLASS IN ('APL','BPL') 
    AND YEAROFJOINING IN (2014,2015);

 groupname | class | yearofjoining | value
-----------+-------+---------------+-------
       332 |   APL |          2014 | test1
       332 |   APL |          2015 | test3
       332 |   BPL |          2014 | test2

(3 rows)

Additional thoughts:

I did not know that you could use an IN relation on both the last part of a partition and clustering key in the same query. So I learned something today!
I feel compelled to warn you that the use of IN on your partition key is known not to perform well (Is the IN relation in Cassandra bad for queries?). In fact, multi-rowkey querying has been identified as a Cassandra anti-pattern.
The more I think about this one, the more I think you'll get better performance if you do a slice query on YEAROFJOINING (instead of an IN). So if the years you want to search for will be in-order, say 2014 and higher, a better query would work like this:

aploetz@cqlsh:stackoverflow> SELECT * FROM CONFIGURATION WHERE GROUPNAME = 332 AND CLASS IN ('APL','BPL') AND YEAROFJOINING >= 2014;

That will be faster because of the clustering key on YEAROFJOINING, which will leverage your on-disk sort order.

Cassandra Select query

1 Answers1