Dictionaries

Dictionaries in ClickHouse are key-value data stores that are fully or partially loaded to the ClickHouse server memory.
Dictionary data is accessed using a set of functions that are faster than standard SQL queries. Access to dictionaries is read-only. You can't write data to a dictionary using ClickHouse.

The main advantage of dictionaries is that they're faster compared to JOIN operations. Dictionaries are useful when you have to frequently access reference lists to get a set of values from a key.

As their data sources, dictionaries can use ClickHouse internal dictionaries or external sources, including HTTP(s) resources or other DBMS (MySQL, ClickHouse, MongoDB, PostgreSQL).

Internal dictionaries

Managed Service for ClickHouse contains a built-in geobase dictionary and a set of functions to work with it. The 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 the built-in dictionary features in the ClickHouse documentation.

If you're not satisfied with the built-in geobase, you can add your own to ClickHouse: Learn how to do this in Adding your own geobase.

External dictionaries

For external dictionaries, HTTP(s) resources and other DBMS (MySQL, ClickHouse, MongoDB, PostgreSQL) can be used as data sources.

Depending on the dictionary settings, ClickHouse fully or partially stores dictionaries in RAM, periodically updates them, and dynamically loads the missing values.

To learn how to add and configure a dictionary using Yandex Managed Service for ClickHouse, see Adding external dictionaries.