Build Cloud-Native apps with Apache Cassandra

GOT QUESTIONS from the Workshop? You're in the right place! Post a question here and we'll get you answers.

Click here for Week 7 Materials and Homework.

Registrations still open!


question

preethikoluvaiya23_193353 avatar image
preethikoluvaiya23_193353 asked ·

How do I query a table with clustering column in the primary key?

Created table with primary key tag clustering column and order by(added_date desc) clause, but still I don't find my result as per query.Please clarify my doubt

cql
error.jpg (55.8 KiB)
1 comment
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.

Hi,

to be sure we understand your issue, would you update your post with the query that fails for you with this table schema?

Thanks!

0 Likes 0 · ·

1 Answer

bettina.swynnerton avatar image
bettina.swynnerton answered ·

Hi @preethikoluvaiya23_193353,

I went through the exercise 4 in dc201 to see what queries you might have tried with this table.

Here is your table:

KVUser@cqlsh:killrvideo> select * from videos_by_tag;

 tag       | added_date                      | video_id                             | title
-----------+---------------------------------+--------------------------------------+------------------------------
  datastax | 2013-10-16 00:00:00.000000+0000 | 4845ed97-14bd-11e5-8a40-8338255b7e33 |              DataStax Studio
  datastax | 2013-04-16 00:00:00.000000+0000 | 5645f8bd-14bd-11e5-af1a-8638355b8e3a | What is DataStax Enterprise?
 cassandra | 2014-01-29 00:00:00.000000+0000 | 1645ea59-14bd-11e5-a993-8138354b7e31 |            Cassandra History
 cassandra | 2013-03-17 00:00:00.000000+0000 | 3452f7de-14bd-11e5-855e-8738355b7e3a |              Cassandra Intro
 cassandra | 2012-04-03 00:00:00.000000+0000 | 245e8024-14bd-11e5-9743-8238356b7e32 |             Cassandra & SSDs

(5 rows)

The following query can be run, because both the partition key and the clustering key are specified in the query:

KVUser@cqlsh:killrvideo> SELECT *
              ... FROM videos_by_tag
              ... WHERE tag = 'cassandra' and added_date >= '2013-1-1'
              ... ORDER BY added_date ASC;

 tag       | added_date                      | video_id                             | title
-----------+---------------------------------+--------------------------------------+-------------------
 cassandra | 2013-03-17 00:00:00.000000+0000 | 3452f7de-14bd-11e5-855e-8738355b7e3a |   Cassandra Intro
 cassandra | 2014-01-29 00:00:00.000000+0000 | 1645ea59-14bd-11e5-a993-8138354b7e31 | Cassandra History

(2 rows)

The following query however fails, because the partition key ("tag") is omitted:

KVUser@cqlsh:killrvideo> SELECT *
              ... FROM videos_by_tag
              ... ORDER BY added_date ASC;
InvalidRequest: Error from server: code=2200 [Invalid query] message="ORDER BY is only supported when the partition key is restricted by an EQ or an IN."

I hope this helps you continue with this exercise.

Cheers!

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.