Clé de partitionnement personnalisée - ClickHouse Documentation

Dans la plupart des cas, vous n’avez pas besoin de clé de partitionnement, et dans la plupart des autres, vous n’avez pas besoin d’une clé de partitionnement plus fine qu’un partitionnement mensuel, sauf dans les cas d’usage d’observabilité où un partitionnement quotidien est courant.Vous ne devez jamais utiliser un partitionnement trop fin. Ne partitionnez pas vos données par identifiant ou nom de client. Utilisez plutôt un identifiant ou un nom de client comme première colonne de l’expression ORDER BY.

Le partitionnement est disponible pour les tables de la famille MergeTree, y compris les tables répliquées et les vues matérialisées. Une partition est un regroupement logique d’enregistrements dans une table selon un critère donné. Vous pouvez définir une partition selon un critère arbitraire, par exemple par mois, par jour ou par type d’événement. Chaque partition est stockée séparément afin de simplifier la manipulation de ces données. Lors de l’accès aux données, ClickHouse utilise le plus petit sous-ensemble possible de partitions. Les partitions améliorent les performances des requêtes contenant une clé de partitionnement, car ClickHouse filtre d’abord sur cette partition avant de sélectionner les parties et les granules qu’elle contient. La partition est spécifiée dans la clause PARTITION BY expr lors de la création d’une table. La clé de partitionnement peut être n’importe quelle expression basée sur les colonnes de la table. Par exemple, pour spécifier un partitionnement par mois, utilisez l’expression toYYYYMM(date_column):

CREATE TABLE visits
(
    VisitDate Date,
    Hour UInt8,
    ClientID UUID
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(VisitDate)
ORDER BY Hour;

La clé de partitionnement peut également être un tuple d’expressions (similaire à la clé primaire). Par exemple :

ENGINE = ReplicatedCollapsingMergeTree('/clickhouse/tables/name', 'replica1', Sign)
PARTITION BY (toMonday(StartDate), EventType)
ORDER BY (CounterID, StartDate, intHash32(UserID));

Dans cet exemple, nous définissons le partitionnement en fonction des types d’événements survenus pendant la semaine en cours. Par défaut, la clé de partitionnement à virgule flottante n’est pas prise en charge. Pour l’utiliser, activez le paramètre allow_floating_point_partition_key. Lors de l’insertion de nouvelles données dans une table, celles-ci sont stockées dans une partie distincte (fragment), triée selon la clé primaire. Dans les 10 à 15 minutes qui suivent l’insertion, les parties de la même partition fusionnent en une seule partie.

Une fusion ne fonctionne que pour les parties de données ayant la même valeur pour l’expression de partitionnement. Cela signifie qu’il ne faut pas créer des partitions trop granulaires (plus d’environ mille partitions). Sinon, la requête SELECT offre de mauvaises performances en raison d’un nombre excessif de fichiers dans le système de fichiers et de descripteurs de fichier ouverts.

Utilisez la table system.parts pour afficher les parties de table et les partitions. Par exemple, supposons que nous ayons une table visits avec un partitionnement par mois. Exécutons la requête SELECT sur la table system.parts :

SELECT
    partition,
    name,
    active
FROM system.parts
WHERE table = 'visits'

┌─partition─┬─name──────────────┬─active─┐
│ 201901    │ 201901_1_3_1      │      0 │
│ 201901    │ 201901_1_9_2_11   │      1 │
│ 201901    │ 201901_8_8_0      │      0 │
│ 201901    │ 201901_9_9_0      │      0 │
│ 201902    │ 201902_4_6_1_11   │      1 │
│ 201902    │ 201902_10_10_0_11 │      1 │
│ 201902    │ 201902_11_11_0_11 │      1 │
└───────────┴───────────────────┴────────┘

La colonne partition contient les noms des partitions. Il y a deux partitions dans cet exemple : 201901 et 201902. Vous pouvez utiliser la valeur de cette colonne pour indiquer le nom de la partition dans les requêtes ALTER … PARTITION. La colonne name contient les noms des parties de données de la partition. Vous pouvez utiliser cette colonne pour indiquer le nom de la partie dans la requête ALTER ATTACH PART. Décomposons le nom de la partie : 201901_1_9_2_11 :

201901 est le nom de la partition.
1 est le numéro minimal du bloc de données.
9 est le numéro maximal du bloc de données.
2 est le niveau du fragment (la profondeur de l’arbre de fusion dont il est issu).
11 est la version de la mutation (si une partie a subi une mutation)

Les parties des tables de l’ancien type portent le nom suivant : 20190117_20190123_2_2_0 (date minimale - date maximale - numéro minimal de bloc - numéro maximal de bloc - niveau).

La colonne active indique l’état de la partie. 1 signifie active ; 0, inactive. Les parties inactives sont, par exemple, des parties source restantes après leur fusion dans une partie plus grande. Les parties de données corrompues sont également indiquées comme inactives. Comme vous pouvez le voir dans l’exemple, il existe plusieurs parties distinctes pour une même partition (par exemple, 201901_1_3_1 et 201901_1_9_2). Cela signifie que ces parties n’ont pas encore été fusionnées. ClickHouse fusionne périodiquement les parties de données insérées, environ 15 minutes après l’insertion. En outre, vous pouvez effectuer une fusion non planifiée à l’aide de la requête OPTIMIZE. Exemple :

OPTIMIZE TABLE visits PARTITION 201902;

┌─partition─┬─name─────────────┬─active─┐
│ 201901    │ 201901_1_3_1     │      0 │
│ 201901    │ 201901_1_9_2_11  │      1 │
│ 201901    │ 201901_8_8_0     │      0 │
│ 201901    │ 201901_9_9_0     │      0 │
│ 201902    │ 201902_4_6_1     │      0 │
│ 201902    │ 201902_4_11_2_11 │      1 │
│ 201902    │ 201902_10_10_0   │      0 │
│ 201902    │ 201902_11_11_0   │      0 │
└───────────┴──────────────────┴────────┘

Les parties inactives seront supprimées environ 10 minutes après la fusion. Une autre façon de voir un ensemble de parties et de partitions consiste à accéder au répertoire de la table : /var/lib/clickhouse/data/<database>/<table>/. Par exemple :

/var/lib/clickhouse/data/default/visits$ ls -l
total 40
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  1 16:48 201901_1_3_1
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 16:17 201901_1_9_2_11
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 15:52 201901_8_8_0
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 15:52 201901_9_9_0
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 16:17 201902_10_10_0
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 16:17 201902_11_11_0
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 16:19 201902_4_11_2_11
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 12:09 201902_4_6_1
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  1 16:48 detached

Les dossiers ‘201901_1_1_0’, ‘201901_1_7_1’, etc. sont les répertoires des parties. Chaque partie correspond à une partition et ne contient des données que pour un mois donné (dans cet exemple, la table utilise un partitionnement par mois). Le répertoire detached contient les parties qui ont été détachées de la table à l’aide de la requête DETACH. Les parties corrompues sont également déplacées dans ce répertoire au lieu d’être supprimées. Le serveur n’utilise pas les parties du répertoire detached. Vous pouvez ajouter, supprimer ou modifier les données de ce répertoire à tout moment : le serveur n’en aura pas connaissance tant que vous n’aurez pas exécuté la requête ATTACH. Notez que, sur un serveur en fonctionnement, vous ne pouvez pas modifier manuellement le jeu de parties ni leurs données dans le système de fichiers, car le serveur n’en aura pas connaissance. Pour les tables non répliquées, vous pouvez le faire lorsque le serveur est arrêté, mais ce n’est pas recommandé. Pour les tables répliquées, le jeu de parties ne peut en aucun cas être modifié. ClickHouse vous permet d’effectuer des opérations sur les partitions : les supprimer, les copier d’une table à une autre ou créer une sauvegarde. Consultez la liste complète des opérations dans la section Manipulations avec les partitions et les parties.

Optimisation de Group By à l’aide de la clé de partitionnement

Pour certaines combinaisons entre la clé de partitionnement de la table et la clé de Group By de la requête, il peut être possible d’exécuter l’agrégation indépendamment pour chaque partition. Nous n’aurons alors pas à fusionner à la fin les données partiellement agrégées provenant de tous les threads d’exécution, car nous avons la garantie que chaque valeur de clé de Group By ne peut pas apparaître dans les ensembles de travail de deux threads différents. L’exemple type est :

CREATE TABLE session_log
(
    UserID UInt64,
    SessionID UUID
)
ENGINE = MergeTree
PARTITION BY sipHash64(UserID) % 16
ORDER BY tuple();

SELECT
    UserID,
    COUNT()
FROM session_log
GROUP BY UserID;

Les performances d’une telle requête dépendent de la structure de la table. L’optimisation est activée par défaut depuis la version 26.7 ; les heuristiques d’exécution l’ignorent automatiquement lorsque la structure des partitions est défavorable — plus précisément, lorsqu’il y a trop peu de partitions (moins de max_threads / 2), trop de partitions (plus de max_number_of_partitions_for_independent_aggregation) ou lorsque la taille des partitions est très déséquilibrée (la plus grande partition contient plus de lignes que deux fois le nombre total de lignes divisé par max_threads). La liste ci-dessous décrit les facteurs de structure qui favorisent de bonnes performances de manière générale ; parmi eux, seuls le nombre de partitions et le déséquilibre des tailles sont pris en compte par les heuristiques d’exécution.

Les facteurs clés pour obtenir de bonnes performances sont les suivants :

le nombre de partitions impliquées dans la requête doit être suffisamment élevé (plus de max_threads / 2), sinon la requête sous-exploitera la machine
les partitions ne doivent pas être trop petites, afin que le traitement par lot ne dégénère pas en traitement ligne par ligne
les partitions doivent être de taille comparable, afin que tous les threads effectuent approximativement la même quantité de travail

Il est recommandé d’appliquer une fonction de hachage aux colonnes de la clause partition by afin de répartir uniformément les données entre les partitions.

Les paramètres pertinents sont :

allow_aggregate_partitions_independently - détermine si l’utilisation de l’optimisation est activée
force_aggregate_partitions_independently - force son utilisation lorsqu’elle est applicable du point de vue de la validité du résultat, mais désactivée par la logique interne qui en évalue la pertinence
max_number_of_partitions_for_independent_aggregation - limite stricte du nombre maximal de partitions que la table peut avoir

​Optimisation de Group By à l’aide de la clé de partitionnement

Optimisation de Group By à l’aide de la clé de partitionnement