Gérer des tables partitionnées

Vous trouverez dans ce document la procédure à suivre pour gérer les tables partitionnées dans BigQuery.

Remarque : Les informations de la section Gérer des tables s'appliquent également aux tables partitionnées.

Obtenir les métadonnées de partition

Vous pouvez obtenir des informations sur les tables partitionnées de différentes manières :

• En utilisant la vue INFORMATION_SCHEMA.PARTITIONS (bêta)
• En utilisant la métatable __PARTITIONS_SUMMARY__ (exclusivement pour l'ancien SQL)

Obtenir des métadonnées de partitions à l'aide des vues INFORMATION_SCHEMA

Lorsque vous interrogez la vue INFORMATION_SCHEMA.PARTITIONS, les résultats de la requête contiennent une ligne pour chaque partition. Par exemple, la requête suivante répertorie toutes les partitions de la table dans l'ensemble de données nommé mydataset :

SELECT table_name, partition_id, total_rows
FROM `mydataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE partition_id IS NOT NULL

Pour en savoir plus, consultez INFORMATION_SCHEMA.PARTITIONS.

Obtenir des métadonnées de partitions avec des métatables

En ancien SQL, vous pouvez obtenir les métadonnées des partitions de tables en interrogeant la métatable __PARTITIONS_SUMMARY__. Les métatables sont des tables en lecture seule contenant des métadonnées.

Interrogez la métatable __PARTITIONS_SUMMARY__ comme suit :

#legacySQL
SELECT
  column
FROM
  [dataset.table$__PARTITIONS_SUMMARY__]

Remarque : GoogleSQL n'est pas compatible avec le séparateur de décorateur de partition ($). Vous ne pouvez donc pas interroger __PARTITIONS_SUMMARY__ en GoogleSQL.

La métatable __PARTITIONS_SUMMARY__ contient les colonnes suivantes :

Valeur	Description
project_id	Nom du projet.
dataset_id	Nom de l'ensemble de données.
table_id	Nom de la table partitionnée par date.
partition_id	Nom (date) de la partition.
creation_time	Heure à laquelle la partition a été créée, exprimée en millisecondes depuis le 1er janvier 1970 UTC.
last_modified_time	Heure à laquelle la partition a été modifiée pour la dernière fois, exprimée en millisecondes depuis le 1er janvier 1970 UTC.

Pour exécuter une tâche de requête qui utilise la métatable __PARTITIONS_SUMMARY__, vous devez au minimum disposer des autorisations bigquery.jobs.create et bigquery.tables.getData.

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

Définir le délai d'expiration de la partition

Lorsque vous créez une table partitionnée par date d'ingestion ou par colonne d'unité de temps, vous pouvez spécifier une date d'expiration de partition. Ce paramètre spécifie la durée pendant laquelle BigQuery conserve les données dans chaque partition. Ce paramètre s'applique à toutes les partitions de la table, mais il est calculé indépendamment pour chaque partition en fonction de l'heure de partition.

Le délai d'expiration d'une partition est calculé à partir de la limite de partition au format UTC. Par exemple, avec le partitionnement quotidien, la limite de partition est à minuit (00:00:00 UTC). Si le délai d'expiration de partition de la table est de six heures, chaque partition expire à 06:00:00 UTC le lendemain. Lorsqu'une partition expire, BigQuery supprime les données qu'elle contient.

Vous pouvez également spécifier un délai d'expiration de partition par défaut au niveau de l'ensemble de données. Si vous définissez le délai d'expiration de partition sur une table, la valeur remplace le délai d'expiration de partition par défaut. Si vous ne spécifiez aucun délai d'expiration de partition (sur la table ou l'ensemble de données), les partitions n'expirent jamais.

Remarque : Les tables partitionnées par plages d'entiers n'acceptent pas les délais d'expiration de partition.

Si vous définissez un délai d'expiration de table, cette valeur est prioritaire sur le délai d'expiration de partition. Par exemple, si le délai d'expiration de table est défini sur cinq jours et que le délai d'expiration de partition est de sept jours, la table et toutes les partitions qu'elle contient sont supprimées au bout de cinq jours.

À tout moment après la création d'une table, vous pouvez mettre à jour le délai d'expiration de partition de la table. Le nouveau paramètre s'applique à toutes les partitions de la table, quelle que soit leur date de création. Les partitions existantes expirent immédiatement si elles sont antérieures au nouveau délai d'expiration. De même, si des données sont copiées ou insérées dans une table partitionnée par colonne d'unité de temps, les partitions plus anciennes que la date d'expiration de la partition configurée pour la table expirent immédiatement.

Lorsqu'une partition expire, les données de cette partition ne sont plus disponibles pour l'interrogation, et le stockage de cette partition n'est pas facturé. BigQuery supprime finalement la partition arrivée à expiration. Jusqu'à cette date, la partition est comptabilisée dans le cadre des quotas de table. Pour supprimer une partition immédiatement, vous pouvez la supprimer manuellement.

Mettre à jour la date d'expiration de la partition

Pour mettre à jour la date d'expiration de la partition d'une table partitionnée, procédez comme suit :

Console

Vous ne pouvez pas mettre à jour le délai d'expiration de partition dans la console Google Cloud.

SQL

Utilisez l'instruction ALTER TABLE SET OPTIONS. L'exemple suivant met à jour le délai d'expiration sur 5 jours. Pour supprimer le délai d'expiration de partition d'une table, définissez partition_expiration_days sur NULL.

1. Dans la console Google Cloud, accédez à la page BigQuery.

   Accéder à BigQuery

2. Dans l'éditeur de requête, saisissez l'instruction suivante :

   ALTER TABLE mydataset.mytable
     SET OPTIONS (
       -- Sets partition expiration to 5 days
       partition_expiration_days = 5);

3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

bq

Exécutez la commande bq update avec l'option --time_partitioning_expiration. Si vous mettez à jour une table partitionnée dans un projet autre que votre projet par défaut, ajoutez l'ID du projet au nom de l'ensemble de données, en respectant le format suivant : project_id:dataset.

bq update \
--time_partitioning_expiration integer_in_seconds \
--time_partitioning_type unit_time \
project_id:dataset.table

Où :

• integer est la durée de vie par défaut (en secondes) des partitions de la table. Il n'y a pas de valeur minimale. Le délai d'expiration correspond à la date de la partition plus la valeur entière. Si vous spécifiez 0, le délai d'expiration de la partition est supprimé et la partition n'expire jamais. Les partitions sans date d'expiration doivent être supprimées manuellement.
• Le valeur de unit_time est DAY, HOUR, MONTH ou YEAR, en fonction de la précision de partitionnement de la table. Cette valeur doit correspondre à la précision que vous avez définie lors de la création de la table.
• project_id est l'ID de votre projet.
• dataset est le nom de l'ensemble de données contenant la table que vous mettez à jour.
• table est le nom de la table que vous mettez à jour.

Exemples :

Saisissez la commande suivante pour faire passer le délai d'expiration des partitions dans mydataset.mytable à cinq jours (432 000 secondes). mydataset se trouve dans le projet par défaut.

bq update --time_partitioning_expiration 432000 mydataset.mytable

Saisissez la commande suivante pour faire passer le délai d'expiration des partitions dans mydataset.mytable à cinq jours (432 000 secondes). mydataset se trouve dans myotherproject, et non dans votre projet par défaut.

bq update \
--time_partitioning_expiration 432000 \
myotherproject:mydataset.mytable

API

Appelez la méthode tables.patch, puis utilisez la propriété timePartitioning.expirationMs pour mettre à jour le délai d'expiration de partition, exprimé en millisecondes. Étant donné que la méthode tables.update remplace l'intégralité de la ressource de table, la méthode tables.patch est à privilégier.

Définir les exigences relatives au filtre de partitionnement

Lorsque vous créez une table partitionnée, vous pouvez exiger que toutes les requêtes effectuées sur la table incluent un filtre de prédicat (une clause WHERE) qui filtre suivant la colonne de partitionnement. Ce paramètre peut améliorer les performances et réduire les coûts, car BigQuery peut utiliser le filtre pour restreindre les partitions qui ne correspondent pas au prédicat.

Pour des informations sur l'ajout de l'option Demander un filtre de partitionnement lors de la création d'une table partitionnée, consultez Créer des tables partitionnées.

Si une table partitionnée présente le paramètre Demander un filtre de partitionnement, chaque requête effectuée sur cette table doit inclure au moins un prédicat qui ne fait référence qu'à la colonne de partitionnement. Les requêtes sans ce prédicat renvoient l'erreur suivante :

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

Pour en savoir plus, consultez la page Interroger des tables partitionnées.

Mettre à jour les exigences relatives au filtre de partitionnement

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

Console

Vous ne pouvez pas utiliser la console Google Cloud pour exiger des filtres de partitionnement après la création d'une table partitionnée.

SQL

Utilisez l'instruction ALTER TABLE SET OPTIONS pour mettre à jour l'exigence de filtre de partition. L'exemple suivant met à jour l'exigence en utilisant true :

1. Dans la console Google Cloud, accédez à la page BigQuery.

   Accéder à BigQuery

2. Dans l'éditeur de requête, saisissez l'instruction suivante :

   ALTER TABLE mydataset.mypartitionedtable
     SET OPTIONS (
       require_partition_filter = true);

3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

bq

Pour mettre à jour une table partitionnée afin d'exiger des filtres de partitionnement à l'aide de l'outil de ligne de commande bq, exécutez la commande bq update avec l'option --require_partition_filter.

Pour mettre à jour une table partitionnée dans un projet autre que votre projet par défaut, ajoutez l'ID du projet à l'ensemble de données, en respectant le format suivant : project_id:dataset.

Exemple :

Pour mettre à jour mypartitionedtable dans l'ensemble de données mydataset de votre projet par défaut, saisissez :

bq update --require_partition_filter mydataset.mytable

Pour mettre à jour mypartitionedtable dans l'ensemble de données mydataset de myotherproject, saisissez :

bq update --require_partition_filter myotherproject:mydataset.mytable

API

Appelez la méthode tables.patch, puis définissez la propriété requirePartitionFilter sur true pour exiger des filtres de partitionnement. Étant donné que la méthode tables.update remplace l'intégralité de la ressource de table, la méthode tables.patch est à privilégier.

Java

Avant d'essayer cet exemple, suivez les instructions de configuration pour Java du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Java.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Table;

// Sample to update require partition filter on a table.
public class UpdateTableRequirePartitionFilter {

  public static void runUpdateTableRequirePartitionFilter() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    updateTableRequirePartitionFilter(datasetName, tableName);
  }

  public static void updateTableRequirePartitionFilter(String datasetName, String tableName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      Table table = bigquery.getTable(datasetName, tableName);
      table.toBuilder().setRequirePartitionFilter(true).build().update();

      System.out.println("Table require partition filter updated successfully");
    } catch (BigQueryException e) {
      System.out.println("Table require partition filter was not updated \n" + e.toString());
    }
  }
}

Copier une table partitionnée

Le processus de copie d'une table partitionnée est identique à la procédure à suivre pour copier une table standard. Pour plus d'informations, consultez la section Copier une table.

Lorsque vous copiez une table partitionnée, prenez en compte les points suivants :

• Copier une table partitionnée dans une nouvelle table de destination

  Toutes les informations de partitionnement sont copiées avec la table. La nouvelle table et l'ancienne table auront des partitions identiques.

• Copier une table non partitionnée dans une table partitionnée existante

  Cette opération n'est possible que pour le partitionnement par date d'ingestion. BigQuery copie les données sources dans la partition qui correspond à la date actuelle. Cette opération n'est pas possible pour les tables partitionnées suivant des colonnes d'unités de temps ou suivant des plages d'entiers.

• Copier une table partitionnée dans une autre table partitionnée

  Les spécifications de partition pour les tables sources et de destination doivent correspondre.

• Copier une table partitionnée dans une table non partitionnée

  La table de destination reste non partitionnée.

• Copier plusieurs tables partitionnées

  Si vous copiez plusieurs tables sources dans une table partitionnée lors de la même tâche, les tables sources ne peuvent pas contenir à la fois des tables partitionnées et non partitionnées.

  Si toutes les tables sources sont des tables partitionnées, leurs spécifications de partition doivent correspondre à la spécification de partition de la table de destination.

Lorsque vous effectuez une copie dans une table existante, vous pouvez spécifier si vous souhaitez y ajouter les données ou écraser la table de destination.

Copier des partitions individuelles

Vous pouvez copier les données d'une ou de plusieurs partitions dans une autre table.

Remarque : Les autorisations requises sont les mêmes que pour la copie d'une table.

Console

La copie de partitions n'est pas possible dans la console Google Cloud.

bq

Pour copier une partition, exécutez la commande bq cp (copier) de l'outil de ligne de commande bq avec un décorateur de partition ($date), par exemple $20160201.

Les paramètres facultatifs peuvent servir à contrôler la disposition en écriture de la partition de destination :

• -a ou --append_table ajoute les données de la partition source à une table ou une partition existante de l'ensemble de données de destination.
• -f ou --force écrase une table ou une partition existante dans l'ensemble de données de destination, et ne demande pas de confirmation.
• -n ou --no_clobber renvoie le message d'erreur suivant si la table ou la partition existe déjà dans l'ensemble de données de destination : Table '<var>project_id:dataset.table</var> or <var>table$date</var>' already exists, skipping. Si -n n'est pas spécifié, le comportement par défaut consiste à vous demander de choisir de remplacer la table ou la partition de destination.
• --destination_kms_key est la clé Cloud KMS gérée par le client et utilisée pour chiffrer la partition ou la table de destination.

La commande cp ne fonctionne pas avec les options --time_partitioning_field ou --time_partitioning_type. Sachez également qu'une tâche de copie ne permet pas de convertir une table partitionnée par temps d'ingestion en table partitionnée.

--destination_kms_key n'est pas présenté ici. Pour en savoir plus, consultez la page Protéger des données avec des clés Cloud KMS.

Si l'ensemble de données source ou de destination se trouve dans un projet autre que votre projet par défaut, ajoutez l'ID du projet aux noms des ensembles de données en respectant le format suivant : project_id:dataset.

(Facultatif) Spécifiez l'option --location et définissez la valeur correspondant à votre emplacement.

bq --location=location cp \
-a -f -n \
project_id:dataset.source_table$source_partition \
project_id:dataset.destination_table$destination_partition

Où :

• location est le nom du site. L'option --location est facultative. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, vous pouvez définir la valeur de l'option sur asia-northeast1. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc.
• project_id est l'ID de votre projet.
• dataset est le nom de l'ensemble de données source ou de destination.
• source_table est la table que vous copiez.
• source_partition est le décorateur de partition de la partition source.
• destination_table est le nom de la table dans l'ensemble de données de destination.
• destination_partition est le décorateur de partition dans la partition de destination.

Exemples :

Remarque : Le séparateur du décorateur de partition ($) est une variable spéciale de l'interface système Unix. Vous devrez peut-être échapper le décorateur si vous utilisez l'outil de ligne de commande. Dans les exemples suivants, le décorateur de partition est échappé : mydataset.table\$20160519, 'mydataset.table$20160519'.

Copier une partition dans une nouvelle table

Saisissez la commande suivante pour copier la partition du 30 janvier 2018 de mydataset.mytable vers une nouvelle table (mydataset.mytable2). mydataset se trouve dans votre projet par défaut.

bq cp -a 'mydataset.mytable$20180130' mydataset.mytable2

Copier une partition dans une table non partitionnée

Saisissez la commande suivante pour copier la partition du 30 janvier 2018 de mydataset.mytable vers une table non partitionnée (mydataset2.mytable2). Le raccourci -a permet d'ajouter les données de la partition à la table de destination non partitionnée. Les deux ensembles de données sont dans votre projet par défaut.

bq cp -a 'mydataset.mytable$20180130' mydataset2.mytable2

Saisissez la commande suivante pour copier la partition du 30 janvier 2018 de mydataset.mytable vers une table non partitionnée (mydataset2.mytable2). Le raccourci -f permet d'écraser la table de destination non partitionnée sans avoir recours à une invite.

bq --location=US cp -f 'mydataset.mytable$20180130' mydataset2.mytable2

Copier une partition dans une autre table partitionnée

Saisissez la commande suivante pour copier la partition du 30 janvier 2018 de mydataset.mytable vers une autre table partitionnée (mydataset2.mytable2). Le raccourci -a permet d'ajouter les données de la partition à la table de destination. Comme aucun décorateur de partition n'est spécifié dans la table de destination, la clé de la partition source est conservée et les données sont copiées dans la partition du 30 janvier 2018 de la table de destination. Vous pouvez également spécifier un décorateur de partition dans la table de destination pour copier les données vers une partition spécifique. mydataset se trouve dans votre projet par défaut. mydataset2 se trouve dans myotherproject, et non dans votre projet par défaut.

bq --location=US cp \
-a \
'mydataset.mytable$20180130' \
myotherproject:mydataset2.mytable2

Saisissez la commande suivante pour copier la partition du 30 janvier 2018 de mydataset.mytable vers la partition du 20 février 2018 d'une autre table partitionnée (mydataset2.mytable2). Le raccourci -f permet d'écraser la partition du 20 février 2018 dans la table de destination sans avoir recours à une invite. Si aucun décorateur de partition n'est utilisé, toutes les données de la table de destination sont écrasées. mydataset se trouve dans votre projet par défaut. mydataset2 se trouve dans myotherproject, et non dans votre projet par défaut.

bq cp \
-f \
'mydataset.mytable$20180130' \
'myotherproject:mydataset2.mytable2$20180220'

Saisissez la commande suivante pour copier la partition du 30 janvier 2018 de mydataset.mytable vers une autre table partitionnée (mydataset2.mytable2). mydataset se trouve dans votre projet par défaut. mydataset2 se trouve dans myotherproject, et non dans votre projet par défaut. Si la table de destination contient des données, le comportement par défaut consiste à vous demander de les écraser.

bq cp \
'mydataset.mytable$20180130' \
myotherproject:mydataset2.mytable2

Remarque : La commande bq cp dotée d'un décorateur de partition s'applique aux partitions basées sur des colonnes, dans lesquelles la partition source et la partition de destination sont identiques. La commande bq cp fonctionne également sur les partitions basées sur la date d'ingestion, lorsque la partition représente la même unité de temps ou une unité de temps précise contenant la partition source. Par exemple, si le décorateur de partition source est $20180130, les décorateurs de partition de destination valides incluent $20180130, $201801 et $2018. Pour copier une partition basée sur des colonnes dans un décorateur de partition complètement différent ou sur une partition basée sur une unité de temps plus précise, utilisez une instruction INSERT SELECT.

Pour copier plusieurs partitions, spécifiez-les sous la forme d'une liste d'éléments séparés par une virgule :

bq cp \
'mydataset.mytable$20180130,mydataset.mytable$20180131' \
myotherproject:mydataset.mytable2

API

Appelez la méthode jobs.insert et configurez une tâche de copie (copy). (Facultatif) Spécifiez votre région dans la propriété location de la section jobReference de la ressource de tâche.

Spécifiez les propriétés suivantes dans la configuration de votre tâche :

• Indiquez la partition, la table et l'ensemble de données sources dans la propriété sourceTables.
• Indiquez la table et l'ensemble de données de destination dans la propriété destinationTable.
• Avec la propriété writeDisposition, indiquez si les données doivent être ajoutées dans la table ou la partition de destination, ou bien si celle-ci doit être écrasée.

Pour copier plusieurs partitions, saisissez les partitions sources (y compris le nom de l'ensemble de données et de la table) dans la propriété sourceTables.

Supprimer une partition

Vous pouvez supprimer une partition individuelle d'une table partitionnée. Toutefois, vous ne pouvez pas supprimer les partitions spéciales __NULL__ ou __UNPARTITIONED__.

Actuellement, vous ne pouvez supprimer qu'une partition à la fois.

Remarque : Les autorisations requises sont les mêmes que pour la suppression d'une table.

Vous pouvez supprimer une partition en spécifiant le décorateur de la partition, sauf s'il s'agit d'une des deux partitions spéciales.

Pour supprimer une partition dans une table partitionnée, procédez comme suit :

Console

La suppression de partitions n'est pas possible dans la console Google Cloud.

SQL

Si une instruction DELETE qualificative couvre toutes les lignes d'une partition, BigQuery supprime la partition entière. Cette suppression s'effectue sans analyser d'octets ni consommer d'emplacements. L'exemple d'instruction DELETE suivant couvre la totalité de la partition d'un filtre dans la pseudo-colonne _PARTITIONDATE :

1. Dans la console Google Cloud, accédez à la page BigQuery.

   Accéder à BigQuery

2. Dans l'éditeur de requête, saisissez l'instruction suivante :

   DELETE mydataset.mytable
   WHERE _PARTITIONDATE IN ('2076-10-07', '2076-03-06');

3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

bq

Exécutez la commande bq rm avec l'option --table (ou le raccourci -t), puis spécifiez le décorateur de partition pour supprimer une partition spécifique.

bq rm --table project_id:dataset.table$partition

Où :

• project_id est l'ID de votre projet. S'il est omis, votre projet par défaut est utilisé.
• dataset est le nom de l'ensemble de données contenant la table.
• table est le nom de la table.
• partition est le décorateur de partition de la partition que vous supprimez.

Les décorateurs de partition ont le format suivant, en fonction du type de partitionnement :

• Partition horaire : yyyymmddhh. Exemple : $2016030100.
• Partition quotidienne : yyyymmdd. Exemple : $20160301.
• Partition mensuelle : yyyymm. Exemple : $201603.
• Partition annuelle : yyyy. Exemple : $2016.
• Partition par plages d'entiers : début de la plage de partition. Exemple : $20.

L'outil de ligne de commande bq vous invite à confirmer l'action. Pour ignorer la confirmation, utilisez l'option --force (ou le raccourci -f).

Remarque : Le séparateur du décorateur de partition ($) est une variable spéciale de l'interface système Unix. Vous devrez peut-être échapper le décorateur si vous utilisez l'outil de ligne de commande. Dans les exemples suivants, le décorateur de partition est échappé : mydataset.table\$20160519, 'mydataset.table$20160519'.

Exemples :

Supprimez la partition du 1er mars 2016 dans une table partitionnée par jour nommée mydataset.mytable dans votre projet par défaut :

bq rm --table 'mydataset.mytable$20160301'

Supprimez la partition de mars 2016 dans une table partitionnée par mois :

bq rm --table 'mydataset.mytable$201603'

Supprimez la plage d'entiers commençant par 20 dans une table partitionnée par plages d'entiers nommée mydataset.mytable :

bq rm --table 'mydataset.mytable$20'

API

Appelez la méthode tables.delete, puis spécifiez le décorateur de table et de partition à l'aide du paramètre tableId.

Sécurité des tables partitionnées

Le contrôle d'accès des tables partitionnées est identique à celui des tables standards. Pour plus d'informations, consultez la page Présentation des contrôles d'accès aux tables.