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:
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:
regions_hierarchy.txtfile 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.
To add an alternative hierarchy of regions, create
regions_hierarchy_<suffix>.txtfiles 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.
Create the file
regions_names.txtwith 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.
To add region names in other languages to your geobase, create
regions_names_<language code>.txtfiles with the same structure. For example, you may create
regions_names_en.txtfor English and
Package your geobase files as
Upload your geobase to Yandex.Cloud
Managed Service for ClickHouse only works with publicly readable geobases that are uploaded to Object Storage:
Upload archive with a geobase in Object Storage.
Set up public read access to the geobase file.
Get a public link to the geobase.
Add the geobase
- Go to the folder page and select Managed Service for ClickHouse.
- Select the cluster and click Edit cluster in the top panel.
- Under DBMS settings, click Configure.
- In the Geobase uri field, provide a public link to the geobase archive in Object Storage.