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

sbhupathiraju86_192716 avatar image
sbhupathiraju86_192716 asked ·

How can I filter a range of partition keys using the > and < operators?

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.

cql
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.

Erick Ramirez avatar image
Erick Ramirez answered ·

OLTP vs Analytics

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.

Demonstration

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!

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.

smadhavan avatar image
smadhavan answered ·

@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) &gt; 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.

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.