Analyse des coûts BigQuery par requête

Pourquoi c'est important

Les vues de coûts agrégées (par utilisateur, par label) vous indiquent où va l'argent à haut niveau, mais pour résoudre réellement le problème, vous devez voir les requêtes individuelles. Cette ventilation par requête vous permet d'identifier les instructions SQL exactes qui consomment le plus de ressources et de prioriser les optimisations avec le meilleur ROI.

Comment ça fonctionne

La requête lit depuis INFORMATION_SCHEMA.JOBS_BY_PROJECT et calcule plusieurs métriques de coût pour chaque exécution de requête : nombre approximatif de slots, coût à la demande (basé sur les octets facturés), heures-slots facturées (pour comparaison Editions) et nombre d'exécutions. Elle applique également les règles de facturation minimum de BigQuery — 10 Mio minimum par requête et 1 minute minimum de durée pour Editions.

Requête SQL

-- Per-query cost breakdown with slot and duration metrics

DECLARE lookback_days INT64 DEFAULT 7;

WITH base_jobs AS (
  SELECT
    project_id,
    query,
    start_time,
    end_time,
    COALESCE(total_bytes_billed, 0) AS bytes_billed,
    total_slot_ms,
    TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS duration_ms,
    ROUND(SAFE_DIVIDE(total_slot_ms,
      TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)), 2) AS avg_slots
  FROM `your-project`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL lookback_days DAY)
    AND state = 'DONE' AND total_slot_ms IS NOT NULL
),
with_billing AS (
  SELECT *,
    CEIL(avg_slots / 100) * 100 AS billed_slots,
    GREATEST(duration_ms, 60000) / 3.6e6 AS billed_hours,
    GREATEST(bytes_billed, 10 * POW(1024, 2)) AS billed_bytes
  FROM base_jobs
),
freq AS (
  SELECT query, COUNT(*) AS exec_count FROM base_jobs GROUP BY query
)
SELECT
...

Explication de la requête

La requête calcule le nombre approximatif de slots en divisant total_slot_ms par le temps d'exécution. Elle applique les règles de facturation minimum de BigQuery : 10 Mio minimum d'octets et 1 minute minimum de durée. Le nombre de slots est arrondi au 100 supérieur (correspondant aux incréments de l'autoscaler). Le coût à la demande est calculé à 6,25 $/Tio, et les heures-slots sont calculées pour la comparaison des coûts Editions.

Points clés

• lightbulb

  Les requêtes avec un coût à la demande élevé mais peu de slots analysent trop de données — elles ont besoin de filtres de partition ou d'élagage de colonnes.

• lightbulb

  Les requêtes avec beaucoup d'heures-slots mais peu d'octets facturés sont coûteuses en calcul — recherchez des JOINs complexes, DISTINCT ou des fonctions de fenêtrage.

• lightbulb

  Les requêtes avec un nombre d'exécutions élevé sont candidates pour la mise en cache des résultats ou les vues matérialisées.

• lightbulb

  Comparez on_demand_cost_usd vs slot_hours pour déterminer quel modèle de facturation serait moins cher par requête.

Meilleures pratiques

1. 1

   Concentrez l'effort d'optimisation sur les 10 premières requêtes par coût — elles représentent typiquement 60-80 % des dépenses totales.

2. 2

   Pour les requêtes fréquemment exécutées, envisagez de matérialiser les résultats ou d'utiliser BI Engine.

3. 3

   Utilisez EXPLAIN pour comprendre le plan d'exécution de la requête avant et après optimisation.

4. 4

   Configurez des alertes automatiques pour les requêtes dépassant un seuil de coût.

Guides associés