InfluxQL to Flux

1.1k views Asked by At

What is the equivalent of this query

select sum("count") from "measurement_name" where time<now() and time>now()-4d group by time(100s),"source"

in Flux? I have tried

from(bucket:"metrics/default_metrics") 
 |> range(start: -4d) 
 |> filter(fn: (r)=> r._measurement == "measurement_name")
 |> group(columns: ["source"]) 
 |> window(every: 100s) 
 |> drop(columns:["_start","_stop","_measurement","column_a","column_b"]) 
 |> yield()

and

from(bucket:"metrics/default_metrics") 
 |> range(start: -4d) 
 |> filter(fn: (r)=> r._measurement == "measurement_name") 
 |> window(every: 100s) 
 |> group(columns: ["source"])  
 |> drop(columns:["_start","_stop","_measurement","column_a","column_b"]) 
 |> yield()

but they all seem to yield different results

This is grouping by time_interval = 100s and source. Supposedly, the grouping by time (and the sum aggregation implicitly?) is done using the window function from Flux but the result from the InfluxQL query (select...) are:

name: measurement_name
tags: source=source_name
time                sum
----                ---
1601022500000000000 39
1601022600000000000 191
1601022700000000000 232
1601022800000000000 145
1601022900000000000 207
1601023000000000000 277
1601023100000000000 160
1601023200000000000 228
1601023300000000000 253
1601023400000000000 167

while the one coming from the Flux queries is

Table: keys: [source]
source:string        _time:time                                 _value:int    _field:string  
----------------------  ------------------------------  --------------------------  -------- 

source_name  2020-09-25T11:46:51.390000000Z                           6              count  
source_name  2020-09-25T11:46:54.124000000Z                           5              count  
source_name  2020-09-25T11:46:57.616000000Z                           6              count  
source_name  2020-09-25T11:46:57.999000000Z                           9              count  
source_name  2020-09-25T11:46:58.064000000Z                           3              count  
source_name  2020-09-25T11:46:58.307000000Z                           6              count  
source_name  2020-09-25T11:47:01.011000000Z                           8              count  
source_name  2020-09-25T11:47:03.634000000Z                           6              count  
source_name  2020-09-25T11:47:03.700000000Z                           8              count  
source_name  2020-09-25T11:47:04.144000000Z                           8              count 

The end goal is to plot this out in Grafana.

Is there also maybe a way to convert back and forth between these two paradigms? Whenever it's possible ofcourse

1

There are 1 answers

0
Phil On BEST ANSWER

You need to include the sum() function explicitly. I suggest using aggregateWindow() too.

from(bucket:"metrics/default_metrics") 
 |> range(start: -4d) 
 |> filter(fn: (r)=> r._measurement == "measurement_name")
 |> group(columns: ["source"])
 |> aggregateWindow(every: 100s, fn: sum)
 |> drop(columns:["_start","_stop","_measurement","column_a","column_b"]) 
 |> yield()

Generally, group does not preserve sort order but aggregateWindow sorts by time before doing its work. That's something to look for. Additionally, the time bounds might not line up exactly between the Flux and InfluxQL queries. I expect them to, but double check it.