Join function query generates error in Flux

influxdb
grafana

#1

Hi everyone,

Currently stuck trying to use the new capabilities of the Flux data scripting language. I am trying to make some math across measurements using the join function. I read this presentation (check out slide 107) by Paul Dix, which shows some examples as well as the git hub documentation. It’s proving to be harder than anticipated.

I setup a local DB, I collect metrics through telegraf and visualize them on Grafana using the plugin for Flux. My first goal was simply to add two measurements together, but even that won’t work.

These are my data subsets (they are both chosen at random, their form is what matter here)

Memory = from (db: "InfluxDB") 
|> filter(fn: (r) => r["_measurement"] == "Memory" AND r["_field"] == "Private_Bytes" AND r["host"] == "RAND")
|> range(start:-15m)

CPU = from (db: "InfluxDB") 
|> filter(fn: (r) => r["_measurement"] == "CPU" AND r["_field"] == "Percent_Processor_Time" AND r["host"] == "RAND")
|> range(start:-15m)

Here is a preview of the data coming from these two queries. The hosts are identical.

I then join these subsets on their time. Since they share the same time series, I expected the line below to join the rows that share the same timestamp and the function to add their values together.

join(tables: {Memory:Memory, CPU:CPU}, on:["_time"], fn:(t) => t.Memory._value + t.CPU._value)

But it doesn’t work. I am unsure where to find logs to understand why it fails. When I yield the result of the join function, I seem to have a table with no records. There is not much information about this on the web. I’m actually surprised there isn’t a dedicated community page for nightly build discussions. Maybe there is. I am still fairly new to InfluxDB, but also to databases in general. I tried several different versions of the queries shown in this post, but I cannot get it to work.

Thanks for reading my post and hopefully helping me.

MP.


#2

Small update/bump. I played with Flux a bit more and looked at the query inspector of Grafana as well as the Flux console response of working/“broken” queries.

If I yield a simple table filtered by field and various tags, as well as time, I have a response in the query inspector and the Flux console outputs nothing in normal mode and a bunch of Json data about the query in verbose mode. I will assume both pictures below are showing the expected behavior.

On the other hand, once I try to yield the joined table, I have no response from the query editor even though I have a status 200 (OK). The Flux console outputs the same error in both mode (see image below).

Capture6

I know nothing about Golang, the error seems to be related to it and since I have very little to work with here, it seems that my journey with Flux is coming to end. I am actually kind of sad since I was looking forward to learning and using it. Hopefully I am not the only one interested in this topic and help will come.

I wish you all good luck as well as a nice week.

MP.


#3

Hi @MPLOUFFE,

I haven’t had a chance to play around with Grafana and Flux yet, but I’ll try and get it up and running and see if I can replicate the issues you’re having. You might also want to post on the Grafana community forums, if you haven’t already. Have you tried the queries you’re using with the Flux CLI or REPL to verify that they return the results you expect outside of the Grafana environment?

And you’re absolutely right, we needed a place for Flux-specific discussions, so we created a new category here on the community site, and moved your post over.


#4

No need to replicate as it was mostly a misunderstanding on my part. I went back to the basics and while experimenting, I found this thread. I started playing around with the window and group functions. Windowing is useless for my use case, but grouping is mandatory. If I do not specify that grouping should be off by piping the following |> group(none: true) the join function does not work. However, once both query aren’t grouped, the join function works like it should. Here is an example of a working join query that calculates the % used memory by using two subset (average available memory of the past 15 minutes and memory capacity of a specific host).

It’s far from perfect, and might defy what Flux was intended for, but it works and I can create a singlestat gauge panel with the response. However, I must admit that Flux seems unreliable for handling multiple queries at the moment. If you create more than 3-4 of these, for example, and use their response simultaneously on a dashboard with automatic refresh every minute, it breaks in less than 5. The panels simply load indefinitely, until Flux is reset. Restarting the Grafana server does not fix the issue, so I’m leaning towards Flux being the culprit.

Anyway, I hope my traces help someone fix their own issues.
Good luck everyone.

MP.


#5

Looks like you have something working but I thought I’d give a few more pointers:

First, the join function requires that the group keys match on all tables being joined. We are looking into ways to remove that constraint and make using join easier. See https://github.com/influxdata/platform/issues/407

Second, if you want join on time you must specify that in the on list, join(..., on:["_time","host"]).

As for why the last function was not working, my guess is its a bug in the query planner. We are actively working to fix the issues in the planner.