Requêtes SELECT dans ClickHouse : différences avec PostgreSQL, PREWHERE, SAMPLE

Requêtes SELECT dans ClickHouse : Comment j'ai rééduqué mon cerveau après 10 ans de PostgreSQL

Quand je me suis assis avec ClickHouse après dix ans de PostgreSQL, j'ai essayé d'exécuter un SELECT * FROM bets WHERE sport = 'football' ORDER BY created_at DESC LIMIT 10 familier. Ça a fonctionné, mais rapidement — suspectement rapidement. Puis j'ai découvert PREWHERE et SAMPLE — des constructions qui n'existent pas et ne peuvent pas exister dans PostgreSQL à cause du stockage en lignes.

ClickHouse n'exécute pas simplement du SQL — il le repense pour une architecture en colonnes. Voici toutes les différences qui ont cassé mes requêtes (et parfois la production).

1. Syntaxe de base : un visage familier avec un caractère en colonnes

Une requête SELECT basique ressemble à ceci :

-- Sélection simple
SELECT user_id, amount, odds 
FROM betting.bets 
WHERE created_at >= today() - 7 
ORDER BY amount DESC 
LIMIT 100;

Mais la différence commence quand on regarde EXPLAIN. PostgreSQL construit des plans avec Seq Scan, Index Scan, Bitmap Heap Scan. ClickHouse affiche le nombre de granules et de partitions lues.

Différence clé : Dans PostgreSQL, SELECT * est parfois acceptable (si vous avez besoin de presque toutes les colonnes). Dans ClickHouse, SELECT * lit toutes les colonnes du disque. Si vous n'avez pas besoin de 20 colonnes sur 30 — listez seulement celles dont vous avez besoin. Nous avons économisé 70% d'E/S disque de cette façon.

2. PREWHERE — Une optimisation que PostgreSQL envie

PREWHERE filtre AVANT que les colonnes soient décompressées et lues.

-- Sans PREWHERE (plus lent)
SELECT user_id, amount, odds, sport
FROM betting.bets
WHERE outcome = 'win' AND amount > 1000;

-- Avec PREWHERE (plus rapide)
SELECT user_id, amount, odds, sport
FROM betting.bets
PREWHERE outcome = 'win'
WHERE amount > 1000;

Comment ça marche :

1. ClickHouse lit d'abord la colonne outcome (un fichier sur le disque)
2. Filtre les lignes, ne gardant que win
3. Pour les lignes filtrées seulement, lit les colonnes restantes
4. Puis applique amount > 1000

Quand ClickHouse applique PREWHERE automatiquement : Si vous écrivez WHERE outcome = 'win', l'optimiseur peut automatiquement déplacer les conditions légères vers PREWHERE. Mais je l'écris toujours explicitement pour les conditions complexes.

Ce qui m'a brûlé : PREWHERE ne fonctionne pas avec les colonnes de la PRIMARY KEY. ClickHouse lit toujours l'index en premier. N'essayez pas d'optimiser ce qui est déjà rapide.

3. SAMPLE — Réponse approximative en 0,1 seconde

En affaires, on vous pose parfois des questions comme : "Estimez le volume approximatif de paris par heure, à 5% près." La précision absolue n'est pas nécessaire.

-- 10% de lignes aléatoires (SAMPLE 0.1)
SELECT 
    toHour(created_at) AS hour,
    count() * 10 AS estimated_total_bets
FROM betting.bets
SAMPLE 0.1
WHERE created_at >= now() - INTERVAL 1 HOUR
GROUP BY hour;

Comment SAMPLE fonctionne physiquement : ClickHouse ne lit pas chaque granule entièrement, mais un sur N. Cela fonctionne car les données sur le disque ne sont pas mélangées — dans un granule, elles sont triées par ORDER BY.

Mes règles pour SAMPLE :

• Pour les agrégats avec des milliards de lignes — SAMPLE 0.01 suffit avec une précision de 2-3%
• Ne pas utiliser pour les calculs exacts (finances, paiements)
• Fonctionne seulement si la table a été créée avec une clé SAMPLE BY (ou ORDER BY)

4. FINAL — Un champ de mines pour les débutants

Si vous utilisez ReplacingMergeTree (un moteur de déduplication), les lignes peuvent avoir plusieurs versions. FINAL force ClickHouse à les fusionner à la volée.

-- Lent (mais parfois nécessaire)
SELECT user_id, max(amount)
FROM betting.bets_replacing
FINAL
GROUP BY user_id;

Pourquoi je n'utilise presque jamais FINAL : Cela force la lecture de toutes les parties et leur fusion en mémoire. Si vous avez un milliard de lignes, la requête manquera de mémoire.

Alternatives à FINAL :

• Regroupement avec argMax (recommandé)
• OPTIMIZE TABLE ... FINAL périodique en arrière-plan
• Ne pas utiliser ReplacingMergeTree du tout

-- Au lieu de FINAL
SELECT user_id, argMax(amount, version) AS last_amount
FROM betting.bets_replacing
GROUP BY user_id;

5. IN/NOT IN avec sous-requêtes vs JOIN

Dans PostgreSQL, JOIN est souvent plus rapide que les sous-requêtes. Dans ClickHouse, c'est l'inverse — IN avec une sous-requête gagne souvent.

-- Rapide dans ClickHouse
SELECT user_id, sum(amount)
FROM betting.bets
WHERE user_id IN (SELECT user_id FROM betting.fraud_users)
GROUP BY user_id;

-- Plus lent (mais plus lisible)
SELECT b.user_id, sum(b.amount)
FROM betting.bets b
JOIN betting.fraud_users f ON b.user_id = f.user_id
GROUP BY b.user_id;

Pourquoi IN est plus rapide : ClickHouse transforme la sous-requête en un ensemble de constantes en mémoire et filtre en utilisant des opérations en colonnes. JOIN nécessite une correspondance ligne par ligne.

Quand JOIN est encore nécessaire :

• Plus de deux tables
• Besoin de colonnes des deux tables dans SELECT
• Conditions de jointure complexes (pas seulement l'égalité)

6. Modificateurs ANY / ALL — Reliques des premiers jours

Ces modificateurs existent pour la compatibilité avec d'autres SGBD. Je les utilise rarement.

-- ANY : comme MIN pour le regroupement
SELECT user_id, ANY(sport) AS any_sport
FROM betting.bets
GROUP BY user_id;

-- ALL : comme MAX
SELECT user_id, ALL(amount) AS all_amounts  -- tableau de tous les montants
FROM betting.bets
GROUP BY user_id;

Mais je préfère les agrégations explicites : min(), max(), groupArray().

7. DISTINCT et ses performances

SELECT DISTINCT dans ClickHouse est plus rapide que dans PostgreSQL, mais pas gratuit.

-- Tous les sports uniques
SELECT DISTINCT sport FROM betting.bets;

-- DISTINCT avec ORDER BY
SELECT DISTINCT user_id, created_at
FROM betting.bets
ORDER BY created_at DESC
LIMIT 100;

Sous le capot : ClickHouse construit une table de hachage en mémoire. Si vous exécutez DISTINCT sur une colonne avec un milliard de valeurs uniques — vous aurez une OOM.

Mes conseils :

• Au lieu de SELECT DISTINCT user_id, utilisez GROUP BY user_id (même résultat)
• Pour un comptage approximatif des uniques — uniq() et uniqHLL12()
• Pour les valeurs uniques les plus fréquentes — topK()

8. FORMAT — Sortie selon les besoins du client

ClickHouse peut renvoyer les résultats dans des dizaines de formats. J'en utilise cinq :

-- Lisible par l'humain (pour la console)
SELECT * FROM bets LIMIT 3 FORMAT Pretty;

-- Compact (par défaut)
SELECT * FROM bets LIMIT 3 FORMAT PrettyCompact;

-- JSON pour API
SELECT * FROM bets LIMIT 3 FORMAT JSON;

-- JSON ligne par ligne (économie de mémoire lors de l'analyse)
SELECT * FROM bets LIMIT 3 FORMAT JSONEachRow;

-- CSV pour Excel
SELECT * FROM bets LIMIT 3 FORMAT CSV;

# En ligne de commande, vous pouvez surcharger le format
clickhouse-client --format=JSON --query="SELECT * FROM bets LIMIT 3"

9. Top 10 des requêtes pour l'analyse des paris (prêtes pour la production)

1. Paris par sport aujourd'hui

SELECT 
    sport,
    count() AS bets,
    sum(amount) AS total_staked,
    round(avg(odds), 2) AS avg_odds
FROM betting.bets
WHERE created_at >= today()
GROUP BY sport
ORDER BY total_staked DESC;

2. Top 10 des joueurs par chiffre d'affaires cette semaine

SELECT 
    user_id,
    count() AS bets,
    sum(amount) AS total_staked,
    sumIf(amount * odds, outcome = 'win') AS total_won,
    round(total_won / total_staked, 4) AS roi
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY user_id
ORDER BY total_staked DESC
LIMIT 10;

3. Volume horaire des paris aujourd'hui

SELECT 
    toHour(created_at) AS hour,
    count() AS bets,
    sum(amount) AS volume
FROM betting.bets
WHERE created_at >= today()
GROUP BY hour
ORDER BY hour;

4. Taux de gain par tranche de cotes

SELECT 
    CASE 
        WHEN odds < 1.5 THEN '1.00-1.49'
        WHEN odds < 2.0 THEN '1.50-1.99'
        WHEN odds < 3.0 THEN '2.00-2.99'
        ELSE '3.00+'
    END AS odds_range,
    count() AS total_bets,
    countIf(outcome = 'win') AS wins,
    round(wins / total_bets, 4) AS win_rate
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY odds_range
ORDER BY odds_range;

5. Heures les plus actives par jour de la semaine

SELECT 
    toDayOfWeek(created_at) AS dow,
    toHour(created_at) AS hour,
    count() AS bets
FROM betting.bets
WHERE created_at >= today() - 30
GROUP BY dow, hour
ORDER BY dow, hour;

6. Mise moyenne et cotes moyennes par utilisateur (LTV)

SELECT 
    user_id,
    avg(amount) AS avg_bet,
    avg(odds) AS avg_odds,
    count() AS total_bets,
    now() - max(created_at) AS hours_since_last_bet
FROM betting.bets
GROUP BY user_id
HAVING total_bets > 100
ORDER BY avg_bet DESC
LIMIT 50;

7. Nombre approximatif de joueurs uniques par heure

SELECT 
    toStartOfHour(created_at) AS hour,
    uniq(user_id) AS unique_users_approx,
    uniqExact(user_id) AS unique_users_exact
FROM betting.bets
WHERE created_at >= today() - 1
GROUP BY hour
ORDER BY hour;

8. Paiements et remboursements par jour

SELECT 
    toDate(created_at) AS day,
    sum(amount) AS staked,
    sumIf(amount * odds, outcome = 'win') AS paid,
    sumIf(amount, outcome = 'void') AS refunded,
    round((paid + refunded) / staked, 4) AS net_hold_pct
FROM betting.bets
GROUP BY day
ORDER BY day DESC
LIMIT 30;

9. Paris combinés vs paris simples

SELECT 
    bet_type,
    count() AS bets,
    avg(amount) AS avg_stake,
    avg(odds) AS avg_odds,
    avgIf(amount * odds, outcome = 'win') AS avg_payout
FROM betting.bets
GROUP BY bet_type;

10. Utilisateurs avec des schémas suspects (fraude)

SELECT 
    user_id,
    count() AS bets_5min,
    stddevPop(amount) AS stake_variance,
    stddevPop(odds) AS odds_variance
FROM betting.bets
WHERE created_at >= now() - INTERVAL 5 MINUTE
GROUP BY user_id
HAVING bets_5min > 30 AND stake_variance < 1 AND odds_variance < 0.1;

10. Erreurs courantes lors de la migration SQL de PostgreSQL vers ClickHouse

Erreur 1 : Utiliser SELECT * dans les sous-requêtes
Dans PostgreSQL, c'est acceptable. Dans ClickHouse, cela lit toutes les colonnes à chaque niveau.

Erreur 2 : S'attendre à ce que ORDER BY dans une sous-requête persiste
Dans ClickHouse, les sous-requêtes ne garantissent pas l'ordre, même avec ORDER BY. Triez seulement au niveau supérieur.

Erreur 3 : Sous-requêtes corrélées
ClickHouse optimise mal les sous-requêtes corrélées. Réécrivez-les en JOIN ou utilisez des fonctions de fenêtre.

-- Mauvais (lent)
SELECT user_id, amount
FROM bets b1
WHERE amount = (SELECT max(amount) FROM bets b2 WHERE b2.user_id = b1.user_id);

-- Bon (rapide)
SELECT user_id, max(amount) AS max_amount
FROM bets
GROUP BY user_id;

Erreur 4 : S'attendre à une intégrité transactionnelle
ClickHouse n'a pas de REPEATABLE READ. Si vous insérez des données pendant une requête — vous pourriez en voir une partie.

Erreur 5 : UPDATE et DELETE sans ALTER TABLE
Dans ClickHouse, ce sont des mutations, asynchrones et lourdes. Ne mettez pas à jour un million de lignes avec une seule commande.

Prochaine étape

Maintenant vous savez comment écrire des SELECT dans ClickHouse sans surprises. Dans le prochain article — agrégations avancées et fonctions de fenêtre.

---

← Précédente: Chargement de données dans ClickHouse : comment j'ai arrêté d'insérer ligne par ligne et accéléré l'ingestion par 500
→ Suivante: Fonctions d'agrégation ClickHouse : comment j'ai cessé de craindre uniqHLL12 et quantileTDigest

— Editorial Team