Types de données ClickHouse : Référence complète pour l'analyse

ClickHouse : Référence complète des types de données pour l'analyse des paris (ce qui m'a coûté cher)

Un octet qui m'a coûté 500 Go d'espace disque

Quand j'ai commencé à travailler avec ClickHouse, j'utilisais String pour tout : user_id, event_time, montant du pari. Un mois plus tard, une table de 2 milliards de lignes pesait 4 téraoctets. Un collègue a regardé le schéma et a dit : « Pourquoi stockes-tu un nombre sous forme de chaîne ? » Il s'avère que String pour user_id prend 8 fois plus d'espace que UInt64. Je l'ai changé — et la table est passée à 800 Go.

ClickHouse propose des dizaines de types de données. Utiliser le bon type ne consiste pas à économiser des gigaoctets — c'est une question de vitesse de requête (moins de données à lire sur le disque) et de stabilité (Decimal au lieu de Float ne vous surprendra pas avec des erreurs d'arrondi).

Ci-dessous, tout ce que j'ai appris de projets réels (analyse des paris, détection de fraude, LTV). À la fin — un schéma prêt à l'emploi pour une plateforme de paris.

1. Types entiers : compter les utilisateurs et les paris

ClickHouse prend en charge les entiers signés (Int) et non signés (UInt) de 8 à 256 bits.

Type	Plage	Taille	Quand je l'utilise
UInt8	0..255	1 octet	Statuts (0/1), codes d'erreur
UInt16	0..65535	2 octets	Numéros de port, petits compteurs
UInt32	0..4,2 milliards	4 octets	Identifiants de pays, types d'événements
UInt64	0..18 quintillions	8 octets	user_id, event_id, montants en centimes
Int128/256	énorme	16/32 octets	Hachages cryptographiques, très grands compteurs

Pratique en production :

user_id UInt64,          -- 8 milliards d'utilisateurs nous suffisent
age UInt8,               -- personne ne vit plus de 255 ans
country_code UInt16,     -- 197 pays dans le monde, mais UInt16 est plus agréable
is_fraud UInt8,          -- 0 ou 1, pourquoi plus ?

Erreur courante : utiliser UInt64 pour tout. Si un champ ne prend que les valeurs 0 ou 1 (un drapeau), UInt8 est 8 fois plus compact. Sur un milliard de lignes, cela représente 8 Go contre 1 Go.

Ce qui m'a coûté cher : J'ai stocké timestamp en UInt64 (temps Unix). Cela fonctionne, mais vous perdez la possibilité d'utiliser des fonctions de date comme toDate(), toHour(), etc. Utilisez DateTime.

2. Float32/Float64 : Portefeuille ou trou ?

odds Float64,            -- les cotes peuvent être 2,5, 1,85, 100,0
probability Float32,     -- pourcentages 0,1..1,0, la précision 32 bits suffit

Pourquoi Float est dangereux pour les finances :

SELECT 0.1 + 0.2 AS float_sum;
-- Résultat : 0.30000000000000004 (classique IEEE 754)

Imaginez que vous ayez 1 million de paris de 0,01 centime chacun. L'erreur d'arrondi devient de l'argent réel. Pour les montants de paris et les gains, utilisez Decimal.

Quand Float est acceptable : cotes (2,15, 1,85), probabilités, pourcentages, métriques d'apprentissage automatique.

3. Decimal(P, S) : L'argent aime la précision

bet_amount Decimal(18, 2),   -- jusqu'à 10^16 roubles, 2 décimales
payout Decimal(20, 2),       -- le gain peut être supérieur à la mise
balance Decimal(32, 2)       -- solde à vie du joueur

• P (précision) — nombre total de chiffres (jusqu'à 38)
• S (échelle) — chiffres après la virgule

Règle que j'ai déduite : pour les roubles et les dollars — Decimal(18,2) suffit avec une marge (des billions). Pour la crypto — Decimal(38,8).

Opérations avec Decimal :

SELECT 
    bet_amount * odds AS potential_payout,  -- Decimal * Float64 → Decimal
    bet_amount + 0.01 AS rounded_up         -- fonctionne, mais attention
FROM bets;

Ce qui m'a coûté cher : ClickHouse ne gère pas Decimal * Decimal avec des échelles différentes — il promeut vers la plus grande. Nous avions des centimes à la 4e décimale qui ne s'arrondissaient jamais. Correctif : caster explicitement avec toDecimal32().

4. String vs FixedString vs LowCardinality(String)

String — Pour tout ce qui est long

session_id String,           -- UUID sans tirets, longueur variable
user_agent String,           -- chaînes longues, valeurs uniques
raw_json String              -- logs JSON

FixedString(N) — Pour les longueurs fixes (rarement nécessaire)

country_code FixedString(2), -- 'FR', 'US', 'DE' exactement 2 octets
md5_hash FixedString(32)     -- toujours 32 caractères

Je ne l'utilise presque jamais : si vous insérez une chaîne plus courte, ClickHouse la complète avec des octets nuls, ce qui provoque des surprises dans les comparaisons.

LowCardinality(String) — La magie pour les valeurs répétées

sport LowCardinality(String),      -- 'football', 'basketball', 'tennis' (répété)
device LowCardinality(String),     -- 'ios', 'android', 'web' (10-20 uniques)
outcome LowCardinality(String)     -- 'win', 'loss', 'void'

Comment ça marche : ClickHouse construit un dictionnaire des valeurs uniques et ne stocke que les index. Pour une colonne avec 10 valeurs uniques, les économies sont de 100x.

Moment réel : dans une table de paris, le champ sport se répétait des milliards de fois. Après avoir remplacé String par LowCardinality(String), la taille de la colonne est passée de 40 Go à 400 Mo.

Quand ne pas l'utiliser : si le nombre de valeurs uniques dépasse 10 000 (par exemple, user_agent). Le dictionnaire gonfle et les performances se dégradent.

5. DateTime vs DateTime64 vs Date : Le temps, c'est de l'argent

Type	Précision	Taille	Quand l'utiliser
Date	jour	2 octets	Partitionnement, rapports quotidiens
Date32	jour (jusqu'en 2106)	4 octets	si année > 2149 nécessaire
DateTime	seconde	4 octets	la plupart des événements
DateTime64(3)	milliseconde	8 octets	paris en direct, ordre des événements
DateTime64(6)	microseconde	8 octets	logs, métriques

Ce que j'utilise dans les projets de production :

event_time DateTime64(3),      -- millisecondes pour l'analyse en direct
registration_date Date,        -- partitionnement quotidien
last_update DateTime           -- la précision à la seconde suffit

Erreur courante : stocker l'heure sous forme de timestamp Unix (UInt64). Vous perdez toutes les fonctions de date/heure :

-- Cela ne fonctionnera pas :
SELECT toHour(event_time_uint) ... -- erreur

-- Vous avez besoin de :
SELECT toHour(toDateTime(event_time_uint)) ... -- conversion supplémentaire

Ce qui m'a coûté cher : J'ai utilisé DateTime pour les paris en direct. Quand cela comptait, 10 événements dans la même seconde étaient indiscernables. Je suis passé à DateTime64(3) — et l'ordre a été rétabli.

6. UUID : Quand la norme compte plus que la vitesse

session_id UUID,
bet_uuid UUID DEFAULT generateUUIDv4()

UUID prend 16 octets (comme deux UInt64). La comparaison est plus lente qu'avec des nombres.

Quand je l'utilise encore : besoin de générer des identifiants côté client sans accès à la base, intégration avec des systèmes externes, systèmes distribués sans générateur unique.

Alternative : UInt128 comme deux nombres 64 bits, mais vous perdez alors des fonctions comme toUUID().

7. Array(T) : Stocker des listes sans normalisation

tags Array(String),                     -- ['football', 'live', 'prematch']
coeff_history Array(Float64),           -- [1.5, 1.8, 2.1] changements de cotes
bet_bundle Array(UInt64)                -- identifiants de paris dans un accumulateur

Où cela a vraiment aidé : nous stockons l'historique des changements de cotes pour un seul événement dans un tableau. Dans une base relationnelle, vous auriez besoin d'une table séparée. ClickHouse fonctionne très bien avec arrayMap, arrayFilter, arrayJoin.

Requête réelle : trouver les événements où les cotes ont baissé de plus de 30 % :

SELECT event_id, coeff_history
FROM events
WHERE arrayExists((x, i) -> i > 1 AND x / coeff_history[i-1] < 0.7, coeff_history);

Limitation : les tableaux imbriqués (Array(Array(String))) sont à peine supportés. Dé-normalisez vers une structure plate.

8. Nullable(T) : Le mal à éviter

bonus_amount Nullable(Decimal(10,2)),
refund_reason Nullable(String)

Nullable ajoute un indicateur supplémentaire par valeur (un masque de bits). Cela signifie :

• Un octet supplémentaire par ligne
• Des agrégations plus lentes (SUM, AVG doivent vérifier NULL)
• Ne fonctionne pas avec certains moteurs (par exemple, pour les clés ORDER BY)

Ma position : dans ClickHouse, j'évite NULL. À la place :

• Nombres : 0 au lieu de NULL
• Chaînes : '' (chaîne vide)
• Dates : '1970-01-01'

Exception : quand 0 est une valeur légitime. Par exemple, un bonus de 0 rouble n'est pas la même chose qu'aucun bonus attribué. Dans ce cas, utilisez Nullable.

9. Enum8/Enum16 : Pour les listes de valeurs finies

outcome Enum8('win' = 1, 'loss' = 2, 'void' = 3),
bet_type Enum8('single' = 1, 'express' = 2, 'system' = 3),
event_status Enum8('scheduled' = 1, 'live' = 2, 'finished' = 3, 'cancelled' = 4)

Avantages : stocké sur 1 octet (Enum8) ou 2 octets (Enum16), comparaisons rapides, sortie lisible.

Fonctionnement interne : ClickHouse stocke des nombres, mais SELECT affiche des chaînes.

INSERT INTO bets (outcome) VALUES ('win');  -- en tant que chaîne
INSERT INTO bets (outcome) VALUES (1);      -- ou en tant que nombre

Erreur courante : essayer de ALTER TABLE ... MODIFY COLUMN pour ajouter une nouvelle valeur à une Enum. ClickHouse ne permet pas de modifier une Enum sans recréer la table. Toutes les valeurs possibles doivent être planifiées à l'avance.

En cas de doute, utilisez LowCardinality(String). Sacrifiez un octet pour la flexibilité.

10. IPv4/IPv6 : Détection des multi-comptes

ip_address IPv4,
client_ip IPv6     -- les opérateurs mobiles utilisent IPv6

Stocké en binaire (4 ou 16 octets), opérations de sous-réseau rapides.

Cas d'utilisation réel : trouver les utilisateurs depuis la même IP :

SELECT user_id, count() AS bets
FROM bets
WHERE ip_address = IPv4StringToNum('192.168.1.1')
  AND created_at >= today() - 7
GROUP BY user_id
HAVING bets > 50;  -- bot potentiel

Fonctions qui sauvent la mise : IPv4NumToString(), IPv4CIDRToRange(), isIPv4String().

Schéma complet pour une plateforme de paris (éprouvé en production)

CREATE TABLE betting.bets_full
(
    -- Identifiants
    bet_id          UInt64 DEFAULT generateUUIDv4() (matérialisé) ???
    -- non, UUID séparément
    bet_uuid        UUID DEFAULT generateUUIDv4(),
    user_id         UInt64,
    event_id        UInt64,
    session_id      String,                          -- pas UUID, vient des logs nginx
    
    -- Horodatages
    created_at      DateTime64(3),                   -- millisecondes pour le direct
    updated_at      DateTime,
    bet_date        Date DEFAULT toDate(created_at), -- colonne matérialisée
    
    -- Champs monétaires (Decimal uniquement !)
    bet_amount      Decimal(18, 2),
    odds            Float64,                         -- les cotes — Float est acceptable
    potential_payout Decimal(20, 2) ALIAS bet_amount * odds,
    real_payout     Decimal(20, 2),
    
    -- Catégories avec répétitions
    sport           LowCardinality(String),
    bet_type        Enum8('single' = 1, 'express' = 2, 'system' = 3),
    outcome         Enum8('win' = 1, 'loss' = 2, 'void' = 3),
    device_type     LowCardinality(String),
    
    -- Listes (historique des changements)
    odds_history    Array(Float64),                  -- changements de cotes dans le temps
    cashout_attempts Array(DateTime64(3)),           -- tentatives de cashout
    
    -- Détection de fraude
    ip_address      IPv4,
    fingerprint     FixedString(32),                 -- hachage du navigateur
    
    -- Nullable uniquement là où vraiment nécessaire
    refund_amount   Nullable(Decimal(18, 2)),       -- NULL si pas de remboursement
    cancellation_reason LowCardinality(String)
)
ENGINE = MergeTree()
PARTITION BY bet_date
ORDER BY (created_at, user_id)
SETTINGS index_granularity = 8192;

Pourquoi ce schéma a survécu en production :

• bet_date est matérialisé à partir de created_at — partitionnement basé sur la date sans calcul supplémentaire
• LowCardinality pour sport et device_type — économise 80 % d'espace
• ALIAS pour potential_payout — non stocké, calculé à la requête
• Pas de Nullable là où 0 ou une chaîne vide suffisent

Et ensuite

Choisir les bons types est la base. Dans les prochains articles, nous aborderons la construction d'agrégations, les fonctions de fenêtre et les vues matérialisées sur ces données.

La table de cet article est dans notre cluster de production depuis un an, avec 3 billions d'enregistrements. Elle pèse 12 To (avec compression ZSTD). Si tout était String, ce serait 40 To. Choisissez vos types avec sagesse.

---

← Précédente: Client ClickHouse : Comment j'ai apprivoisé la console et l'API HTTP dans un projet de paris
→ Suivante: MergeTree dans ClickHouse : comment le moteur découpe l'analyse en granules et fusionne les parties

— Editorial Team