Choix des types de données

L’une des principales raisons des performances des requêtes de ClickHouse est l’efficacité de sa compression des données. Moins il y a de données sur le disque, plus les requêtes et les insertions sont rapides, car cela réduit la surcharge d’E/S. L’architecture orientée colonnes de ClickHouse regroupe naturellement les données similaires côte à côte, ce qui permet aux algorithmes de compression et aux codecs de réduire considérablement leur taille. Pour tirer pleinement parti de ces avantages, il est essentiel de choisir soigneusement les types de données appropriés.

L’efficacité de la compression dans ClickHouse dépend principalement de trois facteurs : la clé d’ordonnancement, les types de données et les codecs, tous définis dans le schéma de la table. Choisir des types de données optimaux améliore immédiatement à la fois le stockage et les performances des requêtes.

Quelques recommandations simples peuvent considérablement améliorer le schéma :

• Utilisez des types précis : Sélectionnez toujours le bon type de données pour les colonnes. Les champs numériques et de date doivent utiliser les types numériques et de date appropriés plutôt que des types String génériques. Cela garantit une sémantique correcte pour le filtrage et les agrégations.

• Évitez les colonnes Nullable : Les colonnes Nullable introduisent une surcharge supplémentaire, car elles nécessitent des colonnes distinctes pour suivre les valeurs nulles. N’utilisez Nullable que si cela est explicitement nécessaire pour distinguer les états vides et nuls. Sinon, des valeurs par défaut ou équivalentes à zéro suffisent généralement. Pour plus d’informations sur les raisons pour lesquelles ce type doit être évité sauf nécessité, voir Évitez les colonnes Nullable.

• Réduisez au minimum la précision numérique : Sélectionnez les types numériques avec la largeur en bits la plus faible possible tout en couvrant la plage de données attendue. Par exemple, préférez UInt16 à Int32 si les valeurs négatives ne sont pas nécessaires et que la plage reste comprise entre 0 et 65535.

• Optimisez la précision des dates et heures : Choisissez le type de date ou de date-heure le moins précis qui répond aux exigences des requêtes. Utilisez Date ou Date32 pour les champs contenant uniquement une date, et préférez DateTime à DateTime64 sauf si une précision à la milliseconde ou plus fine est indispensable.

• Exploitez LowCardinality et les types spécialisés : Pour les colonnes comportant moins d’environ 10 000 valeurs uniques, utilisez les types LowCardinality afin de réduire significativement le stockage grâce à l’encodage par dictionnaire. De même, n’utilisez FixedString que lorsque les valeurs de la colonne sont strictement des chaînes de longueur fixe (par exemple, des codes pays ou devise), et privilégiez les types Enum pour les colonnes ayant un ensemble fini de valeurs possibles afin de bénéficier d’un stockage efficace et d’une validation intégrée des données.

• 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 de l’utiliser si vous avez besoin soit de la validation appliquée au moment de l’insertion (les valeurs non déclarées seront rejetées), soit d’exécuter des requêtes qui exploitent un ordre naturel des valeurs Enum, par exemple une colonne de retours contenant des réponses utilisateur Enum(':(' = 1, ':|' = 2, ':)' = 3).

ClickHouse propose des outils intégrés pour faciliter l’optimisation des types. Par exemple, l’inférence de schéma peut identifier automatiquement les types initiaux. Prenons le jeu de données Stack Overflow, accessible au format Parquet. Une simple inférence de schéma via la commande DESCRIBE fournit un schéma initial non optimisé.

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet')
SETTINGS describe_compact_output = 1

┌─name───────────────────────┬─type──────────────────────────────┐
│ Id                         │ Nullable(Int64)                   │
│ PostTypeId                 │ Nullable(Int64)                   │
│ AcceptedAnswerId           │ Nullable(Int64)                   │
│ CreationDate               │ Nullable(DateTime64(3, 'UTC'))    │
│ Score                      │ Nullable(Int64)                   │
│ ViewCount                  │ Nullable(Int64)                   │
│ Body                       │ Nullable(String)                  │
│ OwnerUserId                │ Nullable(Int64)                   │
│ OwnerDisplayName           │ Nullable(String)                  │
│ LastEditorUserId           │ Nullable(Int64)                   │
│ LastEditorDisplayName      │ Nullable(String)                  │
│ LastEditDate               │ Nullable(DateTime64(3, 'UTC'))    │
│ LastActivityDate           │ Nullable(DateTime64(3, 'UTC'))    │
│ Title                      │ Nullable(String)                  │
│ Tags                       │ Nullable(String)                  │
│ AnswerCount                │ Nullable(Int64)                   │
│ CommentCount               │ Nullable(Int64)                   │
│ FavoriteCount              │ Nullable(Int64)                   │
│ ContentLicense             │ Nullable(String)                  │
│ ParentId                   │ Nullable(String)                  │
│ CommunityOwnedDate         │ Nullable(DateTime64(3, 'UTC'))    │
│ ClosedDate                 │ Nullable(DateTime64(3, 'UTC'))    │
└────────────────────────────┴───────────────────────────────────┘

22 rows in set. Elapsed: 0.130 sec.

En appliquant nos premières règles simples à notre table posts, nous pouvons identifier le type optimal pour chaque colonne :

On obtient alors le schéma optimisé suivant (du point de vue des types) :

CREATE TABLE posts
(
   Id Int32,
   PostTypeId Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 
   'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
   AcceptedAnswerId UInt32,
   CreationDate DateTime,
   Score Int32,
   ViewCount UInt32,
   Body String,
   OwnerUserId Int32,
   OwnerDisplayName String,
   LastEditorUserId Int32,
   LastEditorDisplayName String,
   LastEditDate DateTime,
   LastActivityDate DateTime,
   Title String,
   Tags String,
   AnswerCount UInt16,
   CommentCount UInt8,
   FavoriteCount UInt8,
   ContentLicense LowCardinality(String),
   ParentId String,
   CommunityOwnedDate DateTime,
   ClosedDate DateTime
)
ENGINE = MergeTree
ORDER BY tuple()

Une colonne Nullable (par ex. Nullable(String)) crée une colonne distincte de type UInt8. Cette colonne supplémentaire doit être traitée chaque fois qu'un utilisateur utilise une colonne Nullable. Cela consomme davantage d'espace de stockage et a presque toujours un impact négatif sur les performances.

Pour éviter les colonnes Nullable, envisagez de définir une valeur par défaut pour cette colonne. Par exemple, au lieu de :

CREATE TABLE default.sample
(
    `x` Int8,
    `y` Nullable(Int8)
)
ENGINE = MergeTree
ORDER BY x

utiliser

CREATE TABLE default.sample2
(
    `x` Int8,
    `y` Int8 DEFAULT 0
)
ENGINE = MergeTree
ORDER BY x

Selon votre cas d’usage, une valeur par défaut peut ne pas convenir.