Options Precedence
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
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.
Hydrolix Connector for Apache Spark⚓︎
The Hydrolix Connector for Apache Spark 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
- Click the vertical ellipsis (⋮) and select Edit for an org or table. Select Query options if it's a project.
- Change query option values in the left-side flyout.
- Scroll to the bottom of the flyout and select Save changes.
The changes to the query options go into effect immediately.