How to Query range in Cassandra

Question

I’m trying to query a integer on Cassandra table and get the error Only EQ and IN relation are supported on the partition key(unless you use the token()function) My table is setup Is below with the query I ran. Be aware I did not setup the table nor can I change it currently.

CREATE TABLE USERS(
accName text,
accContext text,
accNumber int,
accCount int,
accHost text,
PRIMARY KEY (accName,accContext,accNumber)
);

SELECT * FROM Users WHERE accName = 'tear' and accContext = 'db1' 
   and accNumber> 20200101 and accNumber<= 202002018;

Answer 1

It looks like your post-edit query:

SELECT * FROM Users
    WHERE accName = 'tear' and accContext = 'db1' 
    and accNumber> 20200101 and accNumber<= 202002018;

...works just fine. If you specify accName (partition key) and accContext (1st clustering key), you absolutely can run a range query on an integer, assuming that it is the next clustering key. However, if you were to eliminate accContext from your query, this would fail because you're not giving Cassandra enough information to efficiently retrieve the desired data.

SELECT * FROM Users WHERE accNumber> 20200101 and accNumber<= 202002018;

So this was your query before being edited with a working solution. And of course, you saw the error stating that range queries on partition keys only work with the token function.

Why is that?

You've probably heard that Cassandra requires a "query based" approach to data modeling. This is because all data usually does not reside on a single node. Cross-node queries are expensive. Forcing range queries on partition keys via token gives you the tools to limit your query to a node that is responsible for a specific token range. In your case, this works:

aaron@cqlsh:stackoverflow> SELECT token(accname),accname FROM Users
    WHERE token(accname) <= -6425313154088713591
      AND token(accname) >  -7367992452875979971;

 system.token(accname) | accname
-----------------------+---------
  -6611994791738996364 |    tear

(1 rows)

Deconstructing what I did here, by using the token function in my SELECT, I can see what the token of the string "tear" is. Next, I can query system.peers to see which node(s) are responsible for that token, as well as to get an idea of the exact token ranges the target nodes are responsible for.