Convert influx1 query to flux (and draw graphs with chart.js)

Dim · March 24, 2021, 7:39am

Hi.
I have an influxdb v1 database with data from several weather stations. I use D3.js to make the graphs for my site.
Currently my query is :

select round(wind_speed*1.94384449*100)/100 as wind_speed, 
round(wind_gust*1.94384449*100)/100 as wind_gust, 
round(wind_lull*1.94384449*100)/100 as wind_lull, 
wind_direction 
from weather_data where "device_sn" = 'windsurf01' or "device_sn" = 'SK-00019693' or "device_sn" = 'SK-00000732' 
and time >= now() -1h 
group by "device_sn"

I am using round() to round some fields in 2 decimal places.

The result is:

{"results":[{"statement_id":0,"series":[{"name":"weather_data","tags":{"device_sn":"SK-00000732"},"columns":["time","wind_speed","wind_gust","wind_lull","wind_direction"],"values":[["2021-03-24T06:19:10Z",12.25,18.6,6.08,7],["2021-03-24T06:20:10Z",11.22,18.08,7.39,5],["2021-03-24T06:21:10Z",11.18,15.55,6.69,13],["2021-03-24T06:22:10Z",8.36,13.65,4.96,4],["2021-03-24T06:23:10Z",12.6,18.6,5.31,9],["2021-03-24T06:24:10Z",11.53,15.82,6.43,2],["2021-03-24T06:25:10Z",12.27,23.54,4.08,2],["2021-03-24T06:26:10Z",8.84,14.17,4.61,3],["2021-03-24T06:27:10Z",8.94,12.07,4.51,353],["2021-03-24T06:28:10Z",9.62,12.07,5.48,15],["2021-03-24T06:29:10Z",12.93,21.38,7.31,13],["2021-03-24T06:30:10Z",12.05,19.9,5.48,356],["2021-03-24T06:31:10Z",12.15,15.73,9.47,356],["2021-03-24T06:32:10Z",8.13,13.82,3.83,355],["2021-03-24T06:33:10Z",14.91,22.94,6.08,357],["2021-03-24T06:34:10Z",12.75,18.16,7.64,354],["2021-03-24T06:35:10Z",11.04,18.78,5.66,0],["2021-03-24T06:36:10Z",10.32,17.3,5.03,12],["2021-03-24T06:37:10Z",8.38,14,4.08,358],["2021-03-24T06:38:10Z",11.37,18.6,5.48,354],["2021-03-24T06:39:10Z",12.03,19.38,4.86,359],["2021-03-24T06:40:10Z",11.72,15.9,5.03,2],["2021-03-24T06:41:10Z",12.11,16.68,7.46,1],["2021-03-24T06:42:10Z",7.89,11.22,3.65,358],["2021-03-24T06:43:10Z",9.89,14.52,4.96,5],["2021-03-24T06:44:09Z",9.1,14.68,6.26,359],["2021-03-24T06:45:09Z",10.22,14.07,4.18,342],["2021-03-24T06:46:09Z",13.08,17.03,7.56,353],["2021-03-24T06:47:09Z",11.47,16.95,7.31,3],["2021-03-24T06:48:14Z",16.97,23.11,10.77,5],["2021-03-24T06:49:14Z",14.58,24.51,5.91,2],["2021-03-24T06:50:14Z",14.15,20.76,5.03,357],["2021-03-24T06:51:14Z",12.91,19.21,6.26,5],["2021-03-24T06:52:14Z",13.49,18.68,7.46,358],["2021-03-24T06:53:14Z",10.83,14.95,5.21,356],["2021-03-24T06:54:14Z",10.5,17.2,5.91,3],["2021-03-24T06:55:14Z",8.55,12.87,4,5],["2021-03-24T06:56:14Z",9.89,16.5,5.83,4],["2021-03-24T06:57:14Z",11.53,15.82,5.03,3],["2021-03-24T06:58:14Z",10.42,14.25,7.08,9],["2021-03-24T06:59:14Z",9.43,13.55,5.73,19],["2021-03-24T07:00:14Z",11.7,17.63,4.68,10],["2021-03-24T07:01:14Z",10.26,13.47,2.18,26],["2021-03-24T07:02:14Z",11.33,16.33,5.73,11],["2021-03-24T07:03:14Z",10.69,16.95,6.86,0],["2021-03-24T07:04:14Z",13.49,17.2,9.12,351],["2021-03-24T07:05:14Z",12.58,18.25,7.21,350],["2021-03-24T07:06:14Z",11.47,15.03,7.31,8],["2021-03-24T07:07:14Z",11.22,14.85,7.21,11],["2021-03-24T07:08:14Z",9.25,17.48,5.38,346],["2021-03-24T07:09:14Z",10.94,17.48,6.86,359],["2021-03-24T07:10:13Z",10.22,15.55,6.26,7],["2021-03-24T07:11:13Z",9.54,15.38,5.83,8],["2021-03-24T07:12:13Z",13.65,19.73,8.61,12],["2021-03-24T07:13:13Z",12.54,18.16,4.86,7],["2021-03-24T07:14:13Z",9.87,14.35,4.86,13],["2021-03-24T07:15:13Z",8.3,13.65,2.7,18],["2021-03-24T07:16:13Z",13.04,18.68,5.73,13],["2021-03-24T07:17:13Z",14.75,18.86,11.22,8],["2021-03-24T07:18:13Z",11.57,15.65,3.73,6]]},{"name":"weather_data","tags":{"device_sn":"SK-00019693"},"columns":["time","wind_speed","wind_gust","wind_lull","wind_direction"],"values":[["2021-03-24T06:19:20Z",13.9,16.06,12.23,345],["2021-03-24T06:20:20Z",13.74,16.21,12.52,355],["2021-03-24T06:21:20Z",12.62,14.62,10.92,5],["2021-03-24T06:22:20Z",13.1,16.62,9.37,357],["2021-03-24T06:23:20Z",16.58,17.84,14.38,355],["2021-03-24T06:24:20Z",14.75,16.79,12.13,359],["2021-03-24T06:25:20Z",13.35,15.05,11.88,348],["2021-03-24T06:26:20Z",14.5,16.21,11.88,355],["2021-03-24T06:27:20Z",13.22,15.82,11.1,0],["2021-03-24T06:28:20Z",15.14,18.87,12.42,353],["2021-03-24T06:29:20Z",14.35,16.66,12.03,358],["2021-03-24T06:30:20Z",14.71,17.71,11.95,351],["2021-03-24T06:31:20Z",17.73,20.41,15.05,352],["2021-03-24T06:32:20Z",17.3,20.2,12.01,350],["2021-03-24T06:33:20Z",18.68,21.05,14.75,344],["2021-03-24T06:34:20Z",16.83,19.83,14.68,350],["2021-03-24T06:35:20Z",16.46,18.39,14.7,353],["2021-03-24T06:36:20Z",16.48,18.49,12.17,353],["2021-03-24T06:37:20Z",17.49,19.48,12.42,340],["2021-03-24T06:38:20Z",17.55,19.21,13.82,341],["2021-03-24T06:39:20Z",13.65,16.1,11.94,353],["2021-03-24T06:40:20Z",14.79,17.61,11.97,352],["2021-03-24T06:41:20Z",14.5,15.88,12.36,348],["2021-03-24T06:42:20Z",13.24,15.01,11.12,348],["2021-03-24T06:43:20Z",12.58,14.42,10.42,347],["2021-03-24T06:44:20Z",12.25,14.38,10.83,352],["2021-03-24T06:45:20Z",14.23,18.97,10.32,345],["2021-03-24T06:46:20Z",15.71,18.62,13,358],["2021-03-24T06:47:20Z",16.93,22,12.23,350],["2021-03-24T06:48:20Z",16.81,21.58,12.05,357],["2021-03-24T06:49:19Z",14.97,17.3,11.74,357],["2021-03-24T06:50:19Z",16.58,20.14,13.41,345],["2021-03-24T06:51:19Z",16.48,19.19,13.51,357],["2021-03-24T06:52:19Z",15.2,20.12,11.94,357],["2021-03-24T06:53:20Z",14.07,16.99,10.48,352],["2021-03-24T06:54:19Z",11.57,13.16,9.78,354],["2021-03-24T06:55:19Z",12.21,14.52,10.15,355],["2021-03-24T06:56:19Z",16.5,20.1,14.05,351],["2021-03-24T06:57:19Z",15.4,19.32,13.22,355],["2021-03-24T06:58:19Z",13.12,16.35,10.63,0],["2021-03-24T06:59:19Z",12.71,14.66,10.48,8],["2021-03-24T07:00:19Z",14.13,15.65,12.23,348],["2021-03-24T07:01:19Z",14.09,16.13,12.56,341],["2021-03-24T07:02:19Z",16.06,19.42,13.41,349],["2021-03-24T07:03:19Z",13.3,15.32,10.69,350],["2021-03-24T07:04:19Z",14.56,16.37,11.97,0],["2021-03-24T07:05:19Z",13.7,16.7,10.77,352],["2021-03-24T07:06:19Z",13.65,15.45,11.37,339],["2021-03-24T07:07:19Z",13.57,14.99,10.94,350],["2021-03-24T07:08:19Z",14.68,16.33,12.21,354],["2021-03-24T07:09:19Z",16.08,18.27,13.78,357],["2021-03-24T07:10:19Z",14.44,16.41,12.27,347],["2021-03-24T07:11:19Z",12.05,14.37,10.56,358],["2021-03-24T07:12:19Z",13.02,16.21,9.97,349],["2021-03-24T07:13:19Z",14.07,16.08,12.56,351],["2021-03-24T07:14:19Z",13.45,15.34,11.39,355],["2021-03-24T07:15:19Z",12.17,13.74,9.21,355],["2021-03-24T07:16:19Z",16.27,19.24,14.11,353],["2021-03-24T07:17:19Z",13.82,15.57,9.58,0],["2021-03-24T07:18:19Z",13.82,16.19,11.16,346]]}]}]}

Now I send the data also to an influxdb v2 and try to query them.
What I want is to get the v2 data with flux, in a form that chart.js can read or at least the same as the v1 result.

from(bucket: "weather-data")
  |> range(start: -1h)
  |> filter(fn: (r) => r["_measurement"] == "weatherdata")
  |> filter(fn: (r) => r["_field"] == "wind_direction" or r["_field"] == "wind_gust" or r["_field"] == "wind_lull" or r["_field"] == "wind_speed")
  |> filter(fn: (r) => r["device_sn"] == "SK-00000732" or r["device_sn"] == "SK-00019693" or r["device_sn"] == "windsurf01")
  |> map(fn: (r) => ({ time: r._time, ---> wind_speed: r._value * 1.94384449 ... <--- }))
  |> group(columns: ["device_sn"])

Can I have some help with the flux query please?

scott · March 24, 2021, 2:43pm

@Dim I think the following will give you what you’re looking for:

from(bucket: "weather-data")
  |> range(start: -1h)
  |> filter(fn: (r) => r["_measurement"] == "weatherdata")
  |> filter(fn: (r) => r["_field"] == "wind_direction" or r["_field"] == "wind_gust" or r["_field"] == "wind_lull" or r["_field"] == "wind_speed")
  |> filter(fn: (r) => r["device_sn"] == "SK-00000732" or r["device_sn"] == "SK-00019693" or r["device_sn"] == "windsurf01")
  |> map(fn: (r) => ({ r with _value: math.round(r._value * 1.94384449) }))
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")

Flux doesn’t pivot fields into rows by default like InfluxQL does. This pivot call should format the data like you would get from InfluxQL.

Dim · March 24, 2021, 2:55pm

Thank you for your help.
I run the above query in Data Explorer with error:
compilation failed: error at @6:39-6:72: expected comma in property list, got DOT
Also, I don’t understand how the math.round(r._value * 1.94384449) will do that math only to wind_spped, wind_gust, wind_lull fields and not to wind_direction.
Additionally the r with makes all the fields to be in result set. But I only need time, device_id, wind_speed, wind_gust, wind_lull and wind_direction.

scott · March 24, 2021, 7:18pm

Sorry @Dim, I didn’t read the query close enough. You could do this instead:

weatherData = from(bucket: "weather-data")
  |> range(start: -1h)
  |> filter(fn: (r) => r["_measurement"] == "weatherdata")
  |> filter(fn: (r) => r["device_sn"] == "SK-00000732" or r["device_sn"] == "SK-00019693" or r["device_sn"] == "windsurf01")

adjustedFields = weatherData
  |> filter(fn: (r) => r["_field"] == "wind_gust" or r["_field"] == "wind_lull" or r["_field"] == "wind_speed")
  |> map(fn: (r) => ({ r with _value: math.round(x: r._value * 1.94384449) }))

windDirection = weatherData
  |> filter(fn: (r) => r["_field"] == "wind_direction")

union(table: {adjustedFields, windDirection})
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")

Edit: added the x parameter key to the math.round() function.

Dim · March 25, 2021, 9:42am

The same error as before:
compilation failed: error at @8:39-8:72: expected comma in property list, got DOT
underlined:
math.round(r._value * 1.94384449)

scott · March 25, 2021, 2:41pm

Oh, sorry. I left out the parameter key in the math.round function:

- math.round(r._value * 1.94384449)
+ math.round(x: r._value * 1.94384449)

Dim · March 26, 2021, 6:59am

This time is worst:
panic: assignment to entry in nil map

I changed the union, because your syntax was wrong:

- union(table: {adjustedFields, windDirection})
+ union(tables: [adjustedFields, windDirection])

Complete query:

import "math"

weatherData = from(bucket: "weather-data")
  |> range(start: -1h)
  |> filter(fn: (r) => r["_measurement"] == "weatherdata")
  |> filter(fn: (r) => r["device_sn"] == "SK-00000732" or r["device_sn"] == "SK-00019693" or r["device_sn"] == "windsurf01")

adjustedFields = weatherData
  |> filter(fn: (r) => r["_field"] == "wind_gust" or r["_field"] == "wind_lull" or r["_field"] == "wind_speed")
  |> map(fn: (r) => ({ r with _value: math.round(x: r._value * 1.94384449) }))

windDirection = weatherData
  |> filter(fn: (r) => r["_field"] == "wind_direction")

union(tables: [adjustedFields, windDirection])
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")

scott · March 26, 2021, 2:34pm

@Dim Nice catch. Sorry about that.

Dim · March 26, 2021, 3:52pm

@scott Yes but the query does not run:
panic: assignment to entry in nil map

scott · March 26, 2021, 4:15pm

That means one of the stream variables isn’t returning any data. Do adjustedFields and windDirection both return streams of tables?

Dim · March 26, 2021, 4:40pm

If I comment out the line
|> map(fn: (r) => ({ r with _value: math.round(x: r._value * 1.94384449) }))
the final result contains all the wanted fields.

Dim · March 28, 2021, 6:38am

Any help?
As I wrote, the query does not run unless I comment the above mentioned line.

scott · March 29, 2021, 4:44pm

may need to:

// ...
|> map(fn: (r) => ({ r with _value: if exists r._value then math.round(x: r._value * 1.94384449) else 0.0 }))

Dim · March 29, 2021, 6:34pm

same error:
panic: assignment to entry in nil map

I think I am going to give up.
Already studying how to migrate to another time-series database that uses the familiar SQL language.

jonathan · April 21, 2021, 7:35pm

I’m sorry for your frustration. I think your query is probably most easily done by using pivot and then map.

select round(wind_speed*1.94384449*100)/100 as wind_speed, 
round(wind_gust*1.94384449*100)/100 as wind_gust, 
round(wind_lull*1.94384449*100)/100 as wind_lull, 
wind_direction 
from weather_data where "device_sn" = 'windsurf01' or "device_sn" = 'SK-00019693' or "device_sn" = 'SK-00000732' 
and time >= now() -1h 
group by "device_sn"

Would become:

from(bucket: "weather-data")
  |> range(start: -1h)
  |> filter(fn: (r) => r["_measurement"] == "weatherdata")
  |> filter(fn: (r) => r["_field"] == "wind_direction" or r["_field"] == "wind_gust" or r["_field"] == "wind_lull" or r["_field"] == "wind_speed")
  |> filter(fn: (r) => r["device_sn"] == "SK-00000732" or r["device_sn"] == "SK-00019693" or r["device_sn"] == "windsurf01")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({r with
    wind_speed: math.round(x: r.wind_speed*1.94384449*100.0)/100.0,
    wind_gust: math.round(x: r.wind_gust*1.94384449*100.0)/100.0,
    wind_lull: math.round(x: r.wind_lull*1.94384449*100.0)/100.0,
  })

I am not sure the reason for the group() call but I don’t think it is necessary so you can likely omit it. If you want the exact same response that influxql would probably do, you can add these to the end:

|> keep(columns: ["_measurement", "wind_speed", "wind_gust", "wind_lull", "wind_direction", "device_sn"])
|> sort(columns: ["_time"])

I think that’s the correct syntax.

Topic		Replies	Views
Converting InfluxQL to Flux with maths functions Fluxlang	2	323	July 14, 2021
How to transform values using map() InfluxDB 2 flux	2	363	March 7, 2023
Calculations with flux (influxdb 2.1.1) InfluxDB 2	0	347	December 5, 2021
[InfluxQL to Flux] show the calculated column in graph Fluxlang influxdb , flux	4	318	May 16, 2022
InfluxQL query to Flux Fluxlang influxql , flux	1	433	April 17, 2023

Convert influx1 query to flux (and draw graphs with chart.js)

Related Topics