Join Tables in InfluxDB 2.0

InfluxDB 2
1

Hi All,
I have question on how to join tables in Flux.
I have one ‘bucket’ EnergyMeter which contains the readings (_measurement) of three energy meters (Normal, Dal and Gas) for a certain timestamp (_time).
InfluxDB now has a table for each of the three meters (0, 1 & 2).
I would like to have this information in one table, so per _time the values for the three meters. Think I should do that with join, correct?
This is my code:

from(bucket: "EnergyMeter")
  |> range(start: -14d)
  |> filter(fn: (r) => r["_measurement"] == "Energy")
  |> filter(fn: (r) => r["_field"] == "Dal" or r["_field"] == "Gas" or r["_field"] == "Normal")
  |> drop(columns: ["_start", "_stop", "_measurement", "table", "_result"])

To join the tables (0, 1, 2) I think I should add something like:

|> join(tables: {d1: "Dal", d2: "Gas", d3: "Normal"}, on: ["_time"])

but I’m getting confused over the table/column naming, and errors…

Screenshot 2021-04-20 at 09.34.44
Screenshot 2021-04-20 at 09.34.442166×308 30.1 KB

Any suggestions?
PPee

2

I think join supports only 2 tables for now. Also as you want to join by time i suggest to use experimental.join that is way faster

1 Like
3

Hi MzazM,

Is there a way to join 3 tables up? Is it impossible? I need to join on time and one more thing.

Thanks

4

You can do consecutive joins to join 3 or more tables. For joining on time do not use experimental.join but use the classic one.

5

Hi,

How do I do this? Here is my queries so far, but it returns nothing. If I join only on two of the metrics, it works well. I want a result with all of _value_cpu, _value_ram, and _value_disk.

Thanks

    cpu = ... // my query
    ram = ... // my query
    disk = ... // my query

    cpuRam = join(
      tables: {cpu: cpu, ram: ram},
      on: ["_time", "region"]
    )

    join(
      tables: {cpuRam: cpuRam, disk: disk},
      on: ["_time", "region"]
    )
6

Hello @Nam_Giang,
To join 3 tables you’ll have to join the first two, store the result in a variable and then join that with the third.

You can also consider using fieldsAsCol() function or grouping by time.

1 Like
7

Thanks Anaisdg,

How do I aggregate on this result over the three values?

8

Here is what I’ve got, this is the correct way right?

      |> group(columns: ["region", "_field"])
      |> aggregateWindow(every: 86400s, fn: sum, createEmpty: false)
      |> schema.fieldsAsCols()
      |> keep(columns: ["_time", "cpu", "memory", "disk"])

This is life saver, thank you very much!

1 Like
9

Awesome! you’re so welcome :slight_smile: