Top requêtes BigQuery par coût

Pourquoi c'est important

Dans la plupart des projets BigQuery, le principe de Pareto s'applique : 20 % des requêtes représentent 80 % des coûts. Trouver et optimiser ces requêtes principales offre le meilleur ROI. Sans cette visibilité, les équipes gaspillent du temps à optimiser des requêtes qui ont peu d'impact.

Comment ça fonctionne

La requête lit les métadonnées des jobs depuis INFORMATION_SCHEMA.JOBS_BY_PROJECT, déduplique par job_id et trie par coût à la demande décroissant. Le coût est estimé à partir de total_bytes_billed à 6,25 $/Tio. Elle calcule également l'utilisation approximative des slots pour chaque requête.

Requête SQL

-- Most expensive queries ranked by on-demand cost

DECLARE lookback_days INT64 DEFAULT 14;

WITH jobs AS (
  SELECT
    user_email,
    query,
    job_id,
    project_id,
    start_time,
    end_time,
    COALESCE(total_bytes_billed, 0) AS bytes_billed,
    total_slot_ms,
    ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY end_time DESC) AS rn
  FROM `your-project`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL lookback_days DAY)
    AND job_type = 'QUERY' AND state = 'DONE' AND total_slot_ms IS NOT NULL
)
SELECT
  user_email,
  query,
  project_id,
  start_time,
  end_time,
  ROUND(bytes_billed / POW(1024, 4), 4) AS tib_scanned,
  ROUND(bytes_billed / POW(1024, 4) * 6.25, 2) AS on_demand_cost_usd,
  ROUND(SAFE_DIVIDE(total_slot_ms,
    TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)), 0) AS avg_slots
FROM jobs
...

Explication de la requête

INFORMATION_SCHEMA.JOBS_BY_PROJECT est filtré sur les jobs de requête terminés dans la fenêtre de rétention. ROW_NUMBER() déduplique sur job_id. Le coût à la demande est calculé comme total_bytes_billed / 1024^4 * 6,25 $. Le nombre approximatif de slots montre l'intensité computationnelle : total_slot_ms / execution_time_ms.

Points clés

• lightbulb

  Les 10 requêtes les plus coûteuses représentent typiquement 50-80 % des coûts totaux du projet.

• lightbulb

  Les requêtes apparaissant plusieurs fois dans les premiers résultats sont candidates à la matérialisation ou à la mise en cache.

• lightbulb

  Les requêtes à coût élevé avec peu de slots analysent trop de données — ajoutez des filtres de partition ou sélectionnez moins de colonnes.

• lightbulb

  Les requêtes à coût élevé avec beaucoup de slots sont limitées par le calcul — simplifiez les JOINs, réduisez les opérations DISTINCT ou utilisez des fonctions approximatives.

Meilleures pratiques

1. 1

   Examinez les 10 premières requêtes hebdomadairement et créez des tickets d'optimisation pour chacune.

2. 2

   Ajoutez des filtres de partition pour éliminer les analyses complètes de tables sur les tables partitionnées par temps.

3. 3

   Remplacez SELECT * par des listes de colonnes explicites pour réduire les octets analysés.

4. 4

   Utilisez l'explication du plan de requête BigQuery pour identifier les étapes qui traitent le plus de données.

Guides associés