Как стать автором
Обновить
0
Ребреин
REBRAIN: Онлайн-практикумы для Инженеров

Как создавать и использовать словари в ClickHouse

Время на прочтение 9 мин
Количество просмотров 16K


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


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


Словарь — это отображение данных в виде keyvalue. Появление словарей очень упростило использование сторонних источников данных в 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 файлам.


Теперь, имея таблицу:


CREATE TEMPORARY TABLE user_visits (user_ip String, user_id UUID);
---
INSERT INTO user_visits VALUES
    ('178.248.237.68', generateUUIDv4()),
    ('82.192.95.175', generateUUIDv4());

Можем посчитать количество уникальных значений по странам. Один из способов это сделать – использовать функции для работы со словарями 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
);

Ответ:


┌─users_country─┬─uniqs─┐
│ Russia        │     1 │
│ Netherlands   │     1 │
└───────────────┴───────┘
2 rows in set. Elapsed: 0.003 sec.

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


  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 и поможет правильно настраивать внешние источники данных.

Теги:
Хабы:
+15
Комментарии 1
Комментарии Комментарии 1

Публикации

Информация

Сайт
rebrainme.com
Дата регистрации
Дата основания
Численность
11–30 человек
Местоположение
Россия

Истории