Cassandra Keys Primary, Partition, and Cluster IN(), and Range Queries

This post explains the new CQL3 terminology and model changes since the legacy thrift architecture. Examples/exercises given here assume your queries are not using ALLOW FILTERING for the fastest queries. ALLOW FILTERING, however, is used also when developing the highest performing systems for carefully selected and bench marked usage patterns.

Migrating from Thrift to CQL3 Terms

Thrift was designed create and manage one row per partition. A partition key was formerly known as the “row key”. CQL3 was designed to create and manage multiple rows in a partition. Now one or more columns (known as partition key) is used to identify the rows in a partition. See:

• http://www.datastax.com/dev/blog/does-cql-support-dynamic-columns-wide-rows 
• http://www.datastax.com/dev/blog/cql3-for-cassandra-experts 
• http://planetcassandra.org/blog/datastax-developer-blog-a-thrift-to-cql3-upgrade-guide/ 

Partition--a group of rows and columns that should be stored and replicated together. See:

• http://www.datastax.com/documentation/cassandra/2.0/cassandra/dml/dml_reading_cluster_row_c.html

RF--the replication factor is the number of times a partition should be replicated in a cluster, data center, or other replication group. Values can be 1, 2, 3, etc. See:
http://www.datastax.com/documentation/cassandra/2.0/cassandra/architecture/architectureDataDistributeReplication_c.html

Replica--a copy of a partition stored on a node.

Partitioner--the algorithm used to determine how data is distributed and replicated across the nodes in a cluster. See:

• http://www.datastax.com/documentation/cassandra/2.0/cassandra/architecture/architecturePartitionerAbout_c.html
• http://www.datastax.com/docs/1.0/cluster_architecture/partitioning

Partition key--the column (formerly row key) or multiple columns (formerly composite row key) defining a grid of rows and columns which should be stored and replicated together. The partition key is hashed by the partitioner to determine which nodes should have replicas of the partition. See:

• http://www.datastax.com/documentation/cql/3.1/cql/ddl/ddl_compound_keys_c.html
• http://www.datastax.com/documentation/cql/3.1/share/glossary/gloss_partition_key.html
• http://www.datastax.com/documentation/cql/3.1/cql/cql_reference/refCompositePk.html

Clustering key--one or more columns, also known as the clustering columns, used to uniquely identify a row within a partition and order it accordingly. See:
http://www.datastax.com/documentation/cql/3.1/cql/ddl/ddl_compound_keys_c.html

Clustering order--one or more clustering columns used to order and group the rows and columns in a partition. See:
http://www.datastax.com/documentation/cql/3.1/cql/cql_reference/refClstrOrdr.html

Static Column--provide a means of storing columns whose values are stored once per partition. Implemented the same as and formerly known as Dynamic Columns. These columns can be referenced in queries and are considered additional columns on every row in the partition. A CQL SELECT * will include them. See:
http://www.datastax.com/dev/blog/cql-in-2-0-6

Overview

Cassandra scalable design allows searches for very specifically keyed data.

• The goal is to access the two or three replicas for one partition which are hashed from the selected key.

Queries that require using many partitions which would involve many nodes are discouraged.

• Secondary indexes result in queries against all nodes in the cluster and should be limited to a system wide batch process are practical but still discouraged.
• Ordering of partition keys via ByteOrderedPartitioner is strongly discouraged.
• IN clauses on the last key in the partition key are allowed should be kept to a minimum as more partitions involve more clusters.

Queries that scan a partition table are discouraged.

• Range queries are discouraged except where necessary and benchmarked.
• Large IN() clauses are discouraged except where necessary and benchmarked.

Range queries are only permitted on clustering keys within one or a few partitions.

• A highly discouraged exception is made for the ByteOrderedPartitioner.

We will use the following table to illustrate.

CREATE KEYSPACE testks WITH replication = {'class': 'SimpleStrategy', 'replication_factor':3};
CREATE TABLE testks.test (pk1 int,
pk2 int,
ck1 int,
ck2 int,
data int,
PRIMARY KEY ((pk1, pk2),ck1,ck2));

INSERT INTO testks.test (pk1, pk2, ck1, ck2, data) VALUES ( 1,1,1,1,4);
INSERT INTO testks.test (pk1, pk2, ck1, ck2, data) VALUES ( 1,1,1,2,5);
INSERT INTO testks.test (pk1, pk2, ck1, ck2, data) VALUES ( 1,2,1,1,5);
INSERT INTO testks.test (pk1, pk2, ck1, ck2, data) VALUES ( 1,2,1,2,6);
INSERT INTO testks.test (pk1, pk2, ck1, ck2, data) VALUES ( 1,2,1,3,7);
INSERT INTO testks.test (pk1, pk2, ck1, ck2, data) VALUES ( 1,2,1,4,8);

SELECT * FROM testks.test ;

 pk1 | pk2 | ck1 | ck2 | data
-----+-----+-----+-----+------
   1 |   3 |   1 |   1 |    6
   1 |   2 |   1 |   1 |    5
   1 |   2 |   1 |   2 |    6
   1 |   2 |   1 |   3 |    7
   1 |   2 |   1 |   4 |    8
   1 |   1 |   1 |   1 |    4
   1 |   1 |   1 |   2 |    5

The above Primary Key is composed of:

1. Partition Keys (pk1 and pk2) used for hashing to a partition and defining rows and columns that are in a partition. Only the rightmost partition column can be used in an IN() clause. Partitions are replicated on nodes defined by the token range assignment.
2. Clustering Keys (ck1 and ck2) used with each partition for uniquely selecting and ordering rows. Only the rightmost clustering column can be used in a range query or IN() clause.

Specific partition selection is strongly encouraged.

1. Only the nodes containing the matching partition will be impacted.
2. The node(s) location and partition location within the node(s) are a direct hash.

SELECT * FROM testks.test WHERE pk1 = 1 and pk2 = 1 ;

 pk1 | pk2 | ck1 | ck2 | data
-----+-----+-----+-----+------
   1 |   1 |   1 |   1 |    4
   1 |   1 |   1 |   2 |    5

Specific clustering keys are also encouraged.

1. The node(s) location and partition location within the node(s) are a direct hash of the Partition Key columns.
2. The Clustering Keys within the partition that are searched with equality will optimally locate the matching rows.

SELECT * FROM testks.test WHERE pk1 = 1 and pk2 = 1 and ck1 = 1 and ck2 = 1;

 pk1 | pk2 | ck1 | ck2 | data
-----+-----+-----+-----+------
   1 |   1 |   1 |   1 |    4

Specific partition and a list or range of the last clustering key allowed. Only the last clustering key may be searched with an IN list or range query. All other clustering keys must be searched with equality.

1. Only the nodes containing the matching partition will be impacted.
2. The node(s) location and partition location within the node(s) are a direct hash.
3. The clustering keys within the partition that are searched with equality will optimally locate the matching rows.
4. The last clustering key can be searched using a scan if queried using an IN list or a range query.

SELECT * FROM testks.test WHERE pk1 = 1 and pk2 = 2 and ck1 = 1 and ck2 IN ( 1, 2, 3 ) ;

 pk1 | pk2 | ck1 | ck2 | data
-----+-----+-----+-----+------
   1 |   2 |   1 |   1 |    5
   1 |   2 |   1 |   2 |    6
   1 |   2 |   1 |   3 |    7

SELECT * FROM testks.test WHERE pk1 = 1 and pk2 = 2 and ck1 = 1 and ck2 >= 2 and ck2 < 100 ;

 pk1 | pk2 | ck1 | ck2 | data
-----+-----+-----+-----+------
   1 |   2 |   1 |   2 |    6
   1 |   2 |   1 |   3 |    7
   1 |   2 |   1 |   4 |    8


The last partition key is allowed to be an IN list and the last clustering key is allowed to be an IN list or a range query.

SELECT * FROM testks.test WHERE pk1 = 1 and pk2 IN ( 1, 2 ) and ck1 = 1 and ck2 >= 2 ;

 pk1 | pk2 | ck1 | ck2 | data
-----+-----+-----+-----+------
   1 |   1 |   1 |   2 |    5
   1 |   2 |   1 |   2 |    6
   1 |   2 |   1 |   3 |    7
   1 |   2 |   1 |   4 |    8

SELECT * FROM testks.test WHERE pk1 = 1 and pk2 IN ( 1, 2 ) ;

 pk1 | pk2 | ck1 | ck2 | data
-----+-----+-----+-----+------
   1 |   1 |   1 |   1 |    4
   1 |   1 |   1 |   2 |    5
   1 |   2 |   1 |   1 |    5
   1 |   2 |   1 |   2 |    6
   1 |   2 |   1 |   3 |    7
   1 |   2 |   1 |   4 |    8

SELECT * FROM testks.test WHERE pk1 = 1 and pk2 IN ( 1, 2 ) and ck1 = 1 and ck2 IN ( 1, 2 ) ;

 pk1 | pk2 | ck1 | ck2 | data
-----+-----+-----+-----+------
   1 |   1 |   1 |   1 |    4
   1 |   1 |   1 |   2 |    5
   1 |   2 |   1 |   1 |    5
   1 |   2 |   1 |   2 |    6