WHERE Rules: What Queries Are Allowed
You now know enough to understand all of Cassandra's WHERE rules. Remember how the data is stored: Cassandra finds a partition by the hash of its key, and the rows inside a partition are sorted by the clustering columns. Every rule comes from this.
Take the table from the last lesson, with PRIMARY KEY ((sensor_id, day), hour):
| WHERE | Allowed? | Why |
|---|---|---|
sensor_id = ? AND day = ? | Yes | Full partition key, so Cassandra can hash it |
... AND hour = 12 | Yes | Finds one row in the sorted rows |
... AND hour >= 12 | Yes | Takes a slice of the sorted rows |
sensor_id = ? (no day) | No | Half a partition key can't be hashed |
hour = 12 (no partition key) | No | Which partition? Cassandra would have to read all of them |
temperature > 20 | No | Not a key column |
The rules
- Give the whole partition key, with
=(orIN). Cassandra hashes all partition key columns together, so it can't find a partition from only part of the key. - Then the clustering columns, from left to right. Use
=on the first ones. Only the last clustering column you use may have a range (<,>,<=,>=). - Anything else needs
ALLOW FILTERING, a secondary index (in the Advanced Features section), or a better table.
Here is a query that breaks rule 1:
SELECT * FROM sensor_readings
WHERE hour = 12;
Output:
InvalidRequest: Error from server: code=2200 [Invalid query] message="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"
And here is a query that follows rules 1 and 2:
SELECT * FROM sensor_readings
WHERE sensor_id = 'temp-1'
AND day = '2024-05-02'
AND hour >= 6;
Output:
sensor_id | day | hour | temperature
-----------+------------+------+-------------
temp-1 | 2024-05-02 | 12 | 25.5
(1 rows)
Several clustering columns
The order in rule 2 matters when a table has more than one clustering column. In this table, sensor_id is the partition key, and day and hour are both clustering columns:
CREATE TABLE sensor_log (
sensor_id text,
day date,
hour int,
temperature decimal,
PRIMARY KEY (sensor_id, day, hour)
);
The rows are sorted by day first, then by hour. If you skip day, Cassandra can't use the sorted order:
SELECT * FROM sensor_log
WHERE sensor_id = 'temp-1' AND hour = 12;
Output:
InvalidRequest: Error from server: code=2200 [Invalid query] message="PRIMARY KEY column "hour" cannot be restricted as preceding column "day" is not restricted"
The same is true when you use a range on day and then also restrict hour. Only the last clustering column you use can have a range.
Reading the error messages
Most broken queries give the same message you saw in the WHERE lesson: "Cannot execute this query as it might involve data filtering". It does not say which rule you broke. When you see it, check your WHERE against the rules: is the full partition key there, and are the clustering columns used in order?
In the exercise, someone wrote a query that breaks rule 1. Fix it.