Словарь

Словарь представляет данные в формате ключ-значение для быстрого поиска.

Словарь в 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 │
└─────────────────┴─────────────────┴───────────────────┴───────┘

Данные будут храниться в нашем словаре без сжатия, поэтому, если бы мы хранили в словаре все столбцы (а мы этого делать не будем), потребовалось бы как минимум 4 ГБ памяти. Словарь будет реплицирован по всему кластеру, поэтому такой объём памяти нужно зарезервировать на каждом узле.

В примере ниже данные для нашего словаря берутся из таблицы 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

Для создания нашего словаря нужен следующий DDL — обратите внимание, что в нём используется приведённый выше запрос:

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) │
└────────────┘

Применив это к нашему предыдущему запросу, мы можем убрать JOIN:

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.

Этот запрос не только намного проще, но и более чем в два раза быстрее! Это можно дополнительно оптимизировать, загружая в словарь только посты, набравшие более 10 голосов за и против, и сохраняя только заранее вычисленное значение controversial.

​

Обогащение данных во время выполнения запроса

Словари можно использовать для поиска значений во время выполнения запроса. Эти значения можно возвращать в результатах или использовать в агрегированиях. Предположим, мы создаём словарь для сопоставления идентификаторов пользователей с их местоположением:

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.

Как и в примере с JOIN выше, мы можем использовать тот же словарь, чтобы эффективно определить, откуда поступает большинство постов:

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.

​

Обогащение на этапе индексации

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

Предположим, что 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.

​

Дополнительные темы по словарям

Рекомендации по выбору структур словарей, использованию словарей вместо JOIN и мониторингу использования словарей см. в разделе Лучшие практики работы со словарями.

​

Обновление словарей

Мы указали для словаря LIFETIME со значениями MIN 600 MAX 900. LIFETIME — это интервал обновления словаря; указанные здесь значения приводят к периодической перезагрузке через случайный промежуток от 600 до 900 с. Такой случайный интервал нужен, чтобы распределить нагрузку на источник словаря при обновлении на большом количестве серверов. Во время обновления запросы по-прежнему могут выполняться к старой версии словаря; только начальная загрузка блокирует запросы. Обратите внимание, что установка (LIFETIME(0)) отключает обновление словарей. Словари можно принудительно перезагрузить с помощью команды SYSTEM RELOAD DICTIONARY.

Для источников баз данных, таких как ClickHouse и Postgres, можно настроить запрос, который будет обновлять словари, только если они действительно изменились (это определяется ответом на запрос), а не по периодическому интервалу. Подробнее см. здесь.

​

Другие типы словарей

ClickHouse также поддерживает словари Hierarchical, Polygon и Regular Expression.

​

Дополнительные материалы

• Лучшие практики работы со словарями — выбор структуры, словари или JOIN, мониторинг
• Использование словарей для ускорения запросов
• Расширенная настройка словарей