Interroger des tables partitionnées

Interroger des tables partitionnées

Vous pouvez interroger des tables partitionnées de plusieurs façons :

• En utilisant Cloud Console
• En exécutant la commande bq query de l'outil de ligne de commande bq
• En appelant la méthode API jobs.insert et en configurant une tâche de requête
• utilisant les bibliothèques clientes

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

Pour interroger une table, vous devez au minimum disposer des autorisations bigquery.tables.getData.

Les rôles IAM prédéfinis suivants incluent des autorisations bigquery.tables.getData :

• bigquery.dataViewer
• bigquery.dataOwner
• bigquery.dataEditor
• bigquery.admin

En outre, si un utilisateur possède les autorisations bigquery.datasets.create, il obtient également un accès bigquery.dataOwner à l'ensemble de données qu'il crée. L'accès bigquery.dataOwner permet à l'utilisateur d'interroger des tables et des vues de l'ensemble de données.

Pour exécuter des tâches de requête, vous devez également disposer des autorisations bigquery.jobs.create. Les rôles IAM prédéfinis suivants incluent les autorisations bigquery.jobs.create :

• bigquery.user
• bigquery.jobUser
• bigquery.admin

Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la page Contrôle des accès.

Pseudo-colonnes de tables partitionnées par date d'ingestion

Lorsque vous créez une table partitionnée par date d'ingestion, deux pseudo-colonnes sont ajoutées à cette 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 par 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 page 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 à 08:15:00 UTC, toutes les lignes de données ajoutées ce jour-là contiennent l'une des valeurs suivantes dans la colonne _PARTITIONTIME : + TIMESTAMP("2016-04-15 08:00:00") pour les tables partitionnées par heure. + TIMESTAMP("2016-04-15") pour les tables partitionnées par jour. + TIMESTAMP("2016-04-01") pour les tables partitionnées par mois. + TIMESTAMP("2016-01-01") pour les tables partitionnées par année.

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 forme de liste séparée 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 d'insertion en flux continu comportent 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. Cette colonne n'est pas compatible avec les tables partitionnées par heure, par mois ou par année.

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 forme de liste séparée 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 d'insertion en flux continu comportent des valeurs NULL dans la colonne _PARTITIONDATE.

Interroger des tables partitionnées par date d'ingestion à l'aide de pseudo-colonnes

Lorsque vous interrogez des données dans des tables partitionnées par 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

Vous pouvez utiliser les pseudo-colonnes _PARTITIONTIME et _PARTITIONDATE pour limiter le nombre de partitions analysées lors d'une requête. Ce processus est également appelé restriction 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, la restriction des partitions permet de réduire le coût des requêtes lorsque les filtres sont constitués d'expressions constantes, qui peuvent être évaluées au début de la requête.

Attention : Certaines utilisations des pseudo-colonnes _PARTITIONTIME et _PARTITIONDATE ne limitent pas le nombre de partitions analysées. Pour plus d'informations, consultez la page Éliminer (limiter) des partitions. Pour voir des exemples de requêtes qui incluent les pseudo-colonnes, mais ne limitent pas le nombre de partitions analysées, consultez la section Requêtes de pseudo-colonnes qui analysent toutes les partitions.

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.table 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.table t2
WHERE
  t1.column2 = "one"

La requête suivante limite les partitions interrogées, en fonction de la première condition de filtre de la clause WHERE. Toutefois, la deuxième condition de filtre de la clause WHERE ne limite pas les partitions interrogées, car elle utilise des valeurs de table dynamiques.

_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 d'une unité de temps.

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
  dataset.table1 t1
CROSS JOIN
  dataset.table2 t2
WHERE
  table1._PARTITIONTIME = TIMESTAMP('2016-03-28')
  AND t1.field1 = "one"

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, et BigQuery ne peut pas déterminer à l'avance les partitions à sélectionner. Pour plus d'informations, consultez la page Éliminer (limiter) des partitions.

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

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 les autres opérations spécifiées dans le filtre WHERE.

SQL standard

#standardSQL
/* Can be slower */
SELECT
  field1
FROM
  dataset.table1
WHERE
  TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15")

Ancien SQL

#legacySQL
/* Can be slower */
SELECT
  field1
FROM
  dataset.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
  dataset.table1
WHERE
  _PARTITIONTIME > TIMESTAMP_SUB(TIMESTAMP('2016-04-15'), INTERVAL 5 DAY)

Ancien SQL

#legacySQL
/* Often performs better */
SELECT
  field1
FROM
  dataset.table1
WHERE
  _PARTITIONTIME > DATE_ADD(TIMESTAMP('2016-04-15'), -5, "DAY")

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. 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 par 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 par 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 le chargement 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 les horodatages 2016-05-01 12:00:00 PST et 2016-05-05 14:00:00 PST :

SQL standard

#standardSQL
SELECT
  field1
FROM
  dataset.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
  dataset.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");

Interroger une table partitionnée par plages d'entiers

Les tables partitionnées par plages d'entiers ne peuvent être interrogées que via le langage SQL standard. Lorsqu'une table partitionnée par plages d'entiers est interrogée, et que des filtres sont appliqués à la colonne de partitionnement des entiers, ces filtres permettent de restreindre les partitions et de réduire le coût des requêtes.

La requête suivante analyse les trois partitions qui commencent par 30, 40 et 50, pour y rechercher une table partitionnée par plages d'entiers avec la spécification de partitionnement customer_id:0:100:10.

bq query --nouse_legacy_sql \
'SELECT * FROM mydataset.mytable WHERE customer_id BETWEEN 30 AND 50'

Voici un exemple de résultat de la commande bq query :

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

Dans cet exemple, chaque partition comporte deux lignes et chaque ligne deux colonnes d'entiers. Par conséquent, la requête va traiter 3 * 2 * 2 * 8 = 96 octets. Vous pouvez examiner les informations ci-dessous sur la tâche :

bq show -j bqjob_r4fce65fa3381528e_000001670994aeb6_1

Voici un exemple de résultat de la commande bq show :

  Job Type    State      Start Time      Duration       User Email        Bytes Processed   Bytes Billed   Billing Tier   Labels
 ---------- --------- ----------------- ---------- --------------------- ----------------- -------------- -------------- --------
  query      SUCCESS   24 Sep 12:19:58   0:00:01    [e-mail]      96                10485760       1

Les instructions LMD sont acceptées. Exemple :

bq query --nouse_legacy_sql \
'DELETE FROM mydataset.mytable WHERE customer_id = 30'

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 la totalité de la table.

bq query --nouse_legacy_sql \
'SELECT * FROM mydataset.mytable WHERE customer_id+1 BETWEEN 30 AND 50'

Décorateurs de table dans les tables partitionnées par plages d'entiers

L'ancien SQL permet de circonscrire une partition dans une table partitionnée par plages d'entiers à l'aide de décorateurs de table. La clé permettant de circonscrire une partition par plages est le début de la plage.

L'exemple suivant interroge la partition par plages commençant par 0. La colonne de partitionnement est customer_id et la partition contient deux lignes.

bq query --use_legacy_sql=true 'SELECT * FROM mydataset.mytable$0'

+-------------+-------+
| customer_id | value |
+-------------+-------+
|           0 |     1 |
|           5 |     6 |
+-------------+-------+

Créer une vue à l'aide des pseudo-colonnes d'une table partitionnée par 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 dataset.partitioned_table :

SQL standard

#standardSQL
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);

Ancien SQL

#legacySQL
SELECT
  *
FROM
  dataset.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 de vues, consultez la page Créer des vues.

Partition _UNPARTITIONED des tables partitionnées par 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 d'insertion en flux 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 Insérer des données en flux 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 forme de liste séparée 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, DATE, DATETIME ou INTEGER 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). Les filtres appliqués à la colonne de partitionnement permettent de restreindre les partitions et de réduire le coût des requêtes.

Les tables partitionnées par heure, par mois et par année ne peuvent être interrogées qu'avec le langage SQL standard.

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.

Remarque : 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 partition. Pour les tables partitionnées par date d'ingestion, il s'agit de la pseudo-colonne _PARTITIONTIME ou _PARTITIONDATE. Par exemple, 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 = "foo"
    WHERE partition_id = "foo" AND f = "bar"

Toutefois, la clause WHERE (partition_id = "foo" OR f = "bar") n'est pas suffisante.

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.

Si vous n'activez pas l'option Demander un filtre de partitionnement lorsque vous créez une table partitionnée, vous pouvez mettre à jour la table afin d'ajouter cette option.

Supprimer des partitions

Pour limiter les partitions analysées dans une requête, utilisez une expression constante dans votre filtre. Si vous utilisez des expressions dynamiques dans votre filtre de requête, BigQuery doit analyser toutes les partitions.

Par exemple, la requête suivante élimine des partitions, car le filtre WHERE t1.ts=CURRENT_TIMESTAMP() contient une expression constante :

#standardSQL
SELECT
  t1.name,
  t2.category
FROM
  table1 t1
INNER JOIN
  table2 t2
ON t1.id_field = t2.field2
WHERE
  t1.ts = CURRENT_TIMESTAMP()

Cependant, la requête suivante n'élimine pas de partitions, car le filtre WHERE t1.ts = (SELECT timestamp from table where key = 2) n'est pas une expression constante ; cela dépend des valeurs dynamiques des champs timestamp et key:

#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 de partitions, car il nécessite un calcul basé sur le champ de partitionnement (ts) et sur un deuxième champ (ts2) :

WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2

Étapes suivantes

• Pour une vue d'ensemble des tables partitionnées, consultez la page Présentation des tables partitionnées.
• Pour plus d'informations sur la création de tables partitionnées, consultez la page Créer des tables partitionnées.