Cannot create materialized view using SELECT query with PER PARTITION LIMIT

uber42 0
Viewed 36

Table:

CREATE TABLE IF NOT EXISTS table (
  a       TEXT,
  b       TEXT,
  c       BIGINT,
  PRIMARY KEY ((a, b), c)
) WITH CLUSTERING ORDER BY (c DESC);

I need to get only one record from each (a, b) partition for the entire selection where c will be in DESC order and b in ASC order:

SELECT * FROM table WHERE a='a-1' ORDER BY b ASC PER PARTITION LIMIT 1 ALLOW FILTERING;

Result:

ORDER BY is only supported when the partition key is restricted by an EQ or an IN.

I tried create materialized view for ordering by b:

CREATE MATERIALIZED VIEW IF NOT EXISTS table_view AS
  SELECT a, b, c
  FROM table
  WHERE a IS NOT NULL AND b IS NOT NULL AND c IS NOT NULL
  PER PARTITION LIMIT 1
  PRIMARY KEY (a, b, c)
  WITH CLUSTERING ORDER BY (b ASC, c DESC);

I get an error while creating on PER PARTITION LIMIT.

Is it really possible to do this? Or maybe there is some workaround for this case?

scylla cql cassandra
2 Answers

Your query is invalid. As the error states, you can only use the ORDER BY clause if you specify the partition key.

In your case, the partition key is (a, b) -- not just column a, but BOTH a AND b. You cannot use ORDER BY on column b because it is part of the partition key.

You can however ORDER BY c because it is a clustering column and not part of the partition key.

SELECT ... FROM table
    WHERE a = ?
    AND b = ?
    ORDER BY c ...

Note that in this example, BOTH a and b are restricted by the equality (EQ) operator. Cheers!

Erick Ramirez0

I'll try to explain why Scylla (and Cassandra) do not support the things you tried to do.

In Scylla (and Cassandra), partition keys are not ordered in any useful way - they are ordered by a hash function of the partition key, not by the partition key itself. In your case, the partition key is (a, b) - that is - the full pair. The restriction WHERE a='...' may match a million different partitions with partition keys ('...', b) for a million different b's, and these are not ordered by b's... Not only are they not ordered by b's - they aren't even colocated on the same node. The only way for Scylla to implement the WHERE a='...' restriction is to do a full-table scan across the entire cluster. This is why you had to add ALLOW FILTERING.

But even then, there is no O(N) way to implement the ORDER by b and this is why Scylla refuses to do it. As I said above, the query WHERE a='...' may return a million different partitions (a, b). Scylla would need to collect those million results, sort them all, and return them ordered by b. It can't do that. Scylla can scan an already-sorted partition (this is what the error message is telling you), but not sort unsorted results.

You can argue that Scylla could do in this case what search engines do, namely - do not sort the full result list up-front (O(nlogn) complexity, O(n) space), but rather collect only the top K results while scanning the entire table. But that makes paging through the entire result set inefficient - Scylla would need to do the entire scan for each page. That's not something that Scylla does in any other case.

Finally, for the materialized view, there is a different problem. You're right that PER PARTITION LIMIT is not supported there. There is a real problem to implement that. Imagine the following scenario:

  1. You add an item with key a=1, b=1, c=1 to the base table. It is added to the view as well.
  2. You add an item with key a=1, b=1, c=2. Because of the per-partition limit, and there is already an item with the same partition key (a=1,b=1), this new item is not inserted into the view.
  3. You now delete the item with key a=1, b=1, c=1. It is deleted from the view as well, but now Scylla needs to figure out that it needs to add a=1,b=1,c=2 to the view because there is now room for this item in the per-partition limit.

Step 3 is difficult and inefficient, so Scylla does not currently support this use case.

Nadav Har'El0
Related