Query-level Session Settings

Settings at the query-level

Overview

There are multiple ways to run statements with specific settings. Settings are configured in layers, and each subsequent layer redefines the previous values of a setting.

Order of priority

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.

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. Custom settings enable you to pass session-specific parameters that can be referenced within queries, policies, or functions. This is useful when you need to:

• Filter data based on user identity or organization
• Apply different business logic based on context
• Maintain stateful information across queries in a session

A custom setting name must begin with one of a number of predefined prefixes from a list you define. The list of prefixes can be specified using the custom_settings_prefixes server setting, defined in your server configuration file.

In the example below, SQL_ is chosen as the custom prefix:

<custom_settings_prefixes>SQL_</custom_settings_prefixes>

In ClickHouse Cloud it is not possible to specify a custom prefix. All custom user settings begin with prefix SQL_.

To define a custom setting use the SET command:

SET SQL_a = 123;

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

SELECT getSetting('SQL_a');

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'
-- highlight-next-line
SETTINGS async_insert = 1

Examine the settings profile and assignment

SHOW ACCESS

┌─ACCESS─────────────────────────────────────────────────────────────────────────────┐
│ ...                                                                                │
# highlight-next-line
│ 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'
-- highlight-next-line
SETTINGS PROFILE log_ingest

Using XML to create a settings profile and user

/etc/clickhouse-server/users.d/users.xml

<clickhouse>
# highlight-start
    <profiles>
        <log_ingest>
            <async_insert>1</async_insert>
        </log_ingest>
    </profiles>
# highlight-end

    <users>
        <ingester>
            <password_sha256_hex>7e099f39b84ea79559b3e85ea046804e63725fd1f46b37f281276aae20f86dc3</password_sha256_hex>
# highlight-start
            <profile>log_ingest</profile>
# highlight-end
        </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                                │
# highlight-next-line
│ CREATE USER ingester IDENTIFIED WITH sha256_password SETTINGS PROFILE log_ingest   │
│ CREATE SETTINGS PROFILE default                                                    │
# highlight-next-line
│ 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
-- highlight-next-line
SETTINGS async_insert=1
VALUES (...)

See also

• View the Settings page for a description of the ClickHouse settings.
• Global server settings