Yandex Cloud
Search
Contact UsGet started
  • Blog
  • Pricing
  • Documentation
  • All Services
  • System Status
    • Featured
    • Infrastructure & Network
    • Data Platform
    • Containers
    • Developer tools
    • Serverless
    • Security
    • Monitoring & Resources
    • ML & AI
    • Business tools
  • All Solutions
    • By industry
    • By use case
    • Economics and Pricing
    • Security
    • Technical Support
    • Customer Stories
    • Gateway to Russia
    • Cloud for Startups
    • Education and Science
  • Blog
  • Pricing
  • Documentation
Yandex project
© 2025 Yandex.Cloud LLC
Yandex Managed Service for ClickHouse®
  • Getting started
    • All guides
      • Information about existing clusters
      • Creating a cluster
      • Updating cluster settings
        • Server level
        • Query level
      • ClickHouse® version upgrade
      • Stopping and starting a cluster
      • Managing backups
      • Deleting a cluster
  • Access management
  • Terraform reference
  • Yandex Monitoring metrics
  • Audit Trails events
  • Public materials
  • Release notes

In this article:

  • Getting the list of ClickHouse® settings at the query level
  • Specifying ClickHouse® settings using the Yandex Cloud interfaces
  • Specifying ClickHouse® settings in a user account
  • Adding a new user with ClickHouse® settings
  • Changing ClickHouse® settings of a user
  • Specifying ClickHouse® settings in the settings profile
  • Creating a profile with ClickHouse® settings
  • Changing ClickHouse® settings in the settings profile
  • Specifying ClickHouse® settings in a session
  • Specifying ClickHouse® settings when connecting to a database via the command line
  1. Step-by-step guides
  2. Clusters
  3. Editing ClickHouse® settings
  4. Query level

Changing ClickHouse® settings at the query level

Written by
Yandex Cloud
Updated at May 5, 2025
  • Getting the list of ClickHouse® settings at the query level
  • Specifying ClickHouse® settings using the Yandex Cloud interfaces
  • Specifying ClickHouse® settings in a user account
    • Adding a new user with ClickHouse® settings
    • Changing ClickHouse® settings of a user
  • Specifying ClickHouse® settings in the settings profile
    • Creating a profile with ClickHouse® settings
    • Changing ClickHouse® settings in the settings profile
  • Specifying ClickHouse® settings in a session
  • Specifying ClickHouse® settings when connecting to a database via the command line

You can specify ClickHouse® settings at the query level to flexibly configure databases in a Managed Service for ClickHouse® cluster. You can specify settings in several ways:

  • Using the Yandex Cloud interfaces. This way, you can specify only the ClickHouse® settings available in Yandex Cloud.

  • Using SQL queries. This way you can specify any ClickHouse® settings at the query level. The method of specifying settings depends on their type:

    • User settings. In the CREATE USER and ALTER USER SQL queries, you can use the SETTINGS condition to provide settings. This way, settings will apply to the selected user only.

      To use this method, enable the User management via SQL option when creating or updating the cluster. After that, you will not be able to manage users using the Yandex Cloud interfaces; however, you cannot disable user management via SQL.

    • Settings at the profile level. In ClickHouse®, the settings profile contains the values and limitations of these settings as well as the list of roles and users to whom the profile applies. ClickHouse® settings are provided in the CREATE SETTINGS PROFILE and ALTER SETTINGS PROFILE SQL queries.

      To use this method, enable the User management via SQL option when creating or updating the cluster.

    • Session settings. During a session, you can specify ClickHouse® settings using the SET SQL query. This way you can specify settings in a cluster with any configuration, but they will apply to the current session only.

      This method is not suitable for all SQL editors: in some of them, every query runs in a separate session. Check the characteristics of your SQL editor before configuring a session.

    • Connection settings. When you connect to a database using the clickhouse-client utility, you can specify ClickHouse® settings using flags. This way you can specify settings in a cluster with any configuration, but they will apply to the currently established connection only.

      You can also specify connection settings in different drivers for ClickHouse® or provide them as URL parameters when sending ClickHouse® HTTP API requests. For more information about these methods, see the ClickHouse® documentation.

Getting the list of ClickHouse® settings at the query levelGetting the list of ClickHouse® settings at the query level

SQL
  1. Connect to the database in the cluster.

  2. Run this request:

    SELECT name, description, value FROM system.settings;
    

    The result contains names, descriptions, and values of ClickHouse® settings at the query level. The result displays values for the current session and the user who established that session.

Specifying ClickHouse® settings using the Yandex Cloud interfacesSpecifying ClickHouse® settings using the Yandex Cloud interfaces

Management console
CLI
Terraform
REST API
gRPC API

To configure ClickHouse®:

  1. In the management console, go to the folder page and select Managed Service for ClickHouse.
  2. Click the name of the cluster you need, then go the Users section.
  3. In the appropriate user row, click and select Settings.
  4. In the Advanced settings list, expand settings and specify the ClickHouse® settings.
  5. Click Save changes.

If you do not have the Yandex Cloud CLI yet, install and initialize it.

The folder specified when creating the CLI profile is used by default. To change the default folder, use the yc config set folder-id <folder_ID> command. You can specify a different folder using the --folder-name or --folder-id parameter.

To configure ClickHouse®:

  1. View the full list of settings specified for a user:

    yc managed-clickhouse user get <username> <cluster_name_or_ID>
    
  2. View the description of the CLI command to update the user settings:

    yc managed-clickhouse user update --help
    
  3. Set the required parameter values:

    yc managed-clickhouse user update <username> \
       --cluster-name=<cluster_name> \
       --settings="<parameter_1_name>=<value_1>,<parameter_2_name>=<value_2>,..."
    

To configure ClickHouse®:

  1. Open the current Terraform configuration file with an infrastructure plan.

    For more information about creating this file, see Creating clusters.

  2. In the Managed Service for ClickHouse® cluster user description, under settings, edit the following parameters:

    resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
      ...
      user {
        name = <username>
        ...
        settings {
          <parameter1_name> = <Value1>
          <parameter2_name> = <Value2>
          ...
        }
      }
      ...
    }
    
  3. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate
      

      Terraform will show any errors found in your configuration files.

  4. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan
      

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply
        
      2. Confirm updating the resources.

      3. Wait for the operation to complete.

For more information, see the Terraform provider documentation.

Time limits

A Terraform provider sets the timeout for Managed Service for ClickHouse® cluster operations:

  • Creating a cluster, including by restoring one from a backup: 60 minutes.
  • Editing a cluster: 90 minutes.
  • Deleting a cluster: 30 minutes.

Operations exceeding the set timeout are interrupted.

How do I change these limits?

Add the timeouts block to the cluster description, for example:

resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
  ...
  timeouts {
    create = "1h30m" # 1 hour 30 minutes
    update = "2h"    # 2 hours
    delete = "30m"   # 30 minutes
  }
}
  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"
    
  2. Use the User.update method and send the following request, e.g., using cURL:

    Warning

    The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the updateMask parameter as a single comma-separated string.

    curl \
        --request PATCH \
        --header "Authorization: Bearer $IAM_TOKEN" \
        --header "Content-Type: application/json" \
        --url 'https://mdb.api.cloud.yandex.net/managed-clickhouse/v1/clusters/<cluster_ID>/users/<username>' \
        --data '{
                  "updateMask": "<list_of_settings_to_update>",
                  "settings": { <ClickHouse®_settings> }
                }'
    

    Where:

    • updateMask: List of parameters to update as a single string, separated by commas.

    • settings: Required ClickHouse® settings with new values.

    You can request the cluster ID with the list of clusters in the folder. You can request the user name with the list of users in the cluster.

  3. View the server response to make sure the request was successful.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"
    
  2. Clone the cloudapi repository:

    cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
    

    Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

  3. Use the ClusterService.Update call and send the following request, e.g., via gRPCurl:

    Warning

    The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the update_mask parameter as an array of paths[] strings.

    Format for listing settings
    "update_mask": {
        "paths": [
            "<setting_1>",
            "<setting_2>",
            ...
            "<setting_N>"
        ]
    }
    
    grpcurl \
        -format json \
        -import-path ~/cloudapi/ \
        -import-path ~/cloudapi/third_party/googleapis/ \
        -proto ~/cloudapi/yandex/cloud/mdb/clickhouse/v1/user_service.proto \
        -rpc-header "Authorization: Bearer $IAM_TOKEN" \
        -d '{
              "cluster_id": "<cluster_ID>",
              "user_name": "<username>",
              "update_mask": {
                "paths": [
                  <list_of_settings_to_update>
                ]
              },
              "settings": { <ClickHouse®_settings> }
            }' \
        mdb.api.cloud.yandex.net:443 \
        yandex.cloud.mdb.clickhouse.v1.UserService.Update
    

    Where:

    • update_mask: List of parameters to update as an array of paths[] strings.

    • settings: Required ClickHouse® settings with new values.

    You can request the cluster ID with the list of clusters in the folder. You can request the user name with the list of users in the cluster.

  4. View the server response to make sure the request was successful.

Specifying ClickHouse® settings in a user accountSpecifying ClickHouse® settings in a user account

You can specify ClickHouse® settings when adding a new user or changing settings of an existing user.

Adding a new user with ClickHouse® settingsAdding a new user with ClickHouse® settings

SQL
  1. Connect to the DB in the cluster using the admin account.

  2. Create a user:

    CREATE USER <username>
       IDENTIFIED WITH sha256_password BY '<user_password>'
       SETTINGS <list_of_ClickHouse®_settings>;
    

    Note

    The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter or underscore.

    The password must be from 8 to 128 characters long.

    In the SETTINGS parameter, apart from with the setting value, you can specify its minimum and maximum value. Here is an example for the idle_connection_timeout setting:

    CREATE USER <username>
       IDENTIFIED WITH sha256_password BY 'password'
       SETTINGS idle_connection_timeout = 60 MIN 5 MAX 120;
    

    For more information about creating users, see the ClickHouse® documentation.

Changing ClickHouse® settings of a userChanging ClickHouse® settings of a user

SQL
  1. Connect to the DB in the cluster using the admin account.

  2. Update the user account.

    ALTER USER <username> SETTINGS <list_of_ClickHouse®_settings>;
    

    For more information on updating accounts, see the ClickHouse® documentation.

Specifying ClickHouse® settings in the settings profileSpecifying ClickHouse® settings in the settings profile

You can specify ClickHouse® settings when creating or updating a settings profile.

Creating a profile with ClickHouse® settingsCreating a profile with ClickHouse® settings

SQL
  1. Connect to the DB in the cluster using the admin account.

  2. Create a settings profile:

    CREATE SETTINGS PROFILE <settings_profile_name>
       SETTINGS <list_of_ClickHouse®_settings>;
    

    In the SETTINGS parameter, apart from with the setting value, you can specify its minimum and maximum value. Here is an example for the idle_connection_timeout setting:

    CREATE SETTINGS PROFILE <settings_profile_name>
       SETTINGS idle_connection_timeout = 60 MIN 5 MAX 120;
    

    You can attach a settings profile to a user:

    CREATE SETTINGS PROFILE <settings_profile_name>
       SETTINGS <list_of_ClickHouse®_settings>
       TO <username>;
    

    For more information about creating settings profiles, see the ClickHouse® documentation.

Changing ClickHouse® settings in the settings profileChanging ClickHouse® settings in the settings profile

SQL
  1. Connect to the DB in the cluster using the admin account.

  2. Update a settings profile:

    ALTER SETTINGS PROFILE <settings_profile_name>
       SETTINGS <list_of_ClickHouse®_settings>;
    

    In this query, you can specify the boundary values of settings and bind the profile to a user. For more information about changing settings profiles, see the ClickHouse® documentation.

Specifying ClickHouse® settings in a sessionSpecifying ClickHouse® settings in a session

SQL
  1. Connect to the database in the cluster.

  2. Run this request:

    SET <username> SETTINGS <list_of_ClickHouse®_settings>;
    

    The applied settings will be valid during the opened session only.

  3. Make sure the settings were applied:

    SELECT value FROM system.settings;
    

    To view the value of a setting, run this query:

    SELECT value FROM system.settings WHERE name='<setting_name>';
    

Specifying ClickHouse® settings when connecting to a database via the command lineSpecifying ClickHouse® settings when connecting to a database via the command line

SQL
  1. Set the dependencies required for connecting to a database.

  2. View the description of the command for connecting to a database:

    clickhouse-client --help
    

    Most flags in the command output are advanced ClickHouse® settings.

  3. Select the flags with the required settings by matching the flag names with the names of ClickHouse® settings.

  4. Specify the selected flags in the command for connecting to a database:

    • Connecting without SSL:

      clickhouse-client --host <FQDN_of_any_ClickHouse®_host> \
                        --user <username> \
                        --database <DB_name> \
                        --port 9000 \
                        --ask-password \
                        <flags_with_ClickHouse®_settings>
      
    • Connecting via SSL:

      clickhouse-client --host <FQDN_of_any_ClickHouse®_host> \
                        --secure \
                        --user <username> \
                        --database <DB_name> \
                        --port 9440 \
                        --ask-password \
                        <flags_with_ClickHouse®_settings>
      
    • Here is an example of connecting without SSL with the idle_connection_timeout setting:

      clickhouse-client --host rc1a-goh2a9tr********.mdb.yandexcloud.net \
                        --user user1 \
                        --database db1 \
                        --port 9440 \
                        --ask-password \
                        --idle_connection_timeout 60
      
  5. Make sure the settings were applied:

    SELECT value FROM system.settings;
    

    To view the value of a setting, run this query:

    SELECT value FROM system.settings WHERE name='<setting_name>';
    

ClickHouse® is a registered trademark of ClickHouse, Inc.

Was the article helpful?

Previous
Server level
Next
ClickHouse® version upgrade
Yandex project
© 2025 Yandex.Cloud LLC