Cassandra Update - Some clustering keys are missing (timestamp)

Question

I have a Cassandra table that looks like:

CREATE TABLE messages (
    user_id INT, 
    message_id INT,
    received_timestamp TIMESTAMP, 
    status TEXT,
    message TEXT, 
    PRIMARY KEY ((user_id, message_id),received_timestamp)) 
    WITH CLUSTERING ORDER BY (received_timestamp DESC);

If I try to update a row such as:

UPDATE messages SET status = 'success' WHERE user_id = 1 AND message_id = 1;

I get an error:

Some clustering keys are missing: received_timestamp

I understand that I need to include the received_timestamp because it's part of the primary key, however I'm only including it in the primary key for ordering purposes. Is there no way to perform an update here if I don't know the received_timestamp value? If not, is there a better way to create this table that might work for this use case? Thanks in advance.

@AshrafulIslam message_id is unique, but there could be multiple messages for one user_id — user172092
If message id is unique then how received_timestamp will sort you message according to timestamp ? There will be only one received_timestamp for an user_id and message_id — Ashraful Islam

Mikhail Baksheev Mikhail Baksheev · Accepted Answer · 2017-07-17T15:50:51

Cassandra does not support ordering data across partition keys, because it will require a lot of network communications in distributed environment. But you can use ordering inside a partition key. If you want to get all messages (or slice of messages between some dates) for specified user ordered by message timestamp, you can use timeuuid type for message_id instead of int:

  CREATE TABLE messages ( 
    user_id int,
    message_id timeuuid,
    status text,
    message text,
    PRIMARY KEY (user_id, message_id))
    WITH CLUSTERING ORDER BY (message_id DESC);

timeuuid type allows you to have unique message id and sort messages by timestamp.

CQL has some useful functions to work with timeuuid

But you should care about number of messages per user because of CQL limits:

Cells in a partition: ~2 billion (2^31); single column value size: 2 GB ( 1 MB is recommended)

Cassandra Update - Some clustering keys are missing (timestamp)

1 Answers