Return executed query string as part of ResultSet

[erikwlarsen]

Use case

Part of our product builds ClickHouse aggregation queries based on client configuration and executes them using the ClickHouseClient.query method. We inject client-supplied literal values and identifiers using the query_params parameter. Our queries can be quite complex, and during active development we sometimes encounter bugs in our query building code. Additionally, performance is a concern, so we take slow-running queries and run them on our ClickHouse database using EXPLAIN to debug the slowness.

This debugging and optimization is made more difficult by the fact that we cannot get the actual query strings executed on the ClickHouse server without doing the query_params substitutions ourselves, since the queries can get quite complicated.

Describe the solution you'd like

We would like to see the actual server-executed query as part of the ResultSet returned from the ClickHouseClient JS library.

Describe the alternatives you've considered

We could write a script to interpolate values ourselves, but this could be subject to bugs, since we don't actually know the techniques that ClickHouse is using to escape values. Additionally, we could probably check the query insights in our ClickHouse dashboard, but query volume is sufficient that this is not very user-friendly, and it does not integrate with our logging or APM platforms.

Additional context

[peter-leonov-ch]

Thanks for submitting an issue!

While I'm analyzing a more foundational response, let's start with a quick question. Did you try replacing the parameters with that script already? If yes, which corner cases have you noticed that a proper solution would cover better?

[peter-leonov-ch]

Analysis

I recently looked at the query processing pipeline.

It has a rather cheap step where it only works with syntax: EXPLAIN AST (docs). It does minimum but the result is likely now what you're looking for:

curl -v 'http://localhost:8123/?param_a=5&param_b=5' --data-binary 'EXPLAIN AST SELECT {a:UInt8} = {b:String}'

returns:

SelectWithUnionQuery (children 1)
 ExpressionList (children 1)
  SelectQuery (children 1)
   ExpressionList (children 1)
    Function equals (children 1)
     ExpressionList (children 2)
      Function _CAST (children 1)
       ExpressionList (children 2)
        Literal UInt64_5
        Literal \'UInt8\'
      Literal \'5\'

And there is also EXPLAIN SYNTAX (docs) that does more than just parsing (it expands names, deals with some function expansion) and returns the full query with parameters interpolated (thanks @chernser for the hint!):

curl -v 'http://localhost:8123/?param_a=5&param_b=5' --data-binary 'EXPLAIN SYNTAX SELECT {a:UInt8} = {b:String}'

returns:

SELECT _CAST(5, \'UInt8\') = \'5\'

This might be what can tick most of the boxes.

And there is just EXPLAIN that does a lot more.

Suggestion

All of the above require another request to the server and some extra processing on the server side. If this is appropriate, the EXPLAIN SYNTAX might be the most robust option as it does not introduce any new moving parts.

There is also an option with system.query_log but on a cluster it might require some extra coordination to get to the right log.

query insights

This is a good start to understand if full queries with params are what you really need, but as a robust sollution, no doubt, it's not feasible.

Did you check the system.opentelemetry_span_log? The docs on Open Telemetry support in CH look promising.

And there is always an option to parse the actual query using the alpha SQL parser: https://github.com/ClickHouse/clickhouse-js-parser and interpolate the parameters manually, but parsing in JS normally produces a lot of of temporary objects and strings and might create a visible pressure on GC.

Let me know which path you'd like to explore more.

[erikwlarsen]

Thanks for the replies Peter. What I'm really hoping to get here is just the text of the query as it is actually executed on the ClickHouse server. Ideally we could get this text field from the query ResultSet and then include it in our OpenSearch logging for easy debugging. I'd rather not have to do a multi-step process for each query we want to look at, if possible.