Query Options Precedence

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

Overview of precedence

Queries to the Hydrolix system use flexible query option to maximize control. This page describes the order of resolution of precedence when the query engine processes options from multiple sources.

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.

See also Projects and Tables for more details 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.

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

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.

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.

Set query options using the API

Use the following Config API calls, one for each level of the hierarchy

Set query options using the UI

The Query options flyout menu is found in different places, depending on the place in the hierarchy.

For organization, visit Data > Organization Settings.

For project, visit Data > Tables and use the + to expand a project.

For table, visit Data > Tables, select a table, and scroll to the Advanced options section of the table detail page.

Adjust the query options values

  1. Click the vertical ellipsis (⋮) and select Edit for an org or table. Select Query options if it's a project.
  2. Change query option values in the left-side flyout.
  3. Scroll to the bottom of the flyout and select Save changes.

The changes to the query options go into effect immediately.