[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for ClickHouse®](../index.md) > [Step-by-step guides](index.md) > Storing and processing data > Managing a custom geobase

# Managing a custom geobase in Managed Service for ClickHouse®

A ClickHouse® geobase consists of text files containing the names and hierarchy of regions. You can add several alternative geobases to ClickHouse® to reflect different points of view on what regions belong to what countries. For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/ru/sql-reference/dictionaries/internal-dicts/).

To add a custom geobase to a ClickHouse® cluster:

1. [Create a geobase](#create).
1. [Upload the geobase to Yandex Object Storage](#upload).
1. [Add the geobase to a ClickHouse® cluster](#add).

If you are not using your custom geobase, [remove it](#disconnect).

## Creating a geobase {#create}

1. Create a file named `regions_hierarchy.txt`. It must be a [TSV](https://en.wikipedia.org/wiki/Tab-separated_values) table without headers, containing the following columns:
   * Region ID (UInt32)
   * Parent region ID (UInt32)
   * Region type (UInt8):
        * `1`: Continent
        * `3`: Country
        * `4`: Federal district
        * `5`: Region
        * `6`: City
   * Population (UInt32): Optional column.
1. To add an alternative hierarchy of regions, create the `regions_hierarchy_<suffix>.txt` files with the same structure. To use an alternative geobase, provide this suffix when calling the function. Here is an example:

    * `regionToCountry(RegionID)`: Uses the default dictionary, `regions_hierarchy.txt`.
    * `regionToCountry(RegionID, 'alt')`: Uses the dictionary with the `alt` suffix, `regions_hierarchy_alt.txt`.

1. Create a file named `regions_names.txt`. It must be a [TSV](https://en.wikipedia.org/wiki/Tab-separated_values) table without headers, containing the following columns:

    * Region ID (UInt32).
    * Region name (String): It cannot contain tab or newline characters, even escaped ones.

1. To add region names in other languages to your geobase, create the `regions_names_<language_code>.txt` files with the same structure. For example, you can create `regions_names_en.txt` for English and `regions_names_tr.txt` for Turkish.
1. Create a `tar`, `tar.gz`, or `zip` archive with the geobase files.

## Uploading a geobase to Yandex Object Storage {#upload}

Managed Service for ClickHouse® only works with geobases uploaded to Yandex Object Storage and accessible for reading:


1. To attach a [service account](../../iam/concepts/users/service-accounts.md) to a cluster, [assign](../../iam/operations/roles/grant.md) the [iam.serviceAccounts.user](../../iam/security/index.md#iam-serviceAccounts-user) role or higher to your Yandex Cloud account.
1. [Upload](../../storage/operations/objects/upload.md) the geobase archive to Yandex Object Storage.
1. [Attach the service account to the cluster](s3-access.md#connect-service-account). You will use this [service account](../../iam/concepts/users/service-accounts.md) to configure access to the geobase archive.
1. [Assign](s3-access.md#configure-acl) the `storage.viewer` role to the service account.
1. In the bucket ACL, [add](../../storage/operations/buckets/edit-acl.md) the `READ` permission to the service account.
1. [Get a link](s3-access.md#get-link-to-object) to the geobase archive.


## Adding a geobase to a ClickHouse® cluster {#add}

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select the folder containing the cluster.
  1. Navigate to **Managed Service for&nbsp;ClickHouse**.
  1. Select your cluster and click **Edit** in the top panel.
  1. Under **DBMS settings**, click **Settings**.
  1. In the **Geobase uri** field, enter a link to the geobase archive in Yandex Object Storage.

- 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 add a geobase:

    1. View the description of the CLI command for updating the cluster configuration:

        ```bash
        yc managed-clickhouse cluster update-config --help
        ```

    1. Run this command, providing the link to the archive with the geobase being added in the `geobase_uri` parameter:

        ```bash
        yc managed-clickhouse cluster update-config <cluster_name_or_ID> \
             --set geobase_uri="<link_to_geobase_archive_in_Object_Storage>"
        ```

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


- Terraform {#tf}

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

        For more on how to create such a file, see the [Creating a cluster](cluster-create.md) section.

    1. In the Managed Service for ClickHouse® cluster settings, add the `geobase_uri` parameter with the link to the archive containing the geobase being added in Yandex Object Storage:

        ```hcl
        resource "yandex_mdb_clickhouse_cluster_v2" "<cluster_name>" {
          ...
          clickhouse = {
            config = {
              geobase_uri = "<link_to_geobase_archive_in_Object_Storage>"
              ...
            }
          ...
          }
          ...
        }
        ```

    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 resource changes.

        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_cluster.md).

    {% note warning "Timeouts" %}
    
    The Terraform provider sets the following timeouts for Managed Service for ClickHouse® cluster operations:
    
    * Creating a cluster, including by restoring from a backup: 60 minutes.
    * Updating a cluster: 90 minutes.
    * Deleting a cluster: 30 minutes.
    
    Operations exceeding the timeout are aborted.
    
    {% cut "How to change these limits" %}
    
    Add the `timeouts` section to your cluster description, such as the following:
    
    ```hcl
    resource "yandex_mdb_clickhouse_cluster_v2" "<cluster_name>" {
      ...
      timeouts = {
        create = "1h30m" # 1 hour 30 minutes
        update = "2h"    # 2 hours
        delete = "30m"   # 30 minutes
      }
    }
    ```
    
    {% endcut %}
    
    {% endnote %}


- 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 [Cluster.Update](../api-ref/Cluster/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>' \
            --data '{
                      "updateMask": "configSpec.clickhouse.config.geobaseUri",
                      "configSpec": {
                        "clickhouse": {
                          "config": {
                            "geobaseUri": "<link>"
                          }
                        }
                      }
                    }'
        ```

        Where:

        * `updateMask`: Comma-separated string of settings to update.

            Here, we only specified a single setting, `configSpec.clickhouse.config.geobaseUri`.

        * `configSpec.clickhouse.config.geobaseUri`: Link to the geobase archive in Object Storage.

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

    1. Check the [server response](../api-ref/Cluster/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 [ClusterService.Update](../api-ref/grpc/Cluster/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/cluster_service.proto \
            -rpc-header "Authorization: Bearer $IAM_TOKEN" \
            -d '{
                  "cluster_id": "<cluster_ID>",
                  "update_mask": {
                    "paths": [
                      "config_spec.clickhouse.config.geobase_uri"
                    ]
                  },
                  "config_spec": {
                    "clickhouse": {
                      "config": {
                        "geobase_uri": "<link>"
                      }
                    }
                  }
                }' \
            mdb.api.cloud.yandex.net:443 \
            yandex.cloud.mdb.clickhouse.v1.ClusterService.Update
        ```

        Where:

        * `update_mask`: List of settings to update as an array of strings (`paths[]`).

            Here, we only specified a single setting, `config_spec.clickhouse.config.geobase_uri`.

        * `config_spec.clickhouse.config.geobase_uri`: Link to the geobase archive in Object Storage.

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

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

{% endlist %}

## Removing a geobase {#disconnect}

{% list tabs group=instructions %}

- Management console {#console}

    1. In the [management console](https://console.yandex.cloud), select the folder containing the cluster.
    1. Navigate to **Managed Service for&nbsp;ClickHouse**.
    1. Select your cluster and click **Edit** in the top panel.
    1. Under **DBMS settings**, click **Settings**.
    1. Delete the value in the **Geobase uri** field.

- 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 remove a geobase:

    1. View the description of the CLI command for updating the cluster configuration:

        ```bash
        yc managed-clickhouse cluster update-config --help
        ```

    1. Run this command, providing an empty value in the `geobase_uri` parameter:

        ```bash
        yc managed-clickhouse cluster update-config <cluster_name_or_ID> \
             --set geobase_uri=""
        ```

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


- 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 [Cluster.Update](../api-ref/Cluster/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>' \
            --data '{
                      "updateMask": "configSpec.clickhouse.config.geobaseUri",
                      "configSpec": {
                        "clickhouse": {
                          "config": {
                            "geobaseUri": ""
                          }
                        }
                      }
                    }'
        ```

        Where:

        * `updateMask`: Comma-separated string of settings to update.

            Here, we only specified a single setting, `configSpec.clickhouse.config.geobaseUri`.

        * `configSpec.clickhouse.config.geobaseUri`: Link to the geobase archive in Object Storage. Set an empty value for this parameter.

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

    1. Check the [server response](../api-ref/Cluster/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 [ClusterService.Update](../api-ref/grpc/Cluster/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/cluster_service.proto \
            -rpc-header "Authorization: Bearer $IAM_TOKEN" \
            -d '{
                  "cluster_id": "<cluster_ID>",
                  "update_mask": {
                    "paths": [
                      "config_spec.clickhouse.config.geobase_uri"
                    ]
                  },
                  "config_spec": {
                    "clickhouse": {
                      "config": {
                        "geobase_uri": ""
                      }
                    }
                  }
                }' \
            mdb.api.cloud.yandex.net:443 \
            yandex.cloud.mdb.clickhouse.v1.ClusterService.Update
        ```

        Where:

        * `update_mask`: List of settings to update as an array of strings (`paths[]`).

            Here, we only specified a single setting, `config_spec.clickhouse.config.geobase_uri`.

        * `config_spec.clickhouse.config.geobase_uri`: Link to the geobase archive in Object Storage. Set an empty value for this parameter.

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

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

{% endlist %}

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