Almacenamiento de tablas de BigQuery por fecha de última consulta

Por qué importa

Las organizaciones grandes pueden acumular miles de tablas que ya no se usan — sobrantes de pipelines antiguos, análisis puntuales o funcionalidades deprecadas. Estas tablas acumulan silenciosamente cargos de almacenamiento cada mes. Al identificar tablas que no han sido consultadas en semanas o meses, puede archivarlas, eliminarlas o convertirlas a niveles de almacenamiento más baratos.

Cómo funciona

La consulta une INFORMATION_SCHEMA.JOBS_BY_PROJECT (para encontrar la última hora de consulta por tabla) con TABLE_STORAGE (para datos de tamaño) y TABLE_OPTIONS (para determinar el modelo de facturación lógico vs físico). Calcula el costo de almacenamiento basado en bytes activos vs a largo plazo usando los precios publicados por GiB de Google.

Consulta 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
...

Explicación de la consulta

La consulta cruza dos fuentes de datos: metadatos de trabajos (para la última hora de consulta por tabla) y metadatos de almacenamiento de tabla (para tamaño y modelo de facturación). Las tablas que nunca fueron consultadas en la ventana de retención aparecen primero (NULLS FIRST). El costo de almacenamiento se calcula usando bytes activos vs a largo plazo con la tarificación apropiada para el modelo de facturación lógico o físico.

Puntos clave

• lightbulb

  Las tablas en la parte superior de la lista (nunca consultadas o consultadas hace mucho) son candidatas ideales para eliminación o archivado.

• lightbulb

  Las tablas grandes que no han sido consultadas pueden seguir siendo referenciadas por otros sistemas — verifique antes de eliminar.

• lightbulb

  Las tablas de más de 90 días automáticamente obtienen tarificación a largo plazo (50% más barata), pero eliminar tablas no usadas ahorra el 100%.

• lightbulb

  Las tablas con modelo de facturación físico cuestan 2x por GiB pero se benefician de la compresión — verifique si sus tablas son altamente compresibles.

Mejores prácticas

1. 1

   Configure una revisión mensual de tablas no consultadas en los últimos 30 días.

2. 2

   Implemente políticas de expiración de tablas (default_table_expiration_ms en datasets) para datos temporales.

3. 3

   Archive tablas importantes pero no usadas en Google Cloud Storage (más barato que el almacenamiento de BigQuery).

4. 4

   Cambie datasets con altos ratios de compresión al modelo de facturación físico para hasta un 70% de ahorro en almacenamiento.

Guías relacionadas