1

I'm trying to leverage Cassandra's strong support for time series data to build a commenting system.

So far I'm thinking that each row would be a particular topic, and each comment would be a new column, stored in event_time descending order such that I could get the latest replies.

CREATE TABLE topic (
    topic_id text,
    user_id text,
    event_time timestamp,
    data text,
    PRIMARY KEY (topic_id,event_time),
) WITH CLUSTERING ORDER BY (event_time DESC);

However, if I want to get the last 10 comments made by 'John' for example, this seems to be difficult with the current model.

Could anyone give any advice on how I could retrieve 'John's comments efficiently, and if I would have to change the data model?

1

In Cassandra you need to take a query-based modeling approach. In your case, I would create a similarly-structured query table like this:

CREATE TABLE topicbyuser (
    topic_id text,
    user_id text,
    event_time timestamp,
    data text,
    PRIMARY KEY (user_id,event_time),
) WITH CLUSTERING ORDER BY (event_time DESC);

And then, if you wanted to query for the last 10 comments made by John, this would work:

SELECT * FROM topicbyuser WHERE user_id='John' LIMIT 10;

You could also add an ORDER BY, but since you have that in your column family definition, you shouldn't need it. Also, you should note that this query table does not replace the original topic table. It works in conjunction with it.

  • Thank you. If however, a user wants to update their comment, I would have to update both tables right? Since transactions are not fully supported, how would you handle edge cases in which one table is updated, but the other fails? – JeffLL Nov 17 '14 at 6:53
  • @AlbertLim For those UPDATEs, you should be able to encapsulate them with the BEGIN BATCH/APPLY BATCH statements. That will tell Cassandra to apply them as a single, logical operation. Check this SO answer for more info on that: stackoverflow.com/questions/26818576/… – Aaron Nov 17 '14 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.