Changing ClickHouse® settings at the query level

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.

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.

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.

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.

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.

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.

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>';
   

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 rc1d-***.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>';