Dictionnaire - ClickHouse Documentation

Dans ClickHouse, un dictionnaire fournit une représentation en mémoire en clé-valeur des données provenant de diverses sources internes et externes, 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

Accélérer les jointures à l’aide d’un dictionnaire

Les dictionnaires peuvent être utilisés pour accélérer un type spécifique de JOIN : le type LEFT ANY, dans lequel la clé de jointure doit correspondre à l’attribut clé du stockage clé-valeur sous-jacent. Dans ce cas, ClickHouse peut exploiter le dictionnaire pour effectuer une Direct Join. Il s’agit de l’algorithme de jointure le plus rapide de ClickHouse. Il s’applique lorsque le moteur de table 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 (qui est essentiellement une table de hachage précalculée), EmbeddedRocksDB et 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.

Exemple

En utilisant le jeu de données Stack Overflow, 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 :

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.

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.

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.

Utilisation d’un dictionnaire

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 faisant exception), vous devez prêter attention à la taille des données. Vérifions la taille de notre table 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 │
└─────────────────┴─────────────────┴───────────────────┴───────┘

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 sont prises en charge, notamment des fichiers, http et des bases de données, y compris Postgres. 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 :

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 :

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.

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 :

SELECT formatReadableSize(bytes_allocated) AS size
FROM system.dictionaries
WHERE name = 'votes_dict'

┌─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 :

SELECT dictGet('votes_dict', ('UpVotes', 'DownVotes'), '11227902') AS votes

┌─votes──────┐
│ (34999,32) │
└────────────┘

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

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.

Enrichissement à l’exécution de la requête

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 :

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 :

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.

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 :

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

Enrichissement au moment de l’indexation

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 :

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 :

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 (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 :

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

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

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.

Aspects avancés des dictionnaires

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.

Actualisation des dictionnaires

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.

Autres types de dictionnaires

ClickHouse prend également en charge les dictionnaires hiérarchiques, Polygon et à expression régulière.

Pour aller plus loin

Bonnes pratiques pour les dictionnaires — choix du layout, dictionnaires vs JOINs, monitoring
Utiliser les dictionnaires pour accélérer les requêtes
Configuration avancée des dictionnaires

​Accélérer les jointures à l’aide d’un dictionnaire

​Exemple

​Utilisation d’un dictionnaire

​Enrichissement à l’exécution de la requête

​Enrichissement au moment de l’indexation

​Aspects avancés des dictionnaires

​Actualisation des dictionnaires

​Autres types de dictionnaires

​Pour aller plus loin