Using Variables to dynamically select MySQL columns containing special values

What Grafana version and what operating system are you using?

Grafana: v10.1.4 (a676a96d91)

What are you trying to achieve?

I would like to make an SQL query to my MySQL database that only queries for columns that I have selected from multi-valued variables.

For me it is important that the column names are escaped, because they contain unusal characters like whitespaces and accented characters, and I can’t change that. Without escaping/enclosing these column names with backticks, my database server won’t find the columns.
However if I test the query with manually editing it so that column names are enclosed in backticks, the database server recognizes the columns as expected, and includes them in the result.

How are you trying to achieve it?

1. try: using the automatic formatting

I have made a dashboard variable that contains all the column names. Technically this variable itself is also based on a query, into the INFORMATION_SCHEMA.COLUMNS table.

In the dashboard panel I have made a new query from my MySQL datasource, with the following as the SQL query command:

SELECT $column FROM MyTable

I’ve got a query error. MySQL expects the column names to not be enclosed by apostrophes, but instead by backticks or without enclosing them at all.

2. try: manual backticks

After this didn’t work, foolishly I tried to add the backticks myself:

SELECT `$column` FROM MyTable

Of course this didn’t work, this way the query is for a column name like `‘column_a’,‘columb_b’`, which of course does not exist.

(Actually this works if I only have one column selected, but I need to select more)

3. try: manual backticks with raw formatting

Then I have discovered the variable interpolation options documented here, and in my last desperation I tried the raw option:

SELECT `${column:raw}` FROM MyTable

But of course this didn’t work either, for the same reason as above.

Basically what I would need is a formatting option like singlequote, but with backticks instead of apostrophes.

There were some other things I have also tried:

  • use the regex in the dashboard variable query options to add the backticks to the column names coming from the query, but that does not seem to support extending the values, only extraction from a more complicated format.
  • query for all columns (SELECT * FROM MyTable), and filtering with a transformation based on regex filtering the columns by their name. This works, but as I have a lot of columns (currently ~50, 150 is expected soon, yet another thing I can’t change), and often I only need a handful of columns at a time. I’m not toally sure about it, but I’m afraid this still means that really all of the columns are queried, and filtering is done on the Graphana side, and I’m worried about the performance.

Additional information

I’ve read a similar question that I have linked below, but it did not solve my problem, because the solution there was to use the variables without them being enclosed in other characters.

What did you expect to happen?

I have expected that the column names in the SELECT part of my MySQL query are enclosed by backticks instead of apostrophes.

How about simply using brackets?


Create additional variable, where you use SQL to create correct string,. e. g. (probably not valid MySQL just to give you idea):

SELECT REPLACE($column, '\'', '`') 

and use this new variable in the final query

I had similar requirement and was able to acheive it using

SELECT ${column:raw} FROM MyTable

Grafana v10.3.3 (252761264e)

1 Like