Cassandra data modeling for range queries using timestamp

0
votes

I need to create a table with 4 columns:

  • timestamp BIGINT
  • name VARCHAR
  • value VARCHAR
  • value2 VARCHAR

I have 3 required queries:

SELECT *
FROM table
WHERE timestamp > xxx
AND timestamp < xxx;

SELECT *
FROM table
WHERE name = 'xxx';

SELECT *
FROM table
WHERE name = 'xxx'
AND timestamp > xxx
AND timestamp < xxx;

The result needs to be sorted by timestamp.
When I use:

CREATE TABLE table (
    timestamp BIGINT,
    name VARCHAR,
    value VARCHAR,
    value2 VARCHAR,
    PRIMARY KEY (timestamp)
);

the result is never sorted.
When I use:

CREATE TABLE table (
    timestamp BIGINT,
    name VARCHAR,
    value VARCHAR,
    value2 VARCHAR,
    PRIMARY KEY (name, timestamp)
);

the result is sorted by name > timestamp which is wrong.

name | timestamp
------------------------
   a | 20170804142825729
   a | 20170804142655569
   a | 20170804142650546
   a | 20170804142645516
   a | 20170804142640515
   a | 20170804142620454
   b | 20170804143446311
   b | 20170804143431287
   b | 20170804143421277
   b | 20170804142920802
   b | 20170804142910787

How do I do this using Cassandra?

1
cassandradata-modelingcql

1 Answers

1
votes

Cassandra order data by clustering key group by partition key

In your case first table have only partition key timestamp, no clustering key. So data will not be sorted.

And For the second table partition key is name and clustering key is timestamp. So your data will sorted by timestamp group by name. Means data will be first group by it's name then each group will be sorted separately by timestamp.

Edited

So you need to add a partition key like below :

CREATE TABLE table (
    year BIGINT,
    month BIGINT,
    timestamp BIGINT,
    name VARCHAR,
    value VARCHAR,
    value2 VARCHAR,
    PRIMARY KEY ((year, month), timestamp)
);

here (year, month) is the composite partition key. You have to insert the year and month from the timestamp. So your data will be sorted by timestamp within a year and month