cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From I PVP <>
Subject Re: Modeling Audit Trail on Cassandra
Date Wed, 16 Mar 2016 20:40:06 GMT
Thanks for answering.

Here is the table definition so far:

CREATE TABLE audit_trail (
auditid timeuuid,
actiontype text,
objecttype text,
executedby uuid ( or timeuuid?),
executedat timestamp,
objectbefore text,
objectafter text,
clientipaddr text,
serveripaddr text,
servername text,
channel text,
PRIMARY KEY (auditid)

objectbefore/after are the only ones that will have JSON content. quering based on the contents
of these two  columns are not a requirement.

At this moment the queries are going to be mainly on executedby ( the employee id).
Stratio’s Cassandra Lucene Index will be used to allow querying/filtering on executedat
(timestamp) ,objecttype(order, customer, ticket, message,account, paymenttransaction,refund
etc.)  and actiontype(create, retrieve, update, delete, approve, activate, unlock, lock etc.)

I am considering to count exclusively on Stratio’s Cassandra Lucene  filtering and avoid
to add  “period” columns like month(int), year(int), day (int).



From: Jack Krupansky <><>
Reply: <>><>
Date: March 16, 2016 at 5:22:36 PM
To: <>><>
Subject:  Re: Modeling Audit Trail on Cassandra

executedby is the ID assigned to an employee.

I'm presuming that JSON is to be used for objectbefore/after. This suggests no ability to
query by individual object fields. I didn't sense any other columns that would be JSON.

-- Jack Krupansky

On Wed, Mar 16, 2016 at 3:48 PM, Tom van den Berge <<>>
Is text the most appropriate data type to store JSON that contain couple of dozen lines ?

It sure is the simplest way to store JSON.

The query requirement  is  "where executedby = ?”.

Since executedby is a timeuuid, I guess you don't want to query a single record, since that
would require you to know the exact timeuuid. Do you mean that you would like to query all
changes in a certain time frame, e.g. today? In that case, you would have to group your rows
in time buckets, e.g. PRIMARY KEY ((period), auditid). Period can be a day, month, or any
other period that suits your situation. Retrieving all changes in a specific time frame is
done by retrieving all relevant periods.


View raw message