Grafana - How to create sql query part variable/macro for Mysql datasource

Question

I have following query in Grafana which is backed by MySql DataSource.

SELECT
  $__timeGroupAlias(ts,$__interval),
  sum(total) AS "total"
FROM hp
WHERE
  $__timeFilter(ts) 
  AND customer_type IN ($CustomerType) AND age IN ($age) AND gender IN ($gender)
GROUP BY 1
ORDER BY $__timeGroup(ts,$__interval)

There are multiple singleStat/panel/graphs in the Dashboard which use different select parameters but the WHERE condition remains same in all of them.

I want to keep the condition as separate constant variable so that I can add just that variable in every query.

I tried to build my query like this.

SELECT
  $__timeGroupAlias(ts,$__interval),
  sum(total) AS "total"
FROM hp
$where_condition
GROUP BY 1
ORDER BY $__timeGroup(ts,$__interval)

and declared where_condition as WHERE $__timeFilter(ts) AND customer_type IN ($CustomerType) AND age IN ($age) AND gender IN ($gender).

But the query fails, because the internal variables ($CustomerType,$age,$gender) are not resolved by query generator and generated query looks like this.

SELECT
  UNIX_TIMESTAMP(ts) DIV 900 * 900 AS "time",
  sum(total) AS "total"
FROM hp
ts BETWEEN FROM_UNIXTIME(1548311714) AND FROM_UNIXTIME(1548398114) 
AND customer_type IN ($CustomerType) AND age IN ($age) AND gender IN ($gender)
GROUP BY 1
ORDER BY UNIX_TIMESTAMP(ts) DIV 900 * 900

Is there a way to resolve the variables which are contained in other variables. Or Is there any other way to externalise part of query which contains variables?

Which variable type did you use for `where_condition` and what is your Grafana version? — Jan Garaj, Jan 25 '19 at 07:51
Grafana version = v5.4.3, I have used Constant type for `where_condition`. — dnsh, Jan 25 '19 at 07:56

score 1 · Accepted Answer · answered Jan 25 '19 at 08:11

constant variable type generates only static string. It didn't substitute any variables. Switch to query type and use MySQL to return string, which will have exact string value for your where_condition variable. Query:

SELECT 'WHERE $__timeFilter(ts) AND customer_type IN ($CustomerType) AND age IN ($age) AND gender IN ($gender)'

IMHO: variable substitution should works also for constant type. You can open feature request for that https://github.com/grafana/grafana/issues.

It works. I had to disable quotes when using this variable like this `${where_condition:csv}`. I will raise feature request for the same. — dnsh, Jan 25 '19 at 08:53

score 0 · Answer 2 · answered Nov 24 '20 at 15:59

I also had issues with quotes, here is my solution that supports 'All' and many selected:

SELECT '
task_run_table.is_system = false 
AND 
(
    ''__all__'' = ''${user:raw}''
    OR 
    task_run_table.user  = any ( string_to_array(''${user:raw}'', '','')::text[])
)
AND 
(
    ''__all__'' = ''${job:raw}''
    OR 
    task_run_table.job_name  = any ( string_to_array(''${job:raw}'', '','')::text[])
)
'

All value was overridden to __all__. It's used like this:

SELECT 
  $__timeGroup(task_run_table.start_time, '$interval', NULL),
  task_run_table.name AS metric,
  COUNT(*) AS value
FROM
  task_run_table
WHERE 
  $__timeFilter(task_run_table.start_time) 
  AND 
  ${default_filter:raw}
  AND 
  task_run_table.state = $state
GROUP BY time, task_run_table.name
ORDER BY time, value DESC

Grafana - How to create sql query part variable/macro for Mysql datasource

2 Answers2