CODEWITHSUNDEEP
databasedatastaxcassandra-2.1
Vishnu
Vishnu

Reputation: 375

How to create table structure which supports multiple WHERE Clause with different ORDER BY in Cassandra

hi every one thank you in advance for your help and sorry for my bed English.
i am newbie with Cassandra database.

now my problem is i want to create a table on which i can fire multiple query with different type of where clause and order by clause

This is my table structure

CREATE TABLE iF NOT EXISTS AD_ChangeLog 
(   AD_ChangeLog_ID int ,
    AD_Session_ID int,
    AD_Table_ID int,
    AD_Column_ID int,
    AD_Client_ID int,
    AD_Org_ID int,
    IsActive text,
    Created timestamp,
    Updated timestamp,
    CreatedBy int,
    UpdatedBy int,
    Record_ID int,
    Oldvalue text,
    NewValue text,
    Undo text,
    Redo text, 
    IsCustomization text, 
    TrxName text, 
    Description text, 
    EventChangeLog text, 
    AD_ChangeLog_UU Text
);   

  and i want to fire this queries on it
1) SELECT * FROM AD_ChangeLog WHERE IsCustomization='Y' AND IsActive='Y' ORDER BY AD_Table_ID, AD_Record_ID, AD_Column_ID;

2) SELECT * FROM AD_ChangeLog  WHERE AD_ChangeLog = x;

3) SELECT * FROM AD_ChangeLog WHERE AD_Table_ID=x AND Record_id=y AND ad_column_id=z ORDER BY UPDATED;

4) SELECT * FROM AD_ChangeLog WHERE AD_Table_ID=x AND Record_id=y ORDER BY UPDATED;

i have tried this but no luck :( 

==> 1) 
CREATE TABLE iF NOT EXISTS AD_ChangeLog 
(   AD_ChangeLog_ID int ,
    AD_Session_ID int,
    AD_Table_ID int,
    AD_Column_ID int,
    AD_Client_ID int,
    AD_Org_ID int,
    IsActive text,
    Created timestamp,
    Updated timestamp,
    CreatedBy int,
    UpdatedBy int,
    Record_ID int,
    Oldvalue text,
    NewValue text,
    Undo text,
    Redo text, 
    IsCustomization text, 
    TrxName text, 
    Description text, 
    EventChangeLog text, 
    AD_ChangeLog_UU Text,
    Primary Key (AD_ChangeLog_ID,Updated)
)With CLUSTERING ORDER BY (Updated DESC);
CREATE INDEX ON AD_ChangeLog (AD_Table_ID);
CREATE INDEX ON AD_ChangeLog (AD_Column_ID);
CREATE INDEX ON AD_ChangeLog (Record_ID);

==> 2)
CREATE TABLE iF NOT EXISTS AD_ChangeLog 
(   AD_ChangeLog_ID int ,
    AD_Session_ID int,
    AD_Table_ID int,
    AD_Column_ID int,
    AD_Client_ID int,
    AD_Org_ID int,
    IsActive text,
    Created timestamp,
    Updated timestamp,
    CreatedBy int,
    UpdatedBy int,
    Record_ID int,
    Oldvalue text,
    NewValue text,
    Undo text,
    Redo text, 
    IsCustomization text, 
    TrxName text, 
    Description text, 
    EventChangeLog text, 
    AD_ChangeLog_UU Text,
    Primary Key ((AD_Table_ID,Record_ID,AD_Column_ID),AD_ChangeLog_ID,Updated)
);

i can't understand how to make this working for all case
if it start working for one case another fails
please provide some guideline
thank you again :)

Upvotes: 0

Views: 61

Answers (1)

Brad Schoening
Brad Schoening

Reputation: 1371

Secondary indexes can support multiple query patterns on a Cassandra table, but your columns appear to have cardinality issues. Secondary indexes are not a good fit for very low or very high cardinality values like Record_ID, IsCustomization and IsActive. A secondary index on these columns will likely not perform well at scale, even if it works in a test bed. See When to use an index for some background regarding indices in Cassandra.

Cassandra data modeling frequently denormalizes tables to implement multiple query patterns on the same data. Basically, duplicating your data directly or with materialized views (available in Cassandra 3.0 and later).

You can create denormalized lookup tables and use light weight transactions to insert into the base and lookup tables:

CREATE TABLE IF NOT EXISTS AD_ChangeLog_IsActive
    AD_ChangeLog_ID int,
    AD_TableID int,
    AD_ColumnID int,
    IsActive text,
    Primary Key ((IsActive, AD_TableID, AD_ColumnID))
);

Because IsActive has only Yes/No values, you'll want your partition key to include an additional column(s) so that the data doesn't hotspot on one or two nodes.

DataStax's DSE engine integrates Cassandra and Solr search which may better support the sort of ad hoc queries you are looking to run.

Upvotes: 1

Related Questions