CODEWITHSUNDEEP
cassandracassandra-3.0
Giri
Giri

Reputation: 4909

How do I select all rows for a clustering column in cassandra?

I have a Partion key: A

Clustering columns: B, C

I do understand I can query like this

Select * from table where A = ?
Select * from table where A = ? and B = ?
Select * from table where A = ? and B = ? and C = ?

On certain cases, I want the B value to be any value in that column.

Is there a way I can query like the following?

Select * from table where A = ? and B = 'any value' and C = ?

Upvotes: 0

Views: 892

Answers (1)

dilsingi
dilsingi

Reputation: 2958

Option 1:

In Cassandra, you should design your data model to suit your queries. Therefore the proper way to support your fourth query (queries by A and C, but not necessarily knowing B value), is to create a new table to handle that specific query. This table will be pretty much the same, except the CLUSTERING COLUMNS will be in slightly different order:

PRIMARY KEY (A, C, B)

Now this query will work:

Select * from table where A = ? and C = ?

Option 2:

Alternatively you can create a materialized view, with a different clustering order. Now Cassandra will keep the MV in sync with your table data.

create materialized view mv_acbd as 
select A, B, C, D 
from TABLE1 
where A is not null and B is not null and C is not null  
primary key (A, C, B);

Now the query against this MV will work like a charm

Select * from mv_acbd where A = ? and C = ?

Option 3:

Not the best, but you could use the following query with your table as it is

Select * from table where A = ? and C = ? ALLOW FILTERING

Relying on ALLOW FILTERING is never a good idea, and is certainly not something that you should do in a production cluster. For this particular case, the scan is within the same partition and performance may vary depending on ratio of how many clustering columns per partition your use case has.

Upvotes: 3

Related Questions