Corrigés des exercices SQL avancés

Base de données : toutes les tables ci-dessous se trouvent dans la base de données otel. Lancez d'abord la commande avec clickhouse client --database otel ... ou exécutez USE otel;.

Exercice 1 : JOIN entre signaux — journaux + traces

WITH slow_traces AS (
    SELECT TraceId, ServiceName, SpanName, Duration
    FROM otel_traces
    WHERE Timestamp >= now() - INTERVAL 1 HOUR
      AND SpanKind = 'Server'             -- user-facing request spans only
      AND SpanName NOT LIKE '%flagd%'     -- exclude long-poll feature-flag streams
    ORDER BY Duration DESC
    LIMIT 10
)
SELECT
    t.TraceId,
    t.ServiceName       AS trace_service,
    t.SpanName,
    t.Duration          AS trace_duration_ns,
    l.Timestamp         AS log_time,
    l.ServiceName       AS log_service,
    l.SeverityText,
    l.Body
FROM slow_traces t
JOIN otel_logs_v2 l ON t.TraceId = l.TraceId
ORDER BY t.Duration DESC, l.Timestamp ASC;

Points pédagogiques :

• Cette requête unique remplace un parcours Kibana en 3 étapes : (1) rechercher les traces les plus lentes dans APM, (2) copier leurs identifiants, (3) rechercher séparément les journaux de chaque identifiant de trace.
• ClickHouse exécute le CTE une fois, puis utilise une jointure par hachage afin de faire correspondre l.TraceId au jeu de résultats de 10 lignes ; l'opération est extrêmement efficace.
• L'index de saut bloom_filter sur TraceId dans otel_logs_v2 accélère la sonde de jointure pour chacun des 10 identifiants de traces lentes.
• groupArray() n'est pas utilisé ici, car nous voulons des lignes de journal distinctes, et non des tableaux. L'exercice 6 emploie groupArray() pour produire un résultat agrégé.

Pourquoi les deux clauses WHERE supplémentaires ? Sans SpanKind = 'Server', les 10 spans les plus lents sont tous des interrogations longues gRPC EventStream internes de flagd : ces spans de gestion restent ouverts environ 10 minutes (Duration ≈ 600,000,000,000 ns) et ne comportent aucun enregistrement de journal associé, de sorte que la JOIN ne renvoie aucune ligne. Limiter la requête à SpanKind = 'Server' (requêtes HTTP entrantes) et exclure flagd par son nom fait apparaître de véritables requêtes lentes visibles par l'utilisateur, comme les spans frontend-proxy / ingress qui durent environ 8 à 9 secondes ; chacun possède des journaux d'application correspondants et la JOIN réussit. L'instrumentation de la démo OTel est naturellement bruitée dans le haut de la distribution des durées : cela rappelle utilement que le « classement des N latences les plus élevées » nécessite presque toujours un filtre par type ou par nom pour être pertinent.

Remarque concernant les valeurs de l'énumération : l'exportateur du collecteur OTel de ClickHouse écrit SpanKind sous la forme 'Server', 'Client', 'Internal', 'Producer', 'Consumer', et non avec les noms du protocole OTel tels que 'SPAN_KIND_SERVER'. Vérifiez toujours les valeurs réellement stockées : SELECT DISTINCT SpanKind FROM otel_traces.

Forme attendue du résultat : plusieurs lignes par trace (toutes les entrées de journal qui partagent ce TraceId), triées par durée de trace, puis par horodatage du journal.

Exercice 2 : fonctions de fenêtre — détection d'anomalies

WITH minute_errors AS (
    SELECT
        ServiceName,
        toStartOfMinute(Timestamp)  AS minute,
        countIf(StatusCode >= 500)  AS errors,
        count()                     AS total,
        if(total > 0, errors / total * 100, 0) AS error_rate_pct
    FROM otel_logs_v2
    WHERE TimestampTime >= now() - INTERVAL 1 HOUR
      AND RequestType != ''
    GROUP BY ServiceName, minute
    HAVING total > 50         -- skip low-sample minutes where the rate is noise
),
with_lag AS (
    SELECT
        ServiceName,
        minute,
        error_rate_pct,
        LAG(error_rate_pct) OVER (PARTITION BY ServiceName ORDER BY minute) AS prev_minute_rate,
        if(prev_minute_rate > 0, error_rate_pct / prev_minute_rate, 0)      AS spike_ratio
    FROM minute_errors
)
SELECT *
FROM with_lag
WHERE spike_ratio > 1.2
ORDER BY spike_ratio DESC;

Points pédagogiques :

• LAG(error_rate_pct) OVER (PARTITION BY ServiceName ORDER BY minute) compare chaque minute à la précédente pour le même service. Sans PARTITION BY, les comparaisons porteraient à tort sur des services différents et produiraient un rapport interservices dénué de sens.
• Dans Elasticsearch, il faudrait une agrégation date_histogram pour obtenir les nombres par minute, récupérer le JSON côté client, puis calculer les écarts en Python/JavaScript. Cela représente au moins 2 appels d'API, du code applicatif et un état pour mémoriser le compartiment précédent.
• Le CTE with minute_errors AS (...) calcule les métriques de base ; la requête externe applique la fonction de fenêtre. Cette structure en deux étapes clarifie l'intention et s'avère souvent plus efficace qu'une requête complexe unique : ClickHouse peut enchaîner les deux étapes sans matérialiser tout le résultat intermédiaire.
• La clause HAVING total > 50 du premier CTE joue un rôle discret mais essentiel : sans elle, une minute comprenant 3 requêtes et 1 erreur (taux de 33 % !) écraserait tous les autres compartiments. Filtrez toujours vos agrégations selon la taille de l'échantillon avant de comparer des rapports.

Pourquoi ces réglages précis (minute / 1,2× / 1 heure) ? Les générateurs de journaux de l'atelier émettent délibérément un taux d'erreur stable d'environ 5 % par service (avec un bruit de Poisson autour de la moyenne). En pratique :

Seuil	Paires de minutes l'ayant dépassé (dernière heure, environ 305 échantillons)
pic > 1,10 (10 %)	40
pic > 1,20 (20 %)	4
pic > 1,50 (50 %)	0
pic > 2,00 (2×)	0
pic > 3,00 (3×)	0

Un saut de 20 % (1,2×) à la minute est le seuil le plus faible qui fait apparaître des événements réellement rares plutôt que le bruit habituel. Exemple de sortie :

ServiceName    minute               rate_pct  prev_minute_rate  spike_ratio
web-frontend   2026-05-09 04:10:00  5.186     4.263             1.216
web-frontend   2026-05-09 03:48:00  5.501     4.549             1.209
api-gateway    2026-05-09 04:24:00  4.714     3.903             1.208
web-frontend   2026-05-09 04:01:00  5.357     4.460             1.201

Dans une règle de production, adaptez ces valeurs à vos propres motifs de trafic ; on utilise généralement 1,5× à 3× sur une fenêtre glissante de 5 minutes. Une règle sur un compartiment horaire avec un facteur 3× (seuil SRE courant pour un « incident évident ») est trop grossière pour les données de l'atelier, car les variations horaires du générateur synthétique sont d'environ 3 % ; en production, avec un véritable trafic d'utilisateurs humains, un facteur horaire de 3× est précisément le bon seuil pour indiquer « nous avons un vrai problème ».

Exercice 3 : GROUP BY sans limite — inventaire complet des points de terminaison

SELECT
    RequestPage,
    count()                                              AS total_requests,
    countIf(StatusCode >= 500)                           AS errors,
    round(errors / total_requests * 100, 2)              AS error_rate_pct,
    quantile(0.95)(toFloat64OrZero(LogAttributes['run_time'])) AS p95_latency
FROM otel_logs_v2
WHERE RequestType != ''
GROUP BY RequestPage
ORDER BY total_requests DESC;

Points pédagogiques :

• L'absence de LIMIT signifie que ClickHouse renvoie chaque valeur unique de RequestPage, qu'il y en ait 100, 10 000 ou 1 000 000. Une agrégation Terms d'Elasticsearch assortie de size ne renverrait que les N premières.
• Dans Elasticsearch, la solution de contournement par agrégation composite parcourt les résultats page par page à l'aide d'after_key. Chaque page exige un appel d'API distinct. ClickHouse réalise l'opération en un seul passage.
• quantile(0.95)(toFloat64OrZero(...)) calcule la latence p95 directement dans le GROUP BY, sans sous-agrégation distincte. Dans Elasticsearch, l'ajout de centiles à une agrégation terms double la charge utile de la réponse et la complexité de la requête.
• toFloat64OrZero() traite sans erreur les lignes où run_time est vide ou non numérique (il renvoie 0 au lieu de lever une erreur).

Exercice 4 : détection de séquences — parcours de requêtes aboutissant à des erreurs

SELECT
    RemoteAddr,
    count() AS occurrence_count
FROM otel_logs_v2
WHERE TimestampTime >= now() - INTERVAL 1 HOUR
GROUP BY RemoteAddr
HAVING sequenceMatch('(?1)(?t<=10).*(?2)(?t<=10).*(?3)')(
    TimestampTime,                                       -- sequenceMatch requires DateTime, not DateTime64
    ServiceName = 'api-gateway'   AND StatusCode = 200,
    ServiceName = 'order-service' AND StatusCode = 200,
    ServiceName = 'payment-service' AND StatusCode >= 500
)
ORDER BY occurrence_count DESC
LIMIT 20;

Points pédagogiques :

• (?1)(?t<=10).*(?2)(?t<=10).*(?3) est un motif semblable à une expression régulière appliqué aux séquences d'événements :
  • (?N) correspond à un événement qui satisfait la condition N ;
  • (?t<=10) limite le délai avant l'événement suivant à 10 secondes au maximum (borne incluse) ;
  • .* correspond à zéro ou plusieurs événements intermédiaires entre les deux conditions d'ancrage ;
  • le motif entier exige que les conditions 1, 2 et 3 surviennent dans l'ordre, chaque condition suivante devant correspondre dans les 10 secondes qui suivent la précédente.
• La fonction opère sur les lignes regroupées par RemoteAddr et traite chaque groupe comme une séquence ordonnée d'événements.
• sequenceMatch() est propre à ClickHouse. Ni le DSL Elasticsearch ni ES|QL ne peuvent exprimer des motifs d'événements ordonnés en plusieurs étapes entre des services.
• L'opérateur de délai (?t op N) accepte <, <=, ==, >=, >. Il diffère de windowFunnel(), qui reçoit la fenêtre sous forme d'argument numérique distinct et indique jusqu'où chaque ligne est parvenue dans l'entonnoir.

Résultat vérifié : sans la limite temporelle (?t<=10) (c'est-à-dire avec '(?1).*(?2).*(?3)'), cette requête renvoie environ 50 des 51 adresses IP distinctes de l'atelier : toutes finissent par emprunter le parcours api-gateway → order-service → payment-service-5xx au cours d'une heure. Le motif correspond donc presque partout et le résultat distingue peu les anomalies. L'ajout d'une limite de 10 secondes entre les événements successifs correspondants ramène le résultat à environ 9 adresses IP, ce qui se rapproche bien davantage du type de signal de « véritable anomalie » recherché en production.

Si votre requête ne renvoie aucune ligne : les noms de services du générateur de journaux peuvent différer. Exécutez SELECT DISTINCT ServiceName FROM otel_logs_v2 WHERE RequestType != '' pour trouver les noms réels des services d'accès web ; l'atelier utilise api-gateway, order-service, payment-service, inventory-service et web-frontend.

Exercice 5 : agrégation conditionnelle — plusieurs métriques de santé des services

SELECT
    ServiceName,
    count()                                                            AS total_events,
    countIf(StatusCode >= 200 AND StatusCode < 300)                   AS success_2xx,
    countIf(StatusCode >= 400 AND StatusCode < 500)                   AS client_errors_4xx,
    countIf(StatusCode >= 500)                                         AS server_errors_5xx,
    round(server_errors_5xx / total_events * 100, 2)                  AS error_rate_pct,
    quantileIf(0.50)(toFloat64OrZero(LogAttributes['run_time']), StatusCode < 500) AS p50_latency_ok,
    quantileIf(0.95)(toFloat64OrZero(LogAttributes['run_time']), StatusCode < 500) AS p95_latency_ok,
    quantileIf(0.50)(toFloat64OrZero(LogAttributes['run_time']), StatusCode >= 500) AS p50_latency_err,
    uniqIf(RemoteAddr, StatusCode >= 500)                             AS unique_affected_ips,
    minIf(Timestamp, StatusCode >= 500)                               AS first_error_at,
    maxIf(Timestamp, StatusCode >= 500)                               AS last_error_at
FROM otel_logs_v2
WHERE TimestampTime >= now() - INTERVAL 1 HOUR
  AND RequestType != ''
GROUP BY ServiceName
ORDER BY error_rate_pct DESC;

Points pédagogiques :

• Le combinateur -If peut être ajouté à toute fonction d'agrégation ClickHouse : countIf, avgIf, sumIf, quantileIf, uniqIf, minIf, maxIf, etc.
• Cette requête unique calcule 12 métriques soumises à 7 conditions différentes en une seule analyse de la table. Dans Elasticsearch, chaque métrique conditionnelle exige sa propre imbrication filter → metric, ce qui produit environ 150 lignes de JSON imbriqué pour une requête équivalente.
• quantileIf(0.95)(latency, StatusCode < 500) calcule p95 uniquement pour les requêtes réussies, ce qu'une seule agrégation ES ne peut pas exprimer sans bucket_script.
• uniqIf(RemoteAddr, StatusCode >= 500) compte les clients distincts affectés, ce qui aide à mesurer l'étendue de l'incident. Dans ES, il faudrait une sous-agrégation cardinality filtrée assortie de son approximation HyperLogLog ; uniqIf dans ClickHouse est également approximatif (HLL), mais sa syntaxe est bien plus simple.

Exercice 6 : analyse des causes racines avec des CTE

WITH
error_services AS (
    SELECT
        ServiceName,
        countIf(StatusCode >= 500) AS errors,
        count()                    AS total
    FROM otel_logs_v2
    WHERE TimestampTime >= now() - INTERVAL 1 HOUR
      AND RequestType != ''
    GROUP BY ServiceName
    HAVING errors > 10
    ORDER BY errors / total DESC
    LIMIT 3
),
top_errors AS (
    SELECT
        l.ServiceName,
        l.Body,
        count() AS occurrences
    FROM otel_logs_v2 l
    INNER JOIN error_services e ON l.ServiceName = e.ServiceName
    WHERE l.StatusCode >= 500
      AND l.TimestampTime >= now() - INTERVAL 1 HOUR
    GROUP BY l.ServiceName, l.Body
    ORDER BY occurrences DESC
    LIMIT 10
),
affected_traces AS (
    SELECT DISTINCT
        l.TraceId,
        l.ServiceName
    FROM otel_logs_v2 l
    INNER JOIN error_services e ON l.ServiceName = e.ServiceName
    WHERE l.StatusCode >= 500
      AND l.TraceId != ''
      AND l.TimestampTime >= now() - INTERVAL 1 HOUR
    LIMIT 50
)
SELECT
    e.ServiceName,
    e.errors,
    e.total,
    round(e.errors / e.total * 100, 2)  AS error_rate_pct,
    groupArray(10)(t.Body)              AS sample_error_messages,
    groupArray(5)(a.TraceId)            AS sample_trace_ids
FROM error_services e
LEFT JOIN top_errors t       ON e.ServiceName = t.ServiceName
LEFT JOIN affected_traces a  ON e.ServiceName = a.ServiceName
GROUP BY e.ServiceName, e.errors, e.total
ORDER BY error_rate_pct DESC;

Points pédagogiques :

• Cette requête unique remplace 3 appels distincts à l'API Elasticsearch et l'assemblage du JSON côté client : (1) taux d'erreur par histogramme de dates, (2) agrégation terms des messages d'erreur, (3) agrégation terms des identifiants de trace.
• groupArray(N)(expr) rassemble jusqu'à N valeurs d'expr dans un tableau par groupe. C'est l'équivalent ClickHouse de la collecte de valeurs d'exemple ; il n'existe pas d'équivalent propre dans ES (top_hits est ce qui s'en rapproche le plus, mais ne fonctionne qu'au sein de sous-agrégations terms, et non entre des JOIN).
• Les CTE de ClickHouse sont calculés une fois, puis réutilisés. error_services est référencé par trois CTE en aval et par le SELECT final ; ClickHouse ne le matérialise qu'une fois.
• La LEFT JOIN du SELECT final garantit qu'une ligne de service apparaît même si elle ne comporte aucun identifiant de trace correspondant (par exemple, si toutes les erreurs étaient dépourvues de TraceId). Une INNER JOIN ferait silencieusement disparaître ces services du résultat.
• Le filtre HAVING errors > 10 d'error_services élimine le bruit des services à très faible trafic qui connaissent une seule erreur. Ajustez le seuil à votre débit d'ingestion.

Pourquoi sample_trace_ids est-il vide dans cet atelier ? La requête renvoie des lignes pour web-frontend, payment-service, order-service (entre autres) : il s'agit des générateurs de journaux sur fichiers introduits dans la partie 1, qui ne propagent pas le contexte de trace. Chaque ligne d'otel_logs_v2 pour ces services comporte donc TraceId = ''. Le CTE affected_traces filtre sur TraceId != '' et ne contient finalement rien ; groupArray(5)(a.TraceId) renvoie donc ['','','','',''] (la LEFT JOIN conserve la ligne parente, mais aucune véritable valeur TraceId n'existe). Pour le vérifier, SELECT ServiceName, countIf(TraceId != '') AS rows_with_trace, count() AS total FROM otel_logs_v2 GROUP BY ServiceName ORDER BY total DESC montre que les services sur fichiers ont rows_with_trace = 0, tandis que les services OTel Demo (frontend-proxy, product-catalog, cart, etc.) présentent des valeurs non nulles. Ces derniers n'émettent toutefois pas actuellement de codes HTTP 5xx par la colonne StatusCode et n'apparaissent donc pas dans error_services. Dans un véritable environnement de production, l'application qui émet les erreurs 5xx et celle qui émet les traces seraient le même service instrumenté avec OTel ; sample_trace_ids servirait alors de point de départ cliquable pour explorer les traces en détail. L'atelier ne peut pas montrer ce parcours de bout en bout avec ses sources de données actuelles, mais le motif SQL est exactement celui à exécuter en production.