project Utilities / Python Influxdb Downsample avatar

utilities/python_influxdb_downsample#24: SQL Support

Issue Information

Issue Type: issue
Status: opened
Reported By: btasker
Assigned To: btasker

Milestone: backlog
Created: 16-Feb-23 13:01


InfluxDB IOx works with SQL, so it'd be useful to look at having support for using SQL.

Toggle State Changes


assigned to @btasker

Thinking this through then:

One thing to watch for, is SQL queries will return wide rather than long tables.

So we'd need a way to identify which columns are fields and which should be considered tags.

The best route is probably to allow that to be specified in YAML - that'd allow jobs to convert fields to tags if needed.

Something like

measurement: sensors
query_type: sql
   - pressure
   - temperature
   - host
   - unit

Which'd then be translated into something like

FROM sensors

I need to check, but we should be able to have the query figure out the time window for us

  date_bin(interval '15 min', time, timestamp '1970-01-01T00:00:00Z') as timebin,
FROM sensors

What we won't be able to do is have the results returned in individual Flux-esque tables.

We can't group by timebin in the query because then we have to use aggregate functions on anything that is'nt part of the group key (pushing the processing overhead onto IOx - as noted in #22 that's probably ok really, but not in keeping with the point of this project).

So, we'd need some processing logic which handles the physical grouping on our side.

There are probably a couple of ways to go at that

  • Just iterate over, building per-time value lists
  • Iterate through each, applying the aggregates as we go

One is (obviously) more efficient, but will need significant changes to existing logic (assuming we want to keep it DRY).

mentioned in issue #26