CODEWITHSUNDEEP
cassandra
piyushGoyal
piyushGoyal

Reputation: 1099

Cassandra Update query | timestamp column as clustering key

I have a table in cassandra with following schema:

CREATE TABLE user_album_entity (
    userId text,
    albumId text,
    updateDateTimestamp timestamp,
    albumName text,
    description text,
    PRIMARY KEY ((userId), updateDateTimestamp)
);

The query required to get data would have a where userId = xxx order by updateTimestamp. Hence the schema had updateDateTimestamp.

Problem comes in updating the column of table.The query is: Update the album information for user where user id = xxx. But as per specs,for update query I would need the exact value of updateDateTimestamp which in normal world scenario, an application would never send.

What should be the answer to such problems since I believe this a very common use case where select query requires ordering on timestamp. Any help is much appreciated.

Upvotes: 2

Views: 1437

Answers (2)

praveen
praveen

Reputation: 1

If your usecase is such that each partition will contain exactly one row, then you can model your table like:

CREATE TABLE user_album_entity ( userId text, albumId text static, updateDateTimestamp timestamp, albumName text static, description text static, PRIMARY KEY ((userId), updateDateTimestamp) );

modelling the table this way enables Update query to be done in following way: UPDATE user_album_entity SET albumId = 'updatedAlbumId' WHERE userId = 'xyz'

Hope this helps.

Upvotes: 0

xmas79
xmas79

Reputation: 5180

The problem is that your table structure allows the same album to have multiple records with the only difference being the timestamp (the clustering key).

Three possible solutions:

  1. Remove the clustering key and sort your data at application level.
  2. Remove the clustering key and add a Secondary Index to the timestamp field.
  3. Remove the clustering key and create a Materialized View to perform the query.

Upvotes: 1

Related Questions