Suppose I have a table of the following composition.
CREATE TABLE rollup (
hashname text,
key text,
day timestamp,
counter_value counter,
PRIMARY KEY (hashname, key, day)
) WITH
...
I want to run a query that looks like
SELECT * FROM rollup WHERE hashname='some_namespaced_hash' AND day>='2013-07-15' AND day<='2013-07-25';
However, this doesn't work because (I think) the following is also relevant to >
,<
, etc.
Composite keys means it now makes sense for CQL to sport the ORDER BY syntax in SELECT queries as well, but it’s still not nearly as flexible as you might be used to, doing ad-hoc queries in SQL. ORDER BY clauses can only select a single column, and that column has to be the second column in a composite PRIMARY KEY. This holds even for tables with more than 2 column components in the primary key
and here, day
is the third column in the primary column key. The only way I can figure to do this is to change the primary compound key to PRIMARY KEY (hashname, day, key)
. I can't find any documentation that tells me how to do this. Is it possible?
Alternatively, am I missing the "correct" way to solve this problem/am I misinterpreting the problem?
You are right, the only way is switch the order of your primary key. The reason is that, currently, your keys are in this order:
so to retrieve a range of days, Cassandra would need to 'skip' for each key. This is inefficient and not supported. You need to have them in this order:
Unfortunately the only way to do this is to rewrite all your data, and there is no inbuilt way of doing this in Cassandra. You could write a script to do this by reading in the data from one CF, switching round the order, then write out to a new CF and switch. If this needs to be done live it's harder but still possible.