Surveillance des échecs de jobs BigQuery avec INFORMATION_SCHEMA

BigQuery stocke les métadonnées de chaque job qu’il exécute dans INFORMATION_SCHEMA.JOBS. Cela inclut non seulement les exécutions de requêtes réussies mais aussi les échecs — avec les codes d’erreur, les messages d’erreur, et l’utilisateur ou le compte de service qui a déclenché le job. Deux patterns de surveillance sont particulièrement utiles : vérifier les jobs échoués dans les 24 dernières heures, et détecter les anomalies de coûts en comparant l’usage du jour avec une moyenne glissante.

Ces requêtes fonctionnent bien comme vérifications planifiées au sein d’un agent IA comme OpenClaw, où l’agent exécute le SQL, interprète les résultats, et formate un résumé lisible pour la livraison sur Slack.

Interroger les jobs échoués

La requête principale pour la surveillance des jobs échoués :

SELECT
  job_id,
  job_type,
  user_email,
  error_result.reason AS error_reason,
  error_result.message AS error_message,
  total_bytes_processed,
  creation_time
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE state = 'DONE'
  AND error_result IS NOT NULL
  AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
ORDER BY creation_time DESC

Champs clés :

• error_result.reason — un code court comme accessDenied, notFound, quotaExceeded, resourcesExceeded. Utile pour catégoriser les échecs sans lire le message d’erreur complet.
• error_result.message — le message d’erreur complet lisible, que vous voudrez passer à un agent pour interprétation.
• user_email — l’identité qui a exécuté le job. Pour les exécutions dbt planifiées, il s’agira d’un compte de service. Pour les échecs ad hoc, c’est un email humain. Savoir qui a déclenché le job échoué est important pour l’attribution de responsabilité lors d’un incident.
• total_bytes_processed — même les jobs échoués peuvent avoir scanné des octets avant d’échouer. Un job qui échoue après avoir scanné 10 To mérite une investigation différente de celui qui échoue avant de traiter quoi que ce soit.

Le préfixe region-eu est l’emplacement multi-région. Remplacez par region-us ou une région spécifique comme europe-west1 selon l’emplacement de vos datasets. La région INFORMATION_SCHEMA doit correspondre à l’endroit où les jobs se sont exécutés.

Filtrer les échecs pertinents

Sans filtrage, la requête sur les jobs échoués inclut tout : les requêtes ad hoc des analystes, les jobs BigQuery ML échoués, les jobs de chargement avec des erreurs de schéma. Pour une alerte de surveillance, vous voulez généralement une vue plus ciblée.

Par projet — lors de la gestion de plusieurs projets clients, délimitez chaque job de surveillance à un projet :

WHERE state = 'DONE'
  AND error_result IS NOT NULL
  AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND project_id = 'client-project-prod'

Par type de job — filtrez sur les jobs de requête uniquement, en excluant les jobs de chargement et d’export qui ont des modes d’échec différents :

  AND job_type = 'QUERY'

Par compte de service — si vous ne vous intéressez qu’aux échecs de votre pipeline dbt :

  AND user_email LIKE '%@your-project.iam.gserviceaccount.com'

Combinaison de filtres — pour une alerte de surveillance dbt ciblée :

SELECT
  job_id,
  user_email,
  error_result.reason AS error_reason,
  error_result.message AS error_message,
  creation_time
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE state = 'DONE'
  AND error_result IS NOT NULL
  AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND project_id = 'client-project-prod'
  AND job_type = 'QUERY'
ORDER BY creation_time DESC

Cela restreint les résultats aux échecs de requêtes dans un projet au cours des 24 dernières heures — le périmètre approprié pour un rapport de surveillance matinal.

Détection des anomalies de coûts

La surveillance des échecs de jobs capture les erreurs. La détection des anomalies de coûts capture quelque chose de plus subtil : les jobs réussis qui ont traité beaucoup plus de données qu’habituellement. Une requête qui a scanné 10× ses données habituelles est peut-être dépourvue d’un filtre de partition, ou quelqu’un a peut-être accidentellement exécuté un scan complet d’un grand dataset.

Le pattern est une comparaison glissante : qu’avons-nous traité aujourd’hui par rapport à la moyenne sur 7 jours ?

WITH daily_stats AS (
  SELECT
    DATE(creation_time) AS job_date,
    SUM(total_slot_ms) / 1000 / 3600 AS slot_hours,
    SUM(total_bytes_processed) / POW(1024, 4) AS total_tb_processed,
    COUNT(*) AS job_count
  FROM `region-eu`.INFORMATION_SCHEMA.JOBS
  WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY)
    AND state = 'DONE'
    AND job_type = 'QUERY'
  GROUP BY 1
),
averages AS (
  SELECT
    AVG(slot_hours) AS avg_slot_hours,
    AVG(total_tb_processed) AS avg_tb_processed
  FROM daily_stats
  WHERE job_date < CURRENT_DATE()
)

SELECT
  d.job_date,
  d.slot_hours,
  a.avg_slot_hours,
  ROUND(d.slot_hours / NULLIF(a.avg_slot_hours, 0), 2) AS ratio_to_avg,
  d.total_tb_processed,
  d.job_count
FROM daily_stats d
CROSS JOIN averages a
WHERE d.job_date = CURRENT_DATE()

La colonne ratio_to_avg est la métrique actionnable. Un ratio de 1,0 signifie que la journée ressemble exactement à la moyenne récente. Un ratio de 2,0 signifie que l’utilisation de slots d’aujourd’hui est le double de la moyenne hebdomadaire. Signalez tout ce qui dépasse 2,0 pour investigation.

La protection NULLIF(a.avg_slot_hours, 0) empêche la division par zéro sur les jours sans données historiques — les nouveaux projets pour lesquels il n’y a pas encore une semaine complète d’historique.

Interpréter l’anomalie

Un ratio supérieur à 2,0 a plusieurs causes possibles, chacune avec une réponse différente :

Une nouvelle requête lourde est apparue. Quelqu’un a ajouté un modèle ou rapport qui n’existait pas auparavant. Vérifiez total_bytes_processed par utilisateur ou compte de service pour identifier qui l’a exécutée.

Une requête planifiée s’est exécutée plus de fois que prévu. Un modèle dbt avec le mauvais planning, ou un cron job qui s’est déclenché plusieurs fois à cause d’une boucle de retry. Vérifiez job_count — s’il est significativement plus élevé que la moyenne, vous avez plus de jobs, pas des jobs plus coûteux.

Un scan complet de table sur un grand dataset. Le nombre de jobs est normal, mais les octets scannés sont élevés. Généralement, cela signifie un filtre de partition manquant ou une requête qui a contourné le clustering. Trouvez la requête individuelle coûteuse avec le pattern des requêtes les plus coûteuses.

Comparaison week-end vs jour de semaine. La moyenne sur 7 jours inclut à la fois les jours de semaine et les week-ends. Si votre charge est principalement durant les heures de bureau en semaine, un pic de requêtes le lundi peut simplement être la variation hebdomadaire normale. Ajustez la fenêtre de comparaison au même jour de la semaine si ce pattern génère de fausses alertes.

Un agent OpenClaw configuré pour signaler les ratios supérieurs à 2,0 et décrire la cause probable produit un résultat en langage naturel : “L’utilisation de slots d’aujourd’hui est 3,1× la moyenne sur 7 jours. Le nombre de jobs est normal (142 vs 139 en moyenne), mais le total des octets traités est inhabituellement élevé à 8,4 To vs 2,1 To en moyenne — suggérant que quelques requêtes ont scanné beaucoup plus de données que d’habitude.” C’est plus actionnable qu’une simple alerte sur un seuil brut.

Planification avec OpenClaw

Pour une surveillance quotidienne avec le planificateur cron d’OpenClaw :

openclaw cron add --name "bq-failure-check" \
  --cron "0 8 * * *" \
  --tz "Europe/Paris" \
  --session isolated \
  --message "Query BigQuery INFORMATION_SCHEMA.JOBS for failed jobs in the past 24 hours using the query in the bq-monitor skill. Summarize failures with job type, error message, and which user or service account triggered them. If no failures, say so briefly." \
  --announce \
  --channel slack \
  --to "channel:C1234567890"

Placez le SQL dans un fichier de skill pour que le message cron reste court. L’agent lit le skill, trouve la requête, l’exécute via le CLI bq ou un client BigQuery, et formate un résumé.

Séparer la surveillance des échecs et la surveillance des anomalies de coûts en deux jobs cron — décalés de quelques minutes — permet de garder chaque rapport ciblé. Un rapport combiné qui mélange “ces jobs ont échoué” avec “les coûts semblent élevés aujourd’hui” est plus difficile à analyser que deux messages distincts avec des objectifs clairs.

Pour les équipes en tarification à la demande, la détection des anomalies de coûts capture des problèmes que la surveillance des échecs ne détecte pas. Les échecs de jobs produisent des symptômes visibles (tableaux de bord obsolètes, modèles cassés) qui sont rapidement remarqués. Les pics de coûts liés à des requêtes inefficaces sont invisibles jusqu’à la clôture du cycle de facturation.

Relation avec les autres surveillances INFORMATION_SCHEMA

La requête sur les jobs échoués et la détection des anomalies de coûts sont complémentaires aux patterns d’attribution des coûts dans Attribution des coûts BigQuery avec INFORMATION_SCHEMA :

• L’attribution des coûts répond à “quelles requêtes sont les plus coûteuses sur 30 jours ?” — pour les décisions d’optimisation
• La surveillance des échecs répond à “qu’est-ce qui a échoué dans les 24 dernières heures ?” — pour la réponse opérationnelle
• La détection des anomalies de coûts répond à “la journée est-elle inhabituelle par rapport à l’historique récent ?” — pour la détection précoce

Exécuter les trois régulièrement — hebdomadairement pour l’attribution, quotidiennement pour les échecs et les anomalies — vous donne à la fois une visibilité opérationnelle et une visibilité d’optimisation à plus long terme. Les requêtes INFORMATION_SCHEMA ne coûtent pratiquement rien à exécuter et peuvent être entièrement automatisées via une surveillance planifiée.