CODEWITHSUNDEEP
cassandracassandra-3.0
PraveenKumar K G
PraveenKumar K G

Reputation: 186

Insert table Mutation to different Cassandra table

I have requirement to keep the old values of a row in a history table for auditing whenever we do row update. Is there any solution available in Apache Cassandra to achieve this? I looked at the Trigger and not much mentioned in the docs. Not sure of performance issues if we use the triggers. Also if we use trigger, will it give the old value for a column when we do update?

Upvotes: 1

Views: 62

Answers (1)

Gunwant
Gunwant

Reputation: 979

Cassandra is best tool to keep the row history. I will try to explain it with an example. Consider the below table design -

CREATE TABLE user_by_id (
userId text,
timestamp timestamp,
name text,
fullname text,
email text,
PRIMARY KEY (userId,timestamp)
) WITH CLUSTERING ORDER BY (timestamp DESC);

With this kind of table design you can keep the history of the record. Here, userid is row partition key and timestamp as clustering key. Every insert for same user will be recorded as different row. for example -

insert into user_by_id (userId,timestamp ,name, fullname, email ) values ('1',<newTimeStamp>,'x',xyz,'[email protected]');
insert into user_by_id (userId,timestamp ,name, fullname, email ) values ('1',<newTimeStamp>,'y',xyz,'[email protected]');
insert into user_by_id (userId,timestamp ,name, fullname, email ) values ('1',<newTimeStamp>,'z',xyz,'[email protected]');

Above insert statements are actually updating values of the name and email column. But, this will be saved in three different rows because of timestamp as a clustering key, timestamp will be different for each row. If you want to get the latest value, just use LIMIT in your select query.

This design keeps the history of the row which can be used foe audit purpose.

Upvotes: 1

Related Questions