Options Precedence

Understand how the query system handles query options set on projects, tables, or received queries.

The query head processes all query options from all sources and applies precedence rules to set the options for each query.

There is no composition of settings, and options with a higher precedence replace prior settings.

Precedence table

Precedence	Source of option	HTTP query API	ClickHouse Native	ClickHouse HTTP	MySQL	Apache Spark
lowest	System defaults	✓	✓	✓	✓	✓
	Org, project, and table	✓	✓	✓	✓
	HTTP query headers	✓
	HTTP query parameters	✓
highest	SQL SETTINGS clause	✓	✓	✓	✓

Query options sources

Query options can arrive from several sources.

System defaults

System defaults are hard-coded. See these values in Table of query options.

Organization, project, and table query options

Query options can be set at any level of the Config API hierarchy. See Projects and Tables for more details on the relationships between org, project, and table.

See the Config column in the Table of Query Options for query options available from this source.

These configuration settings are managed by a cluster administrator. See also Set query options using the API and Set query options using the UI.

HTTP query headers and parameters

The HTTP Query API understands HTTP headers or query parameters with query options. These are sent by the client.

See the Query column in the Table of Query Options for query options accepted using this mechanism.

SQL SETTINGS clause

For SQL dialects that support the SETTINGS clause, all Hydrolix query options are available. These are a part of the SQL query.

See the Settings column in the Table of Query Options for query options accepted using this mechanism.

Use query options

Query application clients can use three mechanisms to send query options along with their queries.

Use HTTP query headers

Send query options in the header X-Hdx-Query-Settings.

Each header can contain multiple comma separated key=value query options. Don't add a space after each comma separator.

Example of Multiple Query Options in a Single HTTP Header

X-Hdx-Query-Settings: hdx_query_pool_name=mypool,hdx_query_max_streams=12

When the query head receives duplicate HTTP headers (allowed in the HTTP specification) it reads all headers and uses the first query option encountered.

In this duplicate header GET example, hdx_query_output_format is set to tsv and the hdx_query_label is set to also-present.

First Query Option Wins if Found in Multiple HTTP Headers

X-Hdx-Query-Settings: hdx_query_output_format=tsv
X-Hdx-Query-Settings: hdx_query_output_format=json,hdx_query_label=also-present

Detailed example of multiple HTTP headers

$ curl \
  -X POST \
  --fail-with-body \
  --verbose \
  --data-binary "SELECT 'example-text'" \
  --header "Accept: application/json" \
  --header "Authorization: Bearer ${HDX_TOKEN}" \
  --header "X-Hdx-Query-Settings: hdx_query_output_format=tsv" \
  --header "X-Hdx-Query-Settings: hdx_query_output_format=json,hdx_query_label=also-present" \
  -- "${HDX_HYDROLIX_URL}/query"

 [ ... elided ... ]

> x-hdx-query-settings: hdx_query_output_format=tsv                                # (1)!
> x-hdx-query-settings: hdx_query_output_format=json,hdx_query_label=also-present  # (2)!
> content-length: 21
> content-type: application/x-www-form-urlencoded

 [ ... elided ... ]

< HTTP/2 200
< content-type: text/tab-separated-values; charset=UTF-8
< server: Hydrolix
< x-clickhouse-format: tsv                                                         # (3)!
< x-clickhouse-query-id: 75db294c-677b-4ca9-a3c5-a0e2976283f7
< x-clickhouse-timezone: UTC

 [ ... elided ... ]

example-text

1. This first HTTP header contains hdx_query_output_format and the value tsv is received and used.
2. The second HTTP header is also accepted. The hdx_query_output_format is ignored because the query option is already set. The new query option hdx_query_label is respected and the label also-present is added to metrics.
3. This illustrates the output format reported by the query head, which is tsv.

Use HTTP query parameters

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

Send Parameter OnlyParameter Takes Precedence Over HeaderSend Header Only

$ curl \
    --get \
    --fail-with-body \
    --silent \
    --url "${HDX_HYDROLIX_URL}/query" \
    --header "Authorization: Bearer ${HDX_TOKEN}" \
                                                                          \
    --data-urlencode "query=SELECT 'example-text' AS text" \
    --data "hdx_query_output_format=tsv"
example-text

The server responded with the tsv format.

$ curl \
    --get \
    --fail-with-body \
    --silent \
    --url "${HDX_HYDROLIX_URL}/query" \
    --header "Authorization: Bearer ${HDX_TOKEN}" \
    --header "X-Hdx-Query-Settings: hdx_query_output_format=CSVWithNames" \
    --data-urlencode "query=SELECT 'example-text' AS text" \
    --data "hdx_query_output_format=tsv"
example-text

The server responded with the tsv format.

$ curl \
    --get \
    --fail-with-body \
    --silent \
    --url "${HDX_HYDROLIX_URL}/query" \
    --header "Authorization: Bearer ${HDX_TOKEN}" \
    --header "X-Hdx-Query-Settings: hdx_query_output_format=CSVWithNames" \
    --data-urlencode "query=SELECT 'example-text' AS text" \

"text"
"example-text"

The server responded with the CSVWithNames format.

Use SQL SETTINGS clause

Use single quotes around string arguments, for example hdx_query_output_format = 'JSON'. Separate multiple query options using a comma ,.

Send Query Admin Comment Field in a SQL SETTINGS Clause

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'

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

Query options SETTINGS only work for SELECT and INSERT_INTO statements

Set query options using the API

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

• Update org query options
• Update project query options
• Update table query options

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.