Having a ton of trouble with Flux and Joins

[Nefariis]

So I am coming from years of usage of InfluxQL, but I need to join two tables together, and it doesn’t appear possible without using Flux - can anyone give me a hand in creating a Flux Query?

Currently I am using Influx 1.8.4 and Grafana 7.5.6

I have two tables that can be gathered using the following InfluxQL query

SELECT 
    "Result", 
    "E/D" as "U-E/D", 
    "K/D" as "U-K/D", 
    "Kills" as "U-Kills", 
    "SelfHeal" as "U-SelfHeal" 
FROM 
    "Us"
GROUP BY 
    "Champion", "Match"

Champion	Result	Match	U-E/D	U-K/D	U-Kills	U-SelfHeal
Barik	Win	1090121066	4.71	0.429	0.467	1304
Barik	Win	1090124021	14	4	0.514	2100
Barik	Win	1090125925	5.33	0.333	0.539	1920
Barik	Win	1090603552	3.571	0.286	0.46	728
Barik	Win	1090876010	4.27	0.545	0.346	1708

and

SELECT 
	sum("Kills")/sum("Deaths") as "T-K/D", 
	(sum("Kills") + sum("Assists"))/sum("Deaths") as "T-E/D", 
	sum("Kills")/sum("Minutes") as "T-Kills", 
	sum("SelfHeal")/sum("Minutes") as "T-SelfHeal" ,
FROM 
	"Them"
WHERE 
     "Result" = 'Winner'
GROUP BY 
	"Champion"

which produces a table like this -

Champion	T-K/D	T-E/D	T-Kills	T-Self Healing
Ash	0.876	3.17	0.422	381
Atlas	0.95	3.01	0.459	568
Barik	1.14	4.3	0.497	1038
BombKing	1.28	2.48	0.796	579

At the very minimum I would like a table like this -

Champion	match	Result	U-E/D	T-E/D	U-K/D	T-K/D	U-Kills	T-Kills	U-SelfHeal	T-SelfHeal
Barik	1090121066	Win	4.71	4.3	0.429	1.14	0.467	0.497	1304	1038
Barik	1090124021	Win	14	4.3	4	1.14	0.514	0.497	2100	1038
Barik	1090125925	Win	5.33	4.3	0.533	1.14	0.539	0.497	1920	1038
Barik	1090603552	Win	3.571	4.3	0.286	1.14	0.46	0.497	728	1038
Barik	1090876010	Win	4.27	4.3	0.545	1.14	0.346	0.497	1708	1038

Which is all the rows from the first table, filled in with the information about a particular champion from the second table - but ideally, I would like to subtract the two columns “U-" and "T-” to make a combined column out of the difference. So “U-E/D” minus “T-E/D” and “U-K/D” minus “T-K/D” and so forth.

Can anyone give me a hand in accomplishing this?

Thank you

[scott]

@Nefariis This is untested, but I believe it will get you what you want:

Us = from(bucket: "example-bucket")
  |> range(start: -1d)
  |> filter(fn: (r) => r._measurement == "Us")
  |> filter(fn: (r) => r._field == "E/D" or r._field == "K/D" or r._field == "Kills" or r._field == "SelfHeal")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> group(columns: ["Champion", "Match"])

Them = from(bucket: "example-bucket")
  |> range(start: -1d)
  |> filter(fn: (r) => r._measurement == "Them")
  |> filter(fn: (r) => r._field == "Kills" or r._field == "Deaths" or r._field == "Assists" or r._field == "Minutes" or r._field == "SelfHeal")
  |> filter(fn: (r) => r.Result == "Winner")
  |> group(columns: ["Chapion"])
  |> sum()
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({
    Champion: r.Chapion,
    "K/D": r.Kills / r.Deaths,
    "E/D": r.Kills + r.Assists / r.Deaths,
    "Kills": r.Kills / r.Minutes,
    "SelfHeal": r.SelfHeal / r.Minutes
    }))

join(tables: [U: Us, T: Them], on: ["Champion"])
  |> map(fn: (r) => ({ 
    Champion: r.Champion,
    Match: r.Match,
    Result: r.Result,
    "E/D": r["E/D_U"] - r["E/D_T"],
    "K/D": r["K/D_U"] - r["K/D_T"],
    Kills: r.Kills_U - r.Kills_T,
    SelfHeal: r.SelfHeal_U - r.SelfHeal_T
  }))

[Nefariis]

First, thank you for taking the time to help me out.

As is it’s kicking up an error on this line -

@scott

join(tables: [U: Us, T: Them], on: ["Champion"])

“Undefined Identifier U”

update…

In attempting to narrow it down, I got the first query (Us) to work as expected and I am receiving this error on the second query (Them).

00 Internal Server Error: {"error":"expected integer cursor type, got *reads.floatMultiShardArrayCursor"}

[Nefariis]

Another Update…

I was able to get this to work mostly as expected…

from(bucket: "paladins")
  |> range(start: -100d)
  |> filter(fn: (r) => r._measurement == "Them")
  |> filter(fn: (r) => r._field == "Kills" or r._field == "Deaths" or r._field == "Assists" or r._field == "Minutes" or r._field == "SelfHeal")
  |> filter(fn: (r) => r.Result == "Winner")
  |> drop(columns: ["Legendary", "Role", "Result"])
  |> sum()
  |> pivot(rowKey:["_start"], columnKey: ["_field"], valueColumn: "_value")
  |> group(columns: ["Champion"])

It doesnt seem to create a single table with 50 rows, instead is seems to create 50 different tables, one row on each table for each character.

I still can not seem to get this to work however,

|> map(fn: (r) => ({
    Champion: r.Champion,
    "K/D": r.Kills / r.Deaths,
    "E/D": r.Kills + r.Assists / r.Deaths,
    "Kills": r.Kills / r.Minutes,
    "SelfHeal": r.SelfHeal / r.Minutes
    })) 

I am getting the error -

"500 Internal Server Error: {"error":"failed to evaluate map function: parameter \"r\" has the wrong type, expected {Minutes: float,Kills: float,Deaths: float,SelfHeal: float,Champion: string,Assists: float,} got {Deaths: int,Minutes: float,_start: time,_stop: time,Champion: string,Assists: int,SelfHeal: int,Patch: string,_measurement: string,Kills: int,}"}"

[scott]

@Nefariis Any chance you have some sample data I could play with?

[scott]

@Nefariis

It doesnt seem to create a single table with 50 rows, instead is seems to create 50 different tables, one row on each table for each character.

Oh, ok. Just change group(columns: ["Champion"]) to group().

[Nefariis]

I think I actually have most of it working now - thank you again for all your help, your sample code pointed me to the right direction and the joined table is displaying exactly the way I want it.

I think the last issue I need to work out is that some of the information is incorrect, and I believe it’s coming from this section.

from(bucket: "paladins")
  |> range(start: -999d)
  |> filter(fn: (r) => r._measurement == "Them")
  |> filter(fn: (r) => r.Result == "Winner")
  |> filter(fn: (r) => r._field =~ /.*/ or (r._field == "Tier" and r._value > 13 and r._value < 15))
  |> drop(columns: ["_measurement","Legendary", "Role", "Result", "Patch"])
  |> sum()
  |> pivot(rowKey:["_start"], columnKey: ["_field"], valueColumn: "_value")
  |> group(columns: ["_measurement"])

namely on this line -

|> filter(fn: (r) => r._field =~ /.*/ or (r._field == "Tier" and r._value > 13 and r._value < 15))

what I would like to do is simply

|> filter(fn: (r) => r.Tier > 13 and r.Tier < 15)

but that doesnt appear to be possible because tier is a Field and not a Tag - and if I just do this, it deletes all the other fields from the query.

|> filter(fn: (r) => r._field == "Tier" and r._value > 13 and r._value < 15)

I couldn’t find a single example of looking for specific value in a field, while also not dropping every other field in the table.

As a side note, is there anyway to make this query more performant? This section takes nearly 20 seconds to run (without the first query/map/join) whereas the same query in InfluxQL only take a few seconds to run.

Thank again for your help

[scott]

I think you could accomplish what you’re trying to do by just slightly updating the predicate logic in your filter function:

// ...
  |> filter(fn: (r) => r._field != "Tier" or (r._field == "Tier" and r._value > 13 and r._value < 15))

Once you get the query working the way you want, we can see if there are opportunities for some performance optimizations.

[Nefariis]

@scott

|> filter(fn: (r) => r._field != "Tier" or (r._field == "Tier" and r._value > 13 and r._value < 15))

yeah the issue definitely stems from the Tier line.

The tiers are from 0 to 26 and if I skip the tier line -

  |> filter(fn: (r) => r._measurement == "Them")
  |> filter(fn: (r) => r.Result == "Winner")

I get the expected table information/data - but when I try this line of code (Tier 0-26) the data is definitely off.

|> filter(fn: (r) => r._field != "Tier" or (r._field == "Tier" and r._value >= 0 and r._value <= 26))

and it looks like it might be because whether you do Tiers 2-5 or 0-26 or 13-16, the minutes are always the same (232k). It looks like the sum is counting the minutes whether it has been filtered or not.

I even tried replacing sum with |> aggregateWindow(every: 10y, fn: sum) and it still returns 232k Minutes regardless of the Tier filter.

kinda to recap, I would expect this (working)

  |> filter(fn: (r) => r._measurement == "Them")
  |> filter(fn: (r) => r.Result == "Winner")

to evaluate identically to this (not working) -

  |> filter(fn: (r) => r._measurement == "Them")
  |> filter(fn: (r) => r.Result == "Winner")
  |> filter(fn: (r) => r._field != "Tier" or (r._field == "Tier" and r._value >= 0 and r._value <= 26))

as every tier is between 0 and 26 - and lastly, all the minutes are being added together instead of just the filtered rows - which is really odd because all of the other fields seem to be filtering.

[Nefariis]

Ok - I am nearly positive that there is a bug of some sort in the filtering function when trying to filter by a field - or possibly Flux was never designed to filter by field?

The Max value of Tier is 26, proven by using the max() function -

|> filter(fn: (r) => r._field != "Tier" or (r._field == "Tier" and r._value >= 0))

seems to work fine with the appropriate data.

|> filter(fn: (r) => r._field != "Tier" or (r._field == "Tier" and r._value >= 1))

does not return the expected data.

|> filter(fn: (r) => r._field != "Tier" or (r._field == "Tier" and r._value >= 0 and r._value <= 26))

definitely does not return the expected data

|> filter(fn: (r) => r._field != "Tier" or (r._field == "Tier" and r._value > 0 and r._value <= 9999))

does not work - but the data gets closer to what’s expected

|> filter(fn: (r) => r._field != "Tier" or (r._field == "Tier" and r._value > 0 and r._value <= 99999))

actually returns the expected data - which leads be to believe that the r._value is being searched through in all the columns regardless of the r._field == "Tier" in front of it.

So how on earth can I filter by fields?