Converting from InfluxQL to SQL (flightSQL) in InfluxDB 3.0

InfluxDB 2
, , ,
1

Hi,
Up until now I was selfhosting InfluxDB 2 but we are now migrating to InfluxDB 3 on AWS.

As I understand it I cannot use InfluxQL. So I have this query which I’m trying to convert to SQL to be able to use the flightSQL plugin in Grafana:
SELECT "$Mesura" FROM /^$Ciutat/ WHERE ("ID" =~ /^$IDs/) AND time >= '${__from:date}' and time < '${__to:date}' GROUP BY "ID"

To start easy I have tried with:
SELECT regs,“ID” FROM iox.“Conca”

I have deleted the variables, so in this case $Mesura=regs and $Ciutat=Conca

this works, but when I try to group BY “ID” I get:
SELECT regs,"ID" FROM iox."Conca" GROUP BY "ID"

flightsql: rpc error: code = InvalidArgument desc = Error while planning query: Error during planning: Projection references non-aggregate values: Expression iox.Conca.regs could not be resolved from available columns: iox.Conca.ID

2

I have also tried querying from influxdb.
The moment I add group by:

SELECT *
FROM "Conca"
WHERE
time >= now() - interval '30 days'
AND
("regs" IS NOT NULL)
GROUP BY "ID"

I get the error:

executing query: failed to create Flight record reader: arrow/flight: could not create flight reader: arrow/ipc: could not read schema from stream: arrow/ipc: could not read message schema: rpc error: code = InvalidArgument desc = Error while planning query: Error during planning: Projection references non-aggregate values: Expression Conca.address could not be resolved from available columns: Conca.ID

However if I don’t add the groupby statement and I use the query modifier everything works. But only on the inflkuxdb’s data explorer window…

Could somebody take a look? Because between this issue and the fact that InfluxQL is not working either I’m paying AWS for something unusable :smiling_face_with_tear:

3

I suggest you have a look at a SQL tutorial, even a simple one (like the W3 one)

When using GROUP BY you can’t reference columns unless they are either a grouping column or use an aggregate function.

a working query should be the following one

SELECT 
   "ID"
  ,COUNT(regs) AS "Whatever"
FROM iox."Conca"
GROUP BY 
  "ID"
4

Hi @Giovanni_Luisotto,

I think my issue is that with InfluxQL the time was implicit, with SQL you have to specifically declare it.

Since I want to create time series graphs I have to select time:

SELECT 
  "ID"
  ,regs as reg
  ,time
FROM iox."Conca"
WHERE time >= now() - interval '30 days'
ORDER BY time ASC

I obviously don’t want to agregate data because I want to see the evolution of “regs” over time.
However, I need to group it by ID. Because in the time series graph I want to have the evolution of regs over time for each ID. So the legend will contain each ID.

I have taken a look at the W3 tutorial but they only mention using GROUP BY with agregate functions. And from your message I supose I need to use the first option:

When using GROUP BY you can’t reference columns unless they are either a grouping column

Do you have a tutorial on how to create a grouping column?

5

I was wondering how to do time grouping and similar stuff using SQL (I’ve not tried it on InflxDB yet) and found this, It covers most use cases, like how to group by time intervals.

That’s not something you create, is just the column you group on…

Generally speaking, you can’t group by something without then using aggregate functions, that’s the whole point of grouping… (and that’s valid for all languages (InfluxQL, SQL, Flux…)
So you are either:

  • Looking for a flat SELECT
    SELECT time,ID,regs FROM ___ WHERE time __

  • Looking for an aggregate (sample form the link above)
    /* 7. Calculating the mean temperature every hour across all sensors*/ SELECT DATE_BIN(INTERVAL '1' minute, time, TIMESTAMP '2022-01-01 00:00:00Z') AS time, avg("temperature") as mean FROM "airSensors" GROUP BY 1

1 Like