Skip to main content

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:

CREATE USER ingester
IDENTIFIED WITH sha256_hash BY '7e099f39b84ea79559b3e85ea046804e63725fd1f46b37f281276aae20f86dc3'
SETTINGS async_insert = 1

Examine the settings profile and assignment​

SHOW ACCESS
┌─ACCESS─────────────────────────────────────────────────────────────────────────────┐
│ ...                                                                                │
│ CREATE USER ingester IDENTIFIED WITH sha256_password SETTINGS async_insert = true  │
│ ...                                                                                │
└────────────────────────────────────────────────────────────────────────────────────┘

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

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

CREATE
SETTINGS PROFILE log_ingest SETTINGS async_insert = 1

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

CREATE USER ingester
IDENTIFIED WITH sha256_hash BY '7e099f39b84ea79559b3e85ea046804e63725fd1f46b37f281276aae20f86dc3'
SETTINGS PROFILE log_ingest

Using XML to create a settings profile and user​

/etc/clickhouse-server/users.d/users.xml
<clickhouse>
    <profiles>
        <log_ingest>
            <async_insert>1</async_insert>
        </log_ingest>
    </profiles>

    <users>
        <ingester>
            <password_sha256_hex>7e099f39b84ea79559b3e85ea046804e63725fd1f46b37f281276aae20f86dc3</password_sha256_hex>
            <profile>log_ingest</profile>
        </ingester>
        <default replace="true">
            <password_sha256_hex>7e099f39b84ea79559b3e85ea046804e63725fd1f46b37f281276aae20f86dc3</password_sha256_hex>
            <access_management>1</access_management>
            <named_collection_control>1</named_collection_control>
        </default>
    </users>
</clickhouse>

Examine the settings profile and assignment​

SHOW ACCESS
┌─ACCESS─────────────────────────────────────────────────────────────────────────────┐
│ CREATE USER default IDENTIFIED WITH sha256_password                                │
│ CREATE USER ingester IDENTIFIED WITH sha256_password SETTINGS PROFILE log_ingest   │
│ CREATE SETTINGS PROFILE default                                                    │
│ CREATE SETTINGS PROFILE log_ingest SETTINGS async_insert = true                    │
│ CREATE SETTINGS PROFILE readonly SETTINGS readonly = 1                             │
│ ...                                                                                │
└────────────────────────────────────────────────────────────────────────────────────┘

Assign a setting to a session​

SET async_insert =1;
SELECT value FROM system.settings where name='async_insert';
┌─value──┐
│ 1      │
└────────┘

Assign a setting during a query​

INSERT INTO YourTable
SETTINGS async_insert=1
VALUES (...)

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:

SET setting_name = DEFAULT

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

SET async_insert = 1;

SELECT value FROM system.settings where name='async_insert';

The response is:

┌─value──┐
│ 1      │
└────────┘

The following command sets its value back to 0:

SET async_insert = DEFAULT;

SELECT value FROM system.settings where name='async_insert';

The setting is now back to its default:

┌─value───┐
│ 0       │
└─────────┘

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.

<custom_settings_prefixes>custom_</custom_settings_prefixes>

To define a custom setting use SET command:

SET custom_a = 123;

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

SELECT getSetting('custom_a');

See Also