Re: Query data with Riak TS
Jan,
What you want to do to calculate the "average" is not supported in the
subset of SQL available currently through Riak TS. However, it is an easy
operation with a small script using one of the supported client libraries.
I personally use the Python client library for all the demonstrations etc.
that I create and your calculation would be a very simple operation with a
results set from Riak TS which is returned as a DataFrame object which
through the Python Pandas library offers a number of one-line methods to
calculate "averages".
Regards
Stephen
On 14 November 2016 at 16:05, Andrei Zavada
wrote:
>
>
> On Mon, Nov 14, 2016 at 5:44 PM, Jan Paulus
> wrote:
>
>> Hello Andrei,
>>
>>
>>
>> thanks for the answer. It is good news that 1.5 will get the ODER BY and
>> LIMIT functionality.
>>
>> Unfortunately the current AVG implementation is not exactly what we are
>> looking for. The current implementation will sum up all points in the time
>> range and will it divide by the number of points. Currently Riak TS return
>> 4.5 for my example. We are looking for an average which takes into account
>> how long a value was persistent.
>>
>>
> This seems to be a peculiar requirement that Riak TS cannot accommodate
> (sorry for not reading your explanation carefully enough). Indeed, the AVG
> function operates on numeric values as isolated scalars, and has no
> knowledge of how long each value has persisted.
>
>
>>
>> Kind Regards,
>>
>> Jan Paulus
>>
>>
>>
>> *Von:* Andrei Zavada [mailto:azav...@contractor.basho.com]
>> *Gesendet:* Montag, 14. November 2016 16:19
>> *An:* Jan Paulus
>> *Cc:* riak-users@lists.basho.com
>> *Betreff:* Re: Query data with Riak TS
>>
>>
>>
>> Hello Jan,
>>
>>
>>
>> Replying 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
>>
>>
>>
>
>
> ___
> riak-users mailing list
> riak-users@lists.basho.com
> http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
>
>
--
{ "name" : "Stephen Etheridge",
"title" : "Solution Architect, EMEA",
"Organisation" : "Basho Technologies, Inc",
"Telephone" : "07814 406662",
"email" : "mailto:setheri...@basho.com";,
"github" : "http://github.com/datalemming";,
"twitter" : "@datalemming"}
___
riak-users mailing list
riak-users@lists.basho.com
http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
Re: Query data with Riak TS
On Mon, Nov 14, 2016 at 5:44 PM, Jan Paulus wrote: > Hello Andrei, > > > > thanks for the answer. It is good news that 1.5 will get the ODER BY and > LIMIT functionality. > > Unfortunately the current AVG implementation is not exactly what we are > looking for. The current implementation will sum up all points in the time > range and will it divide by the number of points. Currently Riak TS return > 4.5 for my example. We are looking for an average which takes into account > how long a value was persistent. > > This seems to be a peculiar requirement that Riak TS cannot accommodate (sorry for not reading your explanation carefully enough). Indeed, the AVG function operates on numeric values as isolated scalars, and has no knowledge of how long each value has persisted. > > Kind Regards, > > Jan Paulus > > > > *Von:* Andrei Zavada [mailto:azav...@contractor.basho.com] > *Gesendet:* Montag, 14. November 2016 16:19 > *An:* Jan Paulus > *Cc:* riak-users@lists.basho.com > *Betreff:* Re: Query data with Riak TS > > > > Hello Jan, > > > > Replying 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 > > > ___ riak-users mailing list riak-users@lists.basho.com http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
AW: Query data with Riak TS
Hello Andrei, thanks for the answer. It is good news that 1.5 will get the ODER BY and LIMIT functionality. Unfortunately the current AVG implementation is not exactly what we are looking for. The current implementation will sum up all points in the time range and will it divide by the number of points. Currently Riak TS return 4.5 for my example. We are looking for an average which takes into account how long a value was persistent. Kind Regards, Jan Paulus Von: Andrei Zavada [mailto:azav...@contractor.basho.com] Gesendet: Montag, 14. November 2016 16:19 An: Jan Paulus Cc: riak-users@lists.basho.com Betreff: Re: Query data with Riak TS Hello Jan, Replying 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 ___ riak-users mailing list riak-users@lists.basho.com http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
Re: Query data with Riak TS
Hello Jan, Replying 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 ___ riak-users mailing list riak-users@lists.basho.com http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
Query data with Riak TS
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. 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 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. Thanks a lot. Jan Paulus ___ riak-users mailing list riak-users@lists.basho.com http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
