CODEWITHSUNDEEP
influxdb
JOY
JOY

Reputation: 53

how to join two measurement of influxdb

I want to join two different measurement of same database.how can join two measurement of same database with respect to timestamp in influxdb database. I have checked with below things in influxdb: select hosta.value + hostb.value from cpu_load as hosta inner join cpu_load as hostb where hosta.host = 'hosta.influxdb.orb' and hostb.host = 'hostb.influxdb.org';

But may query is if the two measurement of same database with different name how can i join or how can i implement above query in my case? consider below thing is my use case- assume mydatabase is mydb and there are two measurement one is cpu and another is network:

  {
        "measurement": "cpu",
        "tags": {
            "container": cont_name,                             
        },

        "fields": {
            "Value": 25.78,

        }
    }
    ] 
{
        "measurement": "network",
        "tags": {
            "container": cont_name,                             
        },

        "fields": {
            "Value": 96,

        }
    }
    ]

I want to merge above to measurement and want to get single measurement with both measurements information?

Is it possible to merge two measurements?

another issue: select s1.value from "cpu" as s1 below error is coming: ERR: error parsing query: found AS, expected ; at line 1, char 39?

Upvotes: 1

Views: 20504

Answers (3)

Tallak Tveide
Tallak Tveide

Reputation: 369

So this can be done in FluxQL, though it is not extremely straightforward.

The vvariable is provided by Grafana according to current view.

Explanation: First get both tables and resample to the minimum required amount of data. Then set the _measurement field to match in the two tables. Finally, union(...) the two tables, and sort the union by _time. At this point maybe aggregate again and then pivot to create a table for each time step.

The createEmpty: true in aggregateWindow(...) makes sure you have one record for each time step, and then use fill(...) to fill in missing records with previous ones. This makes it so you dont need to deal with null records that

This worked for me:

import "math"
import "internal/debug"
    
slow = from(bucket: "test")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "slow"
  )
  |> map(fn: (r) => ({ r with _measurement: "fast"}))
  |> aggregateWindow(every: v.windowPeriod, fn: first, createEmpty: true)
  |> fill(usePrevious: true)

fast = from(bucket: "test")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "fast"
  )
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: true)
  |> fill(usePrevious: true)
  
union(tables: [fast, slow])
|> sort(columns: ["_time"])
|> aggregateWindow(every: v.windowPeriod, fn: first, createEmpty: false)
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")

Upvotes: 1

Samuel Toh
Samuel Toh

Reputation: 19268

Q: Is it possible to merge two measurements? A: No. Cross measurement join is not possible through influx's query engine until v1.7.0 where you can use their new flux query language to do so.

Reference:

https://docs.influxdata.com/flux/v0.7/guides/join

Flux allows you to join on any columns common between two data streams and opens the door for operations such as cross-measurement joins and math across measurements.

Prior to v1.7.0 the only way you can achieve cross-measurement join is through writing a script/program. E.g. Using a influxdb driver to read out the data and then manually merge the dataset yourself.

Upvotes: 2

Simon Wheatley
Simon Wheatley

Reputation: 233

I also spent some time looking into this, and it seems that at InfluxDB version 1.6 this is not possible:

Currently, there is no way to perform cross-measurement math or grouping. All data must be under a single measurement to query it together. InfluxDB is not a relational database and mapping data across measurements is not currently a recommended schema. See GitHub Issue #3552 for a discussion of implementing JOIN in InfluxDB. – https://docs.influxdata.com/influxdb/v1.2/troubleshooting/frequently-asked-questions/#how-do-i-query-data-across-measurements

The issue is being tracked in GitHub here: https://github.com/influxdata/influxdb/issues/3552

Upvotes: 2

Related Questions