Adding your own geobase

Managed Service for ClickHouse contains a built-in geobase dictionary and a set of functions for working with it. The built-in dictionary lets you:

• Get the name of a region in the required language by its ID.
• Get the ID of a city, region, federal district, country, and continent by the region ID.
• Check whether one region is part of another.
• Get a chain of parent regions.

Learn more about geobase functions in the ClickHouse documentation.

If you're not satisfied with the built-in geobase, you can add your own to ClickHouse clusters:

1. Create a geobase.
2. Upload the geobase to Yandex.Cloud.
3. Add the geobase to the ClickHouse cluster.

Create a geobase

Geobases in ClickHouse are text files containing the hierarchy and names of regions. You can add several alternative geobases to ClickHouse to reflect alternative viewpoints on the regional structure of different countries. Learn more in the ClickHouse documentation.

To create 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 - area, 6 - city. There are no values for other types.
   • 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 calling the function. For example:

   regionToCountry(RegionID) — Uses the default dictionary: regions_hierarchy.txt.
   regionToCountry(RegionID, 'ua') — Uses the dictionary with the ua suffix: regions_hierarchy_ua.txt.
   

3. Create the file regions_names.txt 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) — Can't contain tabs or newlines, 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. Package your geobase files as TAR, TAR.GZ, or ZIP archives.

Upload your geobase to Yandex.Cloud

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

1. Upload archive with a geobase in Object Storage.

2. Set up public read access to the geobase file.

3. Get a public link to the geobase.

Add the geobase