BIGQUERY

Les 5 Règles d'Or de l'Optimisation

Ces 5 règles peuvent réduire vos coûts BigQuery de 50 à 90%. Elles sont simples à appliquer et ont un impact immédiat.

Règle #1 : Eviter SELECT *

BigQuery est un moteur columnar. Il ne lit que les colonnes demandées. SELECT * force la lecture de toutes les colonnes.

-- MAUVAIS : Scanne toutes les colonnes (500 GB)
SELECT * FROM `project.dataset.events`;

-- BON : Scanne seulement 3 colonnes (15 GB)
SELECT event_id, user_id, event_type
FROM `project.dataset.events`;

-- Economie : 97% du coût !

Succes

Impact : Cette seule règle peut réduire les coûts de 80-95% sur les tables larges.

Règle #2 : Filtrer sur les colonnes de partition

Le partition pruning permet de ne scanner qu'une fraction de la table.

-- MAUVAIS : Scanne toute la table (365 partitions)
SELECT * FROM `project.dataset.events`
WHERE user_id = 12345;

-- BON : Scanne seulement 1 partition
SELECT * FROM `project.dataset.events`
WHERE _PARTITIONDATE = '2025-01-15'
  AND user_id = 12345;

-- ENCORE MIEUX : Avec une colonne partitionnée nommée
SELECT * FROM `project.dataset.events`
WHERE event_date = '2025-01-15'
  AND user_id = 12345;

Astuce

Astuce : Activez require_partition_filter = TRUE sur vos tables pour forcer le filtrage.

Erreur frequente

Les developpeurs oublient souvent que meme avec une table partitionnee, si le filtre sur la partition n'est pas dans le WHERE, toute la table est scannee. Activez require_partition_filter pour eviter ce piege.

Règle #3 : Utiliser les bonnes fonctions de date

Certaines fonctions empêchent le partition pruning.

-- MAUVAIS : La fonction empêche le pruning
SELECT * FROM `project.dataset.events`
WHERE EXTRACT(YEAR FROM event_date) = 2025;

-- MAUVAIS : DATE() sur une colonne empêche aussi
SELECT * FROM `project.dataset.events`
WHERE DATE(event_timestamp) = '2025-01-15';

-- BON : Comparaison directe
SELECT * FROM `project.dataset.events`
WHERE event_date >= '2025-01-01'
  AND event_date < '2026-01-01';

-- BON : Pour les timestamps
SELECT * FROM `project.dataset.events`
WHERE event_timestamp >= TIMESTAMP('2025-01-15')
  AND event_timestamp < TIMESTAMP('2025-01-16');

Règle #4 : Optimiser les JOINs

L'ordre des tables dans un JOIN impacte les performances et parfois les coûts.

-- BON : Grande table à gauche, petite à droite
SELECT h.*, s.category_name
FROM `project.dataset.huge_events` h        -- 1 TB
JOIN `project.dataset.small_categories` s   -- 1 MB
  ON h.category_id = s.id;

-- BON : Filtrer AVANT le JOIN
SELECT h.*, s.category_name
FROM (
  SELECT * FROM `project.dataset.huge_events`
  WHERE event_date = '2025-01-15'
) h
JOIN `project.dataset.small_categories` s
  ON h.category_id = s.id;

-- BON : Utiliser INNER JOIN plutôt que LEFT si possible
-- (élimine les lignes sans correspondance plus tôt)

Broadcast JOIN pour les petites tables

-- Force la diffusion de la petite table à tous les workers
SELECT /*+ BROADCAST(s) */ h.*, s.name
FROM huge_table h
JOIN small_table s ON h.id = s.id;

Règle #5 : Eviter les sous-requêtes corrélées

Les sous-requêtes corrélées s'exécutent pour chaque ligne. Très coûteux.

-- MAUVAIS : Sous-requête corrélée (exécutée N fois)
SELECT user_id,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.user_id) as order_count
FROM users u;

-- BON : JOIN avec agrégation (exécuté 1 fois)
SELECT u.user_id, COALESCE(o.order_count, 0) as order_count
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) as order_count
  FROM orders
  GROUP BY user_id
) o ON u.user_id = o.user_id;

-- BON : Window function
SELECT user_id, COUNT(*) OVER (PARTITION BY user_id) as order_count
FROM orders;

A retenir

Reduisez d'abord les donnees, puis joignez. Un JOIN entre deux tables de 1 TB coute cher. Un JOIN entre une table filtree de 10 GB et une table de reference de 1 MB coute presque rien.

Dry Run : Estimer avant d'exécuter

Toujours estimer le coût avant d'exécuter une requête coûteuse.

# En CLI
bq query --dry_run --use_legacy_sql=false \
  'SELECT * FROM `project.dataset.large_table`'

# Résultat :
# Query successfully validated. Assuming the tables are not modified,
# running this query will process 1234567890 bytes of data.

Dans la console BigQuery, le dry run est automatique : regardez l'estimation en bas à droite avant de cliquer sur "Run".

Decision concrete

Instaurez une regle d'equipe : toute requete estimee a plus de 100 GB doit etre validee par un pair avant execution. Cela evite les erreurs couteuses et forme les juniors.

Requête d'analyse des coûts

-- Top 20 requêtes les plus coûteuses (dernières 24h)
SELECT
  job_id,
  user_email,
  ROUND(total_bytes_billed / POW(1024, 4) * 5, 2) AS cost_usd,
  ROUND(total_bytes_billed / POW(1024, 3), 2) AS gb_billed,
  TIMESTAMP_DIFF(end_time, start_time, SECOND) AS duration_sec,
  cache_hit,
  query
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND error_result IS NULL
ORDER BY total_bytes_billed DESC
LIMIT 20;

-- Coûts par utilisateur (30 derniers jours)
SELECT
  user_email,
  COUNT(*) as query_count,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS total_tb,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 5, 2) AS total_cost_usd,
  ROUND(AVG(total_bytes_billed) / POW(1024, 3), 2) AS avg_gb_per_query
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
GROUP BY user_email
ORDER BY total_cost_usd DESC
LIMIT 20;

Jonathan Kini

J'aide les équipes data à réduire et maîtriser leurs coûts BigQuery et Snowflake, sans sacrifier la performance. 8 ans de terrain, de la startup aux environnements data à grande échelle.

LinkedIn À propos