ClickHouse® 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 cannot write data to a dictionary using ClickHouse®.

The main advantage of dictionaries is that they are 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, such as HTTP(s) resources or other DBMS's: MySQL®, ClickHouse®, MongoDB, and PostgreSQL.

Internal dictionariesInternal dictionaries

Managed Service for ClickHouse® contains a built-in geobase dictionary and a set of functions to work with it. This dictionary allows you to:

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

You can learn more about the built-in dictionary features in the ClickHouse® documentation.

If you are not satisfied with the built-in geobase, you can add your own geobase to ClickHouse®.

External dictionariesExternal dictionaries

For external dictionaries, you can use HTTP(s) resources and other DBMS's, such as MySQL®, ClickHouse®, MongoDB, and PostgreSQL, as data sources.

Depending on the dictionary settings, ClickHouse® fully or partially stores dictionaries in RAM, updates them on a regular basis, 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.

ClickHouse® is a registered trademark of ClickHouse, Inc.