Multiple outer join transforms on a table?

Grafana Dashboards
1

I’m using the new Transformations in Grafana 7 to build a table composed of many queries from data in Prometheus. This is working really well, using a single Outer Join transformation to link the various series.

I want to link a number of other columns, with a second Outer Join transformation on a different column, and while I can add an additional Outer Join transformation it doesn’t seem to work? Do multiple outer join transformations work?

For example if I have two queries which share a single column: {A, B} and {A, C} then an outer join on A results in a table of {A, B, C}. I also have some other series which do not share A, but do share B: {B, D} and {B, E}. These are actually a 1:1 mapping to A, but don’t have the column in the source data. I want to join on A, and then on B, giving me a table containing {A, B, C, D, E}. Is this possible?

Details:

I currently have 12 queries, and have been able to tweak the data so that these have a common column (a deployment name in kubernetes). I can use a few transforms and field overrides to make a really useful data set:

  • outer join transform on a deployment field
  • add field from calculation transform
  • add field from calculation transform
  • organise fields to hide and rename various

I then have 9 field overrides (Matcher > Filter by field) with various settings for units, decimals, cell display mode (color text background), thresholds, etc.

I’m using Prometheus as the data source, and 4 of these queries do not have the deployment label to join on, but have another label that is a 1:1 mapping. I do have a query which returns this mapping of deployment to label X, which I want to perform a second outer join based on. When I do this I just get multiple series in the table which I can select from the drop down, the join doesn’t seem to work, so I’m having to hack around this by joining in the PromQL on each query (with an on and group_left and some other cruft!). Is there a better way?

2 Likes
2

I’m running into the same exact issue but with two different Postgres datasources. I have 3 tables across 2 data sources. The first table just has a bunch of foreign key ids, the other tables have the primary keys and values. I’d like to do multiple joins to look up the human readable values from these other tables.

Only the first join seems to work in my experience.

3

Welcome @jeffroebodine

Are these datasources on 2 separate postgres servers?

4

Yes. They’re on different servers.

1 Like
5

I am encountering a similar issue. I have three queries from three different data sources that I want to join into one table. While the first join works just fine, it seems like the second join does nothing.

6

We are also in need of a feature like this to help with joining data from different sources.

For example, we have two tables, say A and B, in one data source that need to be joined. We have another table, say C, in another data source that needs to be joined with the result of AjoinB.

The join transforms in Grafana 10.1 join all dataframes produced from panel queries. Binary join and any-arity joins would give flexibility in joining the right data. Additionally, a way to reference intermediate join results in further joins would give flexibility in joining on the correct fields across multiple joins. Alternatively, a way to choose 1 or more fields to join on in a multi-arity join could work.

1 Like