Using latest Grafana 10 release, Ubuntu standard install.
Situation
We have a dashboard in our organization. However, some visualizations can only show IDs rather than useful information. I created a custom datasource that can map those IDs to names and other useful fields. The question has now become, how do I connect both of these? Importantly, the custom datasource is slow (and massive).
What I tried
I tried to make 2 queries, but I do not think that there is a way to use the result of the first query in the second query in our setup. I then resorted to look at transformations and I found the “Merge”, join, and lookup transformation…
What happened
Unfortunately, Merge relies on gathering information first, then merging them. There is no way to only query what you need. Hence, the custom datasource needs to download a massive amount of data to make it work. Join also has limitations and lookup is in alpha/beta and very restricted (to spatial data).
What I expected
I hoped that there was some way that the second query could get IDs or other fields from the first query, using templates. I definitely expected that the Transformations were a bit more powerful and could perform simple lookup in custom datasource.
I looked through many issues, none of which laid out the same problems that I have. For most people, merge seems to work fine, but that will not work with the amount of data that is involved.
Is there anything that can help me with this, anything planned by Grafana, or how can I approach this problem?
Can you elaborate on this? What are your primary datasources? Can you share details about the custom datasource you created? Can you share your queries from the primary datasources and from this custom datasource you created?
I wanted to keep it more generic as it is a general concern.
The primary datasource is a MySQL database. It performs a query returning the id of an object and a metric. Basically, it is a
SELECT * FROM table WHERE $__timeFilter()
The custom datasource connects to an existing GRPC API and gathers name, age, serial number & co of the object. So input is one ID, output is table of id, name, age, serial_no, …
Not quite sure if it is helpful to build you some fake data around it, but if it is and you want it, let me know! But it really is just about the generic problem, nothing to do with specific datasources
This may be an unrealistic suggestion, but can you bring in your GRPC data (with the name, age, etc.) into your MySQL database and then write a new query for Grafana using only the MySQL db? As you have discovered, Merge, Join and Lookup all have some shortcomings, esp. when trying to do the operation using different datasources. It might totally be possible, but I have no personal experience doing that.
I did think about that, but having regular syncs and every other issues coming from 2 datasources would make it very resource-intensive and far more complex than necessary…
I would prefer Grafana having a “lookup” transformation, or some way of accessing result of query A via templating variables in query B. Ideally, I would want a way such that you can write multiple queries one by one, taking a table as input, outputting another table. In our case, that would be the only scalable solution (in case of more complex queries). This would also scale well resource-wise.
Create 2 queries: main mysql query and reference grpc query. Make sure that they have the same name for id field/column. Then add join type transformation and use that id column for joining. At the end you can add organize transformation to hide that id column in the result.
It is not lookup, but join - but I would say it will produces desired result.
I can filter yes, but I do not know what Ids I need to fetch. That is resolved in the first query, the results of which are not available in the second query afaik. That is the problem.
That’s a really dirty way to do it, and not really scalable… I would rather want what I mentioned earlier. I would rather not accept this as solution
It should not be a dashboard setting, but rather private to the Panel itself. Having query A and query B, where B can use a result variable from query A would be a great addition. To me, it also seems very easily optimizable and I don’t really see a reason to not allow it, but that might be my ignorance here.
A less flexible solution, which would still work in this specific case (but not in many others) would be to allow the “lookup” Transformation for datasources, not only pre-defined constants.
