How to scan properly a cassandra table page by page for ranged primary key?

Kokizzu 0
Viewed 15

How to scan a table if i have table like this in Cassandra 3.11:

CREATE TABLE versions (
    root text,
    subroot text,
    key text,
    ts timeuuid,
    size bigint,
    PRIMARY KEY ((root, subroot, key), ts)
) WITH CLUSTERING ORDER BY (ts DESC)

how can I scan properly per 1000 only for root='a', subroot='b', key>='c000000' and key<'c000001' (I need to scan everything started with c000000*, for example c000000-aaaaaa, c000000something, etc)

Because if I do this using sum, it got timedout

SELECT sum(size) 
FROM versions 
WHERE root='a' 
  AND subroot='b' 
  AND key>='c00000' AND key<'c000001' 
ALLOW FILTERING;

Is there a way to fetch everything without ALLOW FILTERING (I can sum using golang code or other language)?

cassandra
1 Answers

Yeah, you still need to do a full table scan with such partitioning. Because you have key column as a part of the partition key, the hash of the values could be distributed to different nodes & belong to the different token ranges. You can't do that efficiently using CQL only, so you need to write your own code or use tools like DSBulk (as I remember, you can use DSBulk as Java library as well) or Spark + Spark Cassandra Connector - both of these tools are heavily optimized for efficient full table scans.

In case if you want to implement it yourself, you need to write a code that will do following:

  • Pull a list of token ranges
  • Create a CQL query that will include your condition + subquery token(root, subroot, key) > + rangeStart AND token(root, subroot, key) <= rangeEnd
  • Send that query to one of the nodes owning the specific token range (I don't know if this functionality exists in Go driver)

Please note that you need to correctly handle ranges - they aren't starting at RANGE_MIN, some ranges could include the RANGE_MIN.

You can look to this Java example - it uses the same algorithm as Spark Cassandra Connector and DSBulk.

Alex Ott0
Related