Cassandra range slicing on composite key

Question

I have columnfamily with composite key like this

CREATE TABLE sometable(
    keya varchar,
    keyb varchar,
    keyc varchar,
    keyd varchar,
    value int,
    date timestamp,
    PRIMARY KEY (keya,keyb,keyc,keyd,date)
);

What I need to do is to

SELECT * FROM sometable
WHERE
    keya = 'abc' AND
    keyb = 'def' AND
    date < '2014-01-01'

And that is giving me this error

Bad Request: PRIMARY KEY part date cannot be restricted (preceding part keyd is either not restricted or by a non-EQ relation)

What's the best way to solve this? Do I need to alter my columnfamily? I also need to query those table with all keya, keyb, keyc, and date.

score 0 · Accepted Answer · answered Apr 15 '14 at 11:22

0

You cannot do it in cassandra. Moreover, such a range slicing is costlier too. You are trying to slice through a set of equalities that have the lower priority according to your schema.

I also need to query those table with all keya, keyb, keyc, and date.

If you are considering to solve this problem, considering having this schema. What i would suggest is to have the keys in a separate schema

create table ( timeuuid id, keyType text, primary key (timeuuid,keyType))

Use the timeuuid to store the values and do a range scan based on that.

create table( timeuuid prevTableId, value int, date timestamp, primary key(prevTableId,date))

Guess , in this way, your table is normalized for better scalability in your use case and may save a lot of disk space if keys are repetitive too.

answered Apr 15 '14 at 11:22

Ananth

971
9
23

According your suggestion, do I need to do something like this: `create table (id timeuuid, keya text, keyb text, keyc text, keyd text, primary key(id, keya, keyb, keyc, keyd)` and `create table (id timeuuid, value int, date timestamp, primary key(id, date)`. When I need to query with keya, keyb, and date, do I have to query the first table and get a list of id, and then query it again with the second table with range slicing in date with a set of id? I don't think that's a good way to do that. Am I missing something? – Andrei Dharma Apr 16 '14 at 05:05
Yes. Since i felt keya, keyb , keyc,keyd are all text , i mentioned it as a keyType and kept it on clustering key portion. This way, you can even get a set of timeuuid for a particular keyType too. Not sure about the key variable in your use case though. If it requires all types to be present in clustering key, it doesnt cause any harm too. But at any cost,you need keya to query keyb. – Ananth Apr 16 '14 at 10:20

Cassandra range slicing on composite key

1 Answers1