How do I get at values other than a filtered field in Flux?

user_616877 0
Viewed 1377

Looking at "Mistake 3" at the best practices for InfluxDb 2.0: https://www.influxdata.com/blog/data-layout-and-schema-design-best-practices-for-influxdb/

Mistake 3: Making ids (such as eventid, orderid, or userid) a tag. This is another example that can cause unbounded cardinality if the tag values aren’t scoped.

Solution 3: Instead, make these metrics a field."

This is all fair and dandy, but then how do I draw out results? It doesn't seem to be described anywhere. All examples show filtering by tag, not by a field. So if I am logging "SensorId, Temp, Humidity" and these are all fields, how do I get the Temp graph for SensorId 97?

In order to filter by field in Flux you'd write:

from(bucket: "TestBucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "sensor" and r["_field"] == "sensorid" and r["_value"]  == 97)

But now I'm stuck with just sensorid values. The temp and hum values have vanished. I am trying to wrap my head around Flux, but it is hard given that you write data as a record and this is what you have in your head when designing your solution. But then in Flux all of a sudden the columns seem to vanish as you try to narrow your result set.

influxdb
1 Answers

Pivot is your friend in these cases. So this bit of code:

|> pivot(
    rowKey:["_time"],
    columnKey: ["_field"],
    valueColumn: "_value"
  )

transforms a result table to use the fields as column names and _value as value for those columns. I'm still left wondering if this is more efficient than using a tag for an id column. Or, more precisely, at what point one becomes more efficient than the other.

user_6168770
Related