Trying to understand continuous queries (I think)

Store
1

Hi.

I have a data source which stores one numeric value per second into an InfluxDB table. I’m using Grafana to visualise the data, and I can see the values there.

I need to create a “running total” of the sum of these values over the past X seconds (I want to have at least three time intervals - the past 10 minutes (600 seconds), the past hour (3600 seconds) and the past 24 hours (86400 seconds)), calculated each second.

Firstly, is a continuous query in Influx the right way to go about this, or can someone suggest a better solution (either in Influx or in Grafana)?

Secondly, if a CQ is the right approach, what am I doing wrong with the following (because it doesn’t work)?

CREATE CONTINUOUS QUERY hourly ON telegraf
RESAMPLE EVERY 1s FOR 1h
BEGIN
SELECT sum(price) INTO hourly FROM mqtt_consumer GROUP BY time(1h), *
END

If I’ve read the documentation* right, that should create a new value every second (“EVERY 1s”) and calculate the sum over the past hour (“FOR 1h”).

But, if I then search for calculated values, I get just a single result:

select * from hourly
name: hourly
time host sum topic

1555426800000000000 systemy.customer.net 1.500003 PBX/Test

Waiting several seconds, or even a few hours, results in no further values being added to the “hourly” table.

Can anyone see what I’m doing wrong, or point me at an alternative solution?

*I’m using InfluxQL Continuous Queries | InfluxDB OSS v1 Documentation as my guide in all this

Thanks,

Antony.

2

Hi @Pooh welcome ,
I will try to answer a part of your question …

You will have only one record in the hourly table (measurement) ,
for each hour.
The timestamp of the record will be the current hour , that is at 09:30 , the timestamp will
be 2019-04-17T09:00:00Z.
Every second the same record will be updated until the hour changes because
the timestamp is the same as you can see in my example

> precision rfc3339
> CREATE CONTINUOUS QUERY hourly ON telegraf 
    RESAMPLE EVERY 1s FOR 1h BEGIN SELECT sum(usage_idle) 
    into hourly from cpu group by 
    time(1h) end

> select * from hourly ;

name: hourly
time                 sum
----                 ---
2019-04-17T09:00:00Z 46920.55333031349
> select * from hourly ;
name: hourly
time                 sum
----                 ---
2019-04-17T09:00:00Z 47215.04350891139
>
3

Okay, so how do I rewrite the query so that it inserts one new value every
second?

What I need is:

At 09:00:00 I get a sum of the raw measurements from 08:00:00 to 08:59:59
At 09:00:01 I get a sum of the raw measurements from 08:00:01 to 09:00:00
At 09:00:02 I get a sum of the raw measurements from 08:00:02 to 09:00:01
At 09:00:03 I get a sum of the raw measurements from 08:00:03 to 09:00:02

(I don’t care if I’ve got those timings out by 1 second, but the point is that
I need a continuous series of the moving sum, not a single summary value per
hour.)

Thanks,

Antony.

4

You can use group by (1s),*

it can have an impact on the performance ,
maybe you can consider writing this data to a different retention policy ?

5

You can use group by (1s),*

That does indeed give me one new value per second, but the value is the sum
over the previous second, not the sum over the previous hour.

it can have an impact on the performance ,
maybe you can consider writing this data to a different retention policy ?

As said in my original post, I’m happy to consider alternative ways of
achieving this if a continuous query is not the right (or best) solution.

How can I set up a retention policy to give me second-by-second values of the
sum of the measurements over the previous hour?

> precision rfc3339
> CREATE CONTINUOUS QUERY hourly ON telegraf

    RESAMPLE EVERY 1s FOR 1h BEGIN SELECT sum(usage_idle)
    into hourly from cpu group by
    time(1h) end

You still have “group by time(1h)” there…

> select * from hourly ;

name: hourly
time                 sum
----                 ---
2019-04-17T09:00:00Z 46920.55333031349

> select * from hourly ;

name: hourly
time                 sum
----                 ---
2019-04-17T09:00:00Z 47215.04350891139

The identical timestamps mean that I cannot show a continuous curve of the sum
values in Grafana.

Antony.

6

Hi Antony ,

I think a CQ cannot give you the result you want ,
to have the sum of 1h , the group by must be 1h and
to have a result every second the group by must be 1s ,

I don’t know if it is possible in FLUX , have you looked at FLUX ?
Get started with Flux | Flux 0.x Documentation