Downsampling data in InfluxDB

InfluxDB 2
1

Hi Guys,

I am using Influxdb to store the performance data of several systems. The database looks somewhat like this:

 ==================================================
 | time       | Connection  | Name         | Value|
 ==================================================
 | 1234554321 | 192.168.2.2 | Parameter_1  | 10   |
 | 1234554322 | 192.168.2.2 | Parameter_2  | 20   |
 | 1234554323 | 192.168.2.2 | Parameter_3  | 30   |
 | 1234554324 | 192.168.3.2 | Parameter_1  | 11   |
 | 1234554325 | 192.168.3.2 | Parameter_2  | 21   |
 | 1234554326 | 192.168.3.2 | Parameter_3  | 31   |
 | 1234554327 | 192.168.2.2 | Parameter_1  | 12   |
 | 1234554328 | 192.168.2.2 | Parameter_2  | 22   |
 | 1234554329 | 192.168.2.2 | Parameter_3  | 32   |
 | 1234554330 | 192.168.3.2 | Parameter_1  | 13   |
 | 1234554331 | 192.168.3.2 | Parameter_2  | 23   |
 | 1234554332 | 192.168.3.2 | Parameter_3  | 33   |
 ==================================================

Now I want to get the average value (Downsample) and store it in a different table like this:

 ======================================================
 | time       | Connection  | Name         | AvgValue |
 ======================================================
 | 1234554333 | 192.168.2.2 | Parameter_1  | 11       |
 | 1234554334 | 192.168.2.2 | Parameter_2  | 21       |
 | 1234554335 | 192.168.2.2 | Parameter_3  | 31       |
 | 1234554336 | 192.168.3.2 | Parameter_1  | 12       |
 | 1234554337 | 192.168.3.2 | Parameter_2  | 22       |
 | 1234554338 | 192.168.3.2 | Parameter_3  | 32       |
 ======================================================

Please help me with a suitable TICK script to do this job.

Thanks in advance.

Cheers,
Subhadeep Datta

3

Hello @sahebdatta,
You can use a continuous query to do this type of downsampling work.

You can’t group by field so you’d have to specify a continuous query for each field key.

CREATE CONTINUOUS QUERY <cq_name> ON <database_name>
BEGIN
SELECT mean("Parameter_1")  INTO <destination_measurement> FROM <measurement> [WHERE <stuff>] GROUP BY time(1h)

Otherwise you could downsample all your fields quite easily with a task in influxdb 2.x.

option task = {
  name: "task-name",
  every: 6h
}

data = from(bucket: "<my source bucket>")
  |> range(start: task.every)
  |> filter(fn: (r) => r["_measurement"] == "<my measurement>")
  |> aggregateWindow(every: 1h, fn: mean, createEmpty: false)

data 
|> to(bucket: "destination-bucket")
1 Like
4

Hi @Anaisdg,

Thanks for the response. I’ll give it a try.
Is it not possible to do this using kapacitor tick-script?

5

If you’re using InfluxDB 1.x and Kapacitor then yes, you can do this with TICK. You can use either a stream or batch node.

Kapacitor as a continous query engine

As far as I know, InfluxDB2 does not support subscriptions, so Kapacitor won’t work for that. I think there is a work around but from what i remember it essentially requires two databases.

1 Like