Cassandra time series query

Question

Hello i want to query data for certain devices that send out alerts periodically. So i want to have a time series. I created a table like this:

CREATE TABLE alerts_by_day (
    day_of_year int,
    year int,
    alert_timestamp timestamp,
    serial_number text,
    alert_id uuid,
    alert_type text,
  PRIMARY KEY((day_of_year, year), serial_number, alert_timestamp, alert_type,alert_id)
) WITH CLUSTERING ORDER BY (serial_number DESC, alert_timestamp DESC, alert_type DESC);

Now i want to create a query or Materialized view in order to be able to query by a time range (i.e.: from Nov 29 2015 to January 20 2016) and by alert type. So i just want to show the alerts for a certain type and time range. I can query for a certain time range:

Select * from alerts_by_day
where day_of_year IN (312,313)
and year IN (2016,2015) 
and alert_timestamp < '2016-11-08 03:09:14-0800'
and alert_timestamp > '2016-11-07 23:13:28-0800'

but i cannot do this:

Select * from alerts_by_day
where day_of_year IN (312,313)
and year IN (2016,2015) 
and alert_timestamp < '2016-11-08 03:09:14-0800'
and alert_timestamp > '2016-11-07 23:13:28-0800'
and alert_type IN ('type1', 'type2')

because i used a restriction that is NOT EQ for alert_timestamp.

How could i achieve that i only see the alerts of type 1 and type 2 ?

here is the error : Unable to execute CQL script : clustering column alert_type cannot be restricted ( precedign column alert_timestamp is restricted by a non-EQ-relation)

I deleted my answer. but you can check this answer http://stackoverflow.com/questions/34422515/cassandra-error-clustering-column-cannot-be-restricted-preceding-column-is-re Im short what are you doing is not possible — root, Nov 29 '16 at 20:43

score 0 · Answer 1 · answered Nov 30 '16 at 05:37

0

You will be able to get filtering and aggregation of timeseries with Spark

answered Nov 30 '16 at 05:37

Carlos Monroy Nieblas

2,225
2
16
27

Cassandra time series query

1 Answers1