Using Cassandra CQL, how can I fetch a range of data including last point before and first point after

2
votes

I have some time series data stored in Cassandra as name, timestamp, value.

I want to fetch data to show on a time series chart so I'm fetching the time range from Cassandra. But in order to render the chart properly I actually need the last point before the range and the first point after so that the chart line continues off the edges of the chart.

My current plan is to have two tables with identical columns, as above, but to order in ascending order of time in one and descending order of time in the other.

Then I could need to make 3 queries.

  • Fetch all points inside the range from the asc table
  • Fetch points from the asc table where time greater than end time limit 1. That gives me the point beyond the end.
  • Fetch points from the desc table where time less than start time limit 1. That gives me the point before the start.

Is this the best way? Any Cassandra experts got any ideas?

Thanks! Chris

1
cassandra

1 Answers

2
votes

I think one table is enough. Check experiment below.

ts - timeseries table; t - timevalue; and you want to find left/right edges of t=3

cqlsh:ks> create table ts(pk int, t int, v int, primary key(pk, t, v));
cqlsh:ks> insert into ts (pk, t, v) values (1, 1, 1);
cqlsh:ks> insert into ts (pk, t, v) values (1, 2, 2);
cqlsh:ks> insert into ts (pk, t, v) values (1, 3, 3);
cqlsh:ks> insert into ts (pk, t, v) values (1, 4, 4);
cqlsh:ks> insert into ts (pk, t, v) values (1, 5, 5);

cqlsh:ks> select * from ts where pk=1 and t<3 order by t desc limit 1;

 pk | t | v
----+---+---
  1 | 2 | 2

(1 rows)
cqlsh:ks> select * from ts where pk=1 and t>3 order by t asc limit 1;

 pk | t | v
----+---+---
  1 | 4 | 4

(1 rows)