What is the syntax of a WHERE IN clause for a compound/composite primary key in Cassandra

Question

What is the syntax of a WHERE IN clause for a compound/composite primary key in Cassandra?

I have a Cassandra table with a compound/composite primary key like this:

PRIMARY KEY ((country, language), collectionid, instanceid)

What would a WHERE IN clause look like for this table if I want to return many rows with the same country and language and many different collectionids and instanceids?

Would it look something like this? Or would this not work? Is there a better way?

WHERE ((country, language, collectionid, instanceid)) IN (("us", "en", "aaa", "111"),("us", "en", "bbb", "222"),("us", "en", "bbb", "333"))

Or would this be more performant?

WHERE country = "us" AND language="en" AND ((collectionid, instanceid)) IN (("aaa", "111"),("bbb", "222"),("bbb", "333"))

Answer 1

So the first WHERE clause won't work. If you've tried it, you've probably seen this error:

[Invalid query] message="Multi-column relations can only be applied to clustering columns but was applied to: country"

The second WHERE clause doesn't work without some adjustments. Basically, use single quotes instead of double, and don't double-up the parens on the IN clause:

> SELECT * FROm collection_data
  WHERE country = 'us' AND language='en'
  AND (collectionid, instanceid) IN (('aaa', 111),('bbb', 222),('bbb', 333)) ;

 country | language | collectionid | instanceid  
---------+----------+--------------+------------
      us |       en |          aaa |        111
      us |       en |          bbb |        222
      us |       en |          bbb |        333

(3 rows)

This query works, because here we've given Cassandra a specific partition ('en','us') to look at.