Analyse des coûts BigQuery par utilisateur

Pourquoi c'est important

Sans attribution des coûts au niveau utilisateur, les organisations peinent souvent à identifier qui est responsable des pics de facturation inattendus. Un seul utilisateur exécutant des requêtes ad hoc non optimisées peut facilement représenter 30 à 50 % des dépenses totales d'un projet. En révélant les coûts par utilisateur, vous pouvez engager des conversations ciblées, définir des budgets et prioriser les formations.

Comment ça fonctionne

La vue INFORMATION_SCHEMA.JOBS_BY_PROJECT de BigQuery contient les métadonnées de chaque job exécuté dans un projet, y compris l'e-mail de l'utilisateur, le total des octets facturés et l'utilisation des slots. Cette requête agrège total_bytes_billed par utilisateur et le convertit en coût à la demande estimé au tarif actuel de Google de 6,25 $/Tio analysé.

Requête SQL

-- Rank users by estimated on-demand cost

DECLARE lookback_days INT64 DEFAULT 30;

WITH jobs AS (
  SELECT
    user_email,
    job_id,
    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 user_email, bytes_billed FROM jobs WHERE rn = 1
)
SELECT
  user_email,
  COUNT(*) AS query_count,
  ROUND(SUM(bytes_billed) / POW(1024, 4), 4) AS tib_scanned,
  ROUND(SUM(bytes_billed) / POW(1024, 4) * 6.25, 2) AS estimated_cost_usd
FROM deduplicated
GROUP BY user_email
ORDER BY estimated_cost_usd DESC

Explication de la requête

La requête collecte chaque job de requête terminé depuis INFORMATION_SCHEMA.JOBS_BY_PROJECT dans la fenêtre de rétention. Elle déduplique les jobs avec ROW_NUMBER() partitionné par job_id (BigQuery peut journaliser le même job plusieurs fois). Ensuite, elle regroupe par user_email, additionne total_bytes_billed et convertit les octets en tébioctets (division par 1024^4) avant de multiplier par le prix à la demande de 6,25 $/Tio.

Points clés

• lightbulb

  Un petit nombre d'utilisateurs représente souvent la majorité des coûts — vérifiez si les 3 premiers utilisateurs représentent plus de 50 % des dépenses totales.

• lightbulb

  Les comptes de service (dbt, Looker, Airflow) figurent fréquemment en tête de liste car ils exécutent des requêtes planifiées à grande échelle.

• lightbulb

  Les utilisateurs avec des coûts élevés mais peu de requêtes exécutent probablement des requêtes non optimisées qui analysent de grandes quantités de données.

• lightbulb

  Comparez les coûts par utilisateur semaine après semaine pour détecter les changements soudains de comportement.

Meilleures pratiques

1. 1

   Configurez des contrôles de coûts personnalisés BigQuery pour limiter les octets facturés par utilisateur par jour.

2. 2

   Exigez des clauses LIMIT ou des filtres de partition pour les requêtes interactives/ad hoc.

3. 3

   Utilisez des labels sur les jobs de requêtes planifiées pour séparer les coûts automatisés des coûts humains.

4. 4

   Partagez ce rapport avec les responsables techniques pour qu'ils puissent accompagner les membres de l'équipe sur les meilleures pratiques de requêtes.

Guides associés