Using Pivot in Flux on InfluxDB data - what am I doing wrong?

Fluxlang
,
1

I have the following query:

from(bucket: "monitoring")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "powermonitor")
|> filter(fn: (r) => r.serial == "SERIAL")
|> filter(fn: (r) => (r._field == "power"))
|> keep(columns: ["_time","_value","phase"])
|> yield(name: "Pre")
|> pivot(rowKey:["_time"], columnKey: ["phase"], valueColumn: "_value")
|> yield(name: "Post")
//|> map(fn: (r) => ({r with _value: r.a + r.b + r.c, _time: r._time }))

Which results in the following output at “Pre”:

image
image717×554 25.1 KB

and the following output at “Post”:

image
image693×552 24 KB

I expected the pivot function to take the “phase” values and populate them into the “a”, “b”, “c” and “net” columns in the same time rows, but they still have their own rows?

Can anyone tell me what I’m doing wrong? (I’ve also tried putting a “group()” in between the “keep” and “Pre” yield, which reduces the number of tables in the “Pre” output to just one, but the “Post” output is still the same).

Thanks :slightly_smiling_face: :upside_down_face:

2

Hi @tjs000,
This is really odd what version of InfluxDB are you using?

Your flux script looks correct.

3

image

By the looks of this, v2.4.0 ? This is a fresh install as of ~1 month ago.

I’ve used pivot previously and didn’t get this result which is why I’m so confused.

4

Still getting used to the different types of reply…

This is still happening, I’m happy to provide basically whatever I can to help diagnose what’s going on because it’s kind of irritating… :smiley:

5

Hi @tjs000,
Sorry for the late reply. I have done some further digging and still can’t quite work out the issue. @Anaisdg do you have any idea what might be happening here?

6

I thought I’d take a stab at this but I’m not able to reproduce it.
With the line protocol:

powermonitor,serial=SERIAL,phase=a power=80 1668451105
powermonitor,serial=SERIAL,phase=b power=424 1668451105
powermonitor,serial=SERIAL,phase=c power=1506 1668451105

and the query:

from(bucket: "test")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "powermonitor")
|> filter(fn: (r) => r.serial == "SERIAL")
|> filter(fn: (r) => (r._field == "power"))
|> keep(columns: ["_time","_value","phase"])
|> pivot(rowKey:["_time"], columnKey: ["phase"], valueColumn: "_value")

I get the expected result (on Influx cloud)

image
image1315×303 18.1 KB

Thanks,
Tom

7

Still have the same issue.

Had to find a way to work around it, did this by changing the order in which I processed the data (that has other benefits as well).

Set up an aggregation task in InfluxDB that runs every 5 minutes to aggregate the data, then used literally the identical pivot query on the new measurement and it works fine to pull the required data out in the right format.

I still don’t have a clue why it doesn’t work on the original data, it clearly should as demonstrated by Tom above, yet it doesn’t so :man_shrugging:

I guess to replicate this workaround in the future, do an arbitrary aggregation (ie a “mean” with a window period covering 1 data point) on the data then pivot it. It might work, it might not, who knows…

Thanks for trying to help all…

T