Если вы открыли эту дверь статью, то наверняка, вы уже имели дело с ClickHouse и можно упустить интересные подробности об его удобстве и скорости, а перейти сразу к делу – собственно, к тому, как создавать словари и работать с ними в ClickHouse.

Что такое словари в ClickHouse?

Словарь — это отображение данных в виде key → value. Появление словарей очень упростило использование сторонних источников данных в ClickHouse, автоматизировав все необходимые ETL-процессы для доставки данных в пригодную для запросов форму.
Из преимуществ использования словарей в ClickHouse можно выделить несколько пунктов:

• ClickHouse имеет поддержку различных вариантов расположения словарей в памяти.
• Поддержка TTL – ClickHouse автоматически обновляет словари и подгружает отсутствующие значения.
• ClickHouse предоставляет несколько вариантов для описания внешних словарей — XML-файлы и DDL-запросы.

Подключение словарей

Подключить собственные словари можно из различных источников данных: локального текстового/исполняемого файла, HTTP(s) ресурса, другой СУБД и т.д.

Конфигурация этих словарей может находиться в одном или нескольких xml-файлах, путь к которым указывается в параметре dictionaries_config в конфигурационном файле ClickHouse.

Словари могут загружаться при старте сервера или при первом использовании, в зависимости от настройки dictionaries_lazy_load.

Также обновление словарей (кроме загрузки при первом использовании) не блокирует запросы — во время обновления запросы используют старую версию словарей.

Для просмотра информации о словарях, сконфигурированных на сервере, есть таблица system.dictionaries, в ней можно найти:

• статус словаря;
• конфигурационные параметры;
• метрики, наподобие количества занятой словарем RAM или количества запросов к словарю с момента его успешной загрузки.

Конфигурация словарей

На данный момент есть способ конфигурации словарей через xml файлы и через DDL-запросы. Вы можете использовать любой удобный для вас способ, но самый простой способ создавать и контролировать словари — это используя DDL-запросы.

Общий внешний вид конфигурации xml словаря:

<yandex>     <!--Необязательный элемент, комментарии к словарям-->     <comment>Some comments</comment>      <!--Необязательный элемент, имя файла с подстановками-->     <include_from>/etc/metrika.xml</include_from>      <dictionary>         <!-- Конфигурация словаря -->     </dictionary>      ...      <dictionary>         <!-- Конфигурация словаря -->     </dictionary> </yandex>

Если вы выбрали создание словарей через DDL-запросы, то не задавайте конфигурацию словаря в конфигурации сервера.

Пример конфигурации словаря:

<dictionary>     <name>clients</name>     <sоurce>         <clickhouse>             <host>myHostName</host>             <port>9000</port>             <user>admin</user>             <password>secret_password</password>             <db>clients</db>             <table>users</table>             <where>id<=10</where>         </clickhouse>     </sоurce>     <lifetime>         <min>3600</min>         <max>5400</max>     </lifetime>     <layout>         <flat/>     </layout>     <structure>         <id>user_id</id>         <attribute>             <name>username</name>             <type>string</type>         </attribute>         <attribute>             <name>age</name>             <type>Int8</type>         </attribute>     </structure> </dictionary>

Поля настройки:

• name — имя словаря;
• source — источник словаря;
• lifetime — периодичность обновления словарей;
• layout — размещение словаря в памяти. От этого значения зависит скорость обработки словаря;
• structure — структура словаря. Ключ и атрибуты, которые можно получить по ключу.

Пример создания словаря через DDL-запрос:

CREATE DICTIONARY dict_users_id (     id UInt64,     username String,     email String,     status UInt16,     hash String ) PRIMARY KEY id SOURCE(MYSQL(     port 3306     user clickhouse     password secret_password     replica(host 'mysql1.fevlake.com' priority 1)     db fevlake_dicts     table users )) LAYOUT(HASHED()) LIFETIME(MIN 3600 MAX 5400);

Источники внешних словарей

Внешние словари можно подключить через множество разных источников. Основные из них — это:

• Локальный файл
• Исполняемый файл
• HTTP(s)
• СУБД

Самые распространенные способы подключения словарей — через локальный файл либо СУБД, поэтому именно их мы и рассмотрим далее.

Локальный файл

Пример подключения словаря через локальный файл имеет следующий вид:

<sоurce>     <file>       <path>/opt/dictionaries/clients.csv</path>       <format>CSV</format>     </file> </sоurce>

Поля настройки:

• path — абсолютный путь к файлу.
• format — формат файла. Поддерживаются все форматы ClickHouse.

Или через DDL-запрос:

SOURCE(FILE(path '/opt/dictionaries/clients.csv' format 'CSV')) SETTINGS(format_csv_allow_single_quotes = 0)

СУБД

Рассмотрим подключение СУБД на примере MySQL базы данных.

Пример настройки:

<sоurce>     <mysql>         <port>3306</port>         <user>clickhouse</user>         <password>secret_password</password>         <replica>             <host>example01-1</host>             <priority>1</priority>         </replica>         <replica>             <host>example01-2</host>             <priority>1</priority>         </replica>         <db>db_name</db>         <table>table_name</table>         <where>id=10</where>         <invalidate_query>SQL_QUERY</invalidate_query>     </mysql> </sоurce>

• port — порт сервера MySQL. Можно задать отдельно для каждой реплики внутри тега <replica>.
• user — имя пользователя MySQL. Можно задать отдельно для каждой реплики внутри тега <replica>.
• password — пароль пользователя MySQL. Можно задать отдельно для каждой реплики внутри тега <replica>.
• replica — блок конфигурации реплики. Блоков может быть несколько.
• db — имя базы данных.
• table — имя таблицы.
• where — условие выбора. Синтаксис полностью совпадает с синтаксисом секции WHERE в MySQL, к примеру, id >= 3 AND id < 10 (необязательный параметр).
• invalidate_query — запрос для проверки статуса словаря (необязательный параметр).

Или через DDL-запрос:

SOURCE(MYSQL(     port 3306     user clickhouse     password secret_password     replica(host 'mysql1.fevlake.com' priority 1)     db fevlake_dicts     table users ))

Хранение словарей в памяти

Существует много способов хранения словарей в памяти ClickHouse:

• flat
• hashed
• sparse_hashed
• cache
• direct
• range_hashed
• complex_key_hashed
• complex_key_cache
• complex_key_direct
• ip_trie

Самые популярные из них всего 3, поскольку скорость обработки словарей при этом максимальна, — это flat, hashed и complex_key_hashed. Давайте рассмотрим примеры этих способов хранения.

Flat

Словари полностью хранятся в оперативной памяти в виде плоских массивов, при этом объем занятой памяти пропорционален размеру самого большого по размеру ключа словаря. Ключ словаря должен иметь тип UInt64 и не должен быть длиннее 500 000, иначе ClickHouse бросит исключение и не создаст словарь.

Этот метод хранения обеспечивает максимальную производительность среди всех доступных способов хранения словаря.

Пример конфигурации:

<layout>     <flat/> </layout>

или

LAYOUT(FLAT())

Hashed

Словарь полностью хранится в оперативной памяти в виде хэш-таблиц и может содержать произвольное количество элементов с произвольными идентификаторами. На практике, количество ключей может достигать десятков миллионов элементов.

Пример конфигурации:

<layout>     <hashed/> </layout>

или

LAYOUT(HASHED())

Сomplex_key_hashed

Этот тип размещения предназначен для использования с составными ключами. Аналогичен hashed способу.

Пример конфигурации:

<layout>     <hashed/> </layout>

или

LAYOUT(COMPLEX_KEY_HASHED())

Ключ и поля словаря

Секция <structure> описывает ключ словаря и поля, доступные для запросов.

Описание в формате XML:

<structure>     <id>user_id</id>     <attribute>         <name>username</name>         <type>string</type>     </attribute>     <attribute>         <name>age</name>         <type>Int8</type>     </attribute> </structure>

Поля настройки:

• <id> — столбец с ключом;
• <attribute> — столбец данных. Можно задать несколько атрибутов.

Ключи

ClickHouse поддерживает следующие виды ключей:

• Числовой ключ. UInt64. Описывается в теге <id> или ключевым словом PRIMARY KEY.
• Составной ключ. Набор значений разного типа. Описывается в теге <key> или ключевым словом PRIMARY KEY.

Числовой ключ

Тип: UInt64.

Пример конфигурации:

<id>     <name>user_id</name> </id>

или

CREATE DICTIONARY (     user_id UInt64,     ... ) PRIMARY KEY user_id ...

• PRIMARY KEY – имя столбца с ключами.

Составной ключ

Ключом может быть кортеж (tuple) из полей произвольных типов. В этом случае layout должен быть complex_key_hashed или complex_key_cache.

Структура ключа задается в элементе <key>. Поля ключа задаются в том же формате, что и атрибуты словаря. Пример:

<key>     <attribute>         <name>field1</name>         <type>String</type>     </attribute>     <attrbute>         <name>field2</name>         <type>UInt32</type>     </attribute>     ... </key>

или

CREATE DICTIONARY ( field1 String, field2 String ... ) PRIMARY KEY field1, field2 ...

Атрибуты

<structure>     ...     <attribute>         <name>Name</name>         <type>ClickHouseDataType</type>         <null_value></null_value>         <expression>rand64()</expression>         <hierarchical>true</hierarchical>         <injective>true</injective>         <is_object_id>true</is_object_id>     </attribute> </structure>

или

CREATE DICTIONARY somename (     Name ClickHouseDataType DEFAULT '' EXPRESSION rand64() HIERARCHICAL INJECTIVE IS_OBJECT_ID )

Как можно использовать словари в ClickHouse

Один из популярных кейсов использования словарей в ClickHouse — это агрегация данных по странам на основе IP (v4) адресов.

Представим, что перед нами задача: из данных колонки с ip String получить в запросе колонку с country String. Для решения данной задачи мы возьмем довольно популярные базы GeoIP2 от MaxMind.

MaxMind предоставляет со своими .mmdb базами API для большинства популярных языков программирования.

В ClickHouse нет возможности загрузить в словарь формат .mmdb, но нам это и не понадобится – MaxMind позволяет загрузить свои базы в виде нескольких CSV, чем мы и воспользуемся.

Для того чтобы связать IP со страной, нам необходимо скачать следующие файлы:

• GeoIP2-Country-Blocks-IPv4.csv – здесь содержатся связи IP префиксов и ID стран;
• GeoIP2-Country-Locations-en.csv – а здесь уже названия стран на английском.

Далее, заведем соответствующие словари с помощью DDL:

CREATE DICTIONARY dicts.geoip_country_blocks_ipv4 (     network String DEFAULT '',     geoname_id UInt64 DEFAULT 0,     registered_country_geoname_id UInt64 DEFAULT 0,     represented_country_geoname_id UInt64 DEFAULT 0,     is_anonymous_proxy UInt8 DEFAULT 0,     is_satellite_provider UInt8 DEFAULT 0 )  PRIMARY KEY network SOURCE(FILE(     path '/var/lib/clickhouse/user_files/GeoIP2-Country-Blocks-IPv4.csv'     format 'CSVWithNames' )) LAYOUT(IP_TRIE()) LIFETIME(300);

В словаре geoip_country_blocks_ipv4 мы должны указать два основных атрибута:

• network – IP префикс сети, он же и будет ключом словаря.
• geoname_id – ID страны.

Остальные атрибуты – в соответствии с заголовком в CSV.

Чтобы ClickHouse мог корректно сопоставить префикс сети и ID, нам необходимо использовать тип размещения ip_trie. Для получения значений из такого словаря необходимо будет передавать IP адрес в числовом представлении.

Теперь geoip_country_locations_en:

CREATE DICTIONARY dicts.geoip_country_locations_en (     geoname_id UInt64 DEFAULT 0,     locale_code String DEFAULT '',     continent_code String DEFAULT '',     continent_name String DEFAULT '',     country_iso_code String DEFAULT '',     country_name String DEFAULT '',     is_in_european_union UInt8 DEFAULT 0 ) PRIMARY KEY geoname_id SOURCE(FILE(     path '/var/lib/clickhouse/user_files/GeoIP2-Country-Locations-en.csv'     format 'CSVWithNames' )) LAYOUT(HASHED()) LIFETIME(300);

Нам нужно связать ID и название страны. В заголовках GeoIP2-Country-Locations-en.csv можно найти следующие атрибуты:

• geoname_id – ID страны, как в предыдущем словаре, но теперь в качестве ключа.
• country_name – название страны.

В качестве типа размещения указываем оптимизированный hashed.

В каждом из словарей необходимо указать пути к соответствующим CSV файлам.

Теперь, имея таблицу user_visits (user_ip String, user_id UUID), можем посчитать количество уникальных значений по странам. Один из способов это сделать – использовать функции для работы со словарями dictGet*:

SELECT      dictGetString('dicts.geoip_city_locations_en', 'country_name', users_country_id) AS users_country,      uniqs FROM (     SELECT          dictGetUInt64('dicts.geoip_country_blocks_ipv4', 'geoname_id', tuple(IPv4StringToNum(user_ip))) AS users_country_id,          uniq(user_id) AS uniqs     FROM user_visits     GROUP BY users_country_id );

Разберем данный запрос:

1. конвертируем строковое представление user_ip в числовое и оборачиваем в кортеж, чтобы соответствовать составному ключу ip_trie-словаря: tuple(IPv4StringToNum(user_ip));
2. используем получившийся ключ, чтобы забрать ID страны как users_country_id: dictGetUInt64('geoip_country_blocks_ipv4', 'geoname_id', ...) as users_country_id;
3. добавляем в запрос саму метрику: uniq(user_id) as uniq_users;
4. агрегируем по ID страны, который взяли из словаря: GROUP BY users_country_id;
5. результат, содержащий ID стран, сопоставляем с названиями: dictGetString('geoip_city_locations_en', 'country_name', users_country_id) AS users_country.

Таким образом возможно сопоставлять не только названия стран. В тех же GeoIP2 базах есть много другой полезной информации, не бойтесь пробовать 🙂

Заключение

На этом первичное знакомство со словарями закончено. Надеюсь, что данная информация расширит ваши возможности использования ClickHouse и поможет правильно настраивать внешние источники данных.