Difficulty in defining Cassandra datamodel

Question

I am new to Casssandra and I feel difficult to implement the datamodel.

I have faced lot of issue to design a single table.

Before i mention the table definition i want to show you the ways we have to retrieve and update record

select * from email where username='suresh' and inactive='N' and type='outbound'
    order by insert_ts desc allow filtering;
update email set inactive='Y' where username='suresh' and inactive='N' 
    and id=101;

To create a table i should follow all cassandra defined rules. I am facing the problem while creating the indexes for the table

If i create primary key like this

PRIMARY KEY(username, inactive,type,insert_ts);

I am able to retrieve record but when i do update, i am getting error saying "Primary key part found in set" error.

If i create primary key and secondary key like below

PRIMARY KEY(username, type,insert_ts);
Secondary index = inactive;

I am able to do update but when i retrieve, I am getting error saying "Secondary index will not be allowed with order by clause"

I have created email table using cql like

Create table email(id int, username varchar, comment text, 
  inactive boolean, insert_ts timestamp, type varchar,
PRIMARY KEY(<<some columns yet to decide>>));

Please suggest me how to create email table which satisfy my queries.

Answer 1

Based on your information, inactive should not be part of the primary key, because it is something that you intend to change over time without creating a new row. Using that as the base assumption, you need to use PRIMARY KEY(username, type, insert_ts);.

You will not be able to both filter by secondary index and use ORDER BY [anything] at the same time. The query engine does not allow this as of 2.0.3. Two mitigating approaches are possible:

1) Don't make inactive an index, and don't use it for filtering.

Given your examples, inactive appears to be a low-cardinality value (Y or N), and furthermore, you are manipulating few rows at a time (you restrict both your queries by username and/or id). Therefore in terms of number of results, omitting inactive from the query should not be expensive. You can filter inactive rows on the client side when using SELECT.

2) Don't use ORDER BY timestamp.

Same as above, except instead of filtering on the client, you're now responsible for sorting on the client.

Decision on which mitigation is more appropriate should be informed by your data and use cases. My hunch is that #1 is the best way to go, since you're introducing an extremely low-cardinality, likely frequently updated index for what seems to be pretty marginal added convenience.

Answer 2

Thanks for your response.

Based on your suggestion i understand that inactive column which has low cardinality should be removed from primary key. I am good, I will do inactive filtering in client side. But, Filtering insert_ts in client side will not a solve my problem, Since there will be a thousands of email record present in that table.

Create table email(id int, username varchar, comment text,
  inactive boolean, insert_ts timestamp, type varchar,
PRIMARY KEY(username,type,insert_ts, id))
With Clustering(Type ASC, insert_ts desc, id asc);

Also i would like to add ID column in primary key, because we have a requirement to display email records with the limit of 100. Cassandra has Limit clause takes care of filtering and i can use id value to find next 100 record.

For example:

Select * from email where username='suresh' and type='outbound' 
  order by type,insert_ts desc, id 
Limit 101;

In this case i know 101 record id and i use it for request which needs to fetch next 100 records.

I hope i understand it well. If you see any gap, please advice me.