Contact UsGet started

Adding your own geobase in Managed Service for ClickHouse®

Written by
Updated at November 18, 2024

Geobases in ClickHouse® are text files containing the hierarchy and names of regions. You can add several alternative geobases to ClickHouse® to support different stances on how regions pertain to countries. For more information, see the ClickHouse® documentation.

To add your own geobase to a ClickHouse® cluster:

  1. Create a geobase.
  2. Upload the geobase to Yandex Object Storage.
  3. Add the geobase to a ClickHouse® cluster.

Creating a geobase

  1. Create a file named regions_hierarchy.txt. The file must be in TSV tabular format without headers and with 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.

  2. To add an alternative hierarchy of regions, create the regions_hierarchy_<suffix>.txt files with the same structure. To use an alternative geobase, pass this suffix when invoking the function. For example:

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

  3. Create a file named regions_names.txt. The file must be in TSV tabular format without headers and with the following columns:

    • Region ID (UInt32)
    • Region name (String): Cannot contain tab or newline characters, even escaped ones.

  4. 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.

  5. Create a tar, tar.gz, or zip archive from the geobase files.

Uploading a geobase to Yandex Object Storage

Managed Service for ClickHouse® only works with publicly readable geobases that are uploaded to Yandex Object Storage:

  1. To bind your service account to the cluster, make sure your account in Yandex Cloud is assigned the iam.serviceAccounts.user role or higher.
  2. Upload the geobase archive to Yandex Object Storage.
  3. Connect a service account to a cluster. Using your service account you will set up access to the geobase archive.
  4. Assign the storage.viewer role to the service account.
  5. In the bucket's ACL, add the READ permission to the service account.
  6. Get a link to the geobase archive.

Adding the geobase to the ClickHouse® cluster

  1. In the management console, go to the folder page and select Managed Service for ClickHouse.
  2. Select the cluster and click Edit in the top panel.
  3. Under DBMS settings, click Settings.
  4. In the Geobase uri field, enter a link to the geobase archive in Yandex Object Storage.

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To add a geobase:

  1. View a description of the update cluster configuration CLI command:

    yc managed-clickhouse cluster update-config --help

  2. Run the command by providing the link to the archive with the connected geobase in the geobase_uri parameter:

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

    You can request the cluster ID and name with a list of clusters in the folder.

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

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

  2. Add the geobase_uri parameter with a link to the archive with the geobase to connect in Yandex Object Storage to the Managed Service for ClickHouse® cluster settings:

    resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
  ...
  clickhouse {
    config {
      geobase_uri = "<link_to_archive_with_geobase_in_Object_Storage>"
      ...
    }
  ...
  }
...
}

  3. Make sure the settings are correct.

    1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

    2. Run the command:

      terraform validate

      If there are errors in the configuration files, Terraform will point to them.

  4. Confirm updating the resources.

    1. Run the command to view planned changes:

      terraform plan

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

    2. If you are happy with the planned changes, apply them:

      1. Run the command:

        terraform apply

      2. Confirm the update of 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
  }
}

To add a geobase to a ClickHouse® cluster, use the update REST API method for the Cluster resource or the ClusterService/Update gRPC API call and provide the link to the geobase archive in Object Storage in the geobaseUri parameter.

Warning

This API method resets any cluster settings that are not provided explicitly in the request to their defaults. To avoid this, make sure to provide the names of the fields you want to change in the updateMask parameter.

ClickHouse® is a registered trademark of ClickHouse, Inc.

Previous
Managing database users
Next
Connecting external dictionaries