> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Словарь

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

export const Image = ({img, alt, size}) => {
  return <Frame>
      <img src={img} alt={alt} />
    </Frame>;
};

Словарь в ClickHouse — это хранящееся в памяти представление данных в формате [ключ-значение](https://en.wikipedia.org/wiki/Key%E2%80%93value_database), получаемых из различных [внутренних и внешних источников](/ru/reference/statements/create/dictionary/sources/overview#dictionary-sources) и оптимизированное для запросов поиска со сверхнизкой задержкой.

Словари полезны для:

* Повышения производительности запросов, особенно при использовании `JOIN`
* Обогащения принимаемых данных на лету без замедления процесса ингестии

<Image img="https://mintcdn.com/private-7c7dfe99-fix-nav-issues/ddNWBC5mE_w-syUp/images/dictionary/dictionary-use-cases.png?fit=max&auto=format&n=ddNWBC5mE_w-syUp&q=85&s=3fe74a27710f5d4109800a28ea9a1f69" size="lg" alt="Сценарии использования словаря в ClickHouse" width="1600" height="838" data-path="images/dictionary/dictionary-use-cases.png" />

<div id="speeding-up-joins-using-a-dictionary">
  ## Ускорение JOIN с помощью словаря
</div>

Словари можно использовать для ускорения определённого типа `JOIN`: [`LEFT ANY`](/ru/reference/statements/select/join#supported-types-of-join), где ключ JOIN должен совпадать с ключевым атрибутом базового хранилища ключ-значение.

<Image img="https://mintcdn.com/private-7c7dfe99-fix-nav-issues/ddNWBC5mE_w-syUp/images/dictionary/dictionary-left-any-join.png?fit=max&auto=format&n=ddNWBC5mE_w-syUp&q=85&s=de2556f940fe5bd35d22e089d7d4beea" size="sm" alt="Использование словаря с LEFT ANY JOIN" width="680" height="704" data-path="images/dictionary/dictionary-left-any-join.png" />

В этом случае ClickHouse может использовать словарь для выполнения [Direct JOIN](https://clickhouse.com/blog/clickhouse-fully-supports-joins-direct-join-part4#direct-join). Это самый быстрый алгоритм JOIN в ClickHouse; он применим, когда базовый [движок таблицы](/ru/reference/engines/table-engines) правой таблицы поддерживает низколатентные запросы ключ-значение. В ClickHouse есть три движка таблиц, которые это обеспечивают: [Join](/ru/reference/engines/table-engines/special/join) (по сути, это заранее вычисленная хеш-таблица), [EmbeddedRocksDB](/ru/reference/engines/table-engines/integrations/embedded-rocksdb) и [Dictionary](/ru/reference/engines/table-engines/special/dictionary). Мы опишем подход на основе словаря, но механизм одинаков для всех трёх движков.

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

<div id="example">
  ### Пример
</div>

Используя [набор данных Stack Overflow](/ru/get-started/sample-datasets/stackoverflow), ответим на вопрос:
*Какой пост о SQL на Hacker News является самым спорным?*

Будем считать пост спорным, если число голосов за и против у него примерно одинаково. Мы вычислим эту абсолютную разницу: чем ближе значение к 0, тем более спорным считается пост. Предположим, что у поста должно быть как минимум 10 голосов за и 10 против — посты, за которые почти не голосуют, вряд ли можно считать по-настоящему спорными.

Поскольку наши данные нормализованы, для этого запроса сейчас требуется `JOIN` с использованием таблиц `posts` и `votes`:

```sql theme={null}
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
```

```response theme={null}
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` и обзор доступных алгоритмов можно найти в [этой серии статей блога](https://clickhouse.com/blog/clickhouse-fully-supports-joins-part1).

Хотя этот запрос выполняется быстро, для хорошей производительности `JOIN` здесь нужно написать очень аккуратно. В идеале мы бы просто отфильтровали посты, содержащие "SQL", а затем посмотрели на количество `UpVote` и `DownVote` для этого подмножества постов, чтобы вычислить нашу Метрику.

<div id="applying-a-dictionary">
  #### Применение словаря
</div>

Чтобы продемонстрировать эти концепции, мы используем словарь для данных о голосовании. Поскольку словари обычно хранятся в памяти ([ssd\_cache](/ru/reference/statements/create/dictionary/layouts/ssd-cache) — исключение), следует учитывать объём данных. Проверим размер нашей таблицы `votes`:

```sql theme={null}
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
```

```response theme={null}
┌─table───────────┬─compressed_size─┬─uncompressed_size─┬─ratio─┐
│ votes           │ 1.25 GiB        │ 3.79 GiB          │  3.04 │
└─────────────────┴─────────────────┴───────────────────┴───────┘
```

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

> В примере ниже данные для нашего словаря берутся из таблицы ClickHouse. Хотя это самый распространённый источник для словарей, поддерживается [целый ряд источников](/ru/reference/statements/create/dictionary/sources/overview#dictionary-sources), включая файлы, http и базы данных, в том числе [Postgres](/ru/reference/statements/create/dictionary/sources/postgresql). Как мы покажем, словари можно автоматически обновлять, что делает их идеальным решением для небольших наборов данных, которые часто меняются и должны быть доступны для прямых JOIN.

Нашему словарю нужен первичный ключ, по которому будут выполняться lookup-операции. По сути, он полностью аналогичен первичному ключу в транзакционной базе данных и должен быть уникальным. В приведённом выше запросе lookup выполняется по ключу JOIN — `PostId`. Соответственно, словарь должен быть заполнен суммарным числом положительных и отрицательных голосов для каждого `PostId` из нашей таблицы `votes`. Вот запрос для получения данных для этого словаря:

```sql theme={null}
SELECT PostId,
   countIf(VoteTypeId = 2) AS UpVotes,
   countIf(VoteTypeId = 3) AS DownVotes
FROM votes
GROUP BY PostId
```

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

```sql theme={null}
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())
```

```response theme={null}
0 rows in set. Elapsed: 36.063 sec.
```

> В самоуправляемом OSS приведённую выше команду нужно выполнить на всех узлах. В ClickHouse Cloud словарь автоматически реплицируется на все узлы. Описанные выше действия были выполнены на узле ClickHouse Cloud с 64 ГБ оперативной памяти; загрузка заняла 36 с.

Чтобы проверить, сколько памяти потребляет наш словарь:

```sql theme={null}
SELECT formatReadableSize(bytes_allocated) AS size
FROM system.dictionaries
WHERE name = 'votes_dict'
```

```response theme={null}
┌─size─────┐
│ 4.00 GiB │
└──────────┘
```

Теперь получить голоса «за» и «против» для конкретного `PostId` можно с помощью простой функции `dictGet`. Ниже мы извлекаем значения для поста `11227902`:

```sql theme={null}
SELECT dictGet('votes_dict', ('UpVotes', 'DownVotes'), '11227902') AS votes
```

```response theme={null}
┌─votes──────┐
│ (34999,32) │
└────────────┘
```

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

```sql theme={null}
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
```

```response theme={null}
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.

<div id="query-time-enrichment">
  ## Обогащение данных во время выполнения запроса
</div>

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

```sql theme={null}
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())
```

С помощью этого словаря можно дополнить результаты постов:

```sql theme={null}
SELECT
        Id,
        Title,
        dictGet('users_dict', 'Location', CAST(OwnerUserId, 'UInt64')) AS location
FROM posts
WHERE Title ILIKE '%clickhouse%'
LIMIT 5
FORMAT PrettyCompactMonoBlock
```

```response theme={null}
┌───────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 выше, мы можем использовать тот же словарь, чтобы эффективно определить, откуда поступает большинство постов:

```sql theme={null}
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
```

```response theme={null}
┌─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.
```

<div id="index-time-enrichment">
  ## Обогащение на этапе индексации
</div>

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

Предположим, что `Location` пользователя в Stack Overflow никогда не меняется (хотя в реальности это не так) — в частности, речь о столбце `Location` таблицы `users`. Предположим, мы хотим выполнить аналитический запрос к таблице `posts` по местоположению. Она содержит `UserId`.

Словарь предоставляет сопоставление идентификатора пользователя с местоположением на основе таблицы `users`:

```sql theme={null}
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, нужно изменить схему:

```sql theme={null}
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`](/ru/reference/statements/create/table#default_values) (где значение можно вставить или вычислить, если оно не указано).

Чтобы заполнить таблицу, можно использовать обычный `INSERT INTO SELECT` из S3:

```sql theme={null}
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')
```

```response theme={null}
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.)
```

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

```sql theme={null}
SELECT Location, count() AS c
FROM posts_with_location
WHERE Location != ''
GROUP BY Location
ORDER BY c DESC
LIMIT 4
```

```response theme={null}
┌─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.
```

<div id="advanced-dictionary-topics">
  ## Дополнительные темы по словарям
</div>

Рекомендации по выбору структур словарей, использованию словарей вместо JOIN и мониторингу использования словарей см. в разделе [Лучшие практики работы со словарями](/ru/concepts/features/dictionaries/best-practices).

<div id="refreshing-dictionaries">
  ### Обновление словарей
</div>

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

Для источников баз данных, таких как ClickHouse и Postgres, можно настроить запрос, который будет обновлять словари, только если они действительно изменились (это определяется ответом на запрос), а не по периодическому интервалу. Подробнее см. [здесь](/ru/reference/statements/create/dictionary/lifetime).

<div id="other-dictionary-types">
  ### Другие типы словарей
</div>

ClickHouse также поддерживает словари [Hierarchical](/ru/reference/statements/create/dictionary/layouts/hierarchical), [Polygon](/ru/reference/statements/create/dictionary/layouts/polygon) и [Regular Expression](/ru/reference/statements/create/dictionary/layouts/regexp-tree).

<div id="more-reading">
  ### Дополнительные материалы
</div>

* [Лучшие практики работы со словарями](/ru/concepts/features/dictionaries/best-practices) — выбор структуры, словари или JOIN, мониторинг
* [Использование словарей для ускорения запросов](https://clickhouse.com/blog/faster-queries-dictionaries-clickhouse)
* [Расширенная настройка словарей](/ru/reference/statements/create/dictionary)