CODEWITHSUNDEEP
cassandracql
Mandroid
Mandroid

Reputation: 7468

Read queries in cql when all clustering columns are not available

In cql, when we write read queries we need to provide columns in same order as they are mentioned in primary key definition, and all of them must be present. But how to write query if we don't have values available for some of the clustering columns.

For example, if primary key is defined as primary key((state), city, name) for a table containing records for users with their state, city and name as part of primary key, then how to write query to find all users with a given name from a state irrespective of their city? Something like

Select * from USERS where state ='....', name='....';

won't work.

Upvotes: 1

Views: 71

Answers (1)

Aaron
Aaron

Reputation: 57748

You have two options here.

  1. Create a query table alongside the original table keyed on PRIMARY KEY ((state),name).

  2. Invoke the ALLOW FILTERING directive on the above query, which filters on only state and name.

While the use of ALLOW FILTERING is normally discouraged (and with good reason), it's not nearly as bad in this case. By specifying state, you've given the query the ability to locate the node which contains the data. Yes, you'll still be "filtering" through data in a non-performant way, but at least it won't be across multiple nodes.

Upvotes: 3

Related Questions