CQL: the key that decides everything

Cassandra in one slide, and a keyspace to work in

The session opens with a definition (p. 1548): "Apache Cassandra is a highly scalable, high-performance non-relational, distributed database designed to handle large amounts of data, providing high availability with no single point of failure." The feature list (p. 1549) adds "Highly and Linearly Scalable", "Quick Response Time", "Flexible Data Storage", "Easy Data Distribution", "BASE Properties" and "Fast Writes".

Behind each claim is a mechanism. No master: every node can serve any request and every row is copied to several nodes, so one dead machine stops nothing. A new node takes over its share of the rows, so capacity grows with the nodes. A write is appended to memory and a log without reading first, so writes are fast. How the nodes share the rows is lesson 22; this lesson is the language you talk to them in. The session sums up the theory in two tables worth knowing word for word:

The vocabulary nests like a filing cabinet. A keyspace is "the outermost container in Cassandra" (p. 1553), roughly a database. It holds tables, the lecture's column families: the keyword TABLE "can be interchanged with COLUMNFAMILY" (p. 1563). Tables hold rows, rows hold columns; lesson 20 draws the picture, and from here on everything is CQL typed at the cqlsh prompt.

-- p. 1553: a keyspace that keeps three copies of every row
CREATE KEYSPACE population
  WITH replication = {'class': 'SimpleStrategy',
                      'replication_factor': 3};

Read it aloud: create a container called population; replicate every row with the simple strategy, three copies. The replication map, strategy plus factor, is the one property a keyspace must declare. On a one-node laptop the statement still runs, with a warning: "Your replication factor 3 for keyspace population is higher than the number of nodes 1".

-- p. 1555: the prompt becomes cqlsh:population>
USE population;

-- p. 1555: list the keyspaces (DESCRIBE tables; and
-- DESCRIBE person; do the same for tables, pp. 1558-1559)
DESCRIBE keyspaces;

-- p. 1556: change the properties of a keyspace
ALTER KEYSPACE population
  WITH replication = {'class': 'SimpleStrategy',
                      'replication_factor': 2};

-- p. 1556: remove the keyspace and every table in it
DROP KEYSPACE population;

PRIMARY KEY: where a row lives, and in which order

The session's first table (p. 1558) describes people:

-- p. 1558: one partition per person, its rows sorted by age
CREATE TABLE person (
  personal_id text,
  name text,
  age varint,
  birth_date text,
  gender text,
  PRIMARY KEY (personal_id, age)
);

Column by column it is SQL; the last line is not. The columns inside PRIMARY KEY "have different meaning depending on the order and the brackets" (p. 1560), and the slide gives both meanings:

The mechanism: Cassandra hashes the partition-key value, and the number that comes out, the token, names the node that stores the row. Rows with the same partition-key value get the same token, so they sit together in one partition on one node, kept sorted by the clustering key. Think of an archive: the hash of the surname picks the building, and inside it the folders stand in date order.

A composite partition key is one value: (Fiat, 2024) and (Fiat, 2023) are two partitions, possibly on two nodes. And the full primary key, partition plus clustering columns, identifies one row: two rows with the same full key are the same row — the trap of section 4.

Inside a partition the order is ascending unless the table says otherwise (p. 1554):

-- p. 1554's option (printed ASC), here DESC:
-- in each partition, the oldest first
CREATE TABLE person_by_age_desc (
  personal_id text,
  age varint,
  name text,
  PRIMARY KEY (personal_id, age)
) WITH CLUSTERING ORDER BY (age DESC);

The papers state the key in words:

Inside the inner brackets: where the row lives. After them: the order the rows keep there. All of it together: which row it is.

Changing a table, and what cannot change

The other table statements work as in SQL (pp. 1561–1563), and each accepts COLUMNFAMILY in place of TABLE. The session adds the address that its INSERT fills in section 4:

-- p. 1562: a new column; the existing rows have no value in it
ALTER TABLE person ADD address text;

-- p. 1562: salary, added the same way, is dropped again
ALTER TABLE person DROP salary;

-- p. 1563: remove every row, keep the table
TRUNCATE TABLE person;

-- p. 1563: remove the table itself
DROP TABLE person;

The key is the one thing ALTER cannot touch: ALTER TABLE person DROP age; answers "Cannot drop PRIMARY KEY column age", so a wrong key means a new table and a copy of the data. And this is what "Schema is flexible" (p. 1551) means in CQL: every table declares its columns, a row may leave any non-key column empty at no cost, and ALTER TABLE adds a column at any time.

Reading: what a WHERE clause may say

The query Cassandra is built for names the partition (p. 1567):

-- one hash, one node, one partition: Francesco's rows
SELECT * FROM person WHERE personal_id = 'FRNTRZ95E12F675T';

Cassandra hashes the value, goes to the node that owns the token and reads one partition. A WHERE clause that does not name the partition is a question one hash cannot answer, and the session tries one at once (p. 1568), "Let's retrieve people by gender":

-- refused: gender is neither a key column nor indexed
SELECT * FROM person WHERE gender = 'Male';

cqlsh answers: "Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING". An exact description: to find the men, Cassandra would read every row on every node and throw most of them away.

The next slide (p. 1569) lists the rules. In order, each with its reason:

1. Name every column of the partition key, with = or IN: "WHERE clauses must include ALL components of the partition key". Half a composite key gives no token.
2. Restrict clustering columns as a prefix, in their order; only the last may be a range. "To filter on a later clustering key, all preceding clustering key(s) must be specified". The rows are sorted by the clustering columns, so such a prefix is one contiguous slice; anything else means hopping. Skip one and cqlsh says, for example, "PRIMARY KEY column "max_speed" cannot be restricted as preceding column "year" is not restricted".
3. No OR. "Clustering Keys constraints producing non-contiguous results will fail": a range with a hole in it cannot be asked for. Conditions are joined by AND only; the red pen below explains why the slide's own example fails.
4. Any other column needs a secondary index, and then only =. "To include additional fields in the query, it is necessary to create a secondary index." A range on an indexed column, gender > 'A', still needs ALLOW FILTERING.
5. ALLOW FILTERING switches the rules off, "although it is not recommended due to bad execution-time performance": Cassandra reads and discards, so the cost grows with the table, not with the answer.

A SELECT with no WHERE, SELECT * FROM car;, needs no ALLOW FILTERING: it reads everything but discards nothing. The index of rule 4 is created as on p. 1564, and the car exercise uses it so (p. 1611):

-- p. 1611: an index on a column that is not part of the key
CREATE INDEX car_price ON car (price);

-- p. 1611: now legal, without partition key or ALLOW FILTERING
SELECT * FROM car WHERE price = 35000;

-- p. 1610: a range on that column: only ALLOW FILTERING helps
SELECT * FROM car WHERE price > 100000 ALLOW FILTERING;

-- p. 1565: remove an index you no longer need
DROP INDEX person_address;

Writing: every write is an upsert

The session inserts a person (p. 1566):

-- p. 1566: Francesco, aged 26
INSERT INTO person (personal_id, address, age,
                    birth_date, gender, name)
VALUES ('FRNTRZ95E12F675T', 'Via Milano 12', 26,
        '12-05-1995', 'Male', 'Francesco Terzani');

Columns and values match in order; the primary-key columns must be there, the others may be left out. The template's USING <option> is not explained on the slide: it sets a time to live, USING TTL 86400 expires the values after a day, or a write timestamp (Cassandra documentation).

Now the question no slide asks: what if a row with that primary key already exists? In SQL, a duplicate-key error. In Cassandra, nothing: the INSERT overwrites the columns it lists and keeps the others. An INSERT is an upsert — update or insert, decided by the primary key alone. Cassandra never reads before writing, part of why writes are fast; to refuse duplicates you ask for it, INSERT … IF NOT EXISTS, a lightweight transaction (not in the slides).

UPDATE follows the same logic (p. 1570): "The WHERE clause must include all components of the primary key (partition key and clustering keys). You cannot update or change primary key values." Leave out age and cqlsh answers "Some clustering keys are missing: age"; try SET age = 27 and it answers "PRIMARY KEY part age found in SET part". When no row has the key, the UPDATE creates one.

-- p. 1570 as printed: "Let's update Francesco's address"
UPDATE person
SET address = 'Via Milani 13'
WHERE personal_id = 'FRNTRZ95E12F675T' AND age = 32;

DELETE needs less (p. 1571): "You must specify the full partition key in the WHERE clause of a DELETE. Instead, Clustering Key(s) are not strictly required." The width of the WHERE decides how much goes:

-- p. 1571: the whole partition, every row of Francesco
DELETE FROM person WHERE personal_id = 'FRNTRZ95E12F675T';

-- one row of the partition
DELETE FROM person
WHERE personal_id = 'FRNTRZ95E12F675T' AND age = 26;

-- one column of one row: the address becomes null
DELETE address FROM person
WHERE personal_id = 'FRNTRZ95E12F675T' AND age = 26;

A delete is itself a write: a marker, a tombstone, hides the old values until a later cleanup (lesson 22). Several writes can also travel together (p. 1572):

-- the template of p. 1572, filled in:
-- one request, one timestamp for every statement
BEGIN BATCH
  INSERT INTO person (personal_id, age, name)
    VALUES ('RSSMRA80A01F205X', 45, 'Maria Rossi');
  UPDATE person SET address = 'Via Roma 1'
    WHERE personal_id = 'RSSMRA80A01F205X' AND age = 45;
  DELETE FROM person
    WHERE personal_id = 'FRNTRZ95E12F675T';
APPLY BATCH;

The batch earns its place elsewhere: when one fact is stored in two tables, a logged batch (the default) writes both copies or neither — background from the Cassandra documentation, and the glue of section 5.

Types, scripts, and the JOIN that is not there

Types first (p. 1577): "text, varint, float, double, Boolean, etc.", plus int, date, timestamp and uuid, filled by the function uuid() (p. 1609). Two kinds are special. A collection keeps several values in one column: list<T> (ordered, duplicates allowed), set<T> (unique values), map<K, V>. The slide shows the list:

-- p. 1577: a column that holds a list of e-mails
CREATE TABLE test (id int PRIMARY KEY, email list<text>);

-- p. 1577: append to the list; a set takes {'x'}, a map {'k': 1.5}
UPDATE test SET email = email + ['a@polimi.it'] WHERE id = 1;

A user-defined type is a named record used as a column type; "it is necessary to define the data type before using it" (p. 1578). The car exercise defines a feature (p. 1608):

-- p. 1608: a feature has a name and a description
CREATE TYPE feature (
  name text,
  description text
);

-- p. 1608: a list of features, the type frozen
ALTER TABLE car ADD features list<frozen<feature>>;

"A frozen data type can only be overwritten; it can't be edited anymore" (p. 1608): the value is stored as one block. DESCRIBE TYPE feature; checks it; ALTER TYPE and DROP TYPE change or remove it (p. 1578). Three commands of the cqlsh console, not CQL statements, complete the toolkit (pp. 1573–1575):

-- p. 1575 (path quoted): run every statement of a file, as if typed
SOURCE 'D:/Program Files/Cassandra/Queries/query_1.txt';

-- p. 1573 (path quoted): query results go to the file until CAPTURE off;
CAPTURE 'D:/Program Files/Cassandra/Outputs/output.txt';

-- p. 1574: one line per column, for wide rows; EXPAND off; stops it
EXPAND on;

Last, the slide everybody expects, "FOREIGN KEY (?)" (p. 1576): "In Cassandra there is no concept of FOREIGN KEYS and/or relationships, if you want any cross-table check to be performed, you have to manage it by yourself." The reason is again the key: two tables place their rows by two different partition keys, on different nodes, so a join would be a conversation between machines for every row. Instead, p. 1551: "Data is denormalized and duplicated for query efficiency", and the lecture's method (lesson 20): "Start from queries, then design the data model" (p. 430); "cassandra is an index factory" (p. 431). The puzzles paper of 30 May 2025 searches purchases by puzzle and by the buyer's city or country — two tables, every purchase written into both:

One table per query: its equality conditions become the partition key, its order the clustering key, and every column it returns is copied in.

The car dealership, end to end

The exercise session (pp. 1606–1612) is a complete exam script on one table. Keyspace and table first:

-- p. 1606: the keyspace of the exercise
CREATE KEYSPACE car_dealer
  WITH replication = {'class': 'SimpleStrategy',
                      'replication_factor': 3};

-- p. 1607: "sorted by max_speed in ascending order"
CREATE TABLE car (
  car_id uuid,
  brand text,
  max_speed varint,
  price float,
  consumption_lt_per_km float,
  PRIMARY KEY (car_id, max_speed)
) WITH CLUSTERING ORDER BY (max_speed ASC);

DESCRIBE car_dealer; checks the keyspace (p. 1606). After USE car_dealer; and the feature column of section 5 — added and dropped again, "As we do not want to deal with complex fields" (p. 1609) — the session inserts a car with a generated key:

-- p. 1609: uuid() generates the unique car_id
INSERT INTO car (car_id, brand, max_speed, price, consumption_lt_per_km)
VALUES (uuid(), 'Ferrari', 320, 200000.00, 30.00);

The rest of the data comes from a file, SOURCE '<path_to_your_folder>/car_data.txt'; (p. 1610). The deck does not show the file; the runs quoted here used nine cars, three Ferraris, three Fiats and three Lancias — the nine of the widget. The queries of pp. 1610–1612 then give:

-- p. 1610: "cost more than 100'000": a range, so ALLOW FILTERING (3 Ferraris)
SELECT * FROM car WHERE price > 100000 ALLOW FILTERING;

-- p. 1611: "exactly 35'000 (without using ALLOW FILTERING)": an index first
CREATE INDEX car_price ON car (price);

-- p. 1611: then the equality (a Fiat and a Lancia)
SELECT * FROM car WHERE price = 35000;

-- p. 1611: the sum of all prices: 805000, printed 8.05e+05 for a float
SELECT SUM(price) FROM car;

-- p. 1612: count the Ferraris and store the result in ferrari_count.txt
CAPTURE '<path_to_folder>/ferrari_count.txt';
CREATE INDEX car_brand ON car (brand);
SELECT COUNT(*) FROM car WHERE brand = 'Ferrari';
CAPTURE off;

Neither aggregate names a partition, so both ask every node, and cqlsh adds the warning "Aggregation query used without partition key". The file receives the count, 3, and that warning; while the capture is on, the screen only says "Now capturing query output to …".

Back to the hook

Read the key of p. 1607 as Cassandra does. car_id is the partition key, so every car is a partition of its own, and max_speed sorts the rows of a partition that holds a single row. SELECT * FROM car; walks the partitions in token order — the order of the hashes, hence 340, 190, 180 — and ORDER BY is refused because it only sorts inside partitions that the WHERE clause names. The repair moves one column into the partition key:

-- the cars of each brand, sorted by speed: brand decides the node
CREATE TABLE car_by_brand (
  brand text,
  max_speed varint,
  car_id uuid,
  price float,
  PRIMARY KEY (brand, max_speed, car_id)
) WITH CLUSTERING ORDER BY (max_speed ASC);

-- 296, 320, 340: one partition, already in order
SELECT * FROM car_by_brand WHERE brand = 'Ferrari';

car_id stays at the end of the key so that it still identifies a car: without it, two Fiats with the same top speed would be one row. With the partition named, ORDER BY works as well: ORDER BY max_speed DESC gives 340, 320, 296. The exam keeps using the session's pattern — "Personal_ID as the partition key and Phone Number as the clustering key, ordering the table based on the Phone Number" (6 Sep 2024) — and there the order is real: a chef with two phone numbers is one partition of two sorted rows.

The past papers: six exercises, five patterns

The first one, worked in full. The ER of 6 Jun 2024 tracks natural disasters: Person (Personal ID, Name, Surname, Postal Code) is the parent of Paramedic, Firefighter and Policeman (Policeman ID, Rank), ISA (P, O). "Consider the Policeman table. Write a Cassandra script to perform the operations listed below." A policeman row carries the inherited Person columns too:

-- "Create the Policeman table with Personal_ID as the partition key and
--  Policeman_ID as the clustering key. (1 PT)"
CREATE TABLE policeman (
  personal_id text,
  policeman_id text,
  name text,
  surname text,
  postal_code text,
  rank text,
  PRIMARY KEY (personal_id, policeman_id)
);

-- "collect the Policemen whose Surname is "Rossi". If any further operation
--  is required, write its CQL code. (1 PT)": surname is not a key column
CREATE INDEX policeman_surname ON policeman (surname);
SELECT * FROM policeman WHERE surname = 'Rossi';

-- "the CQL script to be written in an "operations.txt" file to insert two new
--  policemen ... and the CQL operation to run the whole script from the file. (1 PT)"
-- content of operations.txt
INSERT INTO policeman (personal_id, policeman_id, name, surname, postal_code, rank)
VALUES ('RSSMRA80A01F205X', 'PL001', 'Mario', 'Rossi', '20133', 'Sergeant');
INSERT INTO policeman (personal_id, policeman_id, name, surname, postal_code, rank)
VALUES ('VRDLGU85B02F205Y', 'PL002', 'Luigi', 'Verdi', '20121', 'Captain');
-- in cqlsh
SOURCE 'D:/Cassandra/operations.txt';

A full script opens with the keyspace and USE, as on p. 1606. Postal codes are text, not numbers: a code with a leading zero must keep it. Run with these two rows, the query returns Mario Rossi; surname = 'Rossi' ALLOW FILTERING returns him too, and is the alternative to name as "not recommended".