Top requêtes BigQuery par fréquence

Pourquoi c'est important

Une requête coûtant 0,10 $ par exécution mais s'exécutant 10 000 fois par mois coûte 1 000 $. L'analyse de fréquence révèle souvent des requêtes à faible coût qui s'accumulent en dépenses significatives en raison de leur simple répétition. Ce sont des candidats idéaux pour la mise en cache, la matérialisation planifiée ou l'accélération BI Engine.

Comment ça fonctionne

La requête regroupe chaque texte de requête unique, puis compte les occurrences et additionne total_bytes_billed par groupe. Le résultat montre chaque requête unique avec son nombre d'exécutions et son coût à la demande cumulé.

Requête SQL

-- Find the most frequently repeated queries and their cumulative cost

DECLARE lookback_days INT64 DEFAULT 14;

WITH jobs AS (
  SELECT
    query,
    COALESCE(total_bytes_billed, 0) AS bytes_billed,
    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
),
deduplicated AS (
  SELECT query, bytes_billed FROM jobs WHERE rn = 1
),
grouped AS (
  SELECT
    query,
    COUNT(*) AS exec_count,
    SUM(bytes_billed) AS total_bytes
  FROM deduplicated
  GROUP BY query
)
SELECT
  query,
  exec_count,
  ROUND(total_bytes / POW(1024, 3), 2) AS total_gib_scanned,
  ROUND(total_bytes / POW(1024, 4) * 6.25, 2) AS cumulative_cost_usd
FROM grouped
...

Explication de la requête

Les jobs sont d'abord dédupliqués par job_id, puis regroupés par texte de requête. Le comptage donne la fréquence d'exécution, et la somme des octets facturés donne le coût cumulé. Cette approche permet de faire remonter efficacement les requêtes exécutées de manière répétée à grande échelle.

Points clés

• lightbulb

  Les requêtes s'exécutant >100 fois par jour sont presque certainement automatisées — vérifiez si elles peuvent utiliser des résultats mis en cache.

• lightbulb

  BigQuery met en cache les résultats pendant 24 heures par défaut, mais uniquement pour des requêtes identiques avec des résultats déterministes.

• lightbulb

  Les requêtes avec CURRENT_TIMESTAMP() ou NOW() contournent le cache — envisagez d'utiliser la troncature de date à la place.

• lightbulb

  Les requêtes à haute fréquence analysant les mêmes tables peuvent bénéficier de vues matérialisées ou de BI Engine.

Meilleures pratiques

1. 1

   Activez la mise en cache des résultats BigQuery et évitez les modèles qui la contournent (CURRENT_TIMESTAMP, RAND, etc.).

2. 2

   Créez des vues matérialisées pour les 5 requêtes analytiques les plus fréquemment exécutées.

3. 3

   Consolidez les requêtes dupliquées de différents tableaux de bord ou services en une seule requête planifiée.

4. 4

   Utilisez BI Engine pour les requêtes en moins d'une seconde alimentant des tableaux de bord interactifs.

Guides associés