Combine multiple variables to generate a multi-line SQL query

Grafana Dashboards
1

Dear all,

for Grafana Docker image tag 10.0.1:

I need to compose an SQL query for a plugin (Databricks or Trino) with aggregated values:

SELECT …,

  • AVG(column1) AS column1Aggregated,*
  • AVG(column3) AS column3Aggregated,*
  • …*
  • AVG(column7) AS column7Aggregated,*
    …

I have created a custom variable “function” for the aggregation function with values AVG, MAX, MIN etc. with single value selection.

For selection of the columns, I want to fetch these from the database (“SHOW COLUMNS”) as multi-value selection query variable.

Assume the user has selected $function=AVG and $columns=column1,column3,column5,column6: Is there any way to compose the string “AVG(column1) AS column1Aggregated,AVG(column3) AS column3Aggregated,AVG(column5) AS column5Aggregated,AVG(column6) AS column6Aggregated,” from the two variables? E.g., via a third variable which contains the “AVG(columnX) AS columnXAggregated” parts, then expanding in the final SQL query via ${someVariable:csv}?

Currently, I have made it half the way with a query variable using the Infinity plugin with all signals hard-coded as inline CSV with the lines “$function(column1) AS column1Aggregated” etc. and the regex (?.AS (?.).)*. The selection labels show “column1”, “column2” etc. and the variable values contain the strings to be used in the query, then expanded with ${columns:csv}. However, with this, the column names are all hard-coded in the inline CSV and not fetched from the database itself.

As far as I could see, the “chained variables” section in the documentation does not apply to this. Does anybody have an idea how to solve this? E.g. some “helper plugin” for text editing in variables, or a data source plugin which allows adding a constant part to each value (like $function + “(” + value + ") AS " + value + “Aggregated”)?

Thanks & Regards
Jochen

2

You have SQL, so use SQL power. Use third variable with SQL query, where you will expand that $columns string, concatenate it with $function string correctly and join back to string so result will be correct SQL syntax.

I don’t know which kind of SQL you have (check doc for used SQL engine - string functions). For PostgreSQL it may looks like (not final SQL, it may not be valid SQL syntax - just to give you initial idea):

SELECT 
  'AVG(' 
  || unnest(string_to_array('column1,column3,column5,column6', ',')) 
  || ') AS ' 
  || unnest(string_to_array('column1,column3,column5,column6', ',')) 
  || 'aggregated, '
3

Can one call trino from javascript? You could leverate this plugin and do all that dynamic query stuff in there

4

or use key value pairing for your $function variable wherein the value in key : value is the actual query

image

AVG : select * from tpch.sf1.orders limit 10

then reference that variable as follows
${function:value}

image

query works

image
image863×485 33.5 KB

5

Thank you all!

@yosiasz : No, unfortunately not directly callable from JavaScript. I also don’t see how I could control the datasource plugins via JavaScript. The static datasource would be a solution if it could be used as query for variables, but when selecting it for a query variable, there is no possibility to specify the query - can only be used for panels.

Your second proposal: I haven’t really understood the idea (but maybe my Grafana experience is not good enough to get the point behind that). Assumed that column1 is “orderkey”, column2 is “totalprice”, I would have to replace the star in the query by “somedatatruncationfunction(orderdate) AS groupedDate, ${function:text}(orderkey) AS orderkeyAgg, ${function:text}(totalprice) AS totalpriceAgg” and somehow use the $columns variable to populate the “orderkey”, “totalprice” etc. (not the best example, because averaging the order key makes no sense - just assume it is any numeric value that can be averaged).

@jangaraj : that finally brought me on the right track. Unfortunately, I cannot directly use the output of “SHOW COLUMNS form mydatabase.mytable” in an SQL query - gives some SQL parsing error in Databricks. But I populate a helper variable “columns_from_database” with the column names, and then have a variable “columns” with the Databricks SQL query

SELECT EXPLODE(TRANSFORM(SPLIT(‘${columns_from_database:csv}’, ‘,’), value → CONCAT(‘$function(’, value, ') AS ', value, ‘Agg’)))

and a regex (?<value>.*AS (?<text>.*).*) for shorter display in the variable selection field. In the SQL command for the panel, I can then just use ${columns:csv} for the aggregated columns part of the query.

Note: this works when all selected columns are numeric. If there are data types for which the selected aggregation function (e.g., AVG) doesn’t work, one has to either exclude such columns, or separate the column sets and then merge them with differing aggregation function (…EXPLODE(ARRAY_UNION(TRANSFORM(…), TRANSFORM(…))).

My problem is solved now. In my opinion, the JavaScript solution would have been better if the plugin would have worked with variables, because finally I send an extra SELECT statement just for some string concatenation operation.

Regards
Jochen

1 Like
6

The javascript approach is working for me using the trino rest api and the plugin mentioned

The beauty of the plugin is you dont need to use the trino plugin