Русский
Русский
English
Статистика
Реклама

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


Если вы открыли эту дверь статью, то наверняка, вы уже имели дело с 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 idSOURCE(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 networkSOURCE(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_idSOURCE(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,     uniqsFROM (    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 и поможет правильно настраивать внешние источники данных.

Источник: habr.com
К списку статей
Опубликовано: 06.08.2020 12:10:31
0

Сейчас читают

Комментариев (0)
Имя
Электронная почта

Блог компании ребреин

Sql

Администрирование баз данных

Хранилища данных

Clickhouse

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru