I have a mysql database with different tables. Depending on the name of the parameter, I first need to lookup it’s ID. With this ID, I can then find the values in another table. For example, I want to get the values for parameter “OPPSM001”. To get these values, I need to look in table t_par_ref and get to value of the “param_id” file where “param_name” = “OPPSM001”. I then need a second query in another table t_par_dbl where I can get the value of param_id. I understand mysql in grafana does not support multiple mysql statements, but is there a smart trick to achieve my goal?
Welcome
Please share
- some values from your variable,
- the schema of the tables you want to query and which ones you want to use for visualization
- The visualization you want to use
IMHO that can be sorted by joining on the SQL level, e.g.:
SELECT tpd.param_id, tpd.param_value
FROM t_par_ref tpr
JOIN t_par_dbl tpd ON tpr.param_id = tpd.param_id
WHERE tpr.param_name = 'OPPSM001';
Of course, the syntax/result may not be correct. That’s just an example how to use JOIN; improve it for your use case.
1 Like
I have a table t_par_ref, which has a field “name”. This table also has the field “param_id”. In normal mysql I can lookup the param_id based on the parameter name using:
SELECT param_id FROM t_par_ref WHERE name = "OPPSM001"
This same table also has a field t_raw, which contains the table in which I can find the value of the parameter (example t_par_dbl, t_par_u16, t_par_u32, …). I can lookup which table I should use using
SELECT t_raw FROM t_par_ref WHERE name = "OPPSM001"
With these 2, I can query the table containing my values. I wanted to try the following:
SELECT gentime/1000000 as "time", value as "amount"
FROM (SELECT t_raw FROM t_par_ref WHERE name = "OPPSM001")
WHERE param_id = (SELECT param_id FROM t_par_ref WHERE name = "OPPSM001");
The last line works fine. But in the FROM line, I get an error. I understand grafana does not allow dynamic references to a table. I tried using a grafana variable including all possible table (t_par_dbl, t_par_u16, …), using the following code:
SELECT gentime/1000000 as "time", value as "amount"
FROM ${available_tables}
WHERE param_id = (SELECT param_id FROM t_par_ref WHERE name = "OPPSM001");
This works quite well, but a single dashboard contains a combination of parameters of type u16 and double (as example), which then only return data in case the dashboard variable is set to one or the other table. I do not seem to be able to select a table per parameter. I have 30000 parameters. Last attempt was to create a UNION, but my code does not return anything:
SELECT gentime/1000000 as "time", value as "amount"
FROM
(
SELECT gentime, value FROM t_par_dbl WHERE param_id = (SELECT param_id FROM t_par_ref WHERE name = "OOMAM001")
UNION ALL
SELECT gentime, value FROM t_par_u16 WHERE param_id = (SELECT param_id FROM t_par_ref WHERE name = "OOMAM001")
) AS combined_data;
Any help is welcome. Thanks
in that case create the following view
create view vw_alljedis
as
select param_id , gentime, value, 't_par_dbl ' as t_raw from t_par_dbl union
select param_id , gentime, value, 't_par_u16' as t_raw from t_par_u16
then in grafana
select *
from vw_alljedis va
where t_raw in (
SELECT t_raw FROM t_par_ref WHERE name = "OPPSM001"
)
and param_id in (
SELECT param_id FROM t_par_ref WHERE name = "OPPSM001"
)
or avoiding where in
select *
from vw_alljedis va
join (
SELECT t_raw, param_id
FROM t_par_ref WHERE name = "OPPSM001"
) ref on va.t_raw = ref.t_raw and va.param_id = ref.param_id
Is the ‘create view’ in mysql directly?
1 Like
or without view. so this is not a grafana issue but rather a mysql querying tricks issue and also probably db design issue. this will be problematic if your list of tables are not static meaning you keep creating new t_par_ tables . Why do you have different 't_par_ tables?
select *
from (
select param_id , gentime, value, 't_par_dbl ' as t_raw from t_par_dbl union
select param_id , gentime, value, 't_par_u16' as t_raw from t_par_u16
) va
join t_par_ref ref on va.t_raw = ref.t_raw
and va.param_id = ref.param_id
WHERE ref.name = "OPPSM001"
@yosiasz , thanks so much for your time.
I have a limited set of 5 tables. All these tables have the same columns and the same headers. Can I not use an if loop. Checking the return of
SELECT t_raw FROM t_par_ref WHERE name = "OOMAM001"
If this is t_par_dbl, then use
from t_par_dbl
if this is t_par_16, then use
from t_par_u16
can you show us how to do it?
I tried:
SELECT gentime/1000000 as "time", value as "amount"
FROM IF(STRCMP(SELECT t_raw FROM t_par_ref WHERE name = "OPPSM001","t_par_dbl") = 0 ,t_par_dbl,IF(STRCMP(SELECT t_raw FROM t_par_ref WHERE name = "OPPSM001","t_par_u16") = 0 ,t_par_u16,t_par_u32))
WHERE param_id = (SELECT param_id FROM t_par_ref WHERE name = "OPPSM001");
But any SELECT within the FROM seems to be rejected.
I tried your proposed code:
select *
from (
select param_id , gentime, value, 't_par_dbl ' as t_raw from t_par_dbl union
select param_id , gentime, value, 't_par_u16' as t_raw from t_par_u16
) va
join t_par_ref ref on va.t_raw = ref.t_raw
and va.param_id = ref.param_id
WHERE ref.name = "OPPSM001"
But this starts executing, but this returns the following error:
select va.*
from (
select param_id , gentime, value, 't_par_dbl ’ as t_raw from t_par_dbl union
select param_id , gentime, value, ‘t_par_u16’ as t_raw from t_par_u16
) va
join t_par_ref ref on va.t_raw = ref.t_raw
and va.param_id = ref.param_id
WHERE ref.name = ‘OPPSM001’
see updated query.