ClickHouse: разбираем Dictionaries / Хабр

Привет, Хабр!

В этой статье разберём ещё один инструмент ClickHouse, который часто используется при обогащении данных и позволяет значительно ускорить выполнение тяжелых SQL-запросов с джоинами.

1. Dictionary

В любой аналитической системе мы постоянно сталкиваемся с одной и той же задачей: у нас есть огромная таблица с «фактами» (события, клики, продажи) и несколько таблиц-«справочников» (пользователи, товары, территории и т. д.). Чтобы построить осмысленный отчёт, нам нужно объединить эти таблицы.

В традиционных базах данных для этого используется JOIN. Однако в ClickHouse, который создан для молниеносной обработки миллиардов строк, JOIN с внешней таблицей может стать узким местом. Каждый раз при выполнении такого запроса ClickHouse вынужден заново сопоставлять данные, что требует времени и ресурсов, особенно при высокой нагрузке.

Но как тогда обогащать гигантские таблицы данными из справочников, не жертвуя скоростью? Для этого можно использовать словари (Dictionary).

Словарь (Dictionary) — это специальный объект в ClickHouse, который загружает справочные данные (из таблицы, файла или другого источника) в оперативную память сервера в виде эффективной структуры «ключ-значение». Это позволяет выполнять обогащение данных на лету с минимальными задержками, практически мгновенно.

Ключевые особенности словарей:

Данные находятся в оперативной памяти, что делает доступ к ним чрезвычайно быстрым.
После создания словаря его можно использовать в запросах с помощью специальных функций, таких как dictGet (о ней чуть ниже).
источником данных для словаря может быть таблица ClickHouse, удалённая база данных (например, PostgreSQL, MySQL), HTTP-ресурс или даже локальный файл.

Для создания словаря используется следующий синтаксис (в упрощённом виде):

CREATE DICTIONARY database_name.dictionary_name (
    -- Описание структуры: ключи и атрибуты
    key_column_name_1 data_type_1,
    key_column_name_2 data_type_2,
    ...
    attribute_column_name_1 data_type_3,
    attribute_column_name_2 data_type_4,
    ...
)
-- Обязательные секции
PRIMARY KEY key_column_name_1, key_column_name_2
SOURCE(...)
LAYOUT(...)
LIFETIME(...)
-- Необязательные секции
COMMENT 'Комментарий к словарю'

Подробно разберём каждый из параметров.

1.1 Структура

В ней мы описываем столбцы, которые будут содержаться в словаре. Они делятся на ключи (по которым будет осуществляться поиск) и атрибуты (значения, которые мы хотим получить). Для каждого столбца необходимо определить тип данных, но важно учитывать, что словари не поддерживают LowCardinality ни для ключей, ни для атрибутов. Если наши данные хранятся в этом формате, необходимо будет определить иной тип данных, например LowCardinality(String) → String.

1.2 PRIMARY KEY

Указывает, какой столбец (или столбцы) является уникальным идентификатором для каждой записи в словаре. Именно по этому ключу ClickHouse будет осуществлять быстрый поиск нужных данных.

Ключевое свойство PRIMARY KEY — уникальность. Словарь хранит только одну запись для каждого значения первичного ключа. Если в источнике данных (из которого формируется словарь) окажется несколько строк с одинаковым PRIMARY KEY, то ClickHouse не выдаст ошибку. Он просто возьмёт последнюю встретившуюся ему строку. Это поведение называется «Last Write Wins» (побеждает последняя запись).

Схематично это можно представить следующим образом (для простоты восприятия отобразим словарь как простую таблицу, сам он хранится немного иначе в памяти):

Если мы при создании словаря определили первичный ключ как product_id, то для product_id = 101 попадёт запись со значением price = 550, так как она идёт последней. Если необходимо сохранить все записи, то необходимо задать составной первичный ключ, например product_id, price.

1.3 SOURCE

Определяет, откуда брать данные для словаря. Это может быть таблица в ClickHouse, удалённая база данных и т. д. Секция SOURCE — одна из самых важных, так как именно она определяет, откуда словарь будет брать данные.

Рассмотрим два примера получения данных: из ClickHouse и PostgreSQL.

1.3.1 ClickHouse

Это самый простой случай, когда справочные данные лежат в таблице на том же или удалённом сервере ClickHouse.

SOURCE(
    CLICKHOUSE(
        host 'Наименование сервера'
        port Порт (например 8123)
        user 'Имя пользователя'
        password 'Пароль'
        db 'Наименование базы'
        table 'Наименование таблицы'
        where 'Условие фильтрации'
        query 'SELECT-запрос'
    )
)

Стоит отметить, что в запрос мы можем передать или только параметры table и where, указав таблицу и условие, или только параметр query, содержащий полноценный SQL-запрос. Второй подход (с использованием query) даёт больше гибкости, так как позволяет создать словарь на основе запроса с JOIN, объединив данные из нескольких таблиц в один справочник, но важно, чтобы порядок колонок в PRIMARY KEY и в запросе параметра query совпадал, иначе это может приводить к разного рода ошибкам.

1.3.2 PostgreSQL (или другая внешняя СУБД)

ClickHouse умеет «ходить» за данными для словарей во внешние реляционные СУБД. Код аналогичен коду ClickHouse, за исключением специфичных параметров, которые мы рассматривать не будем. Для создания и работы словаря достаточно указать параметры ниже:

SOURCE(
    POSTGRESQL(
        host 'Наименование сервера'
        port Порт (например 5432)
        user 'Имя пользователя'
        password 'Пароль'
        db 'Наименование базы'
        table 'Наименование таблицы'
        where 'Условие фильтрации'
        query 'SELECT-запрос'
    )
)

Требования к указанию параметров аналогичны требованиям в примере с ClickHouse.

1.4 LAYOUT

Определяет, как данные будут храниться в оперативной памяти. Выбор LAYOUT критически важен для производительности и зависит от типа данных и задач. Разработчики ClickHouse рекомендуют использовать FLAT, HASHED и COMPLEX_KEY_HASHED, которые обеспечивают оптимальную скорость обработки. Давайте рассмотрим их подробно.

1.4.1 FLAT

Этот метод хранения размещает все данные словаря в оперативной памяти, создавая отдельный плоский массив для каждого атрибута (колонки).

Первичный ключ словаря при данном методе хранения в оперативной памяти должен иметь тип UInt и используется как прямой индекс во всех массивах.

Представим, что мы создаём словарь и в качестве источника данных определили таблицу-источник. Метод хранения определили FLAT, а в качестве первичного ключа указали поле product_id:

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

Затем мы хотим для product_id = 4 получить его название. Так как данные находятся в оперативной памяти, ClickHouse мгновенно вернёт значение Ноутбук из массива product_name, обратившись к нему по индексу 4.

Так работает метод FLAT. Он обеспечивает наилучшую производительность среди всех доступных способов хранения словарей и идеально подходит, когда диапазон ключей плотный и не очень большой, то есть ключи идут друг за другом с минимальными пропусками, например, справочник стран с country_id от 1 до 250. Если первичный ключ имеет высокую разреженность (например, значения 1, 100, 10 000 000), ClickHouse создаст в памяти массив на 10 миллионов элементов, большинство из которых будут пустыми, что приведёт к неэффективному расходу оперативной памяти.

1.4.2 HASHED

Этот метод хранения является самым универсальным и часто используемым на практике. Он хранит в памяти данные в виде хеш-таблицы.

Первичный ключ словаря при данном методе хранения в оперативной памяти должен иметь тип UInt*.

Представим, что мы создаём словарь и в качестве источника данных используем немного другую таблицу, где id товара хранится в виде числового значения с шагом в 100 (например, 100, 200, 300 и т. д.). Использовать метод хранения FLAT будет неэффективно, так как в памяти будет зарезервировано место под значения типа 101, 102, 103 и т. д., которых фактически не существует. В данном случае мы можем использовать метод хранения HASHED, который использует память только для тех значений, которые фактически хранятся. В качестве первичного ключа определим поле product_id:

При получении данных из таблицы-источника ClickHouse возьмёт колонку, которую мы определили в качестве PRIMARY KEY, применит к каждому значению некоторую хеш-функцию, сформирует числовые индексы и для каждого атрибута построит хеш-таблицу, где данные запишутся в виде пары ключ-значение:

Теперь, предположим, мы хотим получить наименование товара, где product_id = 100. ClickHouse возьмёт значение id товара, снова применит к нему хеш-функцию, получит числовое значение, найдёт индекс с таким значением в хеш-таблице для атрибута product_name и вернёт наименование товара.

Так работает метод HASHED. Он менее производительный, чем FLAT (хотя и первый, и второй выполняются достаточно быстро за счёт хранения в оперативной памяти), так как требуется выполнить дополнительные операции для получения значения из хеш-таблицы, но более эффективный в части использования оперативной памяти (не резервирует дополнительное место под данные, которых фактически нет).

1.4.3 COMPLEX_KEY_HASHED

Этот метод хранения является расширением hashed для случаев, когда для идентификации уникальности записи необходимо более одного атрибута таблицы (составной первичный ключ). Также данный метод хранения можно использовать, если первичный ключ состоит из одного элемента нечислового типа, например String.

Атрибуты первичного ключа при данном методе хранения в оперативной памяти могут иметь тип данных UInt, String, Date и т. д.

Представим, что мы создаём словарь и в качестве источника данных используем таблицу, где уникальность строки определяется двумя атрибутами: product_code и product_name. В данном случае нам необходимо использовать составной первичный ключ, а метод хранения данных в оперативной памяти — COMPLEX_KEY_HASHED:

При получении данных из таблицы-источника ClickHouse возьмёт колонки, которые мы определили в качестве PRIMARY KEY, сериализует их в единое значение, применит к каждому некоторую хеш-функцию, сформирует индексы в виде числового значения и для каждого атрибута построит хеш-таблицу:

Теперь, предположим, мы хотим получить цвет товара, где product_code = A-100 и product_name = Смартфон. ClickHouse возьмёт значения первичного ключа, сериализует их в единое значение, снова применит к нему хеш-функцию, получит числовое значение, найдёт индекс с таким числом в хеш-таблице для атрибута color и вернёт цвет товара.

Метод COMPLEX_KEY_HASHED работает аналогично HASHED, за исключением сериализации составного первичного ключа в единое значение.

1.5 LIFETIME

Это инструкция для ClickHouse, которая говорит, как часто нужно обновлять данные в словаре. Это необходимо, чтобы информация, которую мы используем в запросах, оставалась свежей и актуальной.

При обновлении данных в словаре ClickHouse снова обращается к источнику данных и получает актуальную информацию.

Рассмотрим два основных способа задать эту настройку:

1.5.1 Точный интервал

Мы указываем конкретное количество секунд, через которое словарь должен обновиться. Это самый распространённый вариант. Например, LIFETIME(600) будет означать, что необходимо обновлять словарь каждые 10 минут (600 секунд).

1.5.2 Случайный интервал (диапазон)

Мы задаём минимальное и максимальное время в секундах. ClickHouse выберет случайный момент внутри этого диапазона для обновления. Например, LIFETIME(MIN 300 MAX 360) будет означать, что необходимо обновлять словарь в случайное время каждые 5–6 минут.

2. Примеры использования

Теперь, когда мы разобрались, как создавать словари, давайте рассмотрим наиболее частые сценарии использования.

2.1 Получение данных по первичному ключу

Основной способ использования словарей — это обогащение данных в запросах SELECT с помощью функции dictGet. Она работает невероятно быстро, так как просто ищет значение по ключу в оперативной памяти.

Важно понимать, что dictGet всегда возвращает актуальное (текущее) значение из словаря. Этот подход идеален для оперативных отчётов, где нужна последняя версия данных. Однако если данные в справочнике меняются, мы не сможем получить историческое значение атрибута на момент прошлого события.

Синтаксис:

-- Если ключ не составной
dictGet('dictionary_name', 'column_name', key)

-- Если ключ составной
dictGet('dictionary_name', 'column_name', (key_1, key_2))

Представим, у нас есть таблица кликов clicks с атрибутом user_id (также является PRIMARY KEY в таблице) и словарь users с информацией о пользователях. Мы хотим обогатить клики именем пользователя:

Для этого мы можем использовать следующий SQL-запрос с функцией dictGet (позволяет получить значение из словаря по первичному ключу):

SELECT
    event_time,
    user_id,
    dictGet('users', 'user_name', user_id) AS user_name
FROM clicks

Если нужно получить несколько атрибутов, мы просто вызываем функцию несколько раз. Рекомендуется использовать типизированные версии (dictGetString, dictGetUInt8 и т. д.) — они работают чуть быстрее и помогают избежать ошибок с типами данных:

SELECT
    event_time,
    user_id,
    dictGetString('users', 'user_name', user_id) AS user_name,
    dictGetUInt8('users', 'age', user_id) AS age
FROM clicks

Таким образом, функции семейства dictGet — это самый производительный и рекомендуемый способ обогащения данных в реальном времени, являющийся стандартом для большинства аналитических запросов.

2.2 Обращение как к таблице

Функция dictGet идеальна для простого извлечения данных по ключу. Но что если нам нужна более сложная логика соединения? Для таких случаев ClickHouse позволяет обращаться к словарю как к обычной таблице в JOIN.

Обратимся снова к таблице clicks и объединим данные со словарём users через JOIN. Получим все атрибуты для конкретного пользователя из словаря. Для этого необходимо выполнить следующий запрос:

SELECT *
FROM clicks c
JOIN users u
ON c.user_id = u.user_id

Этот подход является самым гибким, но и наименее производительным по сравнению с dictGet. Механизм JOIN в ClickHouse более «тяжёлый», и его стоит использовать только тогда, когда логика запроса сложна и не укладывается в простое получение значения по ключу.

2.3 Обогащение данных на уровне DDL

Оба рассмотренных способа обогащают данные в момент выполнения запроса SELECT. Но существует и другой подход — сделать это заранее, ещё на этапе вставки данных в таблицу. Для этого используется конструкция DEFAULT в определении столбца в сочетании с dictGet.

Такой подход особенно полезен, когда данные в справочнике могут со временем меняться (например, пользователь меняет имя или товар переезжает в другую категорию). Обогащение в момент вставки фиксирует состояние атрибута на момент события. Таким образом, в таблице сохранится именно та информация, которая была в словаре в момент вставки, обеспечивая историческую точность данных.

Давайте снова вернёмся к таблице clicks. Для реализации обогащения данных в момент их вставки нам необходимо в структуре таблицы определить DEFAULT-выражения для столбцов, обогащение которых планируется:

CREATE TABLE default.clicks (
    event_time DateTime,
    user_id UInt32,
    -- Обогащаемые поля, вычисляемые автоматически при вставке
    user_name String DEFAULT dictGetString('users', 'user_name', user_id),
    age UInt8 DEFAULT dictGetUInt8('users', 'age', user_id)
)
ENGINE = MergeTree()
ORDER BY event_time

Схематично процесс будет выглядеть следующим образом:

Теперь при INSERT в таблицу clicks достаточно передать event_time и user_id, а ClickHouse сам заполнит поля user_name и age. Любой, кто будет вставлять данные, получит обогащение «из коробки», даже не зная о его существовании.

3. Обогащение данных из внешних систем

Иногда на практике бывают ситуации, когда наши данные (логи) хранятся в ClickHouse, а справочная информация — например, категории товаров, города, данные пользователей или контрагентов — в другой СУБД, предположим, в PostgreSQL. Как нам в таком случае обогатить нашу таблицу в ClickHouse данными, которые хранятся во внешней системе?

Существует несколько вариантов решения задачи, например, использование специальных движков таблиц в ClickHouse или ETL-процессов. Но самый элегантный и производительный способ — это словари.

Как мы помним, при создании словаря необходимо задать значение для параметра SOURCE, указав источник, из которого необходимо получать данные. Это и есть ключ к решению. Мы можем напрямую указать ClickHouse, чтобы он загружал данные для словаря из внешней базы данных.

Представим задачу. Нам необходимо посчитать средний возраст пользователей, которые заходят на наш сайт. У нас есть всё та же таблица clicks в ClickHouse и таблица-справочник users, но теперь она в PostgreSQL:

Создадим словарь в ClickHouse и получим данные со справочной информацией о пользователях из PostgreSQL:

CREATE DICTIONARY default.users (
    user_id UInt32,
    user_name String,
    age UInt8
)
PRIMARY KEY user_id
SOURCE(
    POSTGRESQL(
        host 'your_postgres_host'
        port 5432
        user 'postgres_user'
        password 'postgres_password'
        db 'database_name'
        table 'users'
    )
)
LAYOUT(HASHED())
LIFETIME(3600)

Обогатим наши логи информацией о возрасте пользователя и посчитаем среднее:

SELECT avg(dictGet('users', 'age', user_id))
FROM clicks

Таким образом, мы решили аналитическую задачу, объединив данные из двух разных систем, не прибегая к межсерверным JOIN-ам или сложным ETL-процессам. Вся «магия» произошла внутри ClickHouse.

Надеюсь, эта статья помогла вам понять, что такое словари и как они работают в ClickHouse.

P.S. Если вы хотите систематизировать знания и получить прочную теоретическую базу для дальнейшего освоения ClickHouse на практике, буду рад видеть вас на моем бесплатном курсе ClickHouse с нуля который охватывает все самое необходимое для уверенного старта в работе с технологией. Закрепить пройденную теорию можно на практическом продолжении курса ClickHouse с нуля: практика.

Удачи в изучении!

ClickHouse: разбираем Dictionaries