It was mentioned that we cannot use > or < operators on partition key columns in cassandra. Let's assume I have a table which is partitioned by date (ex: 2020-01-01) and one partition is created for each day and table has 365 days of data. Now, if I have to look at all the data that has is greater than 2020-01-01, how should I go about it?
In Teradata and Hadoop, both of which are distributed systems, using > or < on partition columns are allowed.
Kindly advise.
@sbhupathiraju86_192716, Cassandra distributes the partition across the nodes using the selected partitioner. As only the ByteOrderedPartitioner keeps an ordered distribution of data Cassandra does not support >, >=, <= and < operator directly on the partition key. Instead, it allows you to use the >, >=, <= and < operator on the partition key through the use of the token function. You could do something like below,
SELECT * FROM myTable WHERE token(partitionKey) > token('2020-01-01');
p/s: Using date as partition key is usually not a recommended practice in case if you've many clustering rows as part of a single day causing it to go over 10MB in size.
Range queries on the partition key isn't ideal since it is an analytics query and not OLTP. It will involve having to query lots of nodes in the cluster and wouldn't perform well in an OLTP data centre.
The key to your question is that you're comparing Cassandra which is primarily an OLTP database with analytics systems -- Teradata and Hadoop. If you want to do an apples-for-apples comparison, then you should compare it to Spark + Cassandra using the Spark-cassandra-connector.
A range query on the partition key is a scatter-gather query that is best performed on an isolated Cassandra data centre that only services Analytics workloads so as not to affect the performance of the OTLP application requests.
We only recommend doing range queries on clustering columns since the rows are contiguous. Partition keys are not -- they are randomly distributed around the cluster. Let me illustrate with an example.
Consider this table of events partitioned by date:
CREATE TABLE events_by_date (
event_date date PRIMARY KEY,
event text
)
The table contains 5 events:
cqlsh> SELECT * FROM events_by_date ; event_date | event ------------+-------------------------- 2020-05-12 | International Nurses Day 2020-03-28 | Earth Hour 2020-04-01 | April Fools Day 2020-01-01 | New Year 2020-01-26 | Australia Day
But if we look under the hood, the TOKEN() function shows us that the dates hash into unordered values:
cqlsh> SELECT event_date, TOKEN(event_date), event FROM events_by_date ; event_date | system.token(event_date) | event ------------+--------------------------+-------------------------- 2020-05-12 | -5313626113761958622 | International Nurses Day 2020-03-28 | -5067193322432818646 | Earth Hour 2020-04-01 | 1238660437741292384 | April Fools Day 2020-01-01 | 1343373633300853149 | New Year 2020-01-26 | 6146099649072859714 | Australia Day
I say it's unordered because the token value for May 12 (-5313626113761958622) is smaller than the token for January 1 (1343373633300853149).
If we attempt a range query on the token values, we don't get the expected natural sorting of events:
cqlsh> SELECT event_date, TOKEN(event_date), event FROM events_by_date WHERE TOKEN(event_date) > TOKEN('2020-04-01');
event_date | system.token(event_date) | event
------------+--------------------------+---------------
2020-01-01 | 1343373633300853149 | New Year
2020-01-26 | 6146099649072859714 | Australia Day
The result for a range query for event dates greater than April 1 didn't return the expected events. As Madhavan indicated in his answer, this is because the default Murmur3Partitioner distributes data randomly in a cluster and not in lexical byte-order as the ByteOrderedPartitioner.
As I stated at the start of my answer, this analytics query is best-suited with Apache Spark and not in plain CQL. Cheers!
7 People are following this question.
