Support query parameters

[KGOH]

Hello. I would like to use clickhouse query parameters within the datasource queries.

e.g. I can pass parameters into clickhouse like this:

$ curl "https://play.clickhouse.com/?user=explorer&param_a=hello&param_b=world" --data-binary "select {a:String}"
hello

Solutions I see:

• Just pass all the dashboard variables as clickhouse query-string parameters.
• Give a selection of dashboard variables in the datasource input form.
• Like in the infinity-datasource give a form that maps grafana vars to params using grafana templating. e.g. a list of field pairs <clickhouse param>: <grafana var template>. For example: a:${a}

I see that this feature is mentioned in a TODO comment:

clickhouse-datasource/src/data/ast.ts

Line 67 in fc6df16

// TODO: support query parameters: https://clickhouse.com/docs/en/interfaces/cli#cli-queries-with-parameters

[SpencerTorres]

I think for now this solution (but backwards) would be the correct one:

Just pass all the dashboard variables as clickhouse query-string parameters.

It would be best if the query parameters were turned into dashboard variables. I'm not sure where we would edit/store the parameters. Grafana already has this concept with its variables and I think those should be used.

The reason it's mentioned in the code there is likely to prevent parsing failures

[KGOH]

@SpencerTorres Hello. I see you've closed the issue. Is this feature implemented? How can I try it out?

[SpencerTorres]

Grafana variables would be the way to go about this:
https://grafana.com/docs/grafana/latest/dashboards/variables/

In your query you can write $myVariable or ${myVariable} to have it replaced. As long as this appears in the SQL it will be replaced, so it applies to both the SQL editor and query builder

[KGOH]

@SpencerTorres I understand this, but my initial request was to use this grafana feature in conjunction with clickhouse query parameters
https://clickhouse.com/docs/sql-reference/syntax#defining-and-using-query-parameters

[SpencerTorres]

Query parameters allow you to write generic queries that contain abstract placeholders instead of concrete identifiers. When a query with query parameters is executed, all placeholders are resolved and replaced by the actual query parameter values.

This functionality is covered by Grafana variables though, and better integrated into the Grafana plugin and UI. What functionality would be gained by adding both? I think there's code that removes them now due to the trouble they cause with parsing, the {} syntax conflicts with Grafana's in some cases

I'm open to adding it, but I want to understand what workflow you're unable to implement using the existing variables

[KGOH]

Yeah, sure, @SpencerTorres. My case might be rare, but here are my two reasons:

1. I maintain over 700 panels with ClickHouse SQL queries. Since ClickHouse evolves rapidly, updates occasionally break existing SQL syntax. To catch issues early, I run regression tests by executing these queries against multiple ClickHouse versions. However, the SQL includes Grafana-flavored templating (e.g., $__from, $__to, ${foobar}), which isn’t valid ClickHouse syntax. To work around this, I use regex to manually replace all Grafana variables in my tests before running the queries.

2. When developing these panels, I write and test queries outside Grafana (directly against a ClickHouse database). Once finalized, I put them into Grafana panels. This requires converting variables syntax, e.g.: {__from:DateTime} to Grafana’s ${__from} format.

What I’d like is a way to natively define ClickHouse parameters within the SQL and either:

• Automatically pass all Grafana dashboard variables as ClickHouse parameters, or
• Map Grafana variables to ClickHouse parameters via a configuration in the panel settings.

This would eliminate manual and regex substitutions and streamline testing/development.

[SpencerTorres]

We currently use the postgres AST parser to do the same thing with replacing variables and whatnot. It's not completely compatible with ClickHouse's flavor of SQL (specifically with parameters), and so until that is solved, I don't think we can reliably implement the parameters.

For now I think Grafana variables are the way to go, although I know this doesn't perfectly solve your workflow. It sounds like you already have some tools to swap the values though? I would suggest to keep using those tools. It's not too hard to regex or find/replace the syntax IF you know the variables; implementing a generalized solution would be difficult which is why we can't include it in the plugin right now.

Of course if you have some sample code in mind for how we might be able to solve it, feel free to open a PR. Thanks!