CODEWITHSUNDEEP
databasecassandra
Chris
Chris

Reputation: 1

Primary key in Cassandra's table

I need to create a Cassandra table on which specific queries will take action.

I need to be able to search on this table based on a date range, a type and one or more statuses. More specifically, the query will be like:

select * from table where date in range (from, to) and type = 'some type' and status IN (status1, status2, ..., statusN)

Since Cassandra does not allow comparison operation on the partition key, I decided to break the date (which is an Instant type in Java), into MONTH_YEAR. For example a date 2015/21/12:19:02:01 will be transformed to 122015 (12 is the month and 2015 the year of the date).

So far my idea for the partition key is: ((type, monthAndYear), status, date, uuid).

So when I want to have all records with a specific type, status and inside a date range, I can execute: select * from table where type = 'type' and monthAndYear IN 'monthAndYear1 monthAndYear2, monthAndYear3' and status IN ('status1', status2') and date >= from and date <= to.

My only problem here, is that the result of this query is ordered by the status (as this is the first clustering column of the table). I want all the records to be ordered based on the date field.

If I swap the status with the date fields, Cassandra doesn't allow another equality predicate after a comparison. If I remove the status field from the clustering columns and create an index on it, then again Cassandra doesn't allow IN operation in a column of a primary key when an indexed column exists in the query.

Could you suggest me some solution (if any exist) for this problem?

Thanks

Upvotes: 0

Views: 279

Answers (1)

Matija Gobec
Matija Gobec

Reputation: 880

You can create a primary key as ((type, status), time) with ordering defined on time field and execute a time range query for each status in your list and merge results on application. I don't know what your read latency requirements are but this should give you a desired dataset. You cannot serve all these query patterns from a single table. Look into SASI index maybe you can have a better solution.

Upvotes: 0

Related Questions