Why is this needed:

The current Grafana variable interpolation syntax does not support wrapping dynamic variable values in backticks, which are necessary for certain SQL queries in environments like Databricks. This limitation prevents users from dynamically generating SQL queries that require specific formatting of column names, such as when using the PIVOT function. As a result, users have to manually adjust their queries or implement workarounds, which reduces the efficiency and flexibility that Grafana is known for.

What would you like to be added:

I would like to request an enhancement to the Grafana variable interpolation syntax that allows users to wrap variable values in backticks. This feature would enable users to dynamically generate SQL queries with correctly formatted column names, enhancing the compatibility with SQL dialects that require such formatting. Specifically, the ability to use ${variable:backtick} or a similar syntax to automatically wrap each interpolated value in backticks would be highly beneficial.

Who is this feature for?

This feature is for Grafana users who work with SQL databases that require specific formatting for column names, such as Databricks, and who need to dynamically generate queries based on variable selections. This includes data engineers, analysts, and developers who rely on Grafana for visualizing and interacting with their data in a flexible and automated manner.

Example Queries:

Templated Query with Grafana Variables

Assume we have a Grafana variable named ${metric_ids} which holds multiple numeric metric IDs, and we want to use these IDs dynamically in a SQL query with backticks. The variable ${metric_ids} is populated from a query that selects these IDs from a table.

SELECT
  CAST(timestamp AS TIMESTAMP),
  ${metric_ids:backtick}
FROM (
    SELECT
      timestamp,
      metric_id,
      SUM(value) AS total_value
    FROM
      metrics_data
    GROUP BY
      timestamp,
      metric_id
) AS subquery
PIVOT (
    SUM(total_value) FOR metric_id IN (${metric_ids:singlequote})
) AS pvt
ORDER BY
  CAST(timestamp AS DATE);

Resulting Query with Actual Values

Assume the Grafana variable ${metric_ids} contains the metric IDs 101, 102 and 103 fetched from the table. The resulting query would look like this:

SELECT
  CAST(timestamp AS TIMESTAMP),
  `101`,
  `102`,
  `103`
FROM (
    SELECT
      timestamp,
      metric_id,
      SUM(value) AS total_value
    FROM
      metrics_data
    GROUP BY
      timestamp,
      metric_id
) AS subquery
PIVOT (
    SUM(total_value) FOR metric_id IN ('101', '102', '103')
) AS pvt
ORDER BY
  CAST(timestamp AS DATE);

Explanation:

1. Templated Query:

   • ${metric_ids:backtick}: Intended to wrap each metric ID in backticks.
   • ${metric_ids:singlequote}: Wraps each metric ID in single quotes for the PIVOT clause.
   • The variable ${metric_ids} is filled from a query that selects these IDs from a table, making it impossible to manually change the values.

2. Resulting Query:

   • 101, 102, 103 are the metric IDs used in the SELECT clause with backticks.
   • '101', '102', '103' are used in the PIVOT clause with single quotes.

Why This Feature is Needed:

Currently, Grafana does not support a straightforward way to wrap variable values in backticks, which is necessary for some SQL queries to run correctly, especially when dealing with numeric IDs that need to be treated as column names. Since these IDs are dynamically fetched from a table, it is not feasible to manually adjust the values. Implementing this feature would allow users to dynamically generate correctly formatted SQL queries, enhancing the usability and flexibility of Grafana in more complex SQL environments.

This example demonstrates the need for a feature that allows for more flexible and accurate SQL query generation using Grafana variables, specifically the ability to wrap values in backticks.