Exploitation de ClickHouse

Ce document présente les concepts ClickHouse que vous rencontrerez dans ce laboratoire de migration : ce qu’ils sont, leur raison d’être et leurs différences avec les constructions Snowflake utilisées dans la première partie.

1. Moteurs de table

ClickHouse n’est pas une base de données à moteur unique. Chaque table créée doit déclarer son moteur, qui détermine la manière dont les données sont stockées sur disque, dont les doublons sont traités et les fonctionnalités disponibles. Choisir le mauvais moteur est l’erreur la plus fréquente lors de la conception d’un schéma ClickHouse.

MergeTree

Le moteur de base de presque toutes les tables en production.

CREATE TABLE analytics.dim_taxi_zones (
    zone_id      UInt16,
    borough      String,
    service_zone String
) ENGINE = MergeTree()
ORDER BY zone_id;

Fonctionnement : ClickHouse stocke les données dans des parties, c’est-à-dire des blocs triés et compressés sur disque. Lorsque vous insérez des données, de nouvelles parties sont écrites. En arrière-plan, ClickHouse fusionne continuellement les petites parties pour en former de plus grandes, tout en maintenant les données triées selon la clé ORDER BY. Le nom du moteur vient de ce mécanisme.

Quand l’utiliser : pour toute table qui ne nécessite aucune déduplication et dont les insertions se font uniquement par ajout ou chargements en masse, par exemple les tables de dimensions, d’événements bruts ou de journaux.

Propriété essentielle : aucune contrainte de clé primaire n’est appliquée. Deux lignes dont les valeurs ORDER BY sont identiques sont toutes deux stockées. Si vous avez besoin d’une déduplication, utilisez ReplacingMergeTree.

ReplacingMergeTree(version_col)

Le moteur de déduplication. Il complète MergeTree par une règle : lors d’une fusion en arrière-plan, si deux lignes possèdent la même clé ORDER BY, seule celle dont la valeur version_col est la plus élevée est conservée.

CREATE TABLE analytics.fact_trips (
    trip_id     String,
    pickup_at   DateTime,
    total_amount Float64,
    updated_at  DateTime
) ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (pickup_at, trip_id);

Important — cohérence à terme : la déduplication n’intervient que pendant les fusions en arrière-plan. À un instant donné, votre table peut donc contenir des lignes en double. C’est ce qu’on appelle la cohérence à terme. Pour obtenir des résultats entièrement dédupliqués au moment de la requête, ajoutez FINAL à votre SELECT :

-- Without FINAL: may return duplicates if merges haven't run
SELECT * FROM analytics.fact_trips WHERE trip_id = 'abc';

-- With FINAL: forces deduplication at query time (slower, always correct)
SELECT * FROM analytics.fact_trips FINAL WHERE trip_id = 'abc';

Utilisation par dbt : l’adaptateur dbt-clickhouse emploie principalement la stratégie incrémentielle delete_insert : il supprime explicitement les lignes dont les clés correspondent, puis insère leur nouvelle version, ce qui garantit toujours un résultat correct. ReplacingMergeTree agit comme un filet de sécurité qui élimine les éventuels doublons passés entre les mailles, par exemple après l’échec d’une insertion partielle.

Équivalent dans Snowflake : il n’existe pas d’équivalent direct. Dans Snowflake, vous avez utilisé MERGE INTO ... WHEN MATCHED THEN UPDATE. ClickHouse ne possède pas d’instruction MERGE ; l’association de ReplacingMergeTree et de FINAL produit le même résultat logique.

Vue matérialisée actualisable

ClickHouse prend en charge deux types de vues matérialisées.

Vue matérialisée déclenchée (traditionnelle) : elle s’exécute à chaque INSERT et ne traite que le nouveau lot inséré.

-- Trigger-based: only sees the rows inserted in the current batch
CREATE MATERIALIZED VIEW analytics.mv_realtime_counts
TO analytics.counts_table AS
SELECT pickup_date, count() AS trips
FROM default.trips_raw
GROUP BY pickup_date;

Vue matérialisée actualisable (planifiée) : elle réexécute l’intégralité de la requête selon un calendrier, à la manière d’une tâche cron.

-- Refreshable: runs the full SELECT every 3 minutes
CREATE MATERIALIZED VIEW analytics.mv_hourly_revenue
REFRESH EVERY 180 SECOND AS
SELECT
    toStartOfHour(pickup_at) AS hour_bucket,
    pickup_borough,
    sum(total_amount)        AS revenue
FROM analytics.fact_trips FINAL
GROUP BY hour_bucket, pickup_borough;

Choisir le type adapté :

• Déclenchée : agrégations en temps réel sur des flux d’insertion, lorsque seules les nouvelles données doivent être traitées
• Actualisable : agrégations qui interrogent fact_trips FINAL et doivent donc voir la table entière pour la dédupliquer, ou tableaux de bord qui peuvent tolérer quelques minutes de décalage en échange d’une logique plus simple

Modifier la fréquence d’actualisation :

ALTER TABLE analytics.mv_hourly_revenue MODIFY REFRESH EVERY 60 SECOND;

2. Clés de tri (ORDER BY)

Dans Snowflake, vous avez utilisé CLUSTER BY comme indication pour l’optimiseur. Dans ClickHouse, ORDER BY constitue l’index primaire : il fixe l’ordre physique des données sur disque et pilote toutes les lectures par plage.

Fonctionnement

ClickHouse stocke un index primaire clairsemé, à raison d’une entrée d’index pour environ 8 192 lignes, soit un granule de données. Lorsque vous filtrez sur les colonnes de ORDER BY, ClickHouse ignore des granules entiers sans les lire. C’est ainsi qu’il peut parcourir des milliards de lignes par seconde : la plupart des données ne quittent jamais le disque.

L’ordre des cardinalités est important

Placez toujours les colonnes à faible cardinalité en premier et celles à forte cardinalité en dernier. Dans le cas courant, l’index peut ainsi ignorer un maximum de données.

-- Good: low cardinality (borough, ~6 values) first, then high cardinality (trip_id)
ORDER BY (pickup_borough, toStartOfMonth(pickup_at), trip_id)

-- Bad: high cardinality first — the index can't skip anything useful
ORDER BY (trip_id, pickup_borough, pickup_at)

Comparaison entre CLUSTER BY de Snowflake et ORDER BY de ClickHouse

Exemple : reproduire une clé de clustering Snowflake existante

-- Snowflake
CLUSTER BY (DATE_TRUNC('month', PICKUP_AT), PICKUP_LOCATION_ID)

-- ClickHouse equivalent
ORDER BY (toStartOfMonth(pickup_at), pickup_location_id, trip_id)
-- Note: trip_id added as tiebreaker to ensure unique sort order

Index de saut, en bref

Pour les colonnes absentes de la clé ORDER BY, ClickHouse propose des index de saut (filtre de Bloom, minmax, ensemble) qui stockent des métadonnées au niveau de la colonne pour chaque granule. Ils sont utiles pour filtrer une colonne à faible cardinalité placée après une colonne à forte cardinalité dans la clé de tri.

-- Add a bloom filter skip index on payment_type
ALTER TABLE analytics.fact_trips
ADD INDEX idx_payment_type payment_type TYPE bloom_filter GRANULARITY 4;

3. Traitement du JSON

Le type de colonne VARIANT de Snowflake permet de parcourir un document JSON avec une notation de chemin utilisant les deux-points. ClickHouse emploie à la place des fonctions JSONExtract* explicites.

Tableau de correspondance

Variantes des fonctions

-- Float (returns 0.0 if key missing or wrong type)
JSONExtractFloat(trip_metadata, 'driver', 'rating')

-- String (returns '' if missing)
JSONExtractString(trip_metadata, 'app', 'version')

-- Integer (returns 0 if missing)
JSONExtractInt(trip_metadata, 'driver', 'id')

-- Bool (returns 0/1)
JSONExtractBool(trip_metadata, 'app', 'is_shared')

-- Raw value as string (preserves JSON sub-object)
JSONExtractRaw(trip_metadata, 'route')

Conseil de performance

Si vous interrogez souvent la même colonne JSON, envisagez d’extraire ses champs dans des colonnes typées au niveau du modèle de préparation, dans stg_trips.sql, au lieu d’appeler JSONExtractFloat dans chaque requête en aval. C’est l’approche adoptée par les modèles dbt du laboratoire.

4. Fonctions de date et d’heure

Snowflake et ClickHouse proposent des fonctions comparables pour les dates et les heures, mais leur syntaxe diffère. Voici les correspondances les plus courantes :

Tableau de correspondance

Fonctions pratiques propres à ClickHouse

yesterday()              -- today() - 1 day
toStartOfWeek(col)       -- Monday of the containing week
toStartOfQuarter(col)    -- first day of the quarter
toYear(col)              -- extract year as integer
toMonth(col)             -- extract month as integer (1-12)
toDayOfWeek(col)         -- 1=Monday, 7=Sunday

Syntaxe des intervalles

-- ClickHouse
now() - INTERVAL 7 DAY
now() - INTERVAL 1 HOUR
now() - INTERVAL 30 MINUTE
pickup_at + INTERVAL 90 SECOND

-- Snowflake equivalent
DATEADD('day', -7, CURRENT_TIMESTAMP())
DATEADD('hour', -1, CURRENT_TIMESTAMP())

5. Fonctions approximatives

ClickHouse est conçu pour les charges analytiques dans lesquelles une réponse exacte sur des milliards de lignes est plus lente qu’une estimation suffisamment précise pour un tableau de bord. Il intègre plusieurs fonctions d’agrégation approximatives.

Décompte des valeurs distinctes

-- Exact (like Snowflake COUNT(DISTINCT ...))
SELECT uniqExact(trip_id) FROM analytics.fact_trips FINAL;

-- Approximate — good for "how many unique passengers today?"
SELECT uniq(passenger_id) FROM analytics.fact_trips FINAL;

Centiles

-- P95 trip duration — approximate but uses O(1) memory
SELECT quantileTDigest(0.95)(duration_minutes)
FROM analytics.fact_trips FINAL;

-- Multiple percentiles in one pass
SELECT quantileTDigestMerge(0.5)(state), quantileTDigestMerge(0.95)(state)
FROM analytics.fact_trips FINAL;

Règle pratique : utilisez uniq et quantileTDigest pour les tableaux de bord interactifs. Réservez uniqExact et quantile aux cas qui exigent des valeurs exactes, comme la facturation, les SLA ou la conformité.

6. Dictionnaires

Les dictionnaires sont des tables de recherche en mémoire que ClickHouse maintient chargées et préjointes au moment de la requête. Ils constituent l’équivalent ClickHouse d’une petite table de dimension que vous souhaitez joindre sans payer le coût d’un JOIN complet.

Définition

Un dictionnaire repose sur une source, telle qu’une table ClickHouse, un fichier ou une base externe. Il est chargé en mémoire au démarrage du service ou lors de l’appel de SYSTEM RELOAD DICTIONARIES. Les recherches s’effectuent à l’aide d’une clé et renvoient un ou plusieurs attributs.

Syntaxe CREATE DICTIONARY

-- From 04_create_dictionary.sql
CREATE DICTIONARY analytics.taxi_zones_dict (
    zone_id      UInt16,
    borough      String,
    service_zone String
)
PRIMARY KEY zone_id
SOURCE(CLICKHOUSE(
    TABLE 'dim_taxi_zones'
    DB    'analytics'
))
LIFETIME(MIN 300 MAX 600)   -- refresh every 5-10 minutes
LAYOUT(FLAT());             -- hash map, best for < 1M rows

Options de LAYOUT :

• FLAT() — tableau indexé par une clé entière, le plus rapide, nécessite des clés entières séquentielles
• HASHED() — table de hachage compatible avec n’importe quelle clé entière
• COMPLEX_KEY_HASHED() — table de hachage avec des clés composites ou textuelles

Utilisation de dictGet

-- Instead of: JOIN analytics.dim_taxi_zones USING (zone_id)
SELECT
    trip_id,
    dictGet('analytics.taxi_zones_dict', 'borough', toUInt64(pickup_location_id)) AS pickup_borough,
    dictGet('analytics.taxi_zones_dict', 'borough', toUInt64(dropoff_location_id)) AS dropoff_borough
FROM analytics.fact_trips FINAL;

Quand choisir un dictionnaire plutôt qu’un JOIN

7. Clause SAMPLE

ClickHouse prend en charge l’échantillonnage au niveau des lignes directement dans la syntaxe des requêtes. Cet échantillonnage lit une fraction déterministe des données, ce qui convient à l’analyse exploratoire lorsque des résultats exacts ne sont pas requis.

Syntaxe

-- Read approximately 10% of rows
SELECT count(), avg(total_amount)
FROM analytics.fact_trips SAMPLE 0.1;

-- Read a specific number of rows (approximately)
SELECT trip_id, pickup_at, total_amount
FROM analytics.fact_trips SAMPLE 1000000;

Mise à l’échelle des résultats

Lors d’un échantillonnage, multipliez les agrégats par 1 / sample_rate pour estimer les valeurs de l’ensemble de la table :

-- Estimate total revenue from 10% sample
SELECT sum(total_amount) * 10 AS estimated_total_revenue
FROM analytics.fact_trips SAMPLE 0.1;

Quand utiliser SAMPLE

• Analyse exploratoire (« la logique de ma requête est-elle correcte ? ») avant une exécution sur la table complète
• Tuiles de tableau de bord pour lesquelles des valeurs approximatives sont acceptables
• Entraînement de modèles de ML sur un sous-ensemble représentatif

Remarque : SAMPLE exige que la clé ORDER BY de la table commence par la colonne d’échantillonnage, à moins d’ajouter une clause SAMPLE BY à l’instruction CREATE TABLE. La table trips_raw du laboratoire est créée avec SAMPLE BY cityHash64(trip_id) à cette fin.

8. Remarques sur l’adaptateur dbt-clickhouse

L’adaptateur dbt-clickhouse (dbt-clickhouse>=1.8) prend en charge la plupart des fonctionnalités dbt standard, mais présente quelques comportements propres à ClickHouse qu’il faut connaître.

Stratégie incrémentielle delete_insert

ClickHouse ne possède pas de MERGE INTO. La stratégie delete_insert de l’adaptateur dbt-clickhouse en reproduit le comportement :

1. Supprimer de la table cible les lignes dont la ou les colonnes clés correspondent au lot entrant
2. Insérer l’ensemble complet des lignes entrantes

-- What dbt generates for incremental models
DELETE FROM analytics.fact_trips WHERE trip_id IN (SELECT trip_id FROM __dbt_tmp);
INSERT INTO analytics.fact_trips SELECT * FROM __dbt_tmp;

Configurez-la dans votre modèle :

{{
    config(
        materialized='incremental',
        incremental_strategy='delete_insert',
        unique_key='trip_id',
        engine='ReplacingMergeTree(updated_at)',
        order_by='(pickup_at, trip_id)'
    )
}}

Configuration d’engine et d’order_by

Chaque table MergeTree exige un moteur et un ORDER BY. Indiquez-les tous les deux dans la configuration du modèle dbt :

{{
    config(
        engine='MergeTree()',
        order_by='(zone_id)'
    )
}}

order_by à la place de cluster_by

Vous avez peut-être utilisé cluster_by dans les modèles dbt de Snowflake. Avec dbt-clickhouse, utilisez plutôt order_by. ClickHouse n’offre aucun équivalent au cluster_by de Snowflake : ORDER BY définit toujours le tri physique.

profiles.yml pour ClickHouse Cloud

ClickHouse Cloud exige TLS. Définissez secure: true :

# ~/.dbt/profiles.yml
nyc_taxi_ch:
  target: dev
  outputs:
    dev:
      type: clickhouse
      host: "{{ env_var('CLICKHOUSE_HOST') }}"
      port: 8443
      user: default
      password: "{{ env_var('CLICKHOUSE_PASSWORD') }}"
      schema: analytics       # default database for models without a custom schema
      secure: true
      threads: 4

Nommage des schémas et bases distinctes

ClickHouse emploie le terme base de données là où Snowflake parle de schéma. L’adaptateur dbt-clickhouse fait correspondre les schémas dbt aux bases de données ClickHouse. Dans ce projet, la macro generate_schema_name remplace le comportement par défaut de dbt afin que les modèles configurés avec +schema: analytics aboutissent dans la base analytics, et non dans staging_analytics.

-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
  {%- if custom_schema_name is none -%}
    {{ target.schema }}
  {%- else -%}
    {{ custom_schema_name }}
  {%- endif -%}
{%- endmacro %}

Il s’agit du même modèle que dans le projet dbt Snowflake de la première partie : la macro est volontairement identique afin de conserver un comportement de nommage des schémas cohérent entre les deux adaptateurs.