Skip to main content
Skip to main content
Edit this page

Query-level Settings

There are multiple ways to set ClickHouse query-level settings. Settings are configured in layers, and each subsequent layer redefines the previous values of a setting.

The order of priority for defining a setting is:

  1. Applying a setting to a user directly, or within a settings profile

    • SQL (recommended)
    • adding one or more XML or YAML files to /etc/clickhouse-server/users.d

  2. Session settings

    • Send SET setting=value from the ClickHouse Cloud SQL console or clickhouse client in interactive mode. Similarly, you can use ClickHouse sessions in the HTTP protocol. To do this, you need to specify the session_id HTTP parameter.

  3. Query settings

    • When starting clickhouse client in non-interactive mode, set the startup parameter --setting=value.
    • When using the HTTP API, pass CGI parameters (URL?setting_1=value&setting_2=value...).
    • Define settings in the SETTINGS clause of the SELECT query. The setting value is applied only to that query and is reset to the default or previous value after the query is executed.

Examples​

These examples all set the value of the async_insert setting to 1, and show how to examine the settings in a running system.

Using SQL to apply a setting to a user directly​

This creates the user ingester with the setting async_inset = 1:

Examine the settings profile and assignment​

Using SQL to create a settings profile and assign to a user​

This creates the profile log_ingest with the setting async_inset = 1:

This creates the user ingester and assigns the user the settings profile log_ingest:

Using XML to create a settings profile and user​

Examine the settings profile and assignment​

Assign a setting to a session​

Assign a setting during a query​

Converting a Setting to its Default Value​

If you change a setting and would like to revert it back to its default value, set the value to DEFAULT. The syntax looks like:

For example, the default value of async_insert is 0. Suppose you change its value to 1:

The response is:

The following command sets its value back to 0:

The setting is now back to its default:

Custom Settings​

In addition to the common settings, users can define custom settings.

A custom setting name must begin with one of predefined prefixes. The list of these prefixes must be declared in the custom_settings_prefixes parameter in the server configuration file.

To define a custom setting use SET command:

To get the current value of a custom setting use getSetting() function:

See Also