ServicesÀ proposNotesContact Me contacter →
EN FR
Note

Patterns de pruning de partitions BigQuery

Comment combiner partitionnement et clustering dans BigQuery pour une réduction maximale des scans, y compris les anti-patterns qui désactivent silencieusement le pruning.

Planté
bigquerycost optimizationdata engineering

Le partitionnement divise une table en segments selon les valeurs d’une seule colonne, par date, horodatage ou plage d’entiers. Le clustering trie les données à l’intérieur de ces partitions sur jusqu’à quatre colonnes. Combinés, ces deux mécanismes permettent à BigQuery de sauter des blocs de données non pertinents, réduisant les scans complets de table à des lectures ciblées.

Sur une table bien conçue avec des filtres appropriés, une requête peut scanner 10 à 100 Go sur une table de 10 To — 100 à 1 000 fois moins de données facturées avec la tarification à la demande.

La combinaison partitionnement + clustering

Le pattern standard pour les tables d’événements et de faits est de partitionner par date et de clustériser sur les colonnes filtrées le plus fréquemment :

CREATE TABLE `project.dataset.events`
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_name
AS
SELECT
  event_timestamp,
  user_id,
  event_name,
  event_params
FROM raw.events;

Une requête qui filtre à la fois sur la colonne de partition et une colonne clusterisée déclenche les deux types de pruning :

SELECT user_id, event_name, COUNT(*)
FROM `project.dataset.events`
WHERE DATE(event_timestamp) = '2025-01-01'  -- Partition pruning
  AND user_id = 'abc123'                     -- Cluster pruning
GROUP BY 1, 2;

Le partitionnement seul offre une réduction des coûts de 10 à 100× sur les grandes tables. Ajouter le clustering apporte une réduction supplémentaire de 2 à 10× à l’intérieur des partitions.

Imposer les filtres de partition

L’option require_partition_filter empêche les scans complets accidentels de table en faisant échouer les requêtes sans filtre de partition :

ALTER TABLE `project.dataset.events`
SET OPTIONS (require_partition_filter = true);

Cela est utile sur les datasets partagés où des requêtes SELECT * non filtrées peuvent se produire. Un scan complet d’une table de 10 To coûte 62,50 $ avec la tarification à la demande ; require_partition_filter empêche l’exécution de cette requête.

Dans un contexte dbt, vos modèles de base devraient toujours inclure des filtres de partition. Si vous construisez sur des exports GA4 partitionnés par date, le filtre _TABLE_SUFFIX remplit le même objectif.

Anti-patterns qui désactivent le pruning

Ces patterns désactivent silencieusement le pruning de partition. BigQuery ne prévient pas ; la requête s’exécute, retourne des résultats corrects, et coûte 10 à 100 fois plus que prévu.

Fonctions sur les colonnes de partition

Si votre table est partitionnée par TIMESTAMP, un filtre comme WHERE DATE(event_timestamp) = '2025-01-01' force un scan complet car BigQuery ne peut pas évaluer la fonction au moment de la planification. L’optimiseur doit faire correspondre l’expression de filtre au schéma de partition, et encapsuler la colonne dans une fonction rompt cette correspondance.

La correction dépend de votre schéma :

-- Option A: Partition on a DATE column directly
CREATE TABLE events
PARTITION BY event_date  -- Store a separate DATE column
...


-- Option B: Use a range filter on the timestamp
WHERE event_timestamp >= '2025-01-01'
  AND event_timestamp < '2025-01-02'

L’option A est plus propre pour les tables que vous contrôlez. L’option B fonctionne quand vous interrogez des tables que vous n’avez pas conçues.

Sous-requêtes dans les filtres de partition

-- This does NOT prune
WHERE event_date = (SELECT MAX(event_date) FROM other_table)

BigQuery a besoin de valeurs littérales au moment de la planification pour effectuer le pruning de partition. Une sous-requête produit une valeur au moment de l’exécution, ce qui est trop tard. L’optimiseur ne peut pas savoir quelle partition sauter avant de connaître le résultat de la sous-requête.

Solutions :

  1. Matérialiser la date dans une variable d’abord (en mode scripting)
  2. Utiliser une macro Jinja dans dbt pour injecter la date en tant que valeur littérale
  3. Accepter le coût si la table est suffisamment petite pour que ça n’ait pas d’importance

Sur-partitionnement

Si vos partitions contiennent moins de 1 Go chacune, la surcharge de métadonnées dépasse les bénéfices du pruning. Chaque partition a une surcharge fixe pour la gestion des métadonnées, et avec de nombreuses petites partitions, BigQuery passe plus de temps à gérer les métadonnées de partition qu’il n’en économise grâce au pruning.

Le seuil pratique : si vos partitions quotidiennes font moins de 1 Go, envisagez d’utiliser une granularité de partition plus large (mensuelle plutôt que quotidienne) avec clustering, ou le clustering seul pour les tables plus petites. Le clustering n’a pas de seuil de taille minimum comme le partitionnement.

L’ordre des colonnes de clustering est important

Le clustering trie les données hiérarchiquement sur jusqu’à quatre colonnes. La première colonne a l’effet de pruning le plus fort. Placez votre colonne filtrée le plus fréquemment en premier :

CLUSTER BY user_id, event_name, campaign

Si la plupart de vos requêtes filtrent sur user_id, cet ordre est correct. Si la plupart des requêtes filtrent sur event_name mais seulement parfois sur user_id, inversez-les. Vérifiez INFORMATION_SCHEMA.JOBS pour voir quelles colonnes apparaissent le plus souvent dans les clauses WHERE.

Les bénéfices du clustering n’apparaissent pas dans les estimations de coût à blanc (dry-run). Un dry-run peut estimer 50 Go, mais la requête réelle scanne 5 Go grâce au pruning par clustering. Vérifiez les octets réellement facturés dans INFORMATION_SCHEMA.JOBS pour évaluer l’efficacité du clustering.

Interaction avec les modèles incrémentiels dbt

Le pruning de partition devient encore plus critique pour les modèles incrémentiels dbt. La stratégie insert_overwrite avec des partitions statiques est conçue pour exploiter le pruning de partition : elle remplace uniquement les partitions spécifiées, et les coûts s’adaptent à la taille de la partition plutôt qu’à la taille totale de la table.

Pour les modèles avec la stratégie merge, les incremental_predicates ajoutent des filtres de partition au scan de destination :

{{ config(
    incremental_predicates=[
      "DBT_INTERNAL_DEST.event_date >= date_sub(current_date(), interval 14 day)"
    ]
) }}

Sans ce prédicat, un merge sur une table partitionnée de 10 To scanne tout de même les 10 To côté destination — même si le partitionnement existe. Le prédicat indique à BigQuery quelles partitions vérifier lors de la correspondance du merge, permettant une réduction de scan de 88 %+ côté destination.

Mesurer l’efficacité du pruning

La façon la plus rapide de vérifier si vos requêtes effectuent réellement du pruning :

SELECT
  job_id,
  query,
  total_bytes_billed,
  total_bytes_processed,
  ROUND(total_bytes_billed / POW(1024, 3), 2) AS gb_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND referenced_tables IS NOT NULL
ORDER BY total_bytes_billed DESC
LIMIT 20;

Comparez total_bytes_billed avec la taille totale de la table. Si vous scannez plus de 5 à 10 % d’une grande table partitionnée et clusterisée avec des filtres appropriés, quelque chose ne va pas. Vérifiez les anti-patterns ci-dessus.

Dans l’architecture BigQuery, le partitionnement et le clustering sont les principaux mécanismes pour réduire la quantité de données que Dremel lit depuis Colossus.