We shall have Order!

One of the more misunderstood aspects of Cassandra, is that of how to properly sort your data. In this article will be explore a typical modeling journey from first attempt to final solution. We will discuss the limitations of the ORDER BY clause, as well as to explain how Cassandra clustering order works, and how to take advantage of that order when building a data model.

A common problem that new Cassandra users will attempt to solve, is that of modeling time series data. One of the more typical early mistakes made in time series data modeling, is to design a table that is dependent on a time as its unique key, like this:

This is commonly accompanied by a question such as:

Of course for this problem, designating posttime as a primary key is not going to have the desired effect. As per the documentation (DataStax 2015a) Cassandra will place “data on each node according to the value of the partition key and the range that the node is responsible for.” This is important to understand, as the order in which partitioned rows are returned depends on the order of the hashed token values, and not on the key values themselves.

One mistake is use the Byte Ordered Partitioner (BOP). This is discouraged given its propensity for hot spots and load balancing difficulties. Some have even gone so far as to identify it as an anti-pattern. Often a use case which may appear to be a “good fit” for the BOP can be solved with proper data modeling.

The correct way to model this, is to first find a different column to partition the data by. A proper partitioning key will allow the data to be sorted and queried in the desired order. The column to designate as the partition key depends on the requirements of the application and the particular query you are trying to solve. Be mindful of the cardinality of your potential partitioning key. If it is too low you will get “hot spots” (poor data distribution), and if it is too high you will negate the benefits of the “wide row” data model.

Another incorrect path which new users can find themselves going down, is to designate a “dummy” key as the partition key. In this solution, the “dummy” key always has the same value (lowest possible cardinality), and its use can fool the user into thinking that they have solved their problem. The two main problems with this are that of hot spots and (Strickland 2014) unbounded row growth. As the (constant) partition key value will be hashed the same on each insert, all of the data will be written to the same partition, thus creating hot spots of data. Additionally, Cassandra can only support (McFadin 2014) two billion columns per partition. With all data being written to that one partition, the changes of reaching that limit will increase over time. As these two points are significant potential issues, I cannot stress enough that using a “dummy” partitioning key is a terrible idea.

For our example, we will decide to partition our data by userid with a query table. It is very likely that this new table does not obsolete or replace the original posts table (although we may want to re-evaluate the original table's primary key definition). A common modeling strategy for Cassandra, is to create a table specific to each desired query, like this:

This particular table uses what is known as a compound primary key. The first column listed in the primary key definition is the partitioning key, and the second column is a clustering column. After the primary key, data for each partition (DataStax 2015b) is “clustered by the remaining column or columns of the primary key definition.” This means that the clustering columns will determine the on-disk sort order within each partition. Note that this table definition uses the WITH clause, which indicates that the data should be sorted by posttime in descending order.

However, this can lead to another common incorrect assumption. Consider the following query and question:

In the example table above, queries will return data that is sorted according to the clustering key(s) only when a partition key is also specified. As a partition key is not specified in this query, data will be returned (as previously mentioned) ordered by the hashed partition key values. This becomes apparent when postsbyuser is queried without specifying a partitioning key and using the token() function:

In any case, to serve the above query we need qualify it with a partitioning key (userid). This query will return the 100 most-recent posts for userid 2:

Of course, there are some who will inquire about wanting to display post times for (all) or multiple users at once. To solve for that scenario, a different partitioning key will need to be selected.

One last issue that may still be encountered with this solution, is (again) unbounded row growth. Depending on the frequency of user postings, this solution may be at risk for hitting the limit of two billion columns per partition (probably not, but we will assume so for the purposes of this example). A possible solution for this, is to add an additional partitioning column to our table. Let's say that I know that the postsbyuser table will not exceed the limit of two billion columns per partition in a single calendar year. Then, I could solve this issue by using year as an additional partitioning key:

Now I can query the top most-recent posts, by userid, for a specific year, in descending order by posttime:

Note that the ORDER BY clause does not need to be specified in the SELECT, as the data will already be sorted based on the table definition. The ORDER BY clause could be used if I wanted to alter the sort direction (ASCending vs. DESCending) of the result set.

When it comes to ordering your result sets in Cassandra, remember these points:

• The clustering column(s) determine the on-disk sort order of your data within a partitioning key.
• Do not model your table with a “dummy” partitioning key that always has the same value.
• The ORDER BY clause is not necessary if your desired sort direction already matches the CLUSTERING ORDER in the table definition.
• Be mindful of the limit of two billion columns per wide row (partition), as well as the problems that accompany unbounded row growth.
• For tables supporting queries which care about recent time-based data, you may want to specify a “descending” sort direction in your table definition.
• Under no circumstances should you create a new cluster that uses the Byte Ordered Partitioner.

I hope this article has increased your knowledge of ordering your data in Cassandra. Happy data modeling!

Aaron Ploetz

References:

DataStax (2015a). Consistent Hashing. Retrieved from: http://www.datastax.com/documentation/cassandra/2.1/cassandra/architecture/architectureDataDistributeHashing_c.html

DataStax (2015b). Compound Keys and Clustering. Retrieved from: http://www.datastax.com/documentation/cql/3.1/cql/ddl/ddl_compound_keys_c.html

McFadin (2014). Getting Started With Time Series Data Modeling. Retrieved from: http://planetcassandra.org/blog/getting-started-with-time-series-data-modeling/

Strickland R. (2014). Cassandra High Availability. PP. 144-45. Packt Publishing Ltd. Birmingham, UK.