Retention policies and continuous queries

Hey everyone!

I’m new to InfluxDB and I need a little bit of help with continuous queries and retention policies…

What I’m trying to do is that I have one database in InfluxDB let’s call it “input” and another database which we can call “aggregated”. What I want is to aggregate and transport data from input database where they are ingesting every 0.5sec to aggregated database let’s say 1 datapoint for 30 sec., 1 datapoint every 20sec. and 1 datapoint every 10sec within a minute for example.

I have three CQs that look like:

cq_30sec CREATE CONTINUOUS QUERY cq_30sec ON input BEGIN SELECT mean(*) INTO aggregated.one_hour.cpu_load_short FROM input.autogen.cpu_load_short WHERE time < now() - 30s GROUP BY time(30s) END
cq_20sec CREATE CONTINUOUS QUERY cq_20sec ON input BEGIN SELECT mean(*) INTO aggregated.one_hour.cpu_load_short FROM input.autogen.cpu_load_short WHERE time < now() - 20s GROUP BY time(20s) END
cq_10sec CREATE CONTINUOUS QUERY cq_10sec ON input BEGIN SELECT mean(*) INTO aggregated.one_hour.cpu_load_short FROM input.autogen.cpu_load_short WHERE time < now() - 10s GROUP BY time(10s) END

I visualize it with Grafana where I have two graphs - first for input db where I have datapoints per 0.5sec and second one for aggregated where I have 1 datapoint per 10sec.

What I want to accomplish is to transport datapoints from iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii to --> i i i i i i i i i i i iiiiiii if we imagine that letters i are datapoints.

Let me know if is any further clarification needed.

Any help would be appreciated! Thank you for any reply!!

Yes, what you describe is very sound/valid, but which part isn’t working ?

https://docs.influxdata.com/influxdb/v1.8/guides/downsample_and_retain/#results

Either the cq somehow isn’t running, is pointing from/to the wrong place, or new data (or maybe not enough new data) has been received yet. Unless I’ve misunderstood what the issue is?

AFAIK you don’t need to add the
WHERE time < now() - 30s
in continuous queries. That is added automatically by influx when the query is executed.

1 Like