Query data with Riak TS

Andrei Zavada azavada at contractor.basho.com
Mon Nov 14 10:19:11 EST 2016


Hello Jan,

R​eplying to your questions inline:​​
​

>  Hi,

we are testing Riak TS for our Application right now. I have a couple of
> question how to query the data. We are measuring electric power which comes
> in in odd time intervals.
>


> 1. Is it possible to query the value which has been received bevor at a
> given time? In other words: Get the current / last value of a series.
>

In Riak TS 1.4 (the latest release), you still have to specify the exact
range ​in the WHERE clause.  Because of that, if you are only interested in
a single record that was added last before a certain time, you have to do
some guesswork to arrive at the suitable boundaries.  Depending on the rate
of data ingress, the selection returned may contain one, a few, too many,
or no results at all.  One workaround is to issue a sequence of SELECTs,
starting from a small range, with the lower boundary of the range
progressively moved back until you have the last record included (and
hopefully not many previous records which you are going to discard anyway).

In 1.5, we will support ORDER BY and LIMIT clauses which will allow a much
simpler solution, e.g.:

 SELECT value FROM table
 WHERE time > 0 AND time < $now
 ORDER BY value DESC LIMIT 1


> 2. Is it possible to query the average in respect to time?
> For instance you have such a measurement reading:
> time    | value
> -----------------
> 09:31   |  4
> 10:02   |  6
> 10:05   |  3
> And you want to query the average from 10:00 to 10:10. I would expect a
> value of 4.1
> 4 * 20% + 6 * 30% + 3 * 50% = 4.1
>

​It is definitely possible:​

 ⁠⁠⁠⁠SELECT AVG(val
​ue​
) FROM
​table
​
 WHERE time >
​=​
​'​2016-11-22
10
​:00'​
AND time <
​'2016-11-22 ​
​10:10'​

​Note that you can specify timestamp values in ISO8601 format:
http://docs.basho.com/riak/ts/1.4.0/using/timerepresentations

​

> 3. Is it possible to query 15 minutes average values for the last day? I
> would expect 96 values with the average as described in question 2.
>

No, you will have to issue 96 separate queries, each similar to the one in
(2) but with a different 15-min range.​



> Thanks a lot.
> Jan Paulus
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.basho.com/pipermail/riak-users_lists.basho.com/attachments/20161114/454252f0/attachment-0002.html>


More information about the riak-users mailing list