How to filter Cassandra result based on WRITETIME
I would like to get values whose WRITETIME value is more than a certain time. I tried this request but it fails:
SELECT zoom,idx FROM tiles
WHERE zoom=5 AND writetime(tile) > maxTimeuuid('2015-01-01 00:05+0000')
ALLOW FILTERING;
I am getting this error:
SyntaxException: <ErrorMessage code=2000 [Syntax error in CQL query]
message="line 1:68 no viable alternative at input '(' (...and idx > 0
and [writetime](...)">
For this table:
CREATE TABLE tiles (
zoom int,
idx int,
tile blob,
PRIMARY KEY (zoom, idx)
) WITH COMPACT STORAGE
+3
source to share
1 answer
WRITETIME
is a function used to display the time during which a particular column has been written. It is not part of the PRIMARY KEY and is not indexed, so it cannot be used in your WHERE clause. To be able to query by the time a certain row (not a column) has been written, you must add this to your table as an additional column and as your first clustering key:
CREATE TABLE tilesByLastWritten (
zoom int,
idx int,
tile blob,
lastwritten timeuuid,
PRIMARY KEY (zoom, lastwritten, idx)
) WITH CLUSTERING ORDER BY (lastwritten DESC, idx ASC);
Now this query will work:
aploetz@cqlsh:stackoverflow2> SELECT * FROM tilesByLastWritten
WHERE zoom=5 AND lastwritten > mintimeuuid('2015-07-02 08:30:00-0500');
zoom | lastwritten | idx | tile
------+--------------------------------------+-----+------
5 | 3a439c60-20bf-11e5-b9cb-21b264d4c94d | 1 | null
(1 rows)
Notes:
- Don't use a directive
ALLOW FILTERING
. This basically tells Cassandra that it is ok to pull all table rows from all your nodes and then apply filters. - Do not use
COMPACT STORAGE
to create a table. This was specifically for people to convert new CQL3 tables to the old Thrift engine storage format. If you don't, then you shouldn't use it. - In my example, I specified CLUSTERING ORDER to sort the table
tiles
onlastwritten
DESCending order. Typically timer based applications take care of getting the most recent data, so this usually makes sense. If that doesn't apply to you, then the (default) ASCending order should be good. - In my example, I included
idx
clustering as the last key, mainly for uniqueness. If you have to create queries for this column, a different query table (with a reconfigured primary key) may be required to support this.
For more help in this area, give Patrick McFadin Getting Started Using Timeseries Data Modeling .
+8
source to share