Interroger des tables partitionnées
Vous pouvez interroger des tables partitionnées à l'aide de l'interface utilisateur Web de BigQuery ou de la commande bq query de l'outil de ligne de commande. Vous pouvez également appeler la méthode jobs.insert et configurer une tâche de requête.
Pour en savoir plus sur l'exécution de requêtes, consultez la page Exécuter des requêtes interactives et par lot.
Autorisations requises
Au niveau de l'ensemble de données, pour interroger une table partitionnée, vous devez disposer d'un accès READER à l'ensemble de données contenant la table.
À la place des autorisations définies au niveau de l'ensemble de données, vous pouvez utiliser un rôle IAM au niveau du projet qui inclut des autorisations bigquery.tables.getData.
Les autorisations bigquery.tables.getData sont obligatoires pour lire les données de la table interrogée.
Tous les rôles IAM prédéfinis au niveau du projet incluent les autorisations bigquery.tables.getData, à l'exception de bigquery.user, bigquery.jobUser et bigquery.metadataViewer.
Vous devez également disposer d'autorisations bigquery.jobs.create pour pouvoir exécuter des tâches de requête.
Voici les rôles IAM prédéfinis au niveau du projet qui incluent les autorisations bigquery.jobs.create :
Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la section Contrôle des accès. Pour en savoir plus sur les rôles au niveau de l'ensemble de données, consultez la section Rôles primitifs pour les ensembles de données.
Pseudo-colonnes de tables partitionnées avec date d'ingestion
Lorsque vous créez une table partitionnée avec date d'ingestion, deux pseudo-colonnes sont ajoutées à la table : _PARTITIONTIME et _PARTITIONDATE. La pseudo-colonne _PARTITIONTIME contient un horodatage basé sur la date pour les données chargées dans la table. La pseudo-colonne _PARTITIONDATE contient une représentation de date. Ces deux pseudo-colonnes ont des noms réservés, ce qui signifie que vous ne pouvez créer aucune colonne portant l'un ou l'autre de ces noms dans vos tables.
_PARTITIONTIME et _PARTITIONDATE ne sont disponibles que dans les tables partitionnées avec date d'ingestion. Les tables partitionnées n'ont pas de pseudo-colonnes. Pour en savoir plus sur l'interrogation des tables partitionnées, consultez la section Interroger des tables partitionnées.
Pseudo-colonne _PARTITIONTIME
La pseudo-colonne _PARTITIONTIME contient un horodatage basé sur l'heure UTC, et représente le nombre de microsecondes écoulées depuis l'époque Unix. Par exemple, si des données sont ajoutées à une table le 15 avril 2016, toutes les lignes de données ajoutées ce jour-là contiennent la valeur TIMESTAMP("2016-04-15") dans la colonne _PARTITIONTIME.
Pour interroger 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]
Où :
[COLUMN]est le nom d'une colonne à interroger. Vous pouvez spécifier plusieurs colonnes sous la forme d'une liste d'éléments séparés par une virgule.[DATASET]est l'ensemble de données contenant la table partitionnée.[TABLE]est la table partitionnée.
Les données figurant dans le tampon de diffusion ont des valeurs NULL dans la colonne _PARTITIONTIME.
Pseudo-colonne _PARTITIONDATE
La pseudo-colonne _PARTITIONDATE contient la date UTC correspondant à la valeur de la pseudo-colonne _PARTITIONTIME.
Pour interroger la pseudo-colonne _PARTITIONDATE, vous devez utiliser un alias. Par exemple, la requête suivante sélectionne _PARTITIONDATE en attribuant l'alias pd à la pseudo-colonne :
SELECT _PARTITIONDATE AS pd, [COLUMN] FROM [DATASET].[TABLE]
Où :
[COLUMN]est le nom d'une colonne à interroger. Vous pouvez spécifier plusieurs colonnes sous la forme d'une liste d'éléments séparés par une virgule.[DATASET]est l'ensemble de données contenant la table partitionnée.[TABLE]est la table partitionnée.
Les données figurant dans le tampon de diffusion ont des valeurs NULL dans la colonne _PARTITIONDATE.
Interroger des tables partitionnées avec date d'ingestion à l'aide de pseudo-colonnes
Lorsque vous interrogez des données dans des tables partitionnées avec date d'ingestion, vous référencez des partitions spécifiques. Pour ce faire, vous spécifiez les valeurs dans les pseudo-colonnes _PARTITIONTIME ou _PARTITIONDATE. Exemple :
_PARTITIONTIME >= "2018-01-29 00:00:00" AND _PARTITIONTIME < "2018-01-30 00:00:00"_PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')
ou
_PARTITIONDATE >= "2018-01-29" AND _PARTITIONDATE < "2018-01-30"_PARTITIONDATE BETWEEN '2016-01-01' AND '2016-01-02'
Limiter les partitions interrogées à l'aide de pseudo-colonnes
Limitez le nombre de partitions analysées lors d'une requête avec les pseudo-colonnes _PARTITIONTIME et _PARTITIONDATE. Ce processus est également 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, ce qui réduit le coût de l'analyse à la demande. En général, l'élimination des partitions réduit le coût des requêtes lorsque les filtres peuvent être évalués au début de la requête, sans que des évaluations de sous-requêtes ou des analyses de données ne soient nécessaires.
Par exemple, la requête suivante n'analyse que les partitions comprises entre le 1er janvier 2016 et le 2 janvier 2016 de la table partitionnée :
_PARTITIONTIME
SELECT
[COLUMN]
FROM
[DATASET].[TABLE]
WHERE
_PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01')
AND TIMESTAMP('2016-01-02')
_PARTITIONDATE
SELECT [COLUMN] FROM [DATASET].[TABLE] WHERE _PARTITIONDATE BETWEEN '2016-01-01' AND '2016-01-02'
Exemples d'élimination des partitions
Cet exemple montre comment limiter le nombre de partitions analysées à l'aide d'un filtre de pseudo-colonne dans une sous-requête :
_PARTITIONTIME
SELECT
[COLUMN1],
[COLUMN2]
FROM (
SELECT
[COLUMN1],
[COLUMN2]
FROM
[DATASET].[TABLE]
WHERE
_PARTITIONTIME = TIMESTAMP('2016-03-28')) t1
CROSS JOIN
[DATASET].[TABLE2] t2
WHERE
t1.[COLUMN2] = "one"
_PARTITIONDATE
SELECT
[COLUMN1],
[COLUMN2]
FROM (
SELECT
[COLUMN1],
[COLUMN2]
FROM
[DATASET].[TABLE]
WHERE
_PARTITIONDATE = '2016-03-28') t1
CROSS JOIN
[DATASET].[TABLE2] t2
WHERE
t1.[COLUMN2] = "one"
La requête suivante limite certaines partitions basées sur une partie de la condition de filtre, _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND
TIMESTAMP('2017-03-01'). Elle ne les limite pas en fonction de la condition qui implique la sous-requête :
_PARTITIONTIME
SELECT
[COLUMN]
FROM
[DATASET].[TABLE2]
WHERE
_PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01')
AND _PARTITIONTIME = (SELECT MAX(timestamp) from [DATASET].[TABLE1])
_PARTITIONDATE
SELECT [COLUMN] FROM [DATASET].[TABLE2] WHERE _PARTITIONDATE BETWEEN '2017-01-01' AND '2017-03-01' AND _PARTITIONDATE = (SELECT MAX(date) from [DATASET].[TABLE1])
Requêtes de pseudo-colonnes qui analysent toutes les partitions
Les exemples suivants utilisent des pseudo-colonnes, mais analysent toutes les partitions d'une table partitionnée en fonction de l'heure.
Dans l'ancien SQL, le filtre _PARTITIONTIME ne fonctionne que lorsqu'il est spécifié le plus près possible du nom de la table. Par exemple, la requête suivante analyse toutes les partitions de table1 malgré la présence du filtre _PARTITIONTIME :
#legacySQL
# Scans all partitions on t1
SELECT
t1.field1,
t2.field1
FROM
mydataset.table2 t1
CROSS JOIN
mydataset.table2 t2
WHERE
t1._PARTITIONTIME = TIMESTAMP('2016-03-28')
AND t1.field2 = "one"
N'incluez aucune autre colonne dans un filtre _PARTITIONTIME. Par exemple, la requête suivante ne limite pas les partitions analysées, car field2 est une colonne de la table, et BigQuery ne peut pas déterminer à l'avance les partitions à sélectionner.
# Scans all partitions of table2
SELECT
field1
FROM
mydataset.table2
WHERE
_PARTITIONTIME + field2 = TIMESTAMP('2016-03-28');
Vous ne pouvez pas limiter le nombre de partitions analysées pour une table partitionnée à l'aide de filtres sur _PARTITIONTIME qui incluent des sous-requêtes. Par exemple, la requête suivante ne limite pas les partitions analysées sur la table mydataset.table2 :
# Scans all partitions of table2 SELECT field1 FROM mydataset.table2 WHERE _PARTITIONTIME = (SELECT MAX(timestamp) FROM mydataset.table1)
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 requêtes suivantes traitent le même volume de données, mais le second exemple peut offrir de meilleures performances.
Exemple 1 : La requête suivante peut être plus lente, car elle combine la valeur de la pseudo-colonne avec d'autres opérations dans le filtre WHERE.
SQL standard
#standardSQL
/* Can be slower */
SELECT
field1
FROM
mydataset.table1
WHERE
TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15")
ancien SQL
#legacySQL
/* Can be slower */
SELECT
field1
FROM
mydataset.table1
WHERE
DATE_ADD(_PARTITIONTIME, 5, "DAY") > TIMESTAMP("2016-04-15")
Exemple 2 : La requête suivante peut offrir de meilleures performances, car elle place la pseudo-colonne seule du côté gauche de la comparaison de filtres.
SQL standard
#standardSQL
/* Often performs better */
SELECT
field1
FROM
mydataset.table1
WHERE
_PARTITIONTIME > TIMESTAMP_SUB(TIMESTAMP('2016-04-15'), INTERVAL 5 DAY)
ancien SQL
#legacySQL
/* Often performs better */
SELECT
field1
FROM
mydataset.table1
WHERE
_PARTITIONTIME > DATE_ADD(TIMESTAMP('2016-04-15'), -5, "DAY")
En fonction de 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. Comme les requêtes traitent le même volume de données, le nombre d'octets facturés est identique dans les deux cas.
Interroger des tables partitionnées avec date d'ingestion à l'aide d'une table générique
Les pseudo-colonnes vous permettent non seulement de limiter le nombre de partitions analysées lors d'une requête, mais aussi d'interroger plusieurs tables partitionnées à l'aide d'une table générique. Pour en savoir plus sur l'utilisation d'une table générique avec des tables partitionnées, consultez la section Analyser plusieurs tables partitionnées à l'aide de _PARTITIONTIME.
Interroger des tables partitionnées avec date d'ingestion à l'aide de fuseaux horaires
La valeur de _PARTITIONTIME est basée sur la date UTC lorsque le champ est renseigné, ce qui signifie que les partitions sont divisées en fonction de l'heure suivante : 00h00 UTC. Si vous souhaitez interroger des données basées sur un fuseau horaire autre qu'UTC, vous devez choisir l'une des options suivantes avant de commencer à charger des données dans votre table.
Il existe deux manières d'interroger des données dans une table partitionnée à l'aide d'un fuseau horaire personnalisé, autre qu'UTC. Vous pouvez créer une colonne d'horodatage distincte, ou utiliser des décorateurs de partitions pour charger des données dans une partition spécifique.
Si vous utilisez une colonne d'horodatage, vous pouvez utiliser le partitionnement basé sur UTC par défaut et rendre compte des différences de fuseaux horaires dans vos requêtes SQL. Si vous préférez avoir des partitions regroupées selon un fuseau horaire autre qu'UTC, utilisez des décorateurs de partitions pour charger des données dans des partitions basées sur un fuseau horaire différent.
Interroger des fuseaux horaires à l'aide d'une colonne d'horodatage
Pour ajuster les fuseaux horaires à l'aide d'un horodatage, créez une colonne distincte pour stocker un horodatage vous permettant de traiter les lignes par heure ou par minute.
Pour interroger des données basées sur un fuseau horaire autre qu'UTC, utilisez à la fois la pseudo-colonne _PARTITIONTIME et votre colonne d'horodatage personnalisée.
L'utilisation de _PARTITIONTIME limite l'analyse de la table aux partitions pertinentes, et l'horodatage personnalisé limite davantage les résultats à votre fuseau horaire. Par exemple, pour interroger des données d'une table partitionnée (mydataset.partitioned_table) avec un champ d'horodatage [MY_TIMESTAMP_FIELD] pour les données ajoutées à la table entre le 2016-05-01 08:00:00 PST et le 2016-05-05 14:00:00 PST :
SQL standard
#standardSQL
SELECT
field1
FROM
mydataset.partitioned_table
WHERE
_PARTITIONTIME BETWEEN TIMESTAMP("2016-05-01")
AND TIMESTAMP("2016-05-06")
AND TIMESTAMP_ADD([MY_TIMESTAMP_FIELD], INTERVAL 8 HOUR) BETWEEN TIMESTAMP("2016-05-01 12:00:00")
AND TIMESTAMP("2016-05-05 14:00:00");
ancien SQL
#legacySQL
SELECT
field1
FROM
mydataset.partitioned_table
WHERE
_PARTITIONTIME BETWEEN TIMESTAMP("2016-05-01")
AND TIMESTAMP("2016-05-06")
AND DATE_ADD([MY_TIMESTAMP_FIELD], 8, 'HOUR') BETWEEN TIMESTAMP("2016-05-01 12:00:00")
AND TIMESTAMP("2016-05-05 14:00:00");
Créer une vue à l'aide des pseudo-colonnes d'une table partitionnée avec date d'ingestion
Pour limiter le volume de données lues par une requête à un ensemble de partitions, créez une vue contenant un filtre sur la pseudo-colonne _PARTITIONTIME ou _PARTITIONDATE. Par exemple, la requête suivante permet de créer une vue qui n'inclut que les sept derniers jours de données d'une table nommée mydataset.partitioned_table :
SQL standard
#standardSQL SELECT * FROM mydataset.partitioned_table WHERE _PARTITIONTIME BETWEEN TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 * 24 HOUR),DAY) AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(),DAY);
ancien SQL
#legacySQL SELECT * FROM mydataset.partitioned_table WHERE _PARTITIONTIME BETWEEN TIMESTAMP(UTC_USEC_TO_DAY(NOW() - 7 * 60 * 60 * 24 * 1000000)) AND TIMESTAMP(UTC_USEC_TO_DAY(CURRENT_TIMESTAMP()));
Pour en savoir plus sur la création des vues, consultez la section Créer une vue.
Partition __UNPARTITIONED__ des tables partitionnées avec date d'ingestion
La partition __UNPARTITIONED__ contient temporairement les données qui sont transmises à une table partitionnée alors qu'elles se trouvent dans le tampon de diffusion en continu. 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. Pour en savoir plus, consultez la section Diffuser des données en continu dans des tables partitionnées.
Pour interroger des données dans la partition __UNPARTITIONED__, utilisez la pseudo-colonne _PARTITIONTIME avec la valeur NULL. Exemple :
SELECT [COLUMN] FROM [DATASET].[TABLE] WHERE _PARTITIONTIME IS NULL
Où :
[COLUMN]est le nom d'une colonne à interroger. Vous pouvez spécifier plusieurs colonnes sous la forme d'une liste d'éléments séparés par une virgule.[DATASET]est l'ensemble de données contenant la table partitionnée.[TABLE]est la table partitionnée.
Interroger des tables partitionnées
Les tables partitionnées en fonction d'une colonne TIMESTAMP ou DATE n'ont pas de pseudo-colonnes. Pour limiter le nombre de partitions analysées lors de l'interrogation de tables partitionnées, utilisez un filtre de prédicat (clause WHERE).
Éliminer (limiter) les partitions
Exprimez le filtre de prédicat le plus près possible de l'identifiant de la table. Les requêtes complexes nécessitant l'évaluation de plusieurs étapes d'une requête afin de résoudre le prédicat (telles que des requêtes internes ou des sous-requêtes) n'éliminent pas des partitions de la requête.
Par exemple, la requête suivante élimine des partitions :
#standardSQL SELECT t1.name, t2.category FROM table1 t1 INNER JOIN table2 t2 ON t1.id_field = t2 field2 WHERE t1.ts = CURRENT_TIMESTAMP()
La requête suivante n'élimine pas des partitions (notez l'utilisation d'une sous-requête) :
#standardSQL SELECT t1.name, t2.category FROM table1 t1 INNER JOIN table2 t2 ON t1.id_field = t2.field2 WHERE t1.ts = (SELECT timestamp from table3 where key = 2)
Isoler la colonne de partition dans votre filtre
Isolez la colonne de partition lors de l'expression d'un filtre. Les filtres nécessitant des données provenant de plusieurs champs pour le calcul n'éliminent aucune partition. Par exemple, une requête avec une comparaison de dates qui utilise la colonne de partitionnement et un second champ, ou des requêtes contenant des concaténations de champs, n'éliminent aucune partition.
Par exemple, le filtre suivant n'élimine pas des partitions, car il nécessite un calcul basé sur le champ ts de partitionnement et un second champ ts2 :
WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2
Étapes suivantes
- Pour obtenir une présentation des tables partitionnées, consultez la page Introduction aux tables partitionnées.
- Pour en savoir plus sur les tables partitionnées avec date d'ingestion, consultez la page Créer et utiliser des tables partitionnées avec date d'ingestion.
- Pour en savoir plus sur les tables partitionnées, consultez la page Créer et utiliser des tables partitionnées.
Besoin d'aide ? Consultez notre