Contact UsGet started

Adding your own geobase in Managed Service for ClickHouse®

Written by
Updated at October 17, 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 regions_hierarchy.txt file with a hierarchy of regions. 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 regions_hierarchy_<suffix>.txt files with the same structure. To use an alternative geobase, pass this suffix when invoking 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.

  3. Create the regions_names.txt file with the region names. 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 regions_names_<language_code>.txt files with the same structure. For example, you may 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. Upload the geobase archive to Yandex Object Storage.

  2. Configure access to the geobase archive using a service account:

    1. Connect a service account to a cluster.
    2. Assign the account the role of storage.viewer.
    3. In the bucket ACL, grant the READ permission to the account.

  3. 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 passing the link to the archive with the added geobase in the geobase_uri parameter:

    yc managed-clickhouse cluster update-config <cluster_name_or_ID> \
     --set geobase_uri="<link_to_geobase_archive_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 how to create 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_geobase_archive_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 to be changed in the updateMask parameter.

ClickHouse® is a registered trademark of ClickHouse, Inc.

Previous
Managing database users
Next
Connecting external dictionaries