[Feature request] Allow for Query Hints / Settings to be accessible to query builder for a given driver

[Korg95]

Is your feature request related to a problem? Please describe.

In an example such as ClickHouse, there are many settings that can be applied at the server, session, or query level to optimize performance. However, the best setting for one query may not be ideal for others. Relying on server-level settings for query performance often leads to suboptimal results for certain queries, especially those with varying parameters. Many queries can benefit from runtime hints/settings to improve performance based on their specific needs (Telemetry might be another case).

Currently, Cube.js forces us to use SQL instead of table names in cube definitions as a workaround to apply settings. This causes subqueries to be formed where the setting is only applied to the subquery, rather than the entire query. As a result, the intended settings are not fully applied, leading to performance inefficiencies, particularly for real-time applications. There are also potentially databases where applying settings at the subquery level is not possible, making this workaround even more problematic.

Describe the solution you'd like

We would like Cube.js to support the ability to pass query hints or settings to the query builder, specific to a given driver. Ideally, these hints could be calculated dynamically during query_rewrite and then passed down to the query generation.

Additionally, allowing API callers to pass parameters that would control query hints would provide better control over query execution, especially when there is no direct customer exposure. For example setting of parameters may be calculated via a call to a library in CSharp which is our main language for example. Forcing individuals to use query_rewrite to do it all might be a showstopper in this case.

An alternative on this latter part to avoid this low level hinting would be to use schema to help schematize the options available for a given datastore to pass to the query generation layer. So when you query a cube linked to a datastore, a range of options would be available.

Describe alternatives you've considered

Using raw SQL instead of table names in cube definitions to allow for injection of settings using filter params. This workaround forces the query to become a subquery, limiting the scope of the applied settings to the subquery rather than the entire query. This not only leads to inefficiencies but may also not be possible in languages where settings cannot be applied to subqueries.
Manually setting certain parameters at the server or session level, though this does not offer the flexibility needed for queries with varied workloads and priorities.

Another workaround is to ask cube for the raw SQL and then alter it as required and execute the query yourself. This is useful ofcourse for edge case scenarios where you might do things like alter the query and export the result to s3 but it totally defeats the purpose of cube for regular query scenarios.

Additional context
Some specific use cases where query hints/settings would be beneficial:

• Distributed queries in ClickHouse Cloud require settings to use more than one replica.
• Longer lookback queries could benefit from allocating more threads, ensuring better latency for less frequent, long-running queries.
• Overriding the join algorithm for specific queries to optimize execution plans.
• Setting a query ID for end-to-end query tracing and monitoring.

Providing this flexibility would improve query execution performance, especially for real-time applications.

If there is existing functionality to do this, happy to move this to a question so that others can find the info there.

Thank you!

[igorlukanin]

Hi @Korg95 👋

Thanks for a very elaborate proposal, I really appreciate that. That being said, I have marked this as ClickHouse-specific as we do not (currently?) get many similar requests regarding other data sources.

I'm also thankful for the list of specific use cases. May you please provide an example of parameter setting for each one of them? Some minimal yet working SQL for each example would really help—I don't want to misunderstand what is really required, hence my ask.

My current understanding is that most probably the solution might along the lines of allowing to initialize a database driver in Cube with an additional SQL that is executed once per connection. Further customization would be possible via defining multiple data sources and using the right one in each cube.

[Korg95]

Hi @igorlukanin thank you for your reply! Let me construct a proper response as per your asks to clarify

[alevene]

Setting a query ID for end-to-end query tracing and monitoring.

+1 specifically to this use case. On each query submitted to ClickHouse, the ability to send a custom setting allows for tracing a request from the client to a ClickHouse query.

Example

SELECT
FROM cube.table
SETTINGS SQL_QUERY_ID = '33433698'

Once this is passed through as a setting, queries can be traced with the query_log

SELECT *
FROM clusterAllReplicas('{cluster}', system.query_log)
WHERE mapContains("Settings", 'SQL_QUERY_ID')
AND "Settings"['SQL_QUERY_ID'] = '33433698';

Without Cube having this capability, the client requires a post-processing step to tack this SETTINGS clause on to the end.

[igorlukanin]

@alevene, thanks for providing the details! You can achieve this by passing necessary values (e.g., a tracing id) via the security context and then using those to compose the query: https://cube.dev/docs/reference/data-model/context-variables#compile_context

There might be a performance hit but it should not prevent you from achieving this in principle.

[igorlukanin]

@alevene That being said, let me also add that it is not correct to think that Cube executes queries synchronously, one query to the upstream data source per each query to Cube's API. Cube uses a query queue and caches query results. So, it is possible that the results of a previously run query would be reused for another one—and that would also hinder the tracing goals that you're trying to achieve, I guess. Using the security contact would allow to not have this issue though since cache is not shared between clients with distinct security contexts, as determined by context_to_app_id.

[alevene]

@alevene, thanks for providing the details! You can achieve this by passing necessary values (e.g., a tracing id) via the security context and then using those to compose the query: https://cube.dev/docs/reference/data-model/context-variables#compile_context

There might be a performance hit but it should not prevent you from achieving this in principle.

@igorlukanin thanks for responding. Per this recommendation, how exactly would we use compile_context to achieve this? The SETTINGS clause needs to be placed at the very end of the query (after ORDER BY and/or LIMIT), so is there a way to submit a query via the REST API to do this?
My understanding is that jinja replacement would only affect table and column composition, not produce a trailing clause like this.

[Korg95]

Hi guys, got the time to come with a single concise example and respond to the discussion so far. Thanks @alevene for your example.

Like @alevene said, the issue comes down to where the settings clause is placed for Clickhouse queries.

Say I have a query below and I apply the setting max_threads = 10 applied to it. The reason could be that this query looks over a longer range of time. To aid these adhoc queries running within SLA we might add this setting specifically for this query, for example after analyzing the lookback in days. The key aspect here is to have control over the eventually executing query.

select
    *
from
    table
where date >= 'date_far_in_the_past'
settings max_threads =  10

To achieve this is Cube would mean using a sql: definition instead of sql_table:. The max_threads being passed via security context as you say. The issue however is that what gets generated is a subquery which runs in isolation before the higher level query executes resulting in sub-optimal performance.

select
    *
from
    (select * from table settings max_threads = 10)
where date >= 'date_far_in_the_past'

What are the side-effects of using a subquery?

• Limits the settings clause to the subquery and not the entire query resulting in certain parts of the physical plan lacking resources.
• Prevents setting query id for telemetry purposes as reported above. Note that if a query is cached in cube and therefore we don't get the query id into CH that's okay, usually the query id actually is a "tracing" id generated at the top level request to track all the various sub-requests i.e. span measurement. Being able to search various systems with a single id is a great win.
• Sub Queries increase complexity for the optimizer. This is usually a non-issue but still observable in higher QPS cases.

I think that this feature does apply to other databases. I know that MS SQL Server can only accept an options clause at the very end. labelling is very commonly applied here as well and just like ClickHouse with managing various query settings.

Whilst this is about a settings clause for ClickHouse at its core the issue is that enterprise firms tend to have edge cases that need a non-intrusive entry point to make adjustments that really apply only to that firm.

A simpler idea?
I'd like to touch again on a simpler idea of how to globally benefit all data sources.

In Entity Framework there is the well known concept of query interception. Like the name suggests once the final query is generated a set of interceptors can run. They essentially allow for each interceptor to receive the query about to be executed, allow for analysis, rewrite and the pass it on for execution.

The key benefit over this implementation is that cube in replicating this feature could have access to all the arguments of the query to make these decisions plus anything potentially injected into the security_context etc.

I still think that custom arguments from the application code that may be using libs/calls based in their native language from the query would need to be injectable into the security_context. It must be noted it is a security hazard, limiting it to internal service calls could mitigate this by limiting the feature to service accounts with the correct JWT based role. The choice could be left to the user to decide how much logic to embed into cube and how much to precompute as strings to pass over for interpolation.

The way this could be implemented is in the form of a callback that is passable to the method in the driver class that makes the call to the db. Should the maintainer of the driver want to provide this functionality they can optionally invoke the callback to re-write the query with the interceptor.

The callbacks once per db requiring it could be defined and registered in cube.py/cube.js with the appropriate one being invoked when that db is called. This approach I think disrupts the flow of query the least and allows for the flexibility of non-end-user facing applications to get exactly what they want. In a case of dealing with edge cases which are ever so prevalent in the enterprise world this flexibility would de-risk cube for many evaluators of the project.