блог о bi, №1 в рунете

Работа с внешними словарями

в ClickHouse

Оглавление:

Внешние словари

Способ работы №1

Способ работы №2

Способ работы №3

Внешние словари

ClickHouse поддерживает специальные функции для работы со словарями, которые можно использовать в запросах. Проще и эффективнее использовать словари с помощью функций, чем JOIN с таблицами-справочниками. Операции JOIN плохо работают в ClickHouse!
ClickHouse:

Полностью или частично хранит словари в оперативной памяти.
Периодически обновляет их и динамически подгружает отсутствующие значения.(При создании словаря указываем LIFETIME)
Позволяет создавать внешние словари с помощью xml-файлов или DDL-запросов.

Примечание: Словари можно размещать в памяти множеством способов. Рекомендуется flat, hashed и complex_key_hashed. Скорость обработки словарей при этом максимальна!

Способ работы №1

Рассмотрим пример работы справочников на стандартной модели данных «Звезда»
У нас есть таблица в ХД GreenPlum fact_sales (Рис. 1)

Рис. 1

Нормализуем согласно 3-ей форме и создаем несколько справочников, в итоге у нас имеется нормализованная таблица фактов(Рис. 2) и справочники (Рис.3)

Рис. 2

(Рис.3)

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

Листинг №1 – Пример создания внешнего справочника в ClickHouse


CREATE DICTIONARY schema.name_dict
(

    `id` Int32,

    `contractor_name` Nullable(String)
)
PRIMARY KEY id -- ключ
SOURCE(POSTGRESQL(PORT 5432 HOST '127.0.0.1:8080' USER 'user' PASSWORD 'password' DB 'base' TABLE 'sourse')) -- Источник (GreenPlum)
LIFETIME(MIN 0 MAX 30) -- Частота обновления
LAYOUT(COMPLEX_KEY_HASHED()) -- способ хранения в памяти;

dictGet('dict_name', attr_names, id_expr) – синтаксис функции вызова словаря

dict_name — имя словаря. Строковый литерал.
attr_name — имя столбца словаря. Строковый литерал.
id_expr — значение ключа словаря. Выражение, возвращающее значение типа UInt64 или Tuple в зависимости от конфигурации словаря.

Рис. 4 – Запрос с использованием внешнего справочника

Способ работы №2

В ClickHouse есть возможность подключать справочники по строкам напрямую без id. Например, на Рис.5 видим табличку с продажами в разных городах. У нас есть различные справочники с иерархией Страна – Регион – Город. Перед нами стоит задача найти Регион и Город.

Создаем представление на стороне ХД в GreenPlum, где соединяем Поля Страна – Регион – Город.

Используя, как ключ наименование города получаем название страны и региона Рис.6. Для запросов необходимо использовать те же функции (dictGet с кортежем), что и для словарей с строковым типом или составными ключами:


dictGet('dict_name', 'attr_name', tuple(String key))

Рис. 5

Рис. 6

Способ работы №3

Смоделируем ситуации, когда добавился новый источник данных и нам нужно их объединить две таблицы фактов из разных источников (Source 1 UNION ALL Source 2). Присутствует проблема, где у первого источника и второго источника разные наименования Отделов, но они равнозначащие по смысловому характеру.

Source 1.`Отдел продаж` = Source 2.`Отдел реализации`
Source 1.`Отдел продвижения` = Source 2.`Маркетинговый отдел`

Если решать задачу путем предыдущих способов, то необходимо будет создать два словаря(справочника) т.к. id в source 1 и source 2 могут быть идентичными.
Создаем таблицу маппинг в ХД GreenPlum, где указываем наименование источника, id, наименование отдела в источнике, эталонное название Рис. 7.

Рис. 7

Создаем внешний справочник с указанием составного primary key (`Наименование источника `, id):

После этого выполняем запрос, где подключение справочников происходит через составные ключи (`Source`, id ключ) Рис. 8. Добавлять новые источники будет намного легче и менее трудозатратно.

Рис. 8

Вернуться к блогу