Stockage des tables BigQuery par date de dernière requête

Pourquoi c'est important

Les grandes organisations peuvent accumuler des milliers de tables qui ne sont plus utilisées — restes d'anciens pipelines, analyses ponctuelles ou fonctionnalités dépréciées. Ces tables accumulent silencieusement des frais de stockage chaque mois. En identifiant les tables qui n'ont pas été consultées depuis des semaines ou des mois, vous pouvez les archiver, les supprimer ou les convertir en niveaux de stockage moins coûteux.

Comment ça fonctionne

La requête joint INFORMATION_SCHEMA.JOBS_BY_PROJECT (pour trouver la dernière heure de requête par table) avec TABLE_STORAGE (pour les données de taille) et TABLE_OPTIONS (pour déterminer le modèle de facturation logique vs physique). Elle calcule le coût de stockage basé sur les octets actifs vs long terme en utilisant les tarifs publiés par Go de Google.

Requête SQL

-- Find stale tables sorted by last query date with storage cost

DECLARE lookback_days INT64 DEFAULT 30;

-- US multi-region pricing ($/GiB/month)
DECLARE logical_active_price NUMERIC DEFAULT 0.02;
DECLARE logical_longterm_price NUMERIC DEFAULT 0.01;
DECLARE physical_active_price NUMERIC DEFAULT 0.04;
DECLARE physical_longterm_price NUMERIC DEFAULT 0.02;

WITH last_access AS (
  SELECT
    ref.project_id, ref.dataset_id, ref.table_id,
    MAX(j.creation_time) AS last_query_at
  FROM `your-project`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT j,
    UNNEST(j.referenced_tables) ref
  WHERE j.state = 'DONE' AND j.job_type = 'QUERY'
    AND j.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL lookback_days DAY)
  GROUP BY 1, 2, 3
),
storage_info AS (
  SELECT
    s.project_id,
    s.table_schema AS dataset_id,
    s.table_name AS table_id,
    IFNULL(opt.option_value, 'LOGICAL') AS model,
    IF(opt.option_value = 'PHYSICAL', s.total_physical_bytes, s.total_logical_bytes) AS size_bytes,
    IF(opt.option_value = 'PHYSICAL', s.active_physical_bytes, s.active_logical_bytes) AS active_bytes,
    IF(opt.option_value = 'PHYSICAL', s.long_term_physical_bytes, s.long_term_logical_bytes) AS lt_bytes
  FROM `your-project`.`region-us`.INFORMATION_SCHEMA.TABLE_STORAGE s
...

Explication de la requête

La requête croise deux sources de données : les métadonnées de jobs (pour la dernière heure de requête par table) et les métadonnées de stockage de table (pour la taille et le modèle de facturation). Les tables qui n'ont jamais été consultées dans la fenêtre de rétention apparaissent en premier (NULLS FIRST). Le coût de stockage est calculé en utilisant les octets actifs vs long terme avec la tarification appropriée pour le modèle de facturation logique ou physique.

Points clés

• lightbulb

  Les tables en haut de la liste (jamais consultées ou consultées il y a longtemps) sont des candidates idéales pour la suppression ou l'archivage.

• lightbulb

  Les grandes tables qui n'ont pas été consultées peuvent encore être référencées par d'autres systèmes — vérifiez avant de supprimer.

• lightbulb

  Les tables de plus de 90 jours obtiennent automatiquement la tarification long terme (50 % moins cher), mais la suppression des tables inutilisées économise 100 %.

• lightbulb

  Les tables avec le modèle de facturation physique coûtent 2x par Gio mais bénéficient de la compression — vérifiez si vos tables sont hautement compressibles.

Meilleures pratiques

1. 1

   Configurez une revue mensuelle des tables non consultées dans les 30 derniers jours.

2. 2

   Implémentez des politiques d'expiration des tables (default_table_expiration_ms sur les datasets) pour les données temporaires.

3. 3

   Archivez les tables importantes mais inutilisées vers Google Cloud Storage (moins cher que le stockage BigQuery).

4. 4

   Basculez les datasets avec des ratios de compression élevés vers le modèle de facturation physique pour jusqu'à 70 % d'économies de stockage.

Guides associés