Using query options

Parameters can be set via 4 different mechanisms:

• System level (for selected settings).
• Directly in SQL using SETTINGS within the query.
• In the query string parameters of the HTTP request.
• Via the HTTP Headers sent to the query endpoint.

The order of precedence is as follows:

• System Level settings are overridden by
  • HTTP Header settings are overridden by
    • Query String parameters are overridden by
      • SQL settings embedded in the SQL statement itself

📘

Native vs HTTP Query Interface.

If using the Native query interface HTTP Query String and Header Parameters are not available. A combination of System and SQL level settings should be used.

Setting query options via SQL SETTINGS

You can add a special statement in your SQL query to specify Hydrolix parameters.
This statement is SETTINGS and it has to be included at the end of your query.
You can have multiple Hydrolix settings by separating each one by a ,.

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 are writing the results of the query into S3 and we also provide a comment, that the user generating this query is Rita Miller.

🚧

Settings works only with SELECT

Using the settings in SQL works only for SELECT query, INSERT is not compatible with custom settings

Setting query options via query parameters

You can modify the behaviour of Hydrolix's query engine by attaching one or more optional parameters to your query API requests. These options simply take the form of additional HTTP parameters alongside the required query parameter.

Setting query options via HTTP headers

Additionally, you can set options in the HTTP headers via X-HDX-query-settings header. The header receives a comma-separated set of key=values. Note that you should not add a space after each comma separator.

The header, as required by HTTP protocol, can appear several times. If an option is repeated, the last value in the last header overrides the previous value.

Query example

In the example below, hdx_query_pool_name will be set to the value somepool and hdx_query_max_streamsto 12.

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

Default System-Wide Settings

Default system wide settings can be created through the Hydrolix API Query Options. Not all are covered and a list is provided below of those that are supported.

Standard Options

The following options can be employed to add further functionality to the execution of queries.

Output Formatting

While Hydrolix's query API returns results as JSON by default, it also supports every output format recognized by its underlying Clickhouse engine.

To specify the output format for a query, add an hdx_query_output_format parameter to your API request, setting that parameter's value to any of Clickhouse's supported output formats.

For example, to have the response to a query API GET request formatted as CSV:

https://YOUR-HYDROLIX-HOST.hydrolix.live/query/?query=YOUR-SQL-QUERY&hdx_query_output_format=CSV

hdx_query_output_file_enabled

Indicates whether you want to save a query result to a file on your cloud storage. The query is saved in the format instructed by hdx_query_output_format in a randomly generated filename.

hdx_query_output_filename

Indicates whether you want to save a query result to a file on your cloud storage. The query is saved in the format instructed by hdx_query_output_format in the filename specified. This will overwrite the file if it's already present.

hdx_query_comment

Add a comment to the query which is stored in active query. This allows a user to explain the query. For example, if you have a query running every X min as part of a reporting tool you can include this information in the comment of the query.

hdx_query_admin_comment

Add an admin comment to the query which is stored in active query. This field can be filled automatically by Superset or Grafana to include username information in order to track user activity.

Advanced Settings

Hydrolix also provides additional settings that can be applied to manage query execution and set various fine-tuning attributes on how Hydrolix processes a given query. Circuit Breakers & Rate Limiting options are also provided to protect your infrastructure.

In most cases, you won't need to change any of these settings from their default values. Hydrolix's query engine is already optimized to work with the resource-allocation and caching settings already represented by these defaults.

If you have any questions about improving your queries' performance, please contact Hydrolix support.