Join and filter big measurements with different frequencies

InfluxDB 2
1

I have 2 big measurements with 40+ fields. We want to filter based on these fields with approx 20 fields filter at a time. I did not find any good way to achieve this, hence I have created 2 measurements with these attributes as fields.

One measurement has data in milliseconds and another has it in minutes. It’s not possible to join it as there is no left join.

This is very simple with left join and fill previous. I am clueless here.

Can someone please shed some light on this?

2

Hello @Bhoomi_Desai,
Are you still having problems with mass filtering? Have you considered using regex?

As for joining based on different timestamp precision, I believe you can try using aggregateWindow() or window() function to change the precision of your data before using a join.

Here’s an example of converting my cpu data to s precision.

from(bucket: "my-bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "cpu")
  |> filter(fn: (r) => r["_field"] == "usage_user")
  |> filter(fn: (r) => r["cpu"] == "cpu-total")
  |> limit(n: 15)
  |> aggregateWindow(every: 1s, fn: mean)
  |> fill(column: "_value", usePrevious: true)
3

@Bhoomi_Desai

Assuming your’e running the most recent InfluxDB 2.0 beta, another possibility is:

  1. Filter fields by regex as @Anaisdg suggested.
  2. Use experimental.join().
import "experimental"

t1 = from(bucket: "example-bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "example-measurement" and r._field =~ /someregex/ )

t2 = from(bucket: "example-bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "example-measurement" and r._field =~ /someregex/ )

experimental.join(
  left: t1,
  right: t2,
  fn: (left, right) => ({left with lv: left._value, rv: right._value })
)

Something else you may consider is using the truncateTimeColumn() function to normalize timestamps to a specified unit.

1 Like
4

Thanks for the reply.

Can experimental.join do a left join?

My left-hand side measurement has more data points, I want to do a join by time, and use “previous” values for the missing values of right measurements. Is it possible?

Eg:

Left:
_time: 11:07:01, focus: 0.1, temp: 12
_time: 11:07:02, focus: 0.1, temp: 15
_time: 11:07:10, focus: 0.1, temp: 35

Right:
_time: 11:01:01, type: steel
_time: 11:05:02, type: glass
_time: 11:06:00: type: plastic

I want to filter left measurement for the time where right measurement had steel?

Is it possible?

Right