How to sort by counter in Cassandra?

1
votes

Let's assume I have a forum software, and I would like to sort the threads by the amount of views it has. The views would be stored in a counter.

Having experience in relational databases, I thought this would be simple to solve, turns out it's not. I have thought about creating one massive row with the columns being counters (thus being sorted), but as a single row can only be stored on a single node, this does not seam feasible (beats the point of using Cassandra).

How can I sort by counter column in Cassandra?

2
sortingcassandracounter
This question is already answered here stackoverflow.com/q/8428364/964592. - Fredrik LS
I answered a similar question here: stackoverflow.com/a/19147031/276949 The method does is an unorthodox way of using Cassandra - it was never designed for queries like this. - Martin Konecny

2 Answers

3
votes

You can't sort big data. That's one of the fundamental assumptions.

The only things that you can sort by on cassandra, are the things that cassandra uses to store its data - the row key and the column key.

Moving to NoSQL from normal SQL you have to drop the notion of being able to sort/join data. It's just (generally) not possible in Big Data implementations.

1
votes

To update on this question: Korya is correct that you cannot assume that ALL NoSQL of BigData nature cannot sort (MongoDB can sort and it is NoSql).

Regarding to Cassandra itself: you can sort any given elements of your Primary Key AFTER your partition key inside a Composite Key:

Example: Primary Key ((A),B,C,D);

A is your partition Key. B,C,D are part of your composite Key, and can now be sorted ASC (default) or DESC. If you want something naturally in latest first (ie time) then you would specify it in your schema: WITH CLUSTERING ORDER BY (media_type_id ASC,media_id ASC);

As far as the question goes about counters: You cannot sort the counter inside cassandra because the counter would need to be part of the KEY and the key is unique.

As pointed by Martin the solution refenced by a whitepage example of eBay they explain that two tables are used to keep track.