[SQL, Администрирование баз данных, Хранилища данных] Как создавать и использовать словари в ClickHouse

Ответить на тему

Автор

Сообщение

news_bot ^®

Стаж: 6 лет 9 месяцев
Сообщений: 27286

news_bot ^® написал(а)
06-Авг-2020 14:31

Цитировать

Если вы открыли эту дверь статью, то наверняка, вы уже имели дело с 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())

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

или

LAYOUT(HASHED())

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

или

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.
Пример конфигурации:

или

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
);

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

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

Таким образом возможно сопоставлять не только названия стран. В тех же GeoIP2 базах есть много другой полезной информации, не бойтесь пробовать :)
Заключение
На этом первичное знакомство со словарями закончено. Надеюсь, что данная информация расширит ваши возможности использования ClickHouse и поможет правильно настраивать внешние источники данных.
===========
Источник:
habr.com
===========
Похожие новости:

Теги для поиска: #_sql, #_administrirovanie_baz_dannyh (Администрирование баз данных), #_hranilischa_dannyh (Хранилища данных), #_clickhouse, #_blog_kompanii_rebrein (
Блог компании Ребреин
), #_sql, #_administrirovanie_baz_dannyh (
Администрирование баз данных
), #_hranilischa_dannyh (
Хранилища данных
)

Профиль ЛС

Ответить на тему

Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы

Текущее время: 23-Ноя 05:17
Часовой пояс: UTC + 5