Как создавать и использовать словари в ClickHouse06.08.2020 12:48

hdm1saopaqfgg2-u1bapnqxy80s.png

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


    
    Some comments

    
    /etc/metrika.xml

    
        
    

    ...

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

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


    clients
    
        
            myHostName
            9000
            admin
            secret_password
            clients
            users

            id<=10
        
    
    
        3600
        5400
    
    
        
    
    
        user_id
        
            username
            string
        
        
            age
            Int8

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


  • 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)
  • СУБД

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


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

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


    
      /opt/dictionaries/clients.csv
      CSV

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


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

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

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


СУБД

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

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


    
        3306
        clickhouse
        secret_password
        
            example01-1
            1
        
        
            example01-2
            1
        
        db_name
        table_name

        id=10
        SQL_QUERY


  • port — порт сервера MySQL. Можно задать отдельно для каждой реплики внутри тега .
  • user — имя пользователя MySQL. Можно задать отдельно для каждой реплики внутри тега .
  • password — пароль пользователя MySQL. Можно задать отдельно для каждой реплики внутри тега .
  • 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())


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

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

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


    user_id
    
        username
        string
    
    
        age
        Int8

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


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


Ключи

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


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

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

Тип:  UInt64.

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


    user_id

или

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


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

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

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

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


    
        field1
        String
    
    
        field2
        UInt32
    
    ...

или

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


Атрибуты


    ...
    
        Name
        ClickHouseDataType
        
        rand64()
        true
        true
        true

или

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

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

© Habrahabr.ru