Если вы работаете с ClickHouse, то наверняка слышали про такую фишку, как внешние словари. А если не слышали, самое время узнать, потому что эта штука способна творить чудеса с производительностью запросов и удобством архитектуры.

Зачем нужны словари в ClickHouse?

Представьте классическую задачу аналитики, у нас есть огромная таблица transactions с миллионами строк, транзакции, где суммы в разных валютах. А ещё есть маленькая табличка currency с свойствами валют: код валюты → название, страна, возможно курс или символ. В обычном SQL мы бы сделали JOIN при выборке, чтобы подтянуть название валюты по коду. Но ClickHouse это колоночная СУБД для аналитики, она исторически не очень дружила с джойнами (особенно раньше, до появления распределённых джойнов). Что же делать? Вот тут и выручают словари.

Словарь в ClickHouse по сути, внешний по отношению к основной таблице источник данных, загружаемый в память и оптимизированный под быстрые ключ‑значение запросы. Говоря простыми словами, это как хэш‑таблица в RAM, куда заранее загружены данные из внешнего источника (таблицы БД, файла, API) по ключу. Дальше в запросах вместо тяжёлого JOIN можно вызывать функцию dictGet и за мгновение получать нужное значение по ключу из памяти.

Преимущество в том, что словари изначально проектировались под highload, они обновляются асинхронно, могут жить отдельно на каждом шарде, и обращение к ним очень быстрое — O(1) в большинстве случаев.

Как устроены словари внутри: виды и структуры

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

Перечислю основные виды:

• Flat. Самый быстрый и простой вариант. Данные хранятся в массиве, индексированном по ключу. По сути, если ключ это UInt64, то заводится большой массив, где индекс = значение ключа. Получаем мгновенный доступ по индексу. Ограничение очевидно: подходит для относительно компактных диапазонов ключей. Например, если ключи от 0 до 100_000, flat отлично. А вот если ключи вида случайные UUID или 10-значные номера, flat раздует память (нужно место на максимальный ключ). Flat‑словарь целиком грузится в RAM. Зато у него рекордная скорость извлечения, прямой индекс в массиве.

• Hashed. Данные хранятся в хеш‑структуре (по сути, что‑то вроде unordered_map в C++). Ключи тоже единичные UInt64. Скорость доступа всё ещё высокая, хотя и не такая мгновенная, как flat. Зато память используется только под реально существующие ключи, без дыр. Hashed подходит, когда диапазон ключей огромный или разреженный. Например, у вас идентификаторы клиентов, 64-бит числа без плотного диапазона. Flat бы потребовал массив размером с максимальный ID (может, триллионы), что нереально. Хеш хранит только фактические записи, экономия.

• Cache (кеширующий словарь). Этот тип отличается от двух предыдущих: в память сразу грузится не всё, а только по мере обращения. Первичный источник остаётся внешний (например, MySQL таблица или файл). Когда в запросе вызывается dictGet для ключа, которого ещё нет в памяти, словарь делает запрос к источнику, подтягивает этот ключ, кладёт в свой кеш и возвращает значение. Таким образом, cache‑словарь экономит память, если вы знаете, что на самом деле из миллиона возможных ключей в типичном запросе понадобится десяток. Можно задать размер кеша (по количеству записей). Неиспользуемые ключи постепенно вытесняются. Если запрос вдруг захочет пройтись по всем ключам (например, джойн с большой таблицей без фильтров), он начнёт для каждой записи лезть во внешний источник, получится чудовищно медленно. Разработчики ClickHouse прямо предупреждают, не стоит использовать cache‑словари без нужды, оптимальные по скорости flat и hash. Кеш имеет смысл, только если внешних данных гигабайты, а вы в каждом конкретном отчёте берёте немного. В целом же, лучше постараться уместить словарь в RAM полностью.

• Complex Key Hashed / Complex Key Cache. Это варианты hashed и cache для сложных ключей. Дело в том, что обычные словари работают, когда ключ, одно число (UInt64). А если ключ составной? Например, строковый код валюты "USD" или комбинация (user_id, date) для курсов на определённую дату. Вот для таких случаев есть complex_key_hashed и complex_key_cache. В конфиге такой словарь объявляется с <key>, внутри несколько атрибутов. В запросах для получения значения используется tuple: dictGet('dict_name', 'attr', tuple(key1, key2)). Под капотом ClickHouse хранит такой ключ, скорее всего, сериализованным и хеширует его. Работа похожа на обычный hashed или cache, только для кортежа значений. Допустим, вы хотите словарь курсов валют на определённые даты. Ключ = (код_валюты, дата). Можно использовать complex_key_hashed — он загрузит все пары (USD, 2025-11-28) и так далее Если же данных очень много, а вы обычно спрашиваете курс пары за пару дней, можно рискнуть complex_key_cache, чтобы не держать всю матрицу в памяти.

• Range Hashed (диапазонный словарь). Специальный вид, когда ключом является числовой диапазон (обычно дата‑время или число). Пример применения гео‑справочник по IP‑адресам, где каждому IP (который можно трактовать как UInt32) сопоставлена страна, но хранится это в виде диапазонов IP: [начало диапазона — конец диапазона] → страна. Range‑словари позволяют делать dictGet(...) с указанием не просто ключа, а ключа и диапазонного значения. Например, dictGet('geo_ip_dict', 'country', toUInt32(ipAddress)) найдёт, в какой диапазон попадает переданный IP, и вернёт страну. Или курсы валют с периодом действия: можно хранить курс, указывая начало и конец периода, на который он действителен. В конфигурации range‑словарь задаётся тегами <range_min> и <range_max> для границ диапазона.

• Direct / Sparse Hashed / другие. Помимо основных, ClickHouse развивал и новые виды. Direct похож на flat, но предназначен для случая, когда ключи именно плотный диапазон от 0 до N без пропусков — тогда он ещё эффективнее (по сути, прямое отображение). Sparse это оптимизация hashed для разреженных ключей: он экономнее по памяти, возможно в ущерб скорости. Есть Complex Key Direct и Complex Key Sparse, думаю, по аналогии понятно. И, наконец, IP Trie отдельный тип словаря для оптимизированного поиска по IP‑префиксам (например, хранятся подсети, и нужно быстро находить по IP к какому узлу принадлежит; используется патриция). Также существуют Hierarchical словари для иерархий (когда у записи есть parent id, можно запрашивать значение вышестоящего узла по иерархии). И Polygon — видимо, для географических областей (определяют, в каком полигоне находится точка).

Как выбрать тип словаря? Мой опыт и официальные рекомендации таковы:

• Если ключи это натуральные числа небольшого диапазона, и у вас достаточно памяти, берите flat. Он молниеносный. Например, словарь на 100k строк с ключами 0..100k — flat идеально.

• Если ключи числовые, но диапазон большой или неначинается с нуля/есть большие дыры hashed.

• Если ключи не целые (строки, UUID) или составные,придётся complex_key_hashed (почти всегда hashed лучше, чем cache, если вместится).

• Кеш‑словарь (cache) стоит использовать только при крайней необходимости, когда словарь реально очень большой, а запросы по нему точечные. Иначе можно получить просадки производительности.

• Для диапазонов по дате/времени или IP — range_hashed или ip_trie, если задача подходит.

• Ну и учитывайте объём, flat выделяет сразу массив размером max_key, hashed растёт с числом записей, complex_key_... — хранит хеши строк/кортежей (тоже память). Иногда лучше чуть схитрить: например, строковые ключи можно хешировать вручную до UInt64 и сделать обычный hashed (но риск коллизий, если не крипто‑сильный хеш... сложный вопрос, обычно лучше довериться ClickHouse complex_key).

Источники данных для словарей

Откуда же берутся данные для этих словарей? Ведь их нужно чем‑то наполнить. ClickHouse поддерживает несколько типов источников:

• Статический файл — можно загрузить словарь из локального файла (например, CSV, JSON).

• Таблица ClickHouse — можно в качестве источника указать запрос SELECT из другой таблицы ClickHouse.

• Внешняя БД по ODBC — самые популярные тут MySQL/PostgreSQL. Вы можете напрямую привязать словарь к таблице в MySQL! В конфиге указывается подключение (хост, логин, пароль, база, SQL‑запрос). При загрузке словаря CH сам подключится к MySQL и вытянет все данные.

• HTTP(s) URL — интересный способ: словарь может подгружать данные по HTTP(S) из внешнего сервиса. Это значит, вы можете реализовать свой REST API, который выдаёт справочник в формате CSV/JSON/TabSeparated, и ClickHouse будет его периодически дергать. Например, у вас актуальные курсы валют доступны по URL — можно привязать словарь к этому API.

• Исполняемый скрипт самый гибкий вариант. Указываете путь к исполняемому файлу (скрипту), CH его запускает, тот должен вывести на stdout данные словаря в заданном формате (TabSeparated, например). Таким образом, можно подтянуть вообще любые кастомные данные, преобразовать их на ходу.

При объявлении словаря в конфиге (или через DDL) секция <source> определяет, откуда грузим. Пример: подключим словарь стран из MySQL:

<source>
    <mysql>
        <host>db.company.local</host>
        <port>3306</port>
        <user>analytics</user>
        <password>topsecret</password>
        <db>analytics_db</db>
        <table>dim_country</table>
        <where>is_active = 1</where>   <!-- можно даже условие задать для выборки -->
    </mysql>
</source>

В YAML/DDL формате (в новых версиях ClickHouse можно словари описывать прямо SQL‑синтаксисом) это выглядело бы так:

CREATE DICTIONARY country_dict
(
    code String,
    name String,
    continent String
)
PRIMARY KEY code
SOURCE(MYSQL(
    host 'db.company.local' port 3306
    user 'analytics' password 'topsecret'
    db 'analytics_db' table 'dim_country'
    where 'is_active = 1'
))
LIFETIME(300)
LAYOUT(HASHED());

Создали словарь country_dict, который каждые 300 секунд будет обновляться из MySQL‑таблицы dim_country, подтягивая коды стран, названия и континенты, но только для активных записей. LIFETIME(300), об этом чуть позже, это обновление. LAYOUT(HASHED) — тип хранения, hashed (значит, ключи code у нас строковый, но CH сам под капотом сделает complex_key_hashed, потому что ключ не UInt64).

Кстати, реплики: для MySQL можно указывать несколько хостов <replica> — ClickHouse выберет доступный. Главное помнить: на каждый узел CH устанавливается своё подключение к внешнему источнику. Если у вас кластер из 10 серверов CH и словарь тянется из одной MySQL, то будет 10 коннектов. Нужно, чтобы MySQL выдержал (пул коннектов настроить, например). Иначе при старте все 10 полезут обновляться и могут положить бедный MySQL.

Обновление происходит в фоне с периодичностью LIFETIME. Например, LIFETIME(MIN 600 MAX 900) — это означает: раз в 600–900 секунд (точное время выбирается случайно в этом интервале) словарь перезагрузится. Рандом нужен, чтобы если у вас 100 серверов, они не синхронно долбанули источник. Если хочется обновлять пореже или вручную, можно указать большое время или 0, тогда автообновления не будет. А можно вообще дергать командой SYSTEM RELOAD DICTIONARY имя по необходимости, синхронно перезагрузит словарь на узле.

В случае источников‑БД (MySQL, Postgres, ClickHouse) есть интересная возможность: можно задать специальный запрос проверки изменения. Например, <!-- <invalidate_query>SELECT MAX(updated_at) FROM dim_country</invalidate_query> -->.

Использование словарей в запросах

Хорошо, словарь создали, данные загрузили, как с этим работать? Есть два основных способа:

1. Функции dictGet* традиционный метод. Для каждого словаря автоматически доступны функции вида dictGet<Type>(dictionary, attribute, key). Например, dictGetString('country_dict', 'name', toString('US')) вернёт название страны для кода «US». dictGetUInt32('some_dict', 'population', 42) вернёт население (UInt32) для ключа 42. В общем, надо выбирать соответствующую суффиксу функцию под тип возвращаемого атрибута. Есть и короткие alias‑функции: dictGetString/dictGetUInt64 и так далее, или универсальная dictGet (возвращает Nullable(T) — может потребоваться оборачивать). Чаще всего пользуются конкретными.

   Вы можете вызывать эти функции где угодно в SELECT. Например:

   SELECT 
       t.id,
       t.amount,
       dictGetString('currency_dict', 'name', t.currency_code) AS currency_name
   FROM transactions t
   WHERE t.date = today()

   Это подставит название валюты к каждой транзакции. В экзекьюшене CH для каждой строчки сделает быстренько поиск в словаре.

   Можно также использовать словари в JOIN условиях, но через функции. Например, вместо transactions t JOIN country c ON t.country_id = c.id пишем WHERE dictGet('country_dict', 'id', t.country_id) = т.к. Не самый читаемый синтаксис, но работает.

2. Движок Dictionary более новый способ. ClickHouse позволяет экспонировать словарь как таблицу. В запросе можно напрямую делать SELECT * FROM country_dict как будто это таблица. И даже джоинить: ... FROM transactions t JOIN country_dict AS c ON c.code = t.country_code. При этом движок оптимизирован так, что он не станет пробегать весь словарь для каждого ключа, а под капотом тоже обратится по хешу. В старых версиях была проблема: при прямом JOIN‑е CH мог тупить и сканировать словарь полностью. Но начиная с версии 20.4 он научился автоматом переписывать JOIN с dictionary engine в быстрые точечные лукипы. То есть теперь можно писать красиво JOIN, и оно будет не хуже, чем dictGet‑функции.

   Чтобы использовать словарь как таблицу, убедитесь, что вы его либо создали через DDL, либо прописали в конфиге с параметром <layout><dictionary></layout> (что CH распознаёт как можно экспонировать). Обычно, если словарь создан через CREATE DICTIONARY SQL, он сразу доступен как table. Можно проверить SHOW DICTIONARIES — увидите список. Тогда обычный INNER JOIN работает.

   Пример: мы сделали ранее CREATE DICTIONARY country_dict .... Теперь:

   SELECT t.id, t.amount, c.name
   FROM transactions t
   INNER JOIN country_dict c ON c.code = t.country_code

   И получим транзакции с именем страны. Если вдруг версия старая (до 20.4) — там был нюанс, что он сначала мог все c.code сджойнить. Но сейчас умный движок.

   Вообще, для себя я заметил: dictGet удобно, когда нужно дернуть 1–2 поля. А если надо целиком данные из словаря, проще join/table использовать.

3. Материализованные колонки через словарь. Ещё один трюк: можно определять в таблице столбец с DEFAULT или MATERIALIZED, который подтягивает значение из словаря при вставке. Например:

   ALTER TABLE transactions 
       ADD COLUMN currency_name String MATERIALIZED dictGetString('currency_dict', 'name', currency_code);

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

   Таким образом, словари можно применять гибко — либо на лету обогащать данные, либо даже при записи.

Кастомизация и тонкая настройка словарей

Теперь перейдём к кастомизации. Что можно настраивать у словарей, чтобы они работали по нам нужному сценарию?

• Параметры обновления. Как упоминалось, LIFETIME задаёт интервал автоперезагрузки. Можно указать фиксированное время (MIN = MAX), либо диапазон. Если вам принципиально мгновенное обновление при изменении данных — можно сделать lifetime(1) (каждую секунду — но это реально будет долбить источник, осторожно). Либо использовать invalidate_query для умного обновления по условию. Ещё вариант — вообще отключить автообновление и дергать SYSTEM RELOAD DICTIONARY при событиях (скажем, вы знаете, когда справочник изменяется).

• Размер кеша. Если вы выбрали layout = cache, обязательно укажите max_elements или size_in_cells. Например, LAYOUT(CACHE(SIZE_IN_CELLS 1000000)) — кэш на миллион элементов. По умолчанию может быть небольшой. Иначе рискуете, что кэш будет постоянно вытеснять старые записи и лезть во внешний источник. Правильный размер — непросто предугадать, но хотя бы примерно знать, сколько разных ключей в типичных запросах используется, и взять с запасом.

• Lazy load. В конфигурации сервера есть настройка dictionaries_lazy_load. По умолчанию, кажется, включена. Это значит, словарь не будет загружаться при старте сервера, а только когда к нему обратятся впервые. Зачем это полезно: если у вас 20 словарей, а нужны не всегда, можно не тратить время при запуске на загрузку всех. Но есть риск: первый запрос, который полезет в словарь, может задержаться, пока тот загрузится. Если это критично, можно отключить lazy — тогда при запуске CH сразу прогрузит всё (дольше старт, зато первые запросы быстрые).

• Расширенные типы словарей. Мало кто знает, но ClickHouse поддерживает ещё иерархические словари (layout=hierarchical). Например, у вас справочник организаций с подчинённостью, можно хранить parent_id, и потом вызывать функцию dictGetHierarchy чтобы получить цепочку вверх по иерархии. Это такая специфичная фича для задач типа «получить все категории выше текущей в дереве». Ещё есть полигональные словари (layout=polygon) — это для задач GIS, когда ключ — точка (координаты), а атрибут — например, название области, в которой эта точка находится (словарь хранит многоугольники). Согласен, звучит футуристично, но такие вещи есть. Или регулярные выражения в словаре — ключи задаются regex‑шаблонами, можно делать dictGet по строке, и он найдёт шаблон, куда строка матчится (применимо для классификации логов, IP адресов по маске и так далее).

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

• Просмотр и отладка словарей. Хотите узнать, загрузился ли словарь, сколько памяти занял, когда обновился? В system.dictionaries вам в помощь. Там есть колонка status (может быть LOADED, LOADING, FAILED), element_count, memory_allocated и прочая полезная инфа. Очень выручает, когда словарь не работает: заглянули — а статус FAILED и в last_exception видно, почему (например, ошибка соединения к источнику). Всегда проверяйте system.dictionaries при настройке, чтобы убедиться, что словарь активен.

• DDL vs XML. Раньше словари можно было описывать только в XML‑файлах конфига. Это было неудобно: поменять — надо на каждый сервер залить, перезапустить. Сейчас можно писать CREATE DICTIONARY запросом. Это счастье: словарь хранится как метаданные, его можно включать в дампы, применять ON CLUSTER и так далее Я советую мигрировать на DDL‑словарь, если ещё сидите на конфиге. Минус DDL: в ClickHouse Cloud/Яндекс Облаке, например, только DDL и работает, а в опенсорсе — оба способа. Так вот, DDL проще управлять версионированием (Flyway, manual etc.). Плюс, словарь через DDL — это объект, который легче интегрируется с другими фичами (как тот самый dictionary engine join). В общем, кастомизация хранения — я перешёл все свои проекты на DDL‑описание словарей.

• One node vs cluster. Учтите, что словарь живёт на каждом сервере отдельно. Если кластер из 4 шардов — каждый загрузит свой экземпляр словаря. Поэтому помнить: памяти будет занято в 4х экземплярах. И если словарь ходит в MySQL, то 4 независимых подключения. Это нормально, просто имеет смысл размер словаря держать небольшой, раз он множится на число узлов.

• Безопасность и ACL. В новых версиях ClickHouse есть управление доступом. Словари тоже этому подчиняются. Если у вас разные пользователи, чтобы один мог читать словарь — нужно GRANT SELECT ON dictionary.db_name.dict_name. Иначе может быть ошибка «permission denied» при попытке dictGet. Я пару раз ловил, забыв дать права. Поэтому при кастомизации в проде — не забудьте про права.

• Обработка ошибок. Если словарь не может загрузиться (внешняя БД недоступна, файл не найден) — запросы, пытающиеся его использовать, могут выдавать исключение или пустые значения, в зависимости от настроек. По умолчанию, если словарь не загрузился, dictGet вернёт default value (0 или пустую строку), а в лог запишется Warning. Но лучше мониторить и чинить, чем полагаться на silent fallback.

Напоследок, скажу о производительности: правильно настроенные словари очень быстрые. Например, у нас был словарь на ~50 тысяч элементов (привязка IP к гео‑меткам, layout=ip_trie), запросы лупили по нему сотни миллионов раз, и нормально, держалось sub‑millisecond на тысячу лукапов.

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

Чтобы узнать больше о формате обучения и познакомиться с преподавателями, приходите на бесплатные демо-уроки:

• 8 декабря — Векторное ускорение: Как ClickHouse использует современные процессоры. Записаться

• 18 декабря — Базовые принципы шардирования и репликации в ClickHouse. Записаться