Continuous Queries without GROUP BY time Clause

Fluxlang
,
1

Hello,

I’ve been using Influx now for a few months, Version 1.81.
I started now to use continuous queries, but i’m having the issue that i dont know whether they are executed. Could anyone give me some help with that?
I am using three kinds of RP: 14days, 90days and INF.
Data in the 14 days RP is of high volume, while the 90day RP only consists of:

  1. downsampled 14day data
  2. Data which only occurs 1-5 times a day.

To easy display the data in grafana i wanted to duplicate my data of the 90d into the 14day RP.
Therefore i am using the following CQ:

CREATE CONTINUOUS QUERY test_1 ON mydatabase
RESAMPLE EVERY 2h FOR 7d
BEGIN
SELECT * INTO mydatabase.rp_14.mymeasurement FROM mydatabase.rp_90.mymeasurement GROUP BY *
END

Although it seems not to work, no data is transfered, not even newly inserted one.

Also other queries like:

CREATE CONTINUOUS QUERY test_2 ON mydatabase
BEGIN
SELECT mean(*) INTO mydatabase.rp_90.mymeasurement FROM mydatabase.rp_14.mymeasurement GROUP BY *, time(1d)
END

are not executed.

Any suggestion or help on that issue?
Is there a other possible to achive what i am wanting?

2

About the actual execution of the queries, I simply think the queries are failing, you can enable the InfluxDB _internal monitoring.
The data you are looking for are stored in the cq measurement

About creating CQ without GROUP BY time(), that’s not possible continuous queries require it.

Possible solution #1 - aggregate more often, even with partial result

About showing the data in Grafana, I personally don’t mix different RP, mostly because they have different scopes ad I see no point in mixing them.
A solution could be to run the aggregation from 14d → 90d more often, maybe something like

{...}
RESAMPLE EVERY 30m FOR 24h
BEGIN
{...} INTO mydatabase.rp_14.mymeasurement FROM mydatabase.rp_90.mymeasurement GROUP BY *,time(6h)
END

This will re-aggregate the last 24h of data in the rp_90 every 30m, therefore you will have data updated every 30 minutes, but all on the same RP. some points will have partial results at first but will be updated as the time passes until their values finally freeze.

Possible solution #2 - multiple queries (if possible)

Another way could be to run multiple queries on different RP (Grafana allows that), but I’m unsure about the result since it will produce different series for each query run, therefore the result might look splitted. (also you might want to avoid getting data for the same interval form both RPs).

Possible solution #3 - split the charts/dashboards

Not a real solution, but just use different charts or dashboards for the real-time/short term analysis and for the historical/long therm one.
Which to me comes natural since their purpose is different

Possible solution #4 - parameterized the RP

In Grafana you can parameterize almost everything, RP included. (idk about other tools)
You could parametrize all your queries and let the user decide which data he wants to interrogate using a variable.

1 Like