Voici la partie 3 d’un guide sur la migration de PostgreSQL vers ClickHouse. À l’aide d’un exemple concret, ce guide montre comment modéliser les données dans ClickHouse lors d’une migration depuis PostgreSQL.Nous recommandons aux utilisateurs migrant depuis Postgres de lire le guide sur la modélisation des données dans ClickHouse. Ce guide utilise le même Stack Overflow dataset et présente plusieurs approches à l’aide des fonctionnalités de ClickHouse.
Les utilisateurs venant de bases de données OLTP recherchent souvent un concept équivalent dans ClickHouse. En voyant que ClickHouse prend en charge la syntaxe
PRIMARY KEY, ils peuvent être tentés de définir le schéma de leur table en utilisant les mêmes clés que dans leur base de données OLTP source. Ce n’est pas adapté.
En quoi les clés primaires de ClickHouse sont-elles différentes ?
- Les clés primaires de Postgres sont, par définition, uniques pour chaque ligne. L’utilisation de structures B-tree permet de retrouver efficacement des lignes individuelles à partir de cette clé. Bien que ClickHouse puisse être optimisé pour retrouver une ligne unique, les charges de travail analytiques nécessitent généralement de lire quelques colonnes sur un grand nombre de lignes. Le plus souvent, les filtres doivent identifier un sous-ensemble de lignes sur lequel une agrégation sera effectuée.
- L’efficacité en mémoire et sur disque est primordiale à l’échelle à laquelle ClickHouse est souvent utilisé. Les données sont écrites dans les tables ClickHouse par blocs appelés parts, qui sont ensuite fusionnés en arrière-plan selon certaines règles. Dans ClickHouse, chaque part possède son propre index primaire. Lorsque des parts sont fusionnées, leurs index primaires le sont également. Contrairement à Postgres, ces index ne sont pas construits pour chaque ligne. À la place, l’index primaire d’une part contient une entrée d’index par groupe de lignes : cette technique s’appelle l’indexation éparse.
- L’indexation éparse est possible parce que ClickHouse stocke sur disque les lignes d’une part dans l’ordre d’une clé spécifiée. Au lieu de localiser directement des lignes individuelles (comme avec un index basé sur un B-Tree), l’index primaire épars lui permet d’identifier rapidement, via une recherche binaire sur les entrées d’index, des groupes de lignes susceptibles de correspondre à la requête. Les groupes de lignes potentiellement correspondantes ainsi localisés sont ensuite transmis en parallèle au moteur ClickHouse afin de trouver les correspondances. Cette conception permet de conserver un index primaire compact (il tient entièrement en mémoire principale) tout en accélérant significativement le temps d’exécution des requêtes, en particulier pour les requêtes sur des plages de valeurs, typiques des cas d’usage analytiques.
Toutes les colonnes d’une table seront triées selon la valeur de la clé de tri spécifiée, qu’elles soient incluses ou non dans la clé elle-même. Par exemple, siCreationDateest utilisée comme clé, l’ordre des valeurs dans toutes les autres colonnes correspondra à l’ordre des valeurs de la colonneCreationDate. Plusieurs clés de tri peuvent être spécifiées : le tri suivra alors la même logique qu’une clauseORDER BYdans une requêteSELECT.
Choisir une clé de tri
Partitions
PARTITION BY. Cette clause peut contenir une expression SQL portant sur n’importe quelles colonnes, dont le résultat détermine vers quelle partition une ligne est envoyée.
Les data parts sont associées logiquement à chaque partition sur le disque et peuvent être interrogées isolément. Dans l’exemple ci-dessous, nous partitionnons la table posts par année à l’aide de l’expression toYear(CreationDate). À mesure que des lignes sont insérées dans ClickHouse, cette expression est évaluée pour chaque ligne et la ligne est dirigée vers la partition correspondante si elle existe (si c’est la première ligne pour une année donnée, la partition sera créée).
Applications des partitions
- Gestion des données - Dans ClickHouse, vous devez avant tout considérer le partitionnement comme une fonctionnalité de gestion des données, et non comme une technique d’optimisation des requêtes. En séparant logiquement les données selon une clé, chaque partition peut être gérée indépendamment, par exemple supprimée. Cela vous permet de déplacer efficacement des partitions, et donc des sous-ensembles, entre les niveaux de stockage en fonction du temps ou de faire expirer des données/les supprimer efficacement du cluster. Dans l’exemple ci-dessous, nous supprimons les posts de 2008.
- Optimisation des requêtes - Bien que le partitionnement puisse améliorer les performances des requêtes, 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’améliorer. Cela n’est généralement utile que si la clé de partitionnement ne fait pas partie de la clé primaire et que vous filtrez dessus. En revanche, les requêtes qui doivent parcourir de nombreuses partitions peuvent être moins performantes qu’en l’absence de partitionnement (car celui-ci peut entraîner la création d’un plus grand nombre de parts). L’intérêt de cibler une seule partition sera encore moins marqué, voire nul, si la clé de partitionnement figure déjà parmi les premiers éléments de la clé primaire. Le partitionnement peut aussi être utilisé pour optimiser les requêtes GROUP BY si les valeurs de chaque partition sont uniques. Cependant, de manière générale, vous devez vous assurer que la clé primaire est optimisée et n’envisager le partitionnement comme technique d’optimisation des requêtes que dans des cas exceptionnels, lorsque les schémas d’accès portent sur un sous-ensemble spécifique et prévisible des données, par exemple un partitionnement par jour, avec la plupart des requêtes sur le dernier jour.
Recommandations pour les partitions
En interne, ClickHouse crée des parts pour les données insérées. À mesure que davantage de données sont insérées, le nombre de parts augmente. Afin d’éviter un nombre excessivement élevé de parts, ce qui dégradera les performances des requêtes (davantage de fichiers à lire), les parts sont fusionnées dans un processus asynchrone en arrière-plan. Si le nombre de parts dépasse une limite préconfigurée, ClickHouse lèvera alors une exception lors de l’insert, sous la forme d’une erreur « too many parts ». Cela ne devrait pas se produire dans des conditions normales de fonctionnement et n’arrive que si ClickHouse est mal configuré ou utilisé incorrectement, p. ex. avec de nombreux petits inserts.
Comme les parts sont créées indépendamment pour chaque partition, augmenter le nombre de partitions entraîne une hausse du nombre de parts, c.-à-d. qu’il s’agit d’un multiple du nombre de partitions. Les clés de partitionnement à cardinalité élevée peuvent donc provoquer cette erreur et doivent être évitées.
Vues matérialisées vs projections
ORDER BY pour une table.
Dans la documentation sur la modélisation des données de ClickHouse, nous expliquons comment les vues matérialisées peuvent être utilisées dans ClickHouse pour précalculer des agrégations, transformer des lignes et optimiser les requêtes pour différents schémas d’accès.
Pour ce second cas, nous avons fourni un exemple dans lequel la vue matérialisée envoie les lignes vers une table cible dont la clé de tri diffère de celle de la table d’origine qui reçoit les insertions.
Par exemple, considérez la requête suivante :
UserId n’est pas la clé de tri.
Nous avons précédemment résolu ce problème à l’aide d’une vue matérialisée servant de table de correspondance pour PostId. Le même problème peut être résolu
avec une projection. La commande ci-dessous ajoute une
projection sur ORDER BY user_id.
ALTER, sa création est asynchrone lorsque la commande MATERIALIZE PROJECTION est exécutée. Vous pouvez suivre l’avancement de cette opération avec la requête suivante, en attendant que is_done=1.
EXPLAIN, nous confirmons également que la projection a bien été utilisée pour traiter cette requête :
Quand utiliser les projections
- Une réorganisation complète des données est nécessaire. Bien que l’expression de la
projection puisse, en théorie, utiliser un
GROUP BY,les vues matérialisées sont plus efficaces pour maintenir des agrégats. L’optimiseur de requêtes est également plus susceptible d’exploiter des projections qui reposent sur une simple réorganisation, c.-à-d.SELECT * ORDER BY x. Vous pouvez sélectionner un sous-ensemble de colonnes dans cette expression afin de réduire l’empreinte de stockage. - Les utilisateurs acceptent l’augmentation correspondante de l’empreinte de stockage et le surcoût lié à l’écriture des données en double. Testez l’impact sur la vitesse d’insertion et évaluez le surcoût de stockage.
Depuis la version 25.5, ClickHouse prend en charge la colonne virtuelle
_part_offset dans les
projections. Cela permet de stocker les projections de manière plus économe en espace.Pour plus de détails, voir « Projections »