Словарь в ClickHouse — это хранящееся в памяти представление данных в формате ключ-значение, получаемых из различных внутренних и внешних источников и оптимизированное для запросов поиска со сверхнизкой задержкой.
Словари полезны для:
- Повышения производительности запросов, особенно при использовании
JOIN
- Обогащения принимаемых данных на лету без замедления процесса ингестии
Ускорение JOIN с помощью словаря
JOIN: LEFT ANY, где ключ JOIN должен совпадать с ключевым атрибутом базового хранилища ключ-значение.
В этом случае ClickHouse может использовать словарь для выполнения Direct JOIN. Это самый быстрый алгоритм JOIN в ClickHouse; он применим, когда базовый движок таблицы правой таблицы поддерживает низколатентные запросы ключ-значение. В ClickHouse есть три движка таблиц, которые это обеспечивают: Join (по сути, это заранее вычисленная хеш-таблица), EmbeddedRocksDB и Dictionary. Мы опишем подход на основе словаря, но механизм одинаков для всех трёх движков.
Алгоритм Direct JOIN требует, чтобы правая таблица была основана на словаре, так что данные из этой таблицы, которые нужно объединить, уже находились в памяти в виде низколатентной структуры данных ключ-значение.
Используя набор данных Stack Overflow, ответим на вопрос:
Какой пост о SQL на Hacker News является самым спорным?
Будем считать пост спорным, если число голосов за и против у него примерно одинаково. Мы вычислим эту абсолютную разницу: чем ближе значение к 0, тем более спорным считается пост. Предположим, что у поста должно быть как минимум 10 голосов за и 10 против — посты, за которые почти не голосуют, вряд ли можно считать по-настоящему спорными.
Поскольку наши данные нормализованы, для этого запроса сейчас требуется JOIN с использованием таблиц posts и votes:
WITH PostIds AS
(
SELECT Id
FROM posts
WHERE Title ILIKE '%SQL%'
)
SELECT
Id,
Title,
UpVotes,
DownVotes,
abs(UpVotes - DownVotes) AS Controversial_ratio
FROM posts
INNER JOIN
(
SELECT
PostId,
countIf(VoteTypeId = 2) AS UpVotes,
countIf(VoteTypeId = 3) AS DownVotes
FROM votes
WHERE PostId IN (PostIds)
GROUP BY PostId
HAVING (UpVotes > 10) AND (DownVotes > 10)
) AS votes ON posts.Id = votes.PostId
WHERE Id IN (PostIds)
ORDER BY Controversial_ratio ASC
LIMIT 1
Row 1:
──────
Id: 25372161
Title: How to add exception handling to SqlDataSource.UpdateCommand
UpVotes: 13
DownVotes: 13
Controversial_ratio: 0
1 rows in set. Elapsed: 1.283 sec. Processed 418.44 million rows, 7.23 GB (326.07 million rows/s., 5.63 GB/s.)
Peak memory usage: 3.18 GiB.
Используйте меньшие наборы данных в правой части JOIN: Этот запрос может показаться излишне многословным, поскольку фильтрация по PostId выполняется и во внешнем запросе, и в подзапросе. Это оптимизация производительности, которая позволяет сократить время отклика запроса. Для максимальной производительности всегда следите за тем, чтобы в правой части JOIN находилось меньшее и по возможности минимальное множество данных. Советы по оптимизации производительности JOIN и обзор доступных алгоритмов можно найти в этой серии статей блога.
Хотя этот запрос выполняется быстро, для хорошей производительности JOIN здесь нужно написать очень аккуратно. В идеале мы бы просто отфильтровали посты, содержащие “SQL”, а затем посмотрели на количество UpVote и DownVote для этого подмножества постов, чтобы вычислить нашу Метрику.
Чтобы продемонстрировать эти концепции, мы используем словарь для данных о голосовании. Поскольку словари обычно хранятся в памяти (ssd_cache — исключение), следует учитывать объём данных. Проверим размер нашей таблицы votes:
SELECT table,
formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE table IN ('votes')
GROUP BY table
┌─table───────────┬─compressed_size─┬─uncompressed_size─┬─ratio─┐
│ votes │ 1.25 GiB │ 3.79 GiB │ 3.04 │
└─────────────────┴─────────────────┴───────────────────┴───────┘
В примере ниже данные для нашего словаря берутся из таблицы ClickHouse. Хотя это самый распространённый источник для словарей, поддерживается целый ряд источников, включая файлы, http и базы данных, в том числе Postgres. Как мы покажем, словари можно автоматически обновлять, что делает их идеальным решением для небольших наборов данных, которые часто меняются и должны быть доступны для прямых JOIN.
Нашему словарю нужен первичный ключ, по которому будут выполняться lookup-операции. По сути, он полностью аналогичен первичному ключу в транзакционной базе данных и должен быть уникальным. В приведённом выше запросе lookup выполняется по ключу JOIN — PostId. Соответственно, словарь должен быть заполнен суммарным числом положительных и отрицательных голосов для каждого PostId из нашей таблицы votes. Вот запрос для получения данных для этого словаря:
SELECT PostId,
countIf(VoteTypeId = 2) AS UpVotes,
countIf(VoteTypeId = 3) AS DownVotes
FROM votes
GROUP BY PostId
CREATE DICTIONARY votes_dict
(
`PostId` UInt64,
`UpVotes` UInt32,
`DownVotes` UInt32
)
PRIMARY KEY PostId
SOURCE(CLICKHOUSE(QUERY 'SELECT PostId, countIf(VoteTypeId = 2) AS UpVotes, countIf(VoteTypeId = 3) AS DownVotes FROM votes GROUP BY PostId'))
LIFETIME(MIN 600 MAX 900)
LAYOUT(HASHED())
0 rows in set. Elapsed: 36.063 sec.
В самоуправляемом OSS приведённую выше команду нужно выполнить на всех узлах. В ClickHouse Cloud словарь автоматически реплицируется на все узлы. Описанные выше действия были выполнены на узле ClickHouse Cloud с 64 ГБ оперативной памяти; загрузка заняла 36 с.
Чтобы проверить, сколько памяти потребляет наш словарь:
SELECT formatReadableSize(bytes_allocated) AS size
FROM system.dictionaries
WHERE name = 'votes_dict'
┌─size─────┐
│ 4.00 GiB │
└──────────┘
PostId можно с помощью простой функции dictGet. Ниже мы извлекаем значения для поста 11227902:
SELECT dictGet('votes_dict', ('UpVotes', 'DownVotes'), '11227902') AS votes
┌─votes──────┐
│ (34999,32) │
└────────────┘
WITH PostIds AS
(
SELECT Id
FROM posts
WHERE Title ILIKE '%SQL%'
)
SELECT Id, Title,
dictGet('votes_dict', 'UpVotes', Id) AS UpVotes,
dictGet('votes_dict', 'DownVotes', Id) AS DownVotes,
abs(UpVotes - DownVotes) AS Controversial_ratio
FROM posts
WHERE (Id IN (PostIds)) AND (UpVotes > 10) AND (DownVotes > 10)
ORDER BY Controversial_ratio ASC
LIMIT 3
3 rows in set. Elapsed: 0.551 sec. Processed 119.64 million rows, 3.29 GB (216.96 million rows/s., 5.97 GB/s.)
Peak memory usage: 552.26 MiB.
Обогащение данных во время выполнения запроса
CREATE DICTIONARY users_dict
(
`Id` Int32,
`Location` String
)
PRIMARY KEY Id
SOURCE(CLICKHOUSE(QUERY 'SELECT Id, Location FROM stackoverflow.users'))
LIFETIME(MIN 600 MAX 900)
LAYOUT(HASHED())
SELECT
Id,
Title,
dictGet('users_dict', 'Location', CAST(OwnerUserId, 'UInt64')) AS location
FROM posts
WHERE Title ILIKE '%clickhouse%'
LIMIT 5
FORMAT PrettyCompactMonoBlock
┌───────Id─┬─Title─────────────────────────────────────────────────────────┬─Location──────────────┐
│ 52296928 │ Comparison between two Strings in ClickHouse │ Spain │
│ 52345137 │ How to use a file to migrate data from mysql to a clickhouse? │ 中国江苏省Nanjing Shi │
│ 61452077 │ How to change PARTITION in clickhouse │ Guangzhou, 广东省中国 │
│ 55608325 │ Clickhouse select last record without max() on all table │ Moscow, Russia │
│ 55758594 │ ClickHouse create temporary table │ Perm', Russia │
└──────────┴───────────────────────────────────────────────────────────────┴───────────────────────┘
5 rows in set. Elapsed: 0.033 sec. Processed 4.25 million rows, 82.84 MB (130.62 million rows/s., 2.55 GB/s.)
Peak memory usage: 249.32 MiB.
SELECT
dictGet('users_dict', 'Location', CAST(OwnerUserId, 'UInt64')) AS location,
count() AS c
FROM posts
WHERE location != ''
GROUP BY location
ORDER BY c DESC
LIMIT 5
┌─location───────────────┬──────c─┐
│ India │ 787814 │
│ Germany │ 685347 │
│ United States │ 595818 │
│ London, United Kingdom │ 538738 │
│ United Kingdom │ 537699 │
└────────────────────────┴────────┘
5 rows in set. Elapsed: 0.763 sec. Processed 59.82 million rows, 239.28 MB (78.40 million rows/s., 313.60 MB/s.)
Пиковое потребление памяти: 248.84 MiB.
Обогащение на этапе индексации
Location пользователя в Stack Overflow никогда не меняется (хотя в реальности это не так) — в частности, речь о столбце Location таблицы users. Предположим, мы хотим выполнить аналитический запрос к таблице posts по местоположению. Она содержит UserId.
Словарь предоставляет сопоставление идентификатора пользователя с местоположением на основе таблицы users:
CREATE DICTIONARY users_dict
(
`Id` UInt64,
`Location` String
)
PRIMARY KEY Id
SOURCE(CLICKHOUSE(QUERY 'SELECT Id, Location FROM users WHERE Id >= 0'))
LIFETIME(MIN 600 MAX 900)
LAYOUT(HASHED())
Мы исключаем пользователей с Id < 0, что позволяет использовать словарь типа Hashed. Пользователи с Id < 0 — системные пользователи.
Чтобы задействовать этот словарь при вставке данных в таблицу posts, нужно изменить схему:
CREATE TABLE posts_with_location
(
`Id` UInt32,
`PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
...
`Location` MATERIALIZED dictGet(users_dict, 'Location', OwnerUserId::'UInt64')
)
ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CommentCount)
Location объявлен как столбец MATERIALIZED. Это означает, что значение можно указать в запросе INSERT, но оно всё равно всегда будет вычисляться.
ClickHouse также поддерживает столбцы DEFAULT (где значение можно вставить или вычислить, если оно не указано).
Чтобы заполнить таблицу, можно использовать обычный INSERT INTO SELECT из S3:
INSERT INTO posts_with_location SELECT Id, PostTypeId::UInt8, AcceptedAnswerId, CreationDate, Score, ViewCount, Body, OwnerUserId, OwnerDisplayName, LastEditorUserId, LastEditorDisplayName, LastEditDate, LastActivityDate, Title, Tags, AnswerCount, CommentCount, FavoriteCount, ContentLicense, ParentId, CommunityOwnedDate, ClosedDate FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet')
0 rows in set. Elapsed: 36.830 sec. Processed 238.98 million rows, 2.64 GB (6.49 million rows/s., 71.79 MB/s.)
SELECT Location, count() AS c
FROM posts_with_location
WHERE Location != ''
GROUP BY Location
ORDER BY c DESC
LIMIT 4
┌─Location───────────────┬──────c─┐
│ India │ 787814 │
│ Germany │ 685347 │
│ United States │ 595818 │
│ London, United Kingdom │ 538738 │
└────────────────────────┴────────┘
4 rows in set. Elapsed: 0.142 sec. Processed 59.82 million rows, 1.08 GB (420.73 million rows/s., 7.60 GB/s.)
Peak memory usage: 666.82 MiB.
Дополнительные темы по словарям
LIFETIME со значениями MIN 600 MAX 900. LIFETIME — это интервал обновления словаря; указанные здесь значения приводят к периодической перезагрузке через случайный промежуток от 600 до 900 с. Такой случайный интервал нужен, чтобы распределить нагрузку на источник словаря при обновлении на большом количестве серверов. Во время обновления запросы по-прежнему могут выполняться к старой версии словаря; только начальная загрузка блокирует запросы. Обратите внимание, что установка (LIFETIME(0)) отключает обновление словарей.
Словари можно принудительно перезагрузить с помощью команды SYSTEM RELOAD DICTIONARY.
Для источников баз данных, таких как ClickHouse и Postgres, можно настроить запрос, который будет обновлять словари, только если они действительно изменились (это определяется ответом на запрос), а не по периодическому интервалу. Подробнее см. здесь.
ClickHouse также поддерживает словари Hierarchical, Polygon и Regular Expression.
Последнее изменение 10 июня 2026 г.
