CODEWITHSUNDEEP
databasecassandradata-modelingcql
Aqib Zaman
Aqib Zaman

Reputation: 285

How to model table in cassandra for selecting with where condition

Suppose i have a table just like below

create table userevent(id uuid,eventtype text,sourceip text,user text,sessionid text,roleid int,menu text,action text,log text,date timestamp,PRIMARY KEY (id,eventtype));

 id                                   | action | date                     | eventtype | log      | menu      | roleid | sessionid | sourceip     | user
--------------------------------------+--------+--------------------------+-----------+----------+-----------+--------+-----------+--------------+-------
 6ac47b10-d6bb-11e8-bb9a-59dfa00365c6 |  Login | 2018-10-01 04:05:00+0000 |  DemoType |  demolog |  demomenu |      1 |    Demo_1 | 121.11.11.12 |  Aqib
 62119cf0-d6bb-11e8-bb9a-59dfa00365c6 |  Login | 2018-05-31 22:35:00+0000 | DemoType3 | demolog3 | demomenu3 |      3 |    Demo_3 | 121.11.11.12 | Jasim
 5ebb4600-d6bb-11e8-bb9a-59dfa00365c6 |  Login | 2018-05-31 22:35:00+0000 | DemoType3 | demolog3 | demomenu3 |      3 |    Demo_3 | 121.11.11.12 | Jasim

So how could i select if want full data that satisfies something like user="something" or eventtype="something" etc in my table.

Because when i tried with a simple select query with where condition user='Aqib', its giving error. i know that the data modeling in cassandra is not same as in sql. Any one could help me its very much appreciable.

How to change the above table creation to satisfy below queries,

select * from userevent where user='Aqib';
select * from userevent where eventtype='DemoType';
select * from userevent where action='Login';

etc

Upvotes: 2

Views: 69

Answers (1)

Mohamed Ibrahim Elsayed
Mohamed Ibrahim Elsayed

Reputation: 2964

First things first: there is no OR in Cassandra queries

If in your queries you are always restricting a field like user or eventtype or action then I suggest that you create a separate table for each of these types of queries, one table that supports querying with user field in which the partition key would be the user field and another table with eventtype as the partition key to support querying with the eventtype field, and one table for the action field etc... This complies with Cassandra's data modeling of building tables based on your queries.

So the table that supports querying with user should be:

CREATE TABLE userTable (
    user text,
    id uuid,
    eventtype text,
    sourceip text,
    sessionid text,
    roleid int,
    menu text,
    action text,
    log text,
    PRIMARY KEY (user)
);

Table that supports querying with eventtype should be:

CREATE TABLE eventtypeTable (
    eventtype text,
    id uuid,
    user text,
    sourceip text,
    sessionid text,
    roleid int,
    menu text,
    action text,
    log text,
    PRIMARY KEY (eventtype)
);

And you can create as many tables as you want each supporting a query.

Then when you execute your queries (for example in your application code) if you know you have the value of the user field value then query the table in which the user field is the partition key and restrict the user field value, for example: 

select * from userTable where user='Aqib';

else if you know you have the eventtype field value then you should query the table in which the partition key is the eventtype field for example:

select * from eventtypeTable where eventtype='DemoType';

and similarly for the other fields and their tables.

Upvotes: 2

Related Questions