InfluxDB subquery in WHERE clause

Question

Having some issues wrapping my brain around this one. I have two tables in InfluxDB 1.8.x, here's the relevant data layout

table a
-------------------------------------------
|time               |hostname|device_cache|
|6/14/2022 9:00:30PM|device1 |dm-4        |
|6/14/2022 9:00:30PM|device2 |dm-4        |
|6/14/2022 9:00:30PM|device3 |dm-8        |
-------------------------------------------

table b
-----------------------------------------------------
|time               |hostname|diskiodevice|diskiola1|
|6/14/2022 9:00:30PM|device1 |dm-0        |8        | 
|6/14/2022 9:00:30PM|device1 |dm-4        |7        |
|6/14/2022 9:00:30PM|device3 |dm-3        |9        |
|6/14/2022 9:00:30PM|device2 |dm-2        |8        |
|6/14/2022 9:00:30PM|device3 |dm-8        |15       |
|6/14/2022 9:00:30PM|device2 |dm-4        |9        |
|6/14/2022 9:00:30PM|device3 |dm-3        |1        |
-----------------------------------------------------

So, what I am trying to do is get all the diskiola1 values for the diskiodevices from table b that are defined as device_cache items from table a for a particular hostname entry. Here's what I've tried:

SELECT max("diskiola1")
FROM "table b"
WHERE hostname = 'device1'
AND
time > now() - 10m
AND
"cache_device" IN
( Select distinct("device_cache") as "cache_device" FROM "table a" WHERE hostname = 'device1')
GROUP BY time(20s)

My goal is to have this as a time series in a graph to show the values of diskiola1 for a given host over a period of time for only the device_cache items. This data is given to me to work with, I really can't modify it unfortunately.

Anyone see where I'm going wrong? The error I receive is ERR: error parsing query: found IN, expected ;

user_18488955 · Answer

Unfortunately InfluxQL doesn't support IN operator or for the foreseeable future (see details here). InfluxQL doesn't support JOIN operation either (see details here).

Seems your "table_a" is more like a mapping table while "table_b" is storing the time series data actually. Assuming hostname is a tag while device_cache is a field for "table a"; hostname is a tag while diskiodevice and diskiola1 are fields for "table b". You could try enabling Flux and try following sample codes:

aDistinctDeviceCache = from(bucket:"yourDatabaseName/yourRentionPolicyName")
|> range(start: 2018-05-22T23:30:00Z, stop: 2018-05-23T00:00:00Z)   // start and stop can be changed
|> filter(fn:(r) => r._measurement == "table a" and r.hostname == "device1" and r._field == "device_cache")
|> distinct()

bDevice1 = from(bucket:"yourDatabaseName/yourRentionPolicyName")
|> range(start: -10m)
|> filter(fn:(r) => r._measurement == "table b" and r.hostname == "device1")
|> rename(columns: {diskiodevice: "device_cache"})

maxDiskiola1ForDevice1 = 
join(tables:{aPlus:aDistinctDeviceCache, bPlus:bDevice1}, on:["hostname", "device_cache"])
|> window(every: 20s)
|> max("diskiola1")
|> yield()

This will first grab distinct values from "table_a" and then rename some field of "table_b" so that we can join the two tables together in the last step.

Here are some more tips to convert your InfluxQL to Flux and convert your subqueries.

InfluxDB subquery in WHERE clause

1 Answers