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

# Dictionnaire

> Un dictionnaire fournit une représentation clé-valeur des données pour permettre des recherches rapides.

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

Dans ClickHouse, un dictionnaire fournit une représentation en mémoire en [clé-valeur](https://en.wikipedia.org/wiki/Key%E2%80%93value_database) des données provenant de diverses [sources internes et externes](/fr/reference/statements/create/dictionary/sources/overview#dictionary-sources), optimisée pour des requêtes de consultation à très faible latence.

Les dictionnaires sont utiles pour :

* Améliorer les performances des requêtes, en particulier avec les `JOIN`
* Enrichir les données ingérées à la volée sans ralentir le processus d'ingestion

<Image img="https://mintcdn.com/private-7c7dfe99-mintlify-fbfa8bee/qvDh3oxLPI7c74wx/images/dictionary/dictionary-use-cases.png?fit=max&auto=format&n=qvDh3oxLPI7c74wx&q=85&s=01e98f1fefe5fe608beba3645511a276" size="lg" alt="Cas d'utilisation des dictionnaires dans ClickHouse" width="1600" height="838" data-path="images/dictionary/dictionary-use-cases.png" />

<div id="speeding-up-joins-using-a-dictionary">
  ## Accélérer les jointures à l’aide d’un dictionnaire
</div>

Les dictionnaires peuvent être utilisés pour accélérer un type spécifique de `JOIN` : le [type `LEFT ANY`](/fr/reference/statements/select/join#supported-types-of-join), dans lequel la clé de jointure doit correspondre à l’attribut clé du stockage clé-valeur sous-jacent.

<Image img="https://mintcdn.com/private-7c7dfe99-mintlify-fbfa8bee/qvDh3oxLPI7c74wx/images/dictionary/dictionary-left-any-join.png?fit=max&auto=format&n=qvDh3oxLPI7c74wx&q=85&s=d82e882d6c37ca31d8b312bc0a1c4e3e" size="sm" alt="Utilisation d’un dictionnaire avec LEFT ANY JOIN" width="680" height="704" data-path="images/dictionary/dictionary-left-any-join.png" />

Dans ce cas, ClickHouse peut exploiter le dictionnaire pour effectuer une [Direct Join](https://clickhouse.com/blog/clickhouse-fully-supports-joins-direct-join-part4#direct-join). Il s’agit de l’algorithme de jointure le plus rapide de ClickHouse. Il s’applique lorsque le [moteur de table](/fr/reference/engines/table-engines/index) sous-jacent de la table de droite prend en charge des requêtes clé-valeur à faible latence. ClickHouse propose trois moteurs de table qui le permettent : [Join](/fr/reference/engines/table-engines/special/join) (qui est essentiellement une table de hachage précalculée), [EmbeddedRocksDB](/fr/reference/engines/table-engines/integrations/embedded-rocksdb) et [Dictionary](/fr/reference/engines/table-engines/special/dictionary). Nous allons décrire l’approche basée sur les dictionnaires, mais le mécanisme est le même pour ces trois moteurs.

L’algorithme de jointure directe exige que la table de droite repose sur un dictionnaire, afin que les données à joindre de cette table soient déjà présentes en mémoire sous la forme d’une structure de données clé-valeur à faible latence.

<div id="example">
  ### Exemple
</div>

En utilisant le [jeu de données Stack Overflow](/fr/get-started/sample-datasets/stackoverflow), répondons à la question :
*Quel est le post le plus controversé à propos de SQL sur Hacker News ?*

Nous considérerons qu’un post est controversé lorsqu’il reçoit un nombre similaire de votes positifs et négatifs. Nous calculons cet écart absolu : plus la valeur est proche de 0, plus la controverse est forte. Nous supposerons que le post doit avoir au moins 10 votes positifs et 10 votes négatifs — les posts sur lesquels personne ne vote ne sont pas vraiment controversés.

Avec nos données normalisées, cette requête nécessite actuellement un `JOIN` entre les tables `posts` et `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.
```

> **Utilisez des ensembles de données plus petits du côté droit de `JOIN`** : Cette requête peut sembler plus détaillée que nécessaire, puisque le filtrage sur les `PostId` intervient à la fois dans la requête externe et dans la sous-requête. Il s'agit d'une optimisation des performances qui garantit un temps de réponse rapide. Pour des performances optimales, veillez toujours à ce que le côté droit du `JOIN` soit l'ensemble le plus petit possible. Pour des conseils sur l'optimisation des performances des `JOIN` et pour mieux comprendre les algorithmes disponibles, nous recommandons [cette série d'articles de blog](https://clickhouse.com/blog/clickhouse-fully-supports-joins-part1).

Bien que cette requête soit rapide, elle nous oblige à écrire le `JOIN` avec soin pour obtenir de bonnes performances. Dans l'idéal, nous filtrerions simplement les posts pour ne conserver que ceux contenant "SQL", avant d'examiner les nombres de `UpVote` et de `DownVote` pour le sous-ensemble de blogs afin de calculer notre métrique.

<div id="applying-a-dictionary">
  #### Utilisation d’un dictionnaire
</div>

Pour illustrer ces concepts, nous utilisons un dictionnaire pour nos données de vote. Comme les dictionnaires sont généralement stockés en mémoire ([ssd\_cache](/fr/reference/statements/create/dictionary/layouts/ssd-cache) faisant exception), vous devez prêter attention à la taille des données. Vérifions la taille de notre table `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 │
└─────────────────┴─────────────────┴───────────────────┴───────┘
```

Les données seront stockées sans compression dans notre dictionnaire. Nous avons donc besoin d'au moins 4 Go de mémoire si nous stockons toutes les colonnes (ce que nous ne ferons pas) dans un dictionnaire. Le dictionnaire sera répliqué sur l'ensemble de notre cluster ; cette quantité de mémoire doit donc être réservée *par nœud*.

> Dans l'exemple ci-dessous, les données de notre dictionnaire proviennent d'une table ClickHouse. S'il s'agit de la source de dictionnaire la plus courante, [plusieurs sources](/fr/reference/statements/create/dictionary/sources/overview#dictionary-sources) sont prises en charge, notamment des fichiers, http et des bases de données, y compris [Postgres](/fr/reference/statements/create/dictionary/sources/postgresql). Comme nous allons le montrer, les dictionnaires peuvent être actualisés automatiquement, ce qui en fait un moyen idéal de garantir que de petits jeux de données fréquemment modifiés sont disponibles pour des jointures directes.

Notre dictionnaire nécessite une clé primaire sur laquelle les recherches seront effectuées. Conceptuellement, elle est identique à la clé primaire d'une base de données transactionnelle et doit être unique. Notre requête ci-dessus nécessite une recherche sur la clé de jointure, `PostId`. Le dictionnaire doit ensuite être alimenté avec le total des votes positifs et négatifs par `PostId` à partir de notre table `votes`. Voici la requête permettant d'obtenir les données de ce dictionnaire :

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

La création de notre dictionnaire nécessite l’instruction DDL suivante ; notez l’utilisation de la requête ci-dessus :

```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.
```

> Dans une installation OSS autogérée, la commande ci-dessus doit être exécutée sur tous les nœuds. Dans ClickHouse Cloud, le dictionnaire sera automatiquement répliqué sur tous les nœuds. L’exemple ci-dessus a été exécuté sur un nœud ClickHouse Cloud avec 64GB de RAM, et le chargement a pris 36s.

Pour vérifier la mémoire consommée par notre dictionnaire :

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

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

La récupération des votes positifs et négatifs pour un `PostId` donné peut désormais se faire à l’aide d’une simple fonction `dictGet`. Ci-dessous, nous récupérons les valeurs pour le post `11227902` :

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

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

En tirant parti de cela dans notre requête précédente, nous pouvons supprimer le 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.
```

Non seulement cette requête est beaucoup plus simple, mais elle est aussi plus de deux fois plus rapide ! Il serait possible de l’optimiser davantage en ne chargeant dans le dictionnaire que les posts ayant reçu plus de 10 votes positifs et négatifs, et en n’y stockant qu’une valeur de controverse précalculée.

<div id="query-time-enrichment">
  ## Enrichissement à l’exécution de la requête
</div>

Les dictionnaires peuvent être utilisés pour rechercher des valeurs à l’exécution de la requête. Ces valeurs peuvent être renvoyées dans les résultats ou utilisées dans des agrégations. Supposons que nous créions un dictionnaire pour associer des ID utilisateur à leur lieu :

```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())
```

Nous pouvons utiliser ce dictionnaire pour enrichir les résultats des publications :

```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.
```

Comme dans l’exemple de jointure ci-dessus, nous pouvons utiliser le même dictionnaire pour déterminer efficacement d’où provient la majorité des posts :

```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.)
Peak memory usage: 248.84 MiB.
```

<div id="index-time-enrichment">
  ## Enrichissement au moment de l’indexation
</div>

Dans l’exemple ci-dessus, nous avons utilisé un dictionnaire à l’exécution de la requête pour éviter une jointure. Les dictionnaires peuvent aussi servir à enrichir des lignes au moment de l’insertion. C’est généralement pertinent si la valeur d’enrichissement ne change pas et provient d’une source externe pouvant être utilisée pour alimenter le dictionnaire. Dans ce cas, enrichir la ligne au moment de l’insertion évite d’avoir à interroger le dictionnaire à l’exécution de la requête.

Supposons que le `Location` d’un utilisateur dans Stack Overflow ne change jamais (en réalité, il change) — plus précisément, la colonne `Location` de la table `users`. Supposons que nous voulions exécuter une requête analytique sur la table posts par lieu. Celle-ci contient un `UserId`.

Un dictionnaire fournit une correspondance entre l’identifiant d’un utilisateur et son lieu, en s’appuyant sur la table `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())
```

> Nous excluons les utilisateurs dont l’`Id < 0`, ce qui nous permet d’utiliser le type de Dictionary `Hashed`. Les utilisateurs dont l’`Id < 0` sont des utilisateurs système.

Pour exploiter ce dictionnaire au moment de l’insert dans la table posts, nous devons modifier le schéma :

```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)
```

Dans l’exemple ci-dessus, `Location` est déclarée comme une colonne `MATERIALIZED`. Cela signifie que la valeur peut être fournie dans une requête `INSERT` et qu’elle sera toujours calculée.

> ClickHouse prend également en charge les [colonnes `DEFAULT`](/fr/reference/statements/create/table#default_values) (où la valeur peut être insérée ou calculée si elle n’est pas fournie).

Pour alimenter la table, nous pouvons utiliser l’habituel `INSERT INTO SELECT` depuis 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.)
```

Nous pouvons maintenant obtenir le nom du lieu d’où provient le plus grand nombre de posts :

```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">
  ## Aspects avancés des dictionnaires
</div>

Pour savoir comment choisir les layout de dictionnaire, quand utiliser des dictionnaires plutôt que des JOIN, et comment surveiller l’utilisation des dictionnaires, consultez [Bonnes pratiques pour les dictionnaires](/fr/concepts/features/dictionaries/best-practices).

<div id="refreshing-dictionaries">
  ### Actualisation des dictionnaires
</div>

Nous avons spécifié un `LIFETIME` pour le dictionnaire de `MIN 600 MAX 900`. LIFETIME est l’intervalle de mise à jour du dictionnaire ; les valeurs indiquées ici entraînent un rechargement périodique à un intervalle aléatoire compris entre 600 et 900 s. Cet intervalle aléatoire est nécessaire afin de répartir la charge sur la source du dictionnaire lors des mises à jour sur un grand nombre de serveurs. Pendant les mises à jour, l’ancienne version d’un dictionnaire peut toujours être interrogée ; seul le chargement initial bloque les requêtes. Notez que définir `(LIFETIME(0))` empêche la mise à jour des dictionnaires.
Les dictionnaires peuvent être rechargés de force à l’aide de la commande `SYSTEM RELOAD DICTIONARY`.

Pour les sources de base de données telles que ClickHouse et Postgres, vous pouvez configurer une requête qui mettra à jour les dictionnaires uniquement s’ils ont réellement changé (c’est la réponse de la requête qui le détermine), plutôt qu’à intervalles périodiques. Vous trouverez plus de détails [ici](/fr/reference/statements/create/dictionary/lifetime).

<div id="other-dictionary-types">
  ### Autres types de dictionnaires
</div>

ClickHouse prend également en charge les dictionnaires [hiérarchiques](/fr/reference/statements/create/dictionary/layouts/hierarchical), [Polygon](/fr/reference/statements/create/dictionary/layouts/polygon) et [à expression régulière](/fr/reference/statements/create/dictionary/layouts/regexp-tree).

<div id="more-reading">
  ### Pour aller plus loin
</div>

* [Bonnes pratiques pour les dictionnaires](/fr/concepts/features/dictionaries/best-practices) — choix du layout, dictionnaires vs JOINs, monitoring
* [Utiliser les dictionnaires pour accélérer les requêtes](https://clickhouse.com/blog/faster-queries-dictionaries-clickhouse)
* [Configuration avancée des dictionnaires](/fr/reference/statements/create/dictionary)