Fiche 3 : traduction du schéma

Durée estimée : 20 à 25 minutes Référence : Snowflake face à ClickHouse — section 2 (Différences de dialecte SQL)

Concept

La correspondance des types et la traduction des fonctions constituent la partie la plus mécanique de la migration, mais aussi la plus sujette aux erreurs si elle est effectuée sans attention. Snowflake et ClickHouse possèdent des systèmes de types aux sémantiques différentes ; un mauvais type peut entraîner une perte silencieuse de précision, un stockage excessif ou une logique de requête incorrecte.

Principes essentiels :

1. Explicitez la précision. Le TIMESTAMP_NTZ(9) de Snowflake offre une précision à la nanoseconde. Le DateTime de ClickHouse ne descend qu’à la seconde : ne l’utilisez pas pour une colonne de version. Choisissez DateTime64(3, 'UTC') pour les millisecondes (adaptées à la plupart des besoins réels) ou DateTime64(9, 'UTC') pour les nanosecondes. L’exactitude en dépend : si une colonne de version ReplacingMergeTree n’a qu’une précision à la seconde, deux mises à jour arrivées pendant la même seconde sont indéterministes ; ClickHouse ne peut pas savoir laquelle est la plus récente.

2. Utilisez le plus petit type entier correct. L’INTEGER de Snowflake est un NUMBER(38, 0) — une précision fixe de 38 chiffres stockée sur 128 bits. ClickHouse propose des entiers de taille fixe : Int8, Int16, Int32, Int64, UInt8, UInt16, UInt32, UInt64. Choisir UInt8 pour vendor_id (valeurs 1 à 3) économise 7 octets par ligne par rapport à Int64, soit 350 Mo sur 50 millions de lignes.

3. VARIANT → String. ClickHouse possède un type JSON natif (stable en production depuis la version 25.3), mais il est destiné aux schémas réellement dynamiques dont les noms de champs et la structure sont inconnus lors de la création de la table. Dans cet atelier, la structure de trip_metadata est connue (driver.rating, app.surge_multiplier, etc.) : mieux vaut extraire les champs dans des colonnes typées pendant la migration, ou stocker la valeur en String et utiliser JSONExtract* lors de la requête. Utilisez le type JSON lorsque le schéma est véritablement imprévisible, par exemple pour ingérer des événements clients arbitraires comportant chacun des champs différents. (« Extraire dans des colonnes typées » signifie créer des colonnes de premier niveau distinctes pendant l’ETL — comme FACT_TRIPS.driver_rating obtenu depuis trip_metadata — et non envelopper le JSON dans un Tuple. Une colonne Tuple impose toujours un ensemble fixe de champs et casse dès que les métadonnées d’une course ne correspondent pas à cette forme.)

4. Précision des flottants. Le FLOAT de Snowflake correspond à Float64 dans ClickHouse. Pour les montants monétaires qui exigent un calcul décimal exact, utilisez Decimal(18, 2) ; dans cet atelier, Float64 suffit pour reproduire la source. (C’est une valeur par défaut, pas une règle imposant Float64 à toute colonne FLOAT quelle que soit sa plage : driver_rating, dont les valeurs vont de 1,0 à 5,0 avec une décimale, tient largement dans les quelque 7 chiffres significatifs de Float32 ; son choix dépend de la nullabilité, pas de la précision monétaire protégée par la règle 4.)

5. LowCardinality() — optimisation propre à ClickHouse. Entourer un type de LowCardinality(String) (ou LowCardinality(UInt8), etc.) indique à ClickHouse d’appliquer un encodage par dictionnaire : les valeurs sont stockées comme des références entières plutôt que comme des chaînes répétées. Cela améliore généralement la compression d’un facteur 2 à 5 et accélère GROUP BY pour les chaînes comportant moins d’environ 10 000 valeurs distinctes. Snowflake n’a pas d’équivalent et gère ce point automatiquement. Bons candidats dans cet atelier : pickup_borough (6 valeurs), payment_type (6 valeurs), vehicle_type, vendor_name.

Exercice : correspondance des types de TRIPS_RAW

Associez chaque colonne de NYC_TAXI_DB.RAW.TRIPS_RAW à son type ClickHouse. TRIP_ID est déjà rempli à titre d’exemple : String est idiomatique lors de la migration d’un VARCHAR(36) ; il n’exige aucune conversion, prend en charge toutes les fonctions de chaîne et évite le coût d’analyse des UUID à l’insertion, même si ClickHouse propose aussi un type UUID natif.

Exercice : correspondance des types de FACT_TRIPS

FACT_TRIPS ajoute des colonnes calculées ou dérivées par le pipeline dbt. La plupart reprennent une décision de TRIPS_RAW ; DRIVER_RATING et UPDATED_AT sont nouvelles.

DRIVER_RATING vaut souvent NULL (aucune note donnée). Dans ClickHouse, Nullable(Float64) entraîne une légère surcharge par rapport à une colonne non nullable : un masque de bits séparé accompagne les données pour identifier les lignes nulles. Il faut choisir entre Nullable(Float32) (sémantique nulle explicite) et un simple Float32 associé à une valeur sentinelle comme -1.0 (plus rapide, moins conventionnel). Cet atelier privilégie Nullable(Float32) pour garantir l’exactitude.

Exercice : traduction des fonctions

Traduisez chaque expression Snowflake dans son équivalent ClickHouse. Elles proviennent directement des requêtes Q1 à Q7 de 01-setup-snowflake/queries/. Trois des huit éléments — QUALIFY, MERGE INTO et la lecture du stream CDC — ne se résument pas à une expression sur une ligne ; ils sont donc traités comme des questions sous le tableau.

Questions de réflexion

Après avoir rempli les tableaux, répondez à ces questions. Trois portent sur les traductions de l’exercice 3 qui nécessitaient plusieurs lignes ; les trois autres reprennent les « décisions de traduction non évidentes » de la fiche source — le raisonnement derrière les choix de type pour TRIP_METADATA, FARE_AMOUNT et PICKUP_LOCATION_ID.

Loading worksheet...

Report dans migration-plan.md

Copiez vos choix de types et toute note de traduction non évidente dans la section 5 de migration-plan.md, puis cochez :

- [ ] Schema translation: completed