Substitution for Inner Join

#1

In version 0.8 there was an example where I could do mathematical operations with an inner join function like this:

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';

Is that use case not supported anymore at all or how should I structure my data model so that I can achieve the same results?

Thanks in advance

#2

@Rainer_Hahnekamp You can now store multiple fields in each measurement. Performing math across them works as you have specified. You might find these docs pages useful:

#3

Thanks for your answer, but the documentation didn’t help me. My use case is that I am measuring energy data from different meters (slighty different timestamps). I am using a tag to identify the meter and need to calculate KPIs.

For example my dataset would look like

meter=ENERGY_METER1 value=502.23 1491462194859
meter=ENERGY_METER2 value=230.53 1491462194925
meter=ENERGY_METER1 value=504.13 1491462194959
meter=ENERGY_METER2 value=231.12 1491462195025

So what I had now in mind was to calculate the differen between these two by a inner join

select (meter1.value - meter2.value) * 1.780
from meters as meter1 inner join meters as meter2
where meter1.meter = 'ENERGY_METER1' and meter2.meter = 'ENERGY_METER2'
#4

@Rainer_Hahnekamp Thats not possible using the query language right now. You would have to perform that calculation client side.

#5

I see, are there any tools available or might this be a good opportunity to contributing something to the community?

#6

@Rainer_Hahnekamp I would look into doing this with Kapacitor. There you can join the data and write it back into an InfluxDB instance.

#7

@Rainer_Hahnekamp I have a similar use case – did you have any luck using Kapacitor?

#8

Hi Steve, yes I can confirm that it worked perfect with Kapacitor. I am using version 1.2. Do you want me to send you some example code?

#9

Hi @Rainer_Hahnekamp – yes I would love to see some sample code :slight_smile: . Many thanks!

#10

Hi Steve,

sorry for the very late answer. If your question is still valid, my Kapacitor script for inner joining two measurements separated by the tags “field1” and “field2” looks like the following:

var field1 = batch
    |query('select mean(value) from "data"."autogen".linedata where prettyname = \'Field Nr 1\'')
    .period(1d).every(1d).groupBy(time(1h)).fill(0)
var field2 = batch
    |query('select mean(value) from "data"."autogen".linedata where prettyname = \'Field Nr 2\'')
    .period(1d).every(1d).groupBy(time(1h)).fill(0)

field1
    |join(field2).as('field1', 'field2')
    |eval(lambda: max(("field1.mean" - "field2.mean") * 3.144, 0.0)).as('value')
    |influxDBOut().database('data').measurement('linedata').tag('prettyname', 'Field Nr 3')

Greetings

Stream inner join on a non time column with Kapacitor