How to model cassandra table to allow where clause on a non primary key

Question

I am using cassandra in an experimental project. My model is simple I have the table below:

create table message(
id varchar,
msgId varchar,
tId varchar,
gtName varchar,
status varchar,
text text,
PRIMARY KEY (id, tId)

);

At the time of first insert I would only have id and tId to insert. There will be an immediate update where I can get msgId to persist from the return type of the called method. There will be another call to my app containing the status which would only know the msgId. In that case I will need a lookup in order to update the message table using a where clause on msgId.

How can I possibly get that working fine with cassandra 2.1.0 I am also using spring-data-cassandra:1.1.0.RELEASE

Thanks for your suggestion

score 1 · Answer 1 · edited May 23 '17 at 12:27

A first simple step would be to create a secondary index on that key:

create index on message(msgId);
select * from message where msgId='foo';

Secondary indexes do have some performance concerns and aren't always a good fit, depending on your data model. Another option is to create a second table which maps msgId back to id and tId:

create table msgid (
     msgId varchar,
     id varchar,
     tId varchar,
     primary key (msgId)
);

Here are some useful discussions about usage of secondary indexes:

Thanks a lot, checking the links – black sensei Oct 17 '14 at 17:58 — black sensei, Oct 17 '14 at 17:58

How to model cassandra table to allow where clause on a non primary key

1 Answers1