CODEWITHSUNDEEP
cassandra
user1050619
user1050619

Reputation: 20856

Cassandra querying multiple columns

We are planning to store all the tickets in a cassandra db and here is the table defined.

CREATE TABLE tickets_info (
    ticket_num text,
    created_dt timestamp,
    division text,
    pg text,
    duration text,
    error_count text,
    outage_caused text,
    system_caused text,
    addt_notes text,
    ticket_type text,
    row_create_ts timestamp,
    PRIMARY KEY (ticket_num,created_dt)
) WITH CLUSTERING ORDER BY (created_dt DESC)

My background is mostly on SQL and after designing the table I noticed I cant query the table other than the ticket_num and created_dt as other columns are not being indexed :(.

My need is that the user should write query based on all these columns.

Should I create multiple tables for each query condition?

Table_1

CREATE TABLE ticket_info_division(
     ticket_num text,
     created_dt ts,
     division text
     PRIMARY KEY ((ticket_num, created_dt), division)
)

Table_2

CREATE TABLE ticket_info_pg(
     ticket_num text,
     created_dt ts,
     pg text
     PRIMARY KEY ((ticket_num, created_dt), pg)
)

Table_3

CREATE TABLE ticket_info_pg(
     ticket_num text,
     created_dt ts,
     duration text
     PRIMARY KEY ((ticket_num, created_dt), duration)
)

and merge all the results?

Upvotes: 0

Views: 915

Answers (1)

doanduyhai
doanduyhai

Reputation: 8812

Should I create multiple tables for each query condition?

Yes, that's the basic of Cassandra data modeling and we call it denormalization. Now, with the introduction of materialized views, it is easier for developers because the burden of synchronizing the base table with all the views is done by Cassandra. Read more here: http://www.doanduyhai.com/blog/?p=1930

Upvotes: 2

Related Questions