cassandra primary key column cannot be restricted

Question

I am using Cassandra for the first time in a web app and I got a query problem. Here is my tab :

CREATE TABLE vote (
    doodle_id uuid,
    user_id uuid,
    schedule_id uuid,
    vote int,
    PRIMARY KEY ((doodle_id), user_id, schedule_id)
);

On every request, I indicate my partition key, doodle_id. For example I can make without any problems :

select * from vote where doodle_id = c4778a27-f2ca-4c96-8669-15dcbd5d34a7 and user_id = 97a7378a-e1bb-4586-ada1-177016405142;

But on the last request I made :

select * from vote where doodle_id = c4778a27-f2ca-4c96-8669-15dcbd5d34a7 and schedule_id = c37df0ad-f61d-463e-bdcc-a97586bea633;

I got the following error :

Bad Request: PRIMARY KEY column "schedule_id" cannot be restricted (preceding column "user_id" is either not restricted or by a non-EQ relation)

I'm new with Cassandra, but correct me if I'm wrong, in a composite primary key, the first part is the PARTITION KEY which is mandatory to allow Cassandra to know where to look for data. Then the others parts are CLUSTERING KEY to sort data.

But I still don't get why my first request is working and not the second one ?

If anyone could help it will be a great pleasure.

use allow filtering in your query – Apurva Singh May 04 '22 at 18:03 — Apurva Singh, May 04 '22 at 18:03

score 23 · Accepted Answer · answered Feb 17 '15 at 18:10

23

In Cassandra, you should design your data model to suit your queries. Therefore the proper way to support your second query (queries by doodle_id and schedule_id, but not necessarilly with user_id), is to create a new table to handle that specific query. This table will be pretty much the same, except the PRIMARY KEY will be slightly different:

CREATE TABLE votebydoodleandschedule (
    doodle_id uuid,
    user_id uuid,
    schedule_id uuid,
    vote int,
    PRIMARY KEY ((doodle_id), schedule_id, user_id)
);

Now this query will work:

SELECT * FROM votebydoodleandschedule 
WHERE doodle_id = c4778a27-f2ca-4c96-8669-15dcbd5d34a7 
AND schedule_id = c37df0ad-f61d-463e-bdcc-a97586bea633;

This gets you around having to specify ALLOW FILTERING. Relying on ALLOW FILTERING is never a good idea, and is certainly not something that you should do in a production cluster.

answered Feb 17 '15 at 18:10

Aaron

55,518
11
116
132

Thanks a lot for the two answers ! I understand now a little bit better how Cassandra works. So I created a new table as you mentioned to handle my query and it works fine now. – Orodan Feb 18 '15 at 07:56
and if i want to perform > and < operator in doodleid then how should i create the table? – prem30488 Jan 08 '16 at 09:44
@ParthTrivedi To perform a range query on `doodle_id` you would need to create a new table with a different partition key (figure out what makes sense for your data). Then you could query a range of `doodle_id` for a specific partition key. – Aaron Jan 08 '16 at 12:44
@Aaron could You explain why that second query does not work? Is it because of some internal data storage in Cassandra? Can I read about it somewhere. Thanks in advance. – pWoz Aug 12 '16 at 11:30
1

@pWoz Cassandra works best when it can read a contiguous stream of data from disk, and the clustering keys determine the on-disk sort order. Therefore it is possible to filter on only the first one or few, but you can't skip them because that would require reading randomly from the disk. A good resource on this is Patrick McFadin's post on building PRIMARY KEYs: http://www.planetcassandra.org/blog/the-most-important-thing-to-know-in-cassandra-data-modeling-the-primary-key/ – Aaron Aug 12 '16 at 13:51
Hi @Aaron, I hope I did not understand you wrong, depending upon queries, you are suggesting to create new tables, I am not getting how will that be helpful, because while inserting the data in the table, one would not be able to identify that in which table to keep this data, it all depends on how that data is being used, So, Please explain how will creating a new table will solve this? Are you saying write the same data in both the tables simultaneously or something else? – Saif Ali Khan Sep 24 '19 at 14:52
@SaifAliKhan "Are you saying write the same data in both the tables simultaneously?" Yes, that is exactly what I am saying. This essentially trades disk space for performance. You can use `BATCH` to ensure that writes of the same data to multiple tables are applied atomically, so they don't get out of sync. – Aaron Sep 24 '19 at 14:54
@Aaron, But what if the writing speed is very fast, for eg: In my case, every second around 500 write queries are being performed, in batch only. If I have to write that same data to a new table, it will take a double size as it is taking currently. And we are storing data in 400-500 Gbs, and are expected to grow exponentially. Don't you think this is not a good approach if disk space is a concern? Are there no alternative way? – Saif Ali Khan Sep 24 '19 at 14:58
@SaifAliKhan Then that's up to you to judge for your application. But overall, disk is cheap. It's the obvious trade-off to make, when slow operational speeds cost your business a lot more $ than the cost of another drive. – Aaron Sep 24 '19 at 15:06
@Aaron No, I am just looking for a more better answer than just writing the whole data to a new table so that query can be done easily. I don't think that is an efficient way to do when write is faster than reading. – Saif Ali Khan Sep 24 '19 at 15:33
@SaifAliKhan You're certainly welcome to search for other recommendations, but you'll be hard-pressed to find someone in the Cassandra community who does not endorse the query table approach. – Aaron Sep 24 '19 at 16:05

score 5 · Answer 2 · answered Feb 17 '15 at 17:05

5

The clustering key is also used to find the columns within a given partition. With your model, you'll be able to query by:

doodle_id
doodle_id/user_id
doodle_id/user_id/schedule_id
user_id using ALLOW FILTERING
user_id/schedule_id using ALLOW FILTERING

You can see your primary key as a file path doodle_id#123/user_id#456/schedule_id#789 where all data is stored in the deepest folder (ie schedule_id#789). When you're querying you have to indicate the subfolder/subtree from where you start searching.

Your 2nd query doesn't work because of how columns are organized within partition. Cassandra can not get a continuous slice of columns in the partition because they are interleaved.

You should invert the primary key order (doodle_id, schedule_id, user_id) to be able to run your query.

answered Feb 17 '15 at 17:05

G Quintana

4,556
1
22
23

3

Allow filtering is only for purpose development or debugging, you scan all your data in the table. I broke my production environement using allow filtering.. https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/cqlSelect.html – mik3fly-4steri5k May 22 '20 at 14:49
ALLOW FILTERING is not necessarily just for development: 'if you “know what you are doing”, you can force the execution' - https://docs.scylladb.com/getting-started/dml/#allowing-filtering . They are worried you might accidentally trigger loading of every single row from every node when you only actually want 1 or 2 specific rows returned quickly. It sounds like ALLOW-FILTERING will use as much of the primary key as you give it ("primary key prefix" - with no parts missing). But the above lines are missing partition key (doodle_id) so will fully scan every single row. – Curtis Yallop Jul 23 '20 at 00:16

cassandra primary key column cannot be restricted

2 Answers2

Linked