Order by not work in cassandra

Question

I'm a newbie in cassandra and I have an issue with "sort".

My table is very simple, It like that:

CREATE TABLE test.user_daily_report (
    stringdate bigint,
    m_date date,
    users int,
    PRIMARY KEY (stringdate, m_date)
) WITH CLUSTERING ORDER BY (m_date DESC)
   AND read_repair_chance = 0.0
   AND dclocal_read_repair_chance = 0.1
   AND gc_grace_seconds = 864000
   AND bloom_filter_fp_chance = 0.01
   AND caching = { 'keys' : 'ALL', 'rows_per_partition' : 'NONE' }
   AND comment = ''
   AND compaction = { 'class' : 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold' : 32, 'min_threshold' : 4 }
   AND compression = { 'chunk_length_in_kb' : 64, 'class' : 'org.apache.cassandra.io.compress.LZ4Compressor' }
   AND default_time_to_live = 0
   AND speculative_retry = '99PERCENTILE'
   AND min_index_interval = 128
   AND max_index_interval = 2048
   AND crc_check_chance = 1.0;

But the result not have any effect of sort:

When I try to order by m_date i meet this issue:

Please help me, It makes me confuse a lot.

Thanks

score 1 · Answer 1 · answered Dec 08 '17 at 09:29

1

In contrast to traditional relational database, in Cassandra ORDER BY works only inside partition - when you specify the WHERE condition on partition key (stringdate in your example), i.e., select * from table where stringdate = 'something' order by m_date desc - in this case data related to particular stringdate will be sorted because they are in the same partition.

In your case, you're receiving all results from Cassandra because you added 'ALLOW FILTERING' - in this case, Cassandra performs scanning of the whole cluster using your condition, fetches data, and returns it to you - but it didn't perform any sorting.

I recommend to watch "DS220: Data Modeling" course from DataStax Academy - they provide a lot of useful information...

P.S. Can you describe what task do you want to solve? I believe that you need to change data model.

answered Dec 08 '17 at 09:29

Alex Ott

80,552
8
87
132

Your solution's not work. Because, stringdate is not unique primarikey, it;s a composite key, so It need allow filtering. I tested and get error for it. About my task, I need querry data to plot a chart. From UI, I have post a request to back end with start_date and end_date (e.g,2017-12-06). And I need to get number of user (users) in user_daily_report table follow m_date. I need the response result need to sort ascending/descending by m_date. I already select all record in range [start_date,end_date]. But i can't order them. Please help me – Jame H Dec 08 '17 at 09:50
Sorry, I slightly misunderstood your question, but anyway - my answer is correct - Cassandra doesn't perform sorting in your situation, it can perform sorting only if data is inside the same partition (really, the data is written in given order already) – Alex Ott Dec 08 '17 at 09:53
Dear alex, i tried your query on my db. And it didn't work. It need allow fitering. About my task, i have 2 main fields , date & users. I need to get list user between two different date. And i want my query resul was sorted by date. That's all that i need. Pls help me – Jame H Dec 08 '17 at 10:13
As I mentioned, you will need to change the data model to make it working - for example, organize data into partitions based on some criteria (site name, etc., and then data will be sorted there. `ALLOW FILTERING` is the brute force approach, and doesn't recommended as it scans the whole cluster. Cassandra works best when the tables are built around queries that need to be executed, not that queries are built around tables. – Alex Ott Dec 08 '17 at 10:17
are you using plain cassandra, or DSE? – Alex Ott Dec 08 '17 at 10:17
Dse, it mean datasax. I work on it. About your data model on cassandra, could you tell me more. I just have a table for log and some table for report like user_daily_report. But how to design is better. – Jame H Dec 08 '17 at 10:33
If you're using DataStax, then you can enable Search for particular table to perform "ad-hoc" queries, like yours - in this case the sorting could be made by Solr, not by Cassandra, and Cassandra will only return data. But this is another question. I'm still recommending to take DS220 course on DataStax Academy - it will resolve many questions about Cassandra's functionality – Alex Ott Dec 08 '17 at 10:55
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/160787/discussion-between-jame-h-and-alex-ott). – Jame H Dec 08 '17 at 11:26

Order by not work in cassandra

1 Answers1