Compound Primary Keys in Practice
A primary key that has clustering columns is called a compound primary key. The orders table has one:
CREATE TABLE orders (
customer text,
order_date date,
amount decimal,
PRIMARY KEY (customer, order_date)
);
Here is how you query it.
Read a whole partition
SELECT * FROM orders
WHERE customer = 'bob';
Output:
customer | order_date | amount
----------+------------+--------
bob | 2024-01-20 | 300.00
bob | 2024-02-25 | 45.99
(2 rows)
You get all orders of bob, and only his. Look at order_date: the rows are sorted from the oldest date to the newest. You did not ask for any sorting. The clustering column did this, because the rows are stored in this order.
Narrow it down with the clustering column
When the partition key is fixed with =, you can also use the clustering column in WHERE. An exact match works:
SELECT * FROM orders
WHERE customer = 'alice'
AND order_date = '2024-02-10';
Output:
customer | order_date | amount
----------+------------+--------
alice | 2024-02-10 | 20.25
(1 rows)
A range works too, with >, <, >= or <=:
SELECT * FROM orders
WHERE customer = 'alice'
AND order_date >= '2024-02-01';
Output:
customer | order_date | amount
----------+------------+--------
alice | 2024-02-10 | 20.25
alice | 2024-03-02 | 75.00
alice | 2024-04-18 | 250.00
(3 rows)
Both queries are fast. Cassandra goes to alice's partition and takes a slice of rows that are already sorted.
Read the key out loud
This table was made for one question: "show me the orders of a customer". The partition key is what you look up by (customer). The clustering column is the order you want the rows in (order_date). When you see a new Cassandra table, read its key like this:
PRIMARY KEY (customer, order_date)means "look up by customer, with the rows sorted by date".
In the exercise, read all orders of one customer.