[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for ClickHouse®](../index.md) > [Step-by-step guides](index.md) > Clusters > Editing ClickHouse® settings > Query level

# Changing ClickHouse® settings at the query level

You can specify [ClickHouse® settings at the query level](https://clickhouse.com/docs/enen/operations/settings/query-level) to flexibly configure databases in a Managed Service for ClickHouse® cluster. You can specify settings using several methods:

* Using the [Yandex Cloud interfaces](#yandex-cloud-interfaces). This way you can only specify the [ClickHouse® settings available in Yandex Cloud](../concepts/settings-list.md#user-level-settings).
* Using SQL queries. This way you can specify any ClickHouse® settings at the query level. The way you set up ClickHouse® depends on the type of settings:

   * [User settings](#user). In the `CREATE USER` and `ALTER USER` SQL queries, you can use the `SETTINGS` condition to provide settings. This way, settings will only apply to the selected user.

      To use this method, enable **User management via SQL** when [creating](cluster-create.md) or [updating](update.md#SQL-management) the cluster. Once you enable this option, you will not be able to manage users via the Yandex Cloud interfaces, as user management via SQL cannot be disabled.

   * [Profile-level settings](#settings-profile). In ClickHouse®, the [settings profile](https://clickhouse.com/docs/enen/operations/access-rights#settings-profiles-management) contains the values and limits of these settings, along with the list of roles and users the profile applies to. To provide ClickHouse® settings, use the `CREATE SETTINGS PROFILE` and `ALTER SETTINGS PROFILE` SQL queries.

      To use this method, enable **User management via SQL** when [creating](cluster-create.md) or [updating](update.md#SQL-management) the cluster.

   * [Session settings](#session). During a session, you can provide ClickHouse® settings using the `SET` SQL query. This way you can specify settings in a cluster with any configuration, but they will only apply to the current session.

      This method does not work for all SQL editors, as some of them run each query in a separate session. Check the specifications of your SQL editor before configuring a session.

   * [Connection settings](#connection). When connecting to a database via [clickhouse-client](connect/clients.md#clickhouse-client), you can provide ClickHouse® settings using flags. This way you can specify settings in a cluster with any configuration, but they will only apply to the current connection.

      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 [this ClickHouse® guide](https://clickhouse.com/docs/enen/interfaces/overview).

## Getting a list of ClickHouse® settings at the query level {#get-list}

{% list tabs group=instructions %}

- SQL {#sql}

   1. [Connect](connect/clients.md) to the database in the cluster.
   1. Run this query:

      ```sql
      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.

{% endlist %}

## Specifying ClickHouse® settings using the Yandex Cloud interfaces {#yandex-cloud-interfaces}

{% list tabs group=instructions %}

- Management console {#console}

   To configure ClickHouse®:

   1. In the [management console](https://console.yandex.cloud), select the folder containing the cluster.
   1. Navigate to **Managed Service for ClickHouse**.
   1. Click the name of the cluster you need, then go the **Users** section.
   1. In the appropriate user row, click ![image](../../_assets/console-icons/ellipsis.svg) and select **Settings**.
   1. In the **Advanced settings** list, expand **settings** and [configure ClickHouse®](../concepts/settings-list.md#user-level-settings).
   1. Click **Save changes**.

- CLI {#cli}

   If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install).

   The folder used by default is the one specified when [creating](../../cli/operations/profile/profile-create.md) the CLI profile. To change the default folder, use the `yc config set folder-id <folder_ID>` command. You can also specify a different folder for any command using `--folder-name` or `--folder-id`. If you access a resource by its name, the search will be limited to the default folder. If you access a resource by its ID, the search will be global, i.e., through all folders based on access permissions.

   To configure ClickHouse®:

   1. View the full list of user-level settings:

      ```bash
      yc managed-clickhouse user get <username> <cluster_name_or_ID>
      ```

   1. View the description of the CLI command for updating user settings:

      ```bash
      yc managed-clickhouse user update --help
      ```

   1. Set the parameter values as needed:

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


- Terraform {#tf}

   To configure ClickHouse®:

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

      For information on how to create this file, see [Creating a cluster](cluster-create.md).

   1. In the Managed Service for ClickHouse® cluster user description, under `settings`, edit the parameters as follows:

      ```hcl
      resource "yandex_mdb_clickhouse_user" "<username>" {
        ...
        settings {
          <parameter_1_name> = <value_1>
          <parameter_2_name> = <value_2>
          ...
        }
      }
      ```

   1. 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.
      1. Run this command:
      
         ```bash
         terraform validate
         ```
      
         Terraform will show any errors found in your configuration files.

   1. Confirm updating the resources.

      1. Run this command to view the planned changes:
      
         ```bash
         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.
      
      1. If everything looks correct, apply the changes:
         1. Run this command:
      
            ```bash
            terraform apply
            ```
      
         1. Confirm updating the resources.
         1. Wait for the operation to complete.

   For more information, see [this Terraform provider guide](../../terraform/resources/mdb_clickhouse_user.md).


- REST API {#api}

    1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

        ```bash
        export IAM_TOKEN="<IAM_token>"
        ```

    1. Call the [User.Update](../api-ref/User/update.md) method, e.g., via the following [cURL](https://curl.se/) request:

        {% note 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.
        
        {% endnote %}

        ```bash
        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`: Comma-separated string of settings you want to update.

        * `settings`: Required [ClickHouse® settings](../concepts/settings-list.md#user-level-settings) with new values.

        You can get the cluster ID with the [list of clusters in the folder](cluster-list.md#list-clusters). You can get the username with the [list of users in the cluster](cluster-users.md#list-users).

    1. View the [server response](../api-ref/User/update.md#yandex.cloud.operation.Operation) to make sure your request was successful.

- gRPC API {#grpc-api}

    1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

        ```bash
        export IAM_TOKEN="<IAM_token>"
        ```

    1. Clone the [cloudapi](https://github.com/yandex-cloud/cloudapi) repository:
       
       ```bash
       cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
       ```
       
       Below, we assume that the repository contents reside in the `~/cloudapi/` directory.

    1. Call the [UserService.Update](../api-ref/grpc/User/update.md) method, e.g., via the following [gRPCurl](https://github.com/fullstorydev/grpcurl) request:

        {% note 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.
        
        {% cut "Format for listing settings" %}
        
        ```yaml
        "update_mask": {
            "paths": [
                "<setting_1>",
                "<setting_2>",
                ...
                "<setting_N>"
            ]
        }
        ```
        
        {% endcut %}
        
        {% endnote %}

        ```bash
        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 settings to update as an array of strings (`paths[]`).

        * `settings`: Required [ClickHouse® settings](../concepts/settings-list.md#user-level-settings) with new values.

        You can get the cluster ID with the [list of clusters in the folder](cluster-list.md#list-clusters). You can get the username with the [list of users in the cluster](cluster-users.md#list-users).

    1. View the [server response](../api-ref/grpc/User/create.md#yandex.cloud.operation.Operation) to make sure your request was successful.

{% endlist %}

## Specifying ClickHouse® settings in a user account {#user}

You can provide ClickHouse® settings when [adding a new user](#add-user) or [changing settings](#change-user-settings) of a current user.

### Adding a new user with ClickHouse® settings {#add-user}

{% list tabs group=instructions %}

- SQL {#sql}

   1. [Connect](connect/clients.md) to the database in the cluster as `admin`.
   1. Create a user:

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

      {% note info %}
      
      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.
      
      {% endnote %}

      Under `SETTINGS`, you can specify the setting value along with its minimum and maximum. Here is an example for the [idle_connection_timeout](https://clickhouse.com/docs/enen/operations/settings/settings#idle_connection_timeout) setting:

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

      For more on how to create users, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/sql-reference/statements/create/user).

{% endlist %}

### Changing ClickHouse® settings of a user {#change-user-settings}

{% list tabs group=instructions %}

- SQL {#sql}

   1. [Connect](connect/clients.md) to the database in the cluster as `admin`.
   1. Edit the user account:

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

      For more information on editing accounts, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/sql-reference/statements/alter/user).

{% endlist %}

## Specifying ClickHouse® settings in a settings profile {#settings-profile}

You can specify ClickHouse® settings when [creating](#create-settings-profile) or [updating a settings profile](#change-settings-profile).

### Creating a profile with ClickHouse® settings {#create-settings-profile}

{% list tabs group=instructions %}

- SQL {#sql}

   1. [Connect](connect/clients.md) to the database in the cluster as `admin`.
   1. Create a settings profile:

      ```sql
      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](https://clickhouse.com/docs/enen/operations/settings/settings#idle_connection_timeout) setting:

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

      You can assign a settings profile to a user:

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

      For more information about creating a settings profile, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/sql-reference/statements/create/settings-profile).

{% endlist %}

### Changing ClickHouse® settings in a settings profile {#change-settings-profile}

{% list tabs group=instructions %}

- SQL {#sql}

   1. [Connect](connect/clients.md) to the database in the cluster as `admin`.
   1. Update a settings profile:

      ```sql
      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 updating a settings profile, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/sql-reference/statements/alter/settings-profile).

{% endlist %}

## Specifying ClickHouse® settings in a session {#session}

{% list tabs group=instructions %}

- SQL {#sql}

   1. [Connect](connect/clients.md) to the database in the cluster.
   1. Run this query:

      ```sql
      SET <username> SETTINGS <list_of_ClickHouse®_settings>;
      ```

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

   1. Make sure the settings are applied:

      ```sql
      SELECT value FROM system.settings;
      ```

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

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

{% endlist %}

## Specifying ClickHouse® settings when connecting to a database via the command line {#connection}

{% list tabs group=instructions %}

- SQL {#sql}

   1. [Install the dependencies](connect/clients.md#clickhouse-client) required for connecting to a database.
   1. View the description of the command for connecting to a database:

      ```bash
      clickhouse-client --help
      ```

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

   1. Select the flags with the required settings by matching the flag names with the [names of ClickHouse® settings](https://clickhouse.com/docs/enen/operations/settings/settings).
   1. Provide the selected flags in the database connection command:

      * Connecting without SSL:

         ```bash
         clickhouse-client --host <FQDN_of_any_ClickHouse®_host> \
                           --user <username> \
                           --database <DB_name> \
                           --port 9000 \
                           --ask-password \
                           <flags_with_ClickHouse®_settings>
         ```

      
      * Connecting with SSL:

         ```bash
         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](https://clickhouse.com/docs/enen/operations/settings/settings#idle_connection_timeout) setting:

         ```bash
         clickhouse-client --host rc1a-goh2a9tr********.mdb.yandexcloud.net \
                           --user user1 \
                           --database db1 \
                           --port 9000 \
                           --ask-password \
                           --idle_connection_timeout 60
         ```

   1. Make sure the settings are applied:

      ```sql
      SELECT value FROM system.settings;
      ```

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

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

{% endlist %}

_ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._