Bringing together the Apache Cassandra experts from the community and DataStax.

Want to learn? Have a question? Want to share your expertise? You are in the right place!

Not sure where to begin? Getting Started

 

question

msnaidu avatar image
msnaidu asked ·

Clustering key columns must exactly match columns in CLUSTERING ORDER BY directive

I am trying to create cluster column using the create query

CREATE TABLE videos_by_tag (
       tag text,
       video_id uuid,
       added_date timestamp,
       title text,
       PRIMARY KEY ((tag), added_date, video_id)
) WITH CLUSTERING ORDER BY(added_date DESC);

It's showing the following error. Am i missing something in the create table ?

InvalidRequest: Error from server: code=2200 [Invalid query] message="Clustering key columns must exactly match columns in CLUSTERING ORDER BY directive"

I am using the following version of cassandra on mac os

[cqlsh 5.0.1 | Cassandra 4.0.0.680 | CQL spec 3.4.5


cassandra
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered ·

@sanyasinaidu.malla_37931 The issue is that you need to specify all the clustering columns in the CLUSTERING ORDER BY directive. In your case, you only specified the added_date column:

WITH CLUSTERING ORDER BY(added_date DESC);

Given that you have 2 clustering columns in your table:

    PRIMARY KEY ((tag), added_date, video_id)

you need to explicitly include those 2 clustering columns in the directive like this:

WITH CLUSTERING ORDER BY(added_date DESC, video_id ASC);

even though it is already implied that video_id will be in ascending order.

This was introduced in C* 4.0-alpha1 (CASSANDRA-13426) but has been reverted back in C* 4.0-alpha4 (CASSANDRA-15271) which will be included in a future release of DSE 6.8. Cheers!

2 comments Share
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Are there any detail on the version on DSE that fixes this issue?

0 Likes 0 ·

What is the recommendation how to fix already migrated schemas when we moving from dse 6.7 (where not all clustering columns were explicitly included in CLUSTERING ORDER BY directive) to dse 6.8.9 (where all clustering columns have to be explicitly mentioned in CLUSTERING ORDER BY directive)? Should be those schemas in DB corrected after upgrade to dse 6.8?

0 Likes 0 ·