Query data with Riak TS

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Query data with Riak TS

Jan Paulus

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
[hidden email]
http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query data with Riak TS

Andrei Zavada
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


_______________________________________________
riak-users mailing list
[hidden email]
http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

AW: Query data with Riak TS

Jan Paulus

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:[hidden email]]
Gesendet: Montag, 14. November 2016 16:19
An: Jan Paulus
Cc: [hidden email]
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
[hidden email]
http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query data with Riak TS

Andrei Zavada


On Mon, Nov 14, 2016 at 5:44 PM, Jan Paulus <[hidden email]> 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:[hidden email]]
Gesendet: Montag, 14. November 2016 16:19
An: Jan Paulus
Cc: [hidden email]
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
[hidden email]
http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query data with Riak TS

Stephen Etheridge
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 <[hidden email]> wrote:


On Mon, Nov 14, 2016 at 5:44 PM, Jan Paulus <[hidden email]> 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:[hidden email]]
Gesendet: Montag, 14. November 2016 16:19
An: Jan Paulus
Cc: [hidden email]
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
[hidden email]
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:[hidden email]",
   "twitter" : "@datalemming"}


_______________________________________________
riak-users mailing list
[hidden email]
http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
Loading...