Interroger des tables partitionnées

Ce document décrit certaines considérations spécifiques concernant l'interrogation des tables partitionnées dans BigQuery.

Pour obtenir des informations générales sur l'exécution de requêtes dans BigQuery, consultez la page Exécuter des requêtes interactives et par lot.

Présentation

Si une requête utilise un filtre éligible sur la valeur de la colonne de partitionnement, BigQuery peut analyser les partitions correspondant au filtre et ignorer les partitions restantes. Ce processus est appelé élimination des partitions.

Il s'agit du mécanisme par lequel BigQuery élimine les partitions inutiles de l'analyse de l'entrée. Les partitions éliminées ne sont pas incluses lors du calcul des octets analysés par la requête. En général, l'élimination des partitions permet de réduire le coût des requêtes.

Les comportements d'élimination varient selon les différents types de partitionnement. Vous pouvez donc constater une différence dans le nombre d'octets traités lorsque vous interrogez des tables partitionnées différemment, mais identiques. Pour estimer le nombre d'octets qu'une requête traitera, effectuez une simulation.

Interroger une table partitionnée par colonne d'unité de temps

Pour restreindre les partitions lorsque vous interrogez une table partitionnée par colonne d'unité de temps, incluez un filtre sur la colonne de partitionnement.

Dans l'exemple suivant, supposons que dataset.table soit partitionné sur la colonne transaction_date. L'exemple de requête élimine des dates antérieures à 2016-01-01.

SELECT * FROM dataset.table
WHERE transaction_date >= '2016-01-01'

Interroger une table partitionnée par date d'ingestion

Les tables partitionnées par date d'ingestion contiennent une pseudo-colonne nommée _PARTITIONTIME, qui est la colonne de partitionnement. La valeur de la colonne est le temps d'ingestion UTC pour chaque ligne, tronqué à la limite de la partition (par exemple, horaire ou quotidien), en tant que valeur TIMESTAMP.

Par exemple, si vous ajoutez des données le 15 avril 2021 à 08:15:00 UTC, la colonne _PARTITIONTIME de ces lignes contient les valeurs suivantes :

  • Table partitionnée par heure : TIMESTAMP("2021-04-15 08:00:00")
  • Table partitionnée par jour : TIMESTAMP("2021-04-15")
  • Table partitionnée par mois: TIMESTAMP("2021-04-01")
  • Table partitionnée par année: TIMESTAMP("2021-01-01")

Si la précision des partitions est quotidienne, la table contient également une pseudo-colonne nommée _PARTITIONDATE. La valeur est égale à _PARTITIONTIME tronquée à une valeur DATE.

Ces deux noms de pseudo-colonnes sont réservés. Vous ne pouvez créer aucune colonne portant un nom donné dans vos tables.

Pour restreindre les partitions, filtrez sur l'une de ces colonnes. Par exemple, la requête suivante analyse uniquement les partitions comprises entre le 1er janvier 2016 et le 2 janvier 2016 :

SELECT
  column
FROM
  dataset.table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')

Pour sélectionner la pseudo-colonne _PARTITIONTIME, vous devez utiliser un alias. Par exemple, la requête suivante sélectionne _PARTITIONTIME en attribuant l'alias pt à la pseudo-colonne :

SELECT
  _PARTITIONTIME AS pt, column
FROM
  dataset.table

Pour les tables partitionnées par jour, vous pouvez sélectionner la pseudo-colonne _PARTITIONDATE de la même manière :

SELECT
  _PARTITIONDATE AS pd, column
FROM
  dataset.table

Les pseudo-colonnes _PARTITIONTIME et _PARTITIONDATE ne sont pas renvoyées par une instruction SELECT *. Vous devez les sélectionner explicitement :

SELECT
  _PARTITIONTIME AS pt, *
FROM
  dataset.table

Gérer les fuseaux horaires dans les tables partitionnées par date d'ingestion

La valeur de _PARTITIONTIME est basée sur la date UTC lorsque le champ est renseigné. Si vous souhaitez interroger des données basées sur un fuseau horaire autre qu'UTC, choisissez l'une des options suivantes :

  • Ajustez les différences de fuseaux horaires dans vos requêtes SQL.
  • Utilisez des décorateurs de partitions pour charger des données dans des partitions par date d'ingestion spécifiques, en fonction d'un fuseau horaire différent d'UTC.

Optimiser les performances avec des pseudo-colonnes

Pour améliorer les performances des requêtes, utilisez la pseudo-colonne _PARTITIONTIME seule du côté gauche d'une comparaison.

Par exemple, les deux requêtes suivantes sont équivalentes. Selon la taille de la table, la seconde requête, qui place la pseudo-colonne _PARTITIONTIME seule sur la gauche de l'opérateur de comparaison >, peut offrir de meilleures performances que la première requête. Les deux requêtes traitent le même volume de données.

-- Might be slower.
SELECT
  field1
FROM
  dataset.table1
WHERE
  TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15");

-- Often performs better.
SELECT
  field1
FROM
  dataset.table1
WHERE
  _PARTITIONTIME > TIMESTAMP_SUB(TIMESTAMP('2016-04-15'), INTERVAL 5 DAY);

Pour limiter les partitions analysées dans une requête, utilisez une expression constante dans votre filtre. La requête suivante limite les partitions éliminées en fonction de la première condition de filtre dans la clause WHERE. Toutefois, la deuxième condition de filtre ne limite pas les partitions analysées, car elle utilise des valeurs de table dynamiques.

SELECT
  column
FROM
  dataset.table2
WHERE
  -- This filter condition limits the scanned partitions:
  _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01')
  -- This one doesn't, because it uses dynamic table values:
  AND _PARTITIONTIME = (SELECT MAX(timestamp) from dataset.table1)

Pour limiter les partitions analysées, n'incluez aucune autre colonne dans un filtre _PARTITIONTIME. Par exemple, la requête suivante ne limite pas les partitions analysées, car field1 est une colonne de la table.

-- Scans all partitions of table2. No pruning.
SELECT
  field1
FROM
  dataset.table2
WHERE
  _PARTITIONTIME + field1 = TIMESTAMP('2016-03-28');

Si vous interrogez souvent une période spécifique, envisagez de créer une vue qui filtre la pseudo-colonne _PARTITIONTIME. Par exemple, l'instruction suivante crée une vue qui n'inclut que les sept derniers jours de données d'une table nommée dataset.partitioned_table :

-- This view provides pruning.
CREATE VIEW dataset.past_week AS
  SELECT *
  FROM
    dataset.partitioned_table
  WHERE _PARTITIONTIME BETWEEN
    TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 7 * 24 HOUR), DAY)
    AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, DAY);

Pour en savoir plus sur la création de vues, consultez la page Créer des vues.

Interroger une table partitionnée par plages d'entiers

Pour restreindre les partitions lorsque vous interrogez une table partitionnée par plages d'entiers, incluez un filtre sur la colonne de partitionnement des entiers.

Dans l'exemple suivant, supposons que dataset.table soit une table partitionnée par plages d'entiers avec une spécification de partitionnement de customer_id:0:100:10. L'exemple de requête analyse les trois partitions commençant par 30, 40 et 50.

SELECT * FROM dataset.table
WHERE customer_id BETWEEN 30 AND 50

+-------------+-------+
| customer_id | value |
+-------------+-------+
|          40 |    41 |
|          45 |    46 |
|          30 |    31 |
|          35 |    36 |
|          50 |    51 |
+-------------+-------+

Actuellement, la restriction de partitions n'est pas compatible avec les fonctions sur une colonne partitionnée par plages d'entiers. Par exemple, la requête suivante analyse l'ensemble de la table.

SELECT * FROM dataset.table
WHERE customer_id + 1 BETWEEN 30 AND 50

Interroger les données dans le stockage optimisé en écriture

La partition __UNPARTITIONED__ contient temporairement les données qui sont transmises à une table partitionnée alors qu'elles se trouvent dans le stockage optimisé en écriture. Les données transmises directement à une partition spécifique d'une table partitionnée n'utilisent pas la partition __UNPARTITIONED__. À la place, les données sont transmises directement à la partition.

Les données stockées dans le stockage optimisé en écriture ont des valeurs NULL dans les colonnes _PARTITIONTIME et _PARTITIONDATE.

Pour interroger des données dans la partition __UNPARTITIONED__, utilisez la pseudo-colonne _PARTITIONTIME avec la valeur NULL. Par exemple :

SELECT
  column
FROM dataset.table
WHERE
  _PARTITIONTIME IS NULL

Pour plus d'informations, consultez la section Insérer des données en flux continu dans des tables partitionnées.

Bonnes pratiques pour l'élimination des partitions

Cette section décrit les bonnes pratiques pour rédiger des requêtes qui utilisent la restriction de partition afin d'optimiser les performances des requêtes et de réduire les coûts.

Utiliser une expression de filtre constante

Pour limiter les partitions analysées dans une requête, filtrez la colonne de partitionnement à l'aide d'une expression constante plutôt que d'une expression dynamique.

La requête suivante élimine des partitions :

SELECT
  t1.name, t1.quantity
FROM
  table1 AS t1
WHERE
  t1.ts = CURRENT_TIMESTAMP()

En comparaison, la requête suivante n'élimine pas de partitions, car le prédicat WHERE t1.ts = (SELECT timestamp FROM table3 WHERE key = 2) n'est pas une expression constante. Cette requête compare la colonne de partitionnement à une valeur dynamique, ce qui empêche l'élagage des partitions.

SELECT
  t1.name, t1.quantity
FROM
  table1 AS t1
WHERE
  t1.ts = (SELECT timestamp FROM table3 WHERE key = 2)

De plus, une requête avec les prédicats suivants n'élimine pas de partitions, car elle nécessite un calcul basé sur une deuxième colonne de table non constante ts2 ou duration :

WHERE ts >= ts2

WHERE ts < CURRENT_TIMESTAMP() - duration

Isoler la colonne de partitionnement ou utiliser des fonctions compatibles

Pour éliminer des partitions, les conditions de filtrage doivent être structurées de manière à ce que BigQuery puisse déterminer les partitions à analyser sans lire les données de la table. Pour ce faire, isolez la colonne de partitionnement d'un côté d'un opérateur de comparaison, ou n'encapsulez la colonne que dans une fonction intégrée compatible. Vous pouvez utiliser l'exécution à blanc pour vérifier si l'élimination des partitions est compatible avec votre requête.

Les fonctions intégrées suivantes sur la colonne de partitionnement sont compatibles avec la restriction de partitions si leurs arguments supplémentaires sont constants :

Les autres fonctions et opérations mathématiques complexes nécessitent une analyse complète de la table.

Exemples

Les requêtes suivantes présentent des exemples de prédicats compatibles avec l'élimination de partitions.

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour = '2025-03-30 12:00:00';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour >= '2025-03-30'
  AND datehour < TIMESTAMP_ADD('2025-03-30', INTERVAL 1 DAY);
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE DATE(datehour) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE EXTRACT(DATE FROM datehour) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE CAST(datehour AS DATE) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour >= '2025-01-01' AND datehour < '2025-02-01';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_TRUNC(datehour, MONTH) >= '2025-04-01'
  AND TIMESTAMP_TRUNC(datehour, MONTH) < '2025-07-01';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_DIFF(datehour, '2025-01-01', DAY) < 1;
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_ADD(datehour, INTERVAL 1 DAY) < '2025-01-03';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_SUB(datehour, INTERVAL 1 DAY) < '2025-01-01';

La requête suivante ignore toutes les partitions, car le prédicat ne correspond à aucune ligne.

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE EXTRACT(YEAR FROM datehour) = 1900;

La requête suivante sélectionne le premier jour de chaque mois dans la table et prend en charge l'élagage de partition.

SELECT COUNT(*) FROM bigquery-public-data.wikipedia.pageviews_2025
WHERE DATE(datehour) IN UNNEST(GENERATE_DATE_ARRAY(
  DATE_TRUNC(CURRENT_DATE(), YEAR),
  DATE(DATE_TRUNC(CURRENT_DATE(), YEAR) + INTERVAL 1 YEAR - INTERVAL 1 DAY),
  INTERVAL 1 MONTH
))

Les requêtes avec les prédicats suivants n'éliminent pas les partitions, car elles manipulent la colonne de partitionnement avec des fonctions non compatibles :

WHERE FORMAT_DATE('%Y-%m-%d %H', ts) = '2025-03-28 20';

WHERE EXTRACT(MONTH FROM ts) = 3 AND EXTRACT(HOUR FROM ts) = 20

De même, une requête avec le prédicat suivant n'élimine pas de partitions, car elle manipule la colonne de partitionnement avec une opération arithmétique : 

WHERE ts + INTERVAL 1 DAY > CURRENT_TIMESTAMP()

Pour activer la restriction de partitions, vous devez réécrire l'expression en isolant la colonne de partitionnement ts des fonctions ou opérations arithmétiques non compatibles. Pour les plages horaires, utilisez >= et < pour capturer la plage exacte. Pour les opérations arithmétiques, déplacez l'opération de l'autre côté de la comparaison.

La requête suivante permet la restriction de partitions en isolant la colonne de partitionnement ts pour une période donnée :

WHERE ts >= '2025-03-28 20:00:00' AND ts < '2025-03-28 21:00:00'

La requête suivante permet d'éliminer des partitions en isolant la colonne de partitionnement de l'opération arithmétique :

WHERE ts > CURRENT_TIMESTAMP() - INTERVAL 1 DAY

Filtrer sur plusieurs colonnes

Un prédicat sur la colonne de partitionnement dans une requête ne limite pas les autres éléments sur lesquels vous pouvez filtrer. Vous pouvez inclure des prédicats sur d'autres colonnes dans la même clause WHERE. L'élagage de partitions se produira toujours tant que la condition évaluant la colonne de partitionnement respecte les bonnes pratiques. Notez que AND est important dans l'exemple suivant. Si AND est remplacé par OR, l'élimination des partitions ne fonctionnera pas, car même si une partition ne correspond pas au prédicat sur la colonne de partitionnement, elle ne peut toujours pas être éliminée. Les données de ces partitions avec meter_id = 1234 sont toujours éligibles à la requête.

Notez que les prédicats n'ont pas besoin d'être écrits dans un ordre spécifique. Dans l'exemple de requête suivant, en supposant un partitionnement sur la colonne ts, la restriction de partitions se produit toujours, quel que soit l'emplacement du prédicat.

WHERE meter_id = 1234
  AND ts >= '2025-03-28 20:00:00' AND ts < '2025-03-28 21:00:00'

Exiger un filtre de partitionnement dans les requêtes

Lorsque vous créez une table partitionnée, vous pouvez demander l'utilisation de filtres en activant l'option Demander un filtre de partition. Lorsque cette option est appliquée, toute tentative d'interrogation de la table partitionnée sans spécifier de clause WHERE génère l'erreur suivante :

Cannot query over table 'project_id.dataset.table' without a filter that can be used for partition elimination.

Cette exigence s'applique également aux requêtes sur les vues et les vues matérialisées qui font référence à la table partitionnée.

Pour que le filtre soit éligible pour l'élimination de partitions, il doit y avoir au moins un prédicat qui ne référence qu'une ou plusieurs colonnes de partitionnement. Pour une table partitionnée suivant la colonne partition_id et dont le schéma comporte une colonne supplémentaire f, les deux clauses WHERE suivantes remplissent les conditions requises :

WHERE partition_id = "20221231"

WHERE partition_id = "20221231" AND f = "20221130"

Toutefois, le code suivant n'est pas suffisant et générera une erreur :

WHERE partition_id = "20221231" OR f = "20221130"

Pour les tables partitionnées par date d'ingestion, utilisez la pseudo-colonne _PARTITIONTIME ou _PARTITIONDATE.

Pour plus d'informations sur l'activation de l'option Demander un filtre de partitionnement lors de la création d'une table partitionnée, consultez la section Créer des tables partitionnées. Vous pouvez également mettre à jour ce paramètre sur une table existante.

Étapes suivantes