Partitions de table

Les partitions regroupent les parties de données d’une table de la famille de moteurs MergeTree en unités logiques structurées. Il s’agit d’une manière d’organiser les données qui a un sens sur le plan conceptuel et repose sur des critères précis, comme des intervalles de temps, des catégories ou d’autres attributs clés. Ces unités logiques facilitent la gestion, l’interrogation et l’optimisation des données.

Le partitionnement peut être activé lorsqu’une table est définie initialement via la clause PARTITION BY. Cette clause peut contenir une expression SQL portant sur n’importe quelles colonnes, dont le résultat détermine à quelle partition une ligne appartient.

Pour illustrer cela, nous complétons la table d’exemple Que sont les parties de table en ajoutant une clause PARTITION BY toStartOfMonth(date), qui organise les parties de table de la table en fonction des mois de vente des biens immobiliers :

CREATE TABLE uk.uk_price_paid_simple_partitioned
(
    date Date,
    town LowCardinality(String),
    street LowCardinality(String),
    price UInt32
)
ENGINE = MergeTree
ORDER BY (town, street)
PARTITION BY toStartOfMonth(date);

Vous pouvez interroger cette table dans notre ClickHouse SQL Playground.

Chaque fois qu’un ensemble de lignes est inséré dans la table, au lieu de créer une seule partie de données contenant toutes les lignes insérées (au moins), comme décrit ici, ClickHouse crée une nouvelle partie de données pour chaque valeur unique de clé de partition parmi les lignes insérées :

Le serveur ClickHouse commence par répartir les 4 lignes de l’exemple d’insert illustré dans le diagramme ci-dessus en fonction de leur valeur de clé de partition toStartOfMonth(date). Ensuite, pour chaque partition identifiée, les lignes sont traitées comme d’habitude en suivant plusieurs étapes séquentielles (① Tri, ② Découpage en colonnes, ③ Compression, ④ Écriture sur disque).

Notez que lorsque le partitionnement est activé, ClickHouse crée automatiquement des index MinMax pour chaque partie de données. Il s’agit simplement de fichiers, un pour chaque colonne de la table utilisée dans l’expression de clé de partition, qui contiennent les valeurs minimale et maximale de cette colonne dans la partie de données.

Lorsque le partitionnement est activé, ClickHouse ne fusionne les parts de données qu'au sein d'une même partition, jamais entre partitions. Nous le schématisons pour notre table d'exemple ci-dessus :

Comme le montre le schéma ci-dessus, les parts appartenant à des partitions différentes ne sont jamais fusionnées. Si vous choisissez une clé de partition à cardinalité élevée, les parts réparties sur des milliers de partitions ne pourront jamais être candidates à une fusion, ce qui finit par dépasser les limites préconfigurées et provoquer la redoutable erreur Too many parts. La solution est simple : choisissez une clé de partition pertinente avec une cardinalité inférieure à 1000..10000.

Vous pouvez interroger la liste de toutes les partitions uniques existantes de notre table d’exemple à l’aide de la colonne virtuelle _partition_value :

SELECT DISTINCT _partition_value AS partition
FROM uk.uk_price_paid_simple_partitioned
ORDER BY partition ASC;

Autrement, ClickHouse suit toutes les parts et partitions de toutes les tables dans la table système system.parts, et la requête suivante renvoie pour notre table d’exemple ci-dessus la liste de toutes les partitions, ainsi que le nombre actuel de parts actives et le total des lignes dans ces parts pour chaque partition :

SELECT
    partition,
    count() AS parts,
    sum(rows) AS rows
FROM system.parts
WHERE (database = 'uk') AND (`table` = 'uk_price_paid_simple_partitioned') AND active
GROUP BY partition
ORDER BY partition ASC;

Dans ClickHouse, le partitionnement est avant tout une fonctionnalité de gestion des données. En organisant logiquement les données à l’aide d’une expression de partition, chaque partition peut être gérée indépendamment. Par exemple, le schéma de partitionnement de la table d’exemple ci-dessus permet de ne conserver dans la table principale que les données des 12 derniers mois, en supprimant automatiquement les données plus anciennes à l’aide d’une règle TTL (voir la dernière ligne ajoutée de l’instruction DDL) :

CREATE TABLE uk.uk_price_paid_simple_partitioned
(
    date Date,
    town LowCardinality(String),
    street LowCardinality(String),
    price UInt32
)
ENGINE = MergeTree
PARTITION BY toStartOfMonth(date)
ORDER BY (town, street)
TTL date + INTERVAL 12 MONTH DELETE;

Comme la table est partitionnée par toStartOfMonth(date), des partitions entières (des ensembles de parties de table) qui remplissent la condition TTL seront supprimées, ce qui rend l’opération de nettoyage plus efficace, sans qu’il soit nécessaire de réécrire les parties.

De même, au lieu de supprimer les anciennes données, celles-ci peuvent être déplacées automatiquement et efficacement vers un niveau de stockage plus économique :

CREATE TABLE uk.uk_price_paid_simple_partitioned
(
    date Date,
    town LowCardinality(String),
    street LowCardinality(String),
    price UInt32
)
ENGINE = MergeTree
PARTITION BY toStartOfMonth(date)
ORDER BY (town, street)
TTL date + INTERVAL 12 MONTH TO VOLUME 'slow_but_cheap';

Les partitions peuvent améliorer les performances des requêtes, mais cela dépend fortement des schémas d’accès. Si les requêtes ne ciblent qu’un petit nombre de partitions (idéalement une seule), les performances peuvent s’en trouver améliorées. En pratique, cela n’est utile que si la clé de partition ne fait pas partie de la clé primaire et que vous filtrez dessus, comme dans l’exemple de requête ci-dessous.

SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple_partitioned
WHERE date >= '2020-12-01'
  AND date <= '2020-12-31'
  AND town = 'LONDON';

Cette requête s’exécute sur notre table d’exemple ci-dessus et calcule le prix le plus élevé de tous les biens vendus à Londres en décembre 2020, en filtrant à la fois sur une colonne (date) utilisée dans la clé de partition de la table et sur une colonne (town) utilisée dans la clé primaire de la table (date ne faisant pas partie de la clé primaire).

ClickHouse traite cette requête en appliquant une série de techniques d’exclusion pour éviter d’évaluer des données non pertinentes :

Nous pouvons observer ces étapes d’exclusion des données en inspectant le plan d’exécution physique de notre requête d’exemple ci-dessus via une clause EXPLAIN :

EXPLAIN indexes = 1
SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple_partitioned
WHERE date >= '2020-12-01'
  AND date <= '2020-12-31'
  AND town = 'LONDON';

    ┌─explain──────────────────────────────────────────────────────────────────────────────────────────────────────┐
 1. │ Expression ((Project names + Projection))                                                                    │
 2. │   Aggregating                                                                                                │
 3. │     Expression (Before GROUP BY)                                                                             │
 4. │       Expression                                                                                             │
 5. │         ReadFromMergeTree (uk.uk_price_paid_simple_partitioned)                                              │
 6. │         Indexes:                                                                                             │
 7. │           MinMax                                                                                             │
 8. │             Keys:                                                                                            │
 9. │               date                                                                                           │
10. │             Condition: and((date in (-Inf, 18627]), (date in [18597, +Inf)))                                 │
11. │             Parts: 1/436                                                                                     │
12. │             Granules: 11/3257                                                                                │
13. │           Partition                                                                                          │
14. │             Keys:                                                                                            │
15. │               toStartOfMonth(date)                                                                           │
16. │             Condition: and((toStartOfMonth(date) in (-Inf, 18597]), (toStartOfMonth(date) in [18597, +Inf))) │
17. │             Parts: 1/1                                                                                       │
18. │             Granules: 11/11                                                                                  │
19. │           PrimaryKey                                                                                         │
20. │             Keys:                                                                                            │
21. │               town                                                                                           │
22. │             Condition: (town in ['LONDON', 'LONDON'])                                                        │
23. │             Parts: 1/1                                                                                       │
24. │             Granules: 1/11                                                                                   │
    └──────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

La sortie ci-dessus montre :

... Elapsed: 0.006 sec. Processed 8.19 thousand rows, 57.34 KB (1.36 million rows/s., 9.49 MB/s.)
Peak memory usage: 2.73 MiB.

Cela signifie que ClickHouse a analysé et traité 1 granule (bloc de 8192 lignes) en 6 millisecondes pour calculer le résultat de la requête.

Gardez à l'esprit qu'interroger l'ensemble des partitions est généralement plus lent que d'exécuter la même requête sur une table non partitionnée.

Avec le partitionnement, les données sont généralement réparties sur un plus grand nombre de parties de données, ce qui amène souvent ClickHouse à analyser et traiter un volume de données plus important.

Nous pouvons le démontrer en exécutant la même requête sur la table d'exemple Que sont les parties de table (sans partitionnement activé) et sur notre table d'exemple actuelle ci-dessus (avec le partitionnement activé). Les deux tables contiennent les mêmes données et le même nombre de lignes :

SELECT
    table,
    sum(rows) AS rows
FROM system.parts
WHERE (database = 'uk') AND (table IN ['uk_price_paid_simple', 'uk_price_paid_simple_partitioned']) AND active
GROUP BY table;

Cependant, la table avec partitionnement activé a davantage de parties de données actives, car, comme indiqué plus haut, ClickHouse ne fusionne les parties de données qu'au sein d'une même partition, et non entre les partitions :

SELECT
    table,
    count() AS parts
FROM system.parts
WHERE (database = 'uk') AND (table IN ['uk_price_paid_simple', 'uk_price_paid_simple_partitioned']) AND active
GROUP BY table;

Comme montré plus haut, la table partitionnée uk_price_paid_simple_partitioned comporte plus de 600 partitions, et donc 600 306 parties de données actives. En revanche, pour notre table non partitionnée uk_price_paid_simple, toutes les parties de données initiales ont pu être fusionnées en une seule partie active grâce aux fusions en arrière-plan.

Lorsque nous examinons le plan physique d'exécution de la requête avec une clause EXPLAIN pour notre requête d'exemple ci-dessus, sans le filtre de partition, sur la table partitionnée, nous pouvons voir aux lignes 19 et 20 de la sortie ci-dessous que ClickHouse a identifié 671 des 3257 granules existantes (blocs de lignes), réparties sur 431 des 436 parties de données actives existantes, comme pouvant potentiellement contenir des lignes correspondant au filtre de la requête, et qui seront donc analysées et traitées par le moteur de requête :

EXPLAIN indexes = 1
SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple_partitioned
WHERE town = 'LONDON';

    ┌─explain─────────────────────────────────────────────────────────┐
 1. │ Expression ((Project names + Projection))                       │
 2. │   Aggregating                                                   │
 3. │     Expression (Before GROUP BY)                                │
 4. │       Expression                                                │
 5. │         ReadFromMergeTree (uk.uk_price_paid_simple_partitioned) │
 6. │         Indexes:                                                │
 7. │           MinMax                                                │
 8. │             Condition: true                                     │
 9. │             Parts: 436/436                                      │
10. │             Granules: 3257/3257                                 │
11. │           Partition                                             │
12. │             Condition: true                                     │
13. │             Parts: 436/436                                      │
14. │             Granules: 3257/3257                                 │
15. │           PrimaryKey                                            │
16. │             Keys:                                               │
17. │               town                                              │
18. │             Condition: (town in ['LONDON', 'LONDON'])           │
19. │             Parts: 431/436                                      │
20. │             Granules: 671/3257                                  │
    └─────────────────────────────────────────────────────────────────┘

Le plan d’exécution physique de la requête pour cette même requête d’exemple exécutée sur la table sans partitions montre, aux lignes 11 et 12 des résultats ci-dessous, que ClickHouse a identifié 241 des 3083 blocs de lignes existants dans l’unique partie de données active de la table, susceptibles de contenir des lignes correspondant au filtre de la requête :

EXPLAIN indexes = 1
SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple
WHERE town = 'LONDON';

    ┌─explain───────────────────────────────────────────────┐
 1. │ Expression ((Project names + Projection))             │
 2. │   Aggregating                                         │
 3. │     Expression (Before GROUP BY)                      │
 4. │       Expression                                      │
 5. │         ReadFromMergeTree (uk.uk_price_paid_simple)   │
 6. │         Indexes:                                      │
 7. │           PrimaryKey                                  │
 8. │             Keys:                                     │
 9. │               town                                    │
10. │             Condition: (town in ['LONDON', 'LONDON']) │
11. │             Parts: 1/1                                │
12. │             Granules: 241/3083                        │
    └───────────────────────────────────────────────────────┘

Lors de l’exécution de la requête sur la version partitionnée de la table, ClickHouse analyse et traite 671 blocs de lignes (~5,5 millions de lignes) en 90 millisecondes :

SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple_partitioned
WHERE town = 'LONDON';

┌─highest_price─┐
│     594300000 │ -- 594.30 million
└───────────────┘

1 row in set. Elapsed: 0.090 sec. Processed 5.48 million rows, 27.95 MB (60.66 million rows/s., 309.51 MB/s.)
Peak memory usage: 163.44 MiB.

En revanche, pour l’exécution de la requête sur la table non partitionnée, ClickHouse analyse et traite 241 blocs (~ 2 millions de lignes) en 12 millisecondes :

SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple
WHERE town = 'LONDON';

┌─highest_price─┐
│     594300000 │ -- 594.30 million
└───────────────┘

1 row in set. Elapsed: 0.012 sec. Processed 1.97 million rows, 9.87 MB (162.23 million rows/s., 811.17 MB/s.)
Peak memory usage: 62.02 MiB.