Documentation
ProductPricingDocsBlog

Query Options Precedence

Explains query option hierarchy, precedence and support in interfaces such as HTTP API and SQL dialects.

Suggest Edits

Overview of precedence

Query options provide control over the Hydrolix query system. These settings control output format, pool selection, query rate limiting, circuit breakers and observability features. They're available over the many Query Interfaces.

The wide array of mechanisms for setting query options is intended to support as many query interfaces and client applications as possible. For example, some HTTP query clients can set query parameters, but can't easily add HTTP headers.

For every query, options are collected from all of the below supported by that
interface. Later-defined query options always take precedence.

  • System defaults (all interfaces)
  • Organization (org), project, table query options (in-cluster interfaces)
  • HTTP query headers (HTTP query API only)
  • HTTP query parameters (HTTP query API only)
  • SQL SETTINGS clause (for clients with SQL dialect supporting SETTINGS)

The last applied query option setting wins.

Categories of query options

See Table of Query Options for a listing of the several categories of query options.

Inheritance hierarchy

Every query processes the options hierarchy gathering options that will apply for the query execution.

Options encountered later completely replace prior settings, and there is no composition of settings.

Therefore, the SQL SETTINGS clause has the highest precedence.

System defaults

System defaults are listed in Table of Query Options.

Organization, project, and table query options

Query options can be set at any level of the cluster hierarchy. Use the Hydrolix UI or the following Config API calls:

See also Projects and Tables for more detail on the relationships between org, project, and table.

HTTP query headers

The HTTP Query API accepts query options with the header X-HDX-query-settings. In the case of duplicate HTTP headers (allowed by specification) later settings will take precedence.

Each X-HDX-query-settings header may contain multiple comma separated key=value query options. Don't add a space after each comma separator.

HTTP query parameters

Attach one or more optional parameters to your query API requests, to convey query options to the Hydrolix query engine for a specific request. These options take the form of additional HTTP parameters alongside the required query parameter.

Example: Parameters take precedence

When a single HTTP request includes the same query option setting in both HTTP headers and parameters, the latter will win.

In this simple GET example, demonstrating both query parameters and headers, hdx_query_pool_name will be set to the value alternatepool and hdx_query_max_streamsto 10.

GET <YOUR-HYDROLIX-HOST>/query/?query=....&hdx_query_pool_name=alternatepool
X-HDX-query-settings: hdx_query_pool_name=mypool,hdx_query_max_streams=12
...
X-HDX-query-settings: hdx_query_max_streams=10

SQL SETTINGS clause

For SQL dialects that support the SETTINGS clause, all Hydrolix query options are available. Multiple query options should be separated by a comma ,.

Use single quotes around string arguments, for example hdx_query_output_format = 'JSON'.

Example: Set query admin comment using SQL

In this example, we're writing the results of the query into the storage bucket and providing a comment that the user generating this query is Rita Miller.

SELECT COUNT() FROM
sample.cts
WHERE timestamp >= toDateTime(1636289714)
AND timestamp <= toDateTime(1636376114)
AND arrayJoin(data.leaf_cert.all_domains) LIKE '%hydrolix.live%'
SETTINGS hdx_query_output_file_enabled='true', 
         hdx_query_admin_comment='User: Rita Miller'

🚧

Query options SETTINGS only work for SELECT

SQL statements other than SELECT use other Hydrolix subsystems, like Ingest, for example, which don't support these query options.

Support across interfaces

The precedence hierarchy is the same regardless of query interface used, but the HTTP header and query parameters are unsupported via ClickHouse or MySQL interfaces, as these are transported over a non-HTTP protocol.

HTTP Query API

  • System defaults
  • Organization, project, table query options
  • HTTP query headers
  • HTTP query parameters
  • SQL SETTINGS

ClickHouse native and MySQL Clients

Both ClickHouse native and MySQL dialects support the SETTINGS option. Query options precedence for clients using these interfaces is as follows:

  • System defaults
  • Organization, project, table query options
  • SQL SETTINGS

The ClickHouse SQL dialect also supports the SQL FORMAT clause which understands any ClickHouse formats. If a FORMAT clause is supplied, it will take precedence over any setting of query_hdx_output_format.

Apache Spark Connector

The Apache Spark Connector embeds the Hydrolix engine into the client software, which natively reads Hydrolix partitions.

The Spark SQL dialect doesn't support SETTINGS so the system defaults apply.

Updated about 9 hours ago