Jeu de données Stack Overflow
s3://datasets-documentation/stackoverflow/parquet/ :
Les clés primaires et les relations indiquées ne sont pas appliquées au moyen de contraintes (Parquet est un format de fichier, pas un format de table) et servent uniquement à montrer comment les données sont liées ainsi que les clés uniques qu’elles possèdent.
Le jeu de données Stack Overflow contient plusieurs tables liées entre elles. Pour toute tâche de modélisation de données, nous recommandons de commencer par charger la table principale. Il ne s’agit pas nécessairement de la plus volumineuse, mais plutôt de celle sur laquelle porteront la plupart des requêtes analytiques. Cela vous permettra de vous familiariser avec les principaux concepts et types de ClickHouse, ce qui est particulièrement important si vous venez d’un environnement principalement OLTP. Cette table pourra devoir être remaniée à mesure que des tables supplémentaires seront ajoutées, afin d’exploiter pleinement les fonctionnalités de ClickHouse et d’obtenir des performances optimales. Le schéma ci-dessus n’est volontairement pas optimal pour les besoins de ce guide.
Définir le schéma initial
posts sera la cible de la plupart des requêtes d’analytique, nous nous concentrons sur la définition du schéma de cette table. Ces données sont disponibles dans le bucket S3 public s3://datasets-documentation/stackoverflow/parquet/posts/*.parquet, à raison d’un fichier par année.
Le chargement de données depuis S3 au format Parquet est la méthode la plus courante et recommandée pour importer des données dans ClickHouse. ClickHouse est optimisé pour traiter le format Parquet et peut potentiellement lire et insérer des dizaines de millions de lignes par seconde depuis S3.ClickHouse offre une fonctionnalité d’inférence de schéma qui permet d’identifier automatiquement les types d’un jeu de données. Cette fonctionnalité est prise en charge pour tous les formats de données, y compris Parquet. Nous pouvons l’exploiter pour identifier les types ClickHouse des données à l’aide de la fonction de table S3 et de la commande
DESCRIBE. Notez ci-dessous que nous utilisons le motif glob *.parquet pour lire tous les fichiers du dossier stackoverflow/parquet/posts.
La fonction de table S3 permet d’interroger directement depuis ClickHouse les données stockées dans S3. Cette fonction est compatible avec tous les formats de fichiers pris en charge par ClickHouse.Cela nous donne un schéma initial non optimisé. Par défaut, ClickHouse les convertit en types Nullable équivalents. Nous pouvons créer une table ClickHouse à l’aide de ces types avec une simple commande
CREATE EMPTY AS SELECT.
ORDER BY () signifie que nous n’avons pas d’index, et plus précisément aucun ordre dans nos données. Nous y reviendrons plus tard. Pour l’instant, retenez simplement que toutes les requêtes nécessiteront un parcours linéaire complet.
Pour confirmer que la table a été créée :
INSERT INTO SELECT, en les lisant avec la fonction de table S3. L’opération suivante charge les données posts en environ 2 min sur une instance ClickHouse Cloud à 8 cœurs.
La requête ci-dessus charge 60 M de lignes. Bien que ce soit peu pour ClickHouse, les utilisateurs disposant d’une connexion Internet plus lente peuvent souhaiter ne charger qu’un sous-ensemble de données. Pour cela, il suffit de préciser, au moyen d’un motif glob, les années à charger, par ex.https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/2008.parquetouhttps://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/{2008, 2009}.parquet. Voir ici comment les motifs glob peuvent être utilisés pour cibler des sous-ensembles de fichiers.
Optimisation des types
Pour comprendre pourquoi ClickHouse compresse si bien les données, nous recommandons cet article. En résumé, en tant que base de données orientée colonnes, les valeurs sont écrites par colonne. Si ces valeurs sont triées, les mêmes valeurs se retrouvent côte à côte. Les algorithmes de compression exploitent ces motifs continus dans les données. En complément, ClickHouse dispose de codecs et de types de données granulaires qui permettent d’affiner encore davantage les techniques de compression.La compression dans ClickHouse est influencée par 3 facteurs principaux : la clé de tri, les types de données et les éventuels codecs utilisés. Tous ces éléments sont configurés via le schéma. Le principal gain initial en matière de compression et de performances des requêtes peut être obtenu grâce à un simple processus d’optimisation des types. Quelques règles simples peuvent être appliquées pour optimiser le schéma :
- Utilisez des types stricts - Notre schéma initial utilisait des Strings pour de nombreuses colonnes manifestement numériques. L’utilisation des types appropriés garantit la sémantique attendue lors du filtrage et des agrégations. Il en va de même pour les types de date, qui ont été correctement fournis dans les fichiers Parquet.
- Évitez les colonnes Nullable - Par défaut, les colonnes ci-dessus ont été considérées comme Null. Le type Nullable permet aux requêtes de faire la différence entre une valeur vide et une valeur Null. Cela crée une colonne distincte de type UInt8. Cette colonne supplémentaire doit être traitée chaque fois qu’un utilisateur travaille avec une colonne nullable. Cela entraîne un espace de stockage supplémentaire et affecte presque toujours négativement les performances des requêtes. N’utilisez Nullable que s’il existe une différence entre la valeur vide par défaut d’un type et Null. Par exemple, une valeur de 0 pour les valeurs vides dans la colonne
ViewCountsera probablement suffisante pour la plupart des requêtes et n’affectera pas les résultats. Si les valeurs vides doivent être traitées différemment, elles peuvent souvent aussi être exclues des requêtes à l’aide d’un filtre. Utilisez la précision minimale pour les types numériques - ClickHouse propose plusieurs types numériques conçus pour différentes plages de valeurs et différents niveaux de précision. Cherchez toujours à minimiser le nombre de bits utilisés pour représenter une colonne. En plus des entiers de tailles différentes, par ex. Int16, ClickHouse propose des variantes non signées dont la valeur minimale est 0. Celles-ci peuvent permettre d’utiliser moins de bits pour une colonne ; par ex. UInt16 a une valeur maximale de 65535, soit le double de celle d’un Int16. Préférez ces types aux variantes signées plus larges lorsque c’est possible. - Précision minimale pour les types de date - ClickHouse prend en charge plusieurs types de date et de date time. Date et Date32 peuvent être utilisés pour stocker uniquement des dates, le second prenant en charge une plage de dates plus large au prix d’un plus grand nombre de bits. DateTime et DateTime64 prennent en charge les date times. DateTime est limité à une granularité à la seconde et utilise 32 bits. DateTime64, comme son nom l’indique, utilise 64 bits mais prend en charge une granularité allant jusqu’à la nanoseconde. Comme toujours, choisissez la version la moins précise qui reste acceptable pour les requêtes, afin de minimiser le nombre de bits nécessaires.
- Utilisez LowCardinality - Les colonnes de type nombre, chaîne, Date ou DateTime avec un faible nombre de valeurs uniques peuvent potentiellement être encodées à l’aide du type LowCardinality. Ce Dictionary encode les valeurs, ce qui réduit la taille sur le disque. Envisagez cette option pour les colonnes contenant moins de 10k valeurs uniques. FixedString pour les cas particuliers - Les chaînes de longueur fixe peuvent être encodées avec le type FixedString, par ex. les codes de langue et de devise. Cela est efficace lorsque les données ont une longueur exacte de N octets. Dans tous les autres cas, cela réduira probablement l’efficacité, et LowCardinality est à privilégier.
- Enums pour la validation des données - Le type Enum peut être utilisé pour encoder efficacement des types énumérés. Les Enums peuvent être codés sur 8 ou 16 bits, selon le nombre de valeurs uniques qu’ils doivent stocker. Envisagez cette option si vous avez besoin soit de la validation associée au moment de l’insert (les valeurs non déclarées seront rejetées), soit d’exécuter des requêtes qui exploitent un ordre naturel dans les valeurs Enum, par ex. imaginez une colonne de feedback contenant des réponses d’utilisateurs
Enum(':(' = 1, ':|' = 2, ':)' = 3).
Conseil : Pour trouver la plage de toutes les colonnes, ainsi que le nombre de valeurs distinctes, vous pouvez utiliser la requête simple SELECT * APPLY min, * APPLY max, * APPLY uniq FROM table FORMAT Vertical. Nous recommandons d’exécuter cela sur un sous-ensemble plus restreint des données, car cela peut être coûteux. Cette requête exige que les valeurs numériques soient au moins définies comme telles pour obtenir un résultat précis, c.-à-d. pas comme String.
En appliquant ces règles simples à notre table posts, nous pouvons identifier un type optimal pour chaque colonne :
On obtient alors le schéma suivant :
INSERT INTO SELECT, en lisant les données de notre table précédente pour les insérer dans celle-ci :
Choisir une clé de tri
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, siQuelques règles simples peuvent vous aider à choisir une clé de tri. Les critères ci-dessous peuvent parfois entrer en conflit ; examinez-les donc dans cet ordre. Ce processus vous permettra d’identifier plusieurs clés, 4 à 5 étant généralement suffisantes :CreationDateest utilisée comme clé, l’ordre des valeurs dans toutes les autres colonnes correspondra à celui des valeurs de la colonneCreationDate. Plusieurs clés de tri peuvent être spécifiées — elles suivront alors la même sémantique qu’une clauseORDER BYdans une requêteSELECT.
- Sélectionnez les colonnes qui correspondent à vos filtres les plus courants. Si une colonne est fréquemment utilisée dans des clauses
WHERE, privilégiez son inclusion dans votre clé par rapport à des colonnes moins souvent utilisées. Privilégiez les colonnes qui permettent d’exclure une grande partie du total des lignes lorsqu’un filtre est appliqué, afin de réduire la quantité de données à lire. - Privilégiez les colonnes susceptibles d’être fortement corrélées avec d’autres colonnes de la table. Cela contribuera à garantir que ces valeurs sont elles aussi stockées de manière contiguë, ce qui améliore la compression.
Les opérations
GROUP BYetORDER BYsur les colonnes de la clé de tri peuvent ainsi devenir plus économes en mémoire.
Exemple
posts, supposons que nos utilisateurs souhaitent effectuer des analyses avec filtrage par date et par type de publication, par ex. :
“Quelles questions ont reçu le plus de commentaires au cours des 3 derniers mois ?”.
La requête permettant de répondre à cette question avec notre précédente table posts_v2, qui utilise des types optimisés mais n’a pas de clé de tri :
La requête est ici très rapide, même si les 60 M de lignes ont toutes été parcourues linéairement — ClickHouse est tout simplement rapide :) Il faudra nous croire sur parole : les clés de tri valent vraiment le coup à l’échelle des To et des Po !Sélectionnons les colonnes
PostTypeId et CreationDate comme clés de tri.
Dans notre cas, on peut supposer que les utilisateurs filtreront toujours sur PostTypeId. Cette colonne a une cardinalité de 8 et constitue donc le choix logique pour le premier élément de notre clé de tri. Comme un filtrage à la granularité de la date sera probablement suffisant (tout en restant bénéfique pour les filtres sur la date et l’heure), nous utilisons toDate(CreationDate) comme 2e composant de notre clé. Cela produira également un index plus compact, car une date peut être représentée sur 16 bits, ce qui accélère le filtrage. Le dernier élément de notre clé est CommentCount, afin de faciliter la recherche des posts les plus commentés (le tri final).
Suite : Techniques de modélisation des données
Posts notre table centrale, à partir de laquelle s’exécutent la plupart des requêtes analytiques. Même si les autres tables peuvent toujours être interrogées isolément, nous partons du principe que la plupart des analyses seront effectuées dans le contexte de posts.
Dans cette section, nous utilisons des variantes optimisées de nos autres tables. Bien que nous en fournissions les schémas, nous omettons, par souci de concision, les choix effectués. Ceux-ci reposent sur les règles décrites précédemment, et nous laissons au lecteur le soin de les déduire.Les approches suivantes visent toutes à minimiser le recours aux JOIN afin d’optimiser les lectures et d’améliorer les performances des requêtes. Bien que les JOIN soient entièrement pris en charge dans ClickHouse, nous recommandons de les utiliser avec parcimonie (2 à 3 tables dans une requête avec JOIN conviennent) pour obtenir des performances optimales.
ClickHouse ne gère pas les clés étrangères. Cela n’interdit pas les JOIN, mais signifie que l’intégrité référentielle reste à la charge de l’utilisateur au niveau de l’application. Dans les systèmes OLAP comme ClickHouse, l’intégrité des données est souvent gérée au niveau de l’application ou pendant le processus d’ingestion des données, plutôt que d’être imposée par la base de données elle-même, où cela entraîne une surcharge importante. Cette approche offre davantage de flexibilité et permet une insertion plus rapide des données. Cela correspond à l’orientation de ClickHouse, axée sur la vitesse et la capacité de montée en charge des requêtes en lecture et en insertion sur de très grands jeux de données.Afin de minimiser l’utilisation des JOIN au moment de l’exécution des requêtes, les utilisateurs disposent de plusieurs outils/approches :
- Dénormalisation des données - Dénormalisez les données en combinant des tables et en utilisant des types complexes pour les relations non 1:1. Cela implique souvent de déplacer les JOIN du moment des requêtes vers celui de l’insertion.
- Dictionaries - Une fonctionnalité spécifique à ClickHouse pour gérer les direct joins et les recherches clé-valeur.
- Vues matérialisées incrémentielles - Une fonctionnalité de ClickHouse permettant de déplacer le coût d’un calcul de l’exécution des requêtes vers l’insertion, y compris la possibilité de calculer des valeurs agrégées de manière incrémentielle.
- Vues matérialisées actualisables - Comme les vues matérialisées utilisées dans d’autres systèmes de base de données, cette fonctionnalité permet de recalculer périodiquement les résultats d’une requête et de mettre le résultat en cache.