Changing ClickHouse® settings at the query level

1. Connect to the database in the cluster.

2. Run this query:

   SELECT name, description, value FROM system.settings;
   

   The result contains names, descriptions, and values of ClickHouse® settings at the query level. All these are values for the current session and the user who initiated that session.

To configure ClickHouse®:

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

1. Connect to the database in the cluster as admin.

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.

   Under SETTINGS, you can specify the setting value along with its minimum and maximum. 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 this ClickHouse® guide.

1. Connect to the database in the cluster as admin.

2. Edit the user account:

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

   For more information on editing accounts, see this ClickHouse® guide.

1. Connect to the database in the cluster as admin.

2. Create a settings profile:

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

   Under SETTINGS, you can specify the setting value along with its minimum and maximum. 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 assign 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 this ClickHouse® guide.

1. Connect to the database in the cluster as admin.

2. Update a settings profile:

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

   In this query, you can define the setting minimum and maximum values and assign the profile to a user. For more information about changing settings profiles, see this ClickHouse® guide.

1. Connect to the database in the cluster.

2. Run this query:

   SET <username> SETTINGS <list_of_ClickHouse®_settings>;
   

   The settings you apply will only be valid for the current session.

3. Make sure the settings are 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. Provide the selected flags in the database connection command:

   • 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 with 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 are 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>';