Primary Key, Partition Key and Clustering Key in Cassandra

A primary key identifies the location and order of stored data. It is defined at the time of table creation using PRIMARY KEY:

?

CREATE TABLE users (   userid text PRIMARY KEY,   roll_no int,   first_name text,   last_name text );

When a table has single primary key, Cassandra uses one column name as the partition key.

Once primary key is defined, it can not be changed. If you want to change it, create a new table and store the existing data to this new table.

Partition Key

Multiple column can be included as part of primary column. The first element of the primary key is called partition key. It specifies which node will hold a particular table row. The primary key must consist atleast one partition key.

When data is inserted into the cluster, a hash function is applied on the partition key and the hash value is used to determine which node or replica will get the data. A partition key will always belong to one node and that partition’s data will always be found on that node. Cassandra utilizes Murmur3 which will take an arbitrary input and create a consistent token value. That token value will be inside the range of tokens owned by single node.

e.g. rows whose partition key values range from 1 to 100 may reside in node A, partition key values range from 101 to 200 may reside in node B and so on.

?

CREATE TABLE store_by_location (    country text,    state text,    city text,    store_name text,    PRIMARY KEY (country, state) );

First column of the primary key above is the partition key, other columns are clustering keys. All stores within a country reside on the same partition. So when we query the table, we get results consisting of store in the given country. Searching stores by country is easy in this table and all the records are clustered in the order of the state column. So basically a row that belongs to same country will be stored like:

Compound Primary Key

A compound primary key is comprised of one or more columns that are referenced in the primary key. One part of the compound primary key is called partition key, whereas the other part is called the clustering key. It can have various combination of columns:

• C1: Primary key has only one partition key and no cluster key.
• (C1, C2): C1 is partition key, C2 is cluster key.
• (C1, C2, C3, ...): C1 is partition key and all other i.e. C2, C3,... are cluster key.
• (C1, (C2, C3, ...)): C1 is partition key and all other i.e. C2, C3,... are cluster key.
• ((C1, C2, ...), (C3, C4, ...)): (C1, C2, ...) are partition key and (C3, C4,...) are cluster key.

Here, C1, C2, ... are table columns. 

Clustering key

Clustering keys sorts data within a partition. In the store_by_location table, country is the partition key and state is the clustering keys. Clustering keys are sorted in ascending order by default. So when we query for all stores in a country, the result will be ordered first by state in ascending order as shown in above diagram.

Ordering Results

You can do on-disk sorting of columns to order the result in ascending or descending order. The ascending order is the default order. If you need results in descending order, you can specify a clustering order to store columns on disk in the reverse order.

?

CREATE TABLE store_by_location (    country text,    state text,    city text,    store_name text,    description text,    PRIMARY KEY (country, state, city, store_name) ) WITH CLUSTERING ORDER BY (state DESC, city ASC, store_name ASC);

The result set will now contain stores ordered first by state in descending order, followed by city in ascending order, and finally store_name in ascending order.

You must specify the sort order for each of the clustering keys in the ORDER BY statement. The partition key is not part of the ORDER BY statement because its values are hashed and therefore won’t be close to each other in the cluster.