英文:
How can I reference first query results in the second query in a Grafana panel?
问题
我有一个在Grafana中显示ID及其对应计数的面板。但是,我想要解析并替换这些ID为它们在不同数据库中存储的相应名称。在Grafana中实现这一目标的最佳方法是什么?
这是从ClickHouse数据库查询的,我想要的其余数据存储在PostgreSQL中。
目前,我创建了一个额外的模板变量,用于执行此查询,并创建了另一个面板,显示来自PostgreSQL的数据。但这不是理想的解决方案,我宁愿在同一个面板中显示这些数据。
我研究了数据源的合并和连接,但似乎我需要查询整个用户表,而我只想解析15个用户的ID。
英文:
I have a panel in Grafana that displays IDs and their respective counts. However, I would like to resolve and replace these IDs with their corresponding names, which are stored in a different database. What is the best way to achieve this in Grafana?
This is queried from clickhouse db, the rest of the data I want is stored in postgresql.
For now I created an additional template variable with this query and have another panel which displays data from postgres. But this isn't ideal and I would rather it displayed in the same panel somehow.
I looked into merge and joining of datasources but it seems like I would need to query the entire user table when I just want to resolve IDs of 15 users.
答案1
得分: 1
你可以使用Grafana的转换功能来合并两个查询的结果。
通常情况下,你不能在一个查询中使用另一个查询的结果。
但在这种情况下,你可以使用一个小技巧:创建一个基于一个数据源的查询的隐藏变量,然后使用这个变量来查询另一个数据源。
对于你的情况,你需要按照以下算法进行操作。
准备仪表板变量
前往:仪表板设置 > 变量 > 新变量:
- 选择变量类型:查询,
- 名称:不重要。在这个示例中,我将使用
query0
, - 在仪表板上不显示:无,
- 数据源:您的ClickHouse数据源,
- 查询:从现有面板中提取只有名称以逗号分隔的查询。类似于以下内容:
-- 我不熟悉ClickHouse,并且没有访问一些公共演示,所以查询应该经过双重检查。
SELECT groupArray(userid) FROM myTable
-- 结果应该类似于:
-- ['123123-213-123-123232','123213-333-333-333122']
- 正则表达式:
.(.+).
(用于去掉周围的方括号), - 运行查询。
在值的预览中,您应该看到一个格式为 '123123-213-123-123232','123213-333-333-333122'
的字符串。
创建筛选查询并连接
前往当前数据的面板。
- 将数据源更改为
--混合--
, - + 查询
- 选择数据源为Postgres,
- 切换从
Builder
到Code
(右上角), - 选择您的用户信息。类似以下内容:
select userId, userName from my_users t where t.userId in ($query0)
- 运行查询。底部应该出现时间序列选择器,
- 切换到“Transform”选项卡,
- 选择“按字段连接”。字段:
userId
。
这里我假设两个数据库中都有包含用户的唯一标识符的列名为 userId
。根据您的情况进行正确设置。
注意,如果数据库中包含用户ID的列不同,您需要使用别名使它们相同:据我所知,Grafana可以连接具有不同名称的字段。
英文:
You can join results of two queries using Grafana's transformation.
Generally, you can't use results of one query in another query.
But in this exact case you could use a little trick: create hidden variable based on query to one data source, and then use this variable to query another data source.
For your case you'll need to follow below algorithm.
Prepare dashboard variable
Goto: Dashboard stettings > Variables > New variable:
- Select variable type: Query,
- Name: not important. In this example I will use
query0
, - Show on dashboard: Nothing,
- Data source: Your clickhouse data source,
- Query: query from existing panel with names only name concatenated by comma. Something like this:
-- I'm not familiar with clickhouse, and I don't have access to some public demo, so query should be double-checked.
SELECT groupArray(userid) FROM myTable
-- result should be something like:
-- ['123123-213-123-123232','123213-333-333-333122']
- Regex:
.(.+).
(to drop surrounding square brackets), - <kbd>Run query</kbd>.
In Preview of values you should see one string of format '123123-213-123-123232','123213-333-333-333122'
Create filtered query and join it
Go to panel with current data.
- Change Data source to
-- Mixed --
, - <kbd>+ Query</kbd>
- select Data source Postgres,
- switch from
Builder
toCode
(top right corner), - right select for your user info. Something along the lines:
select userId, userName from my_users t where t.userId in ($query0)
- <kbd>Run query</kbd>. In the bottom should appear selector of time series,
- switch to tab
Transform
, - select
Join by field
. Field:userId
.
Here I assume column containing guid of user named userId
in both databases. Correct to suite your situation.
Caution, if column containing user ids in databases differs, you'll need to use alias to make it the same: AFAIK, Grafana can join on fields with different names.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论