Jack/Tom 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). Thanks -- IPVP From: Jack Krupansky <jack.krupan...@gmail.com><mailto:jack.krupan...@gmail.com> Reply: user@cassandra.apache.org <user@cassandra.apache.org>><mailto:user@cassandra.apache.org> Date: March 16, 2016 at 5:22:36 PM To: user@cassandra.apache.org <user@cassandra.apache.org>><mailto:user@cassandra.apache.org> 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 <t...@drillster.com<mailto:t...@drillster.com>> wrote: 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. Tom
