20

I am just getting start on Cassandra and I was trying to create tables with different partition and clustering keys to see how they can be queried differently.

I created a table with primary key of the form - (a),b,c where a is the partition key and b,c are clustering key.

When querying I noticed that the following query:

select * from tablename where b=val;

results in:

Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING

And using "ALLOW FILTERING" gets me what I want (even though I've heard its bad for performance).

But when I run the following query:

select * from tablename where c=val;

It says:

PRIMARY KEY column "c" cannot be restricted (preceding column "b" is either not restricted or by a non-EQ relation)

And there is no "ALLOW FILTERING" option at all.

MY QUESTION IS - Why are all clustering keys not treated the same? column b which is adjacent to the partition key 'a' is given an option of 'allow filtering' which allows querying on it while querying on column 'c' does not seem possible at all (given the way this table is laid out).

ALLOW FILTERING gets cassandra to scan through all SSTables and get the data out of it when the partition key is missing, then why cant we do the same column c?

Aaron
  • 55,518
  • 11
  • 116
  • 132
user3376961
  • 867
  • 2
  • 12
  • 17

1 Answers1

27

It's not that clustering keys are not treated the same, it's that you can't skip them. This is because Cassandra uses the clustering keys to determine on-disk sort order within a partition.

To add to your example, assume PRIMARY KEY ((a),b,c,d). You could run your query (with ALLOW FILTERING) by specifying just b, or b and c. But it wouldn't allow you to specify c and d (skipping b) or b and d (skipping c).

And as a side node, if you really want to be able to query by only b or only c, then you should support those queries with additional tables designed as such. ALLOW FILTERING is a band-aid, and is not something you should ever do in a production Cassandra deployment.

Aaron
  • 55,518
  • 11
  • 116
  • 132
  • 1
    Thanks that definitely helps understanding how cassandra works! – user3376961 May 27 '15 at 19:49
  • are there any chances that we are not thinking what will happen in production? I mean we know that all our development work will be soon carried out to production. Production is always in consideration. – Karl Anthony Baluyot Aug 19 '19 at 09:03
  • @KarlAnthonyBaluyot My team runs one of the largest open source Apache Cassandra implementations in the world. Not sure what you meant here, but I'm *always* thinking about production. – Aaron Aug 19 '19 at 13:06
  • I mean why would they offer a band-aid solution like ALLOW FILTERING if in production should not ever do as you said. – Karl Anthony Baluyot Aug 19 '19 at 14:57
  • Would cassandra allow fetching based on a and b without using allow clustering? – Jaspreet Jolly May 13 '20 at 07:46
  • 1
    @JaspreetJolly Yes. That's because specifying `a` & `b` provides a complete partition key and does not skip primary key components. – Aaron May 13 '20 at 15:42