Gérer les données de tables partitionnées

Ce document explique comment gérer les données de tables partitionnées dans BigQuery. Vous pouvez effectuer les opérations suivantes sur ces données :

Charger des données dans une table partitionnée
Parcourir (ou prévisualiser) les données d'une table partitionnée
Interroger les données d'une table partitionnée
Ajouter ou écraser des données dans une table partitionnée
Modifier les données d'une table partitionnée à l'aide des instructions DML (langage de manipulation de données)
Copier les données d'une table partitionnée
Diffuser des données en flux continu dans des tables partitionnées
Exporter les données d'une table partitionnée

Pour en savoir plus sur la gestion des schémas de table, consultez la page Modifier des schémas de table.

Charger des données dans une table partitionnée

Vous pouvez créer une table partitionnée lorsque vous chargez des données ou créer une table partitionnée vide et charger les données ultérieurement. Lorsque vous chargez des données dans une table partitionnée, vous pouvez spécifier le schéma ou utiliser la détection automatique de schéma pour les formats de données acceptés.

Pour plus d'informations sur le chargement des données, consultez la documentation correspondant au format et à la zone de vos données source :

Pour plus d'informations sur le chargement de données dans une table partitionnée à partir de Cloud Storage, consultez :
Pour plus d'informations sur le chargement de données dans une table partitionnée à partir d'une source locale, consultez la page Charger des données dans BigQuery à partir d'une source de données locale.

Java

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Java décrite dans le 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 en langage Java.

Afficher sur GitHub Commentaires

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TimePartitioning;
import java.time.Duration;
import java.time.temporal.ChronoUnit;
import java.util.UUID;

public class LoadPartitionedTable {

  public static void runLoadPartitionedTable() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "/path/to/file.csv";
    loadPartitionedTable(datasetName, tableName, sourceUri);
  }

  public static void loadPartitionedTable(String datasetName, String tableName, String sourceUri)
      throws Exception {
    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();

      TableId tableId = TableId.of(datasetName, tableName);

      Schema schema =
          Schema.of(
              Field.of("name", StandardSQLTypeName.STRING),
              Field.of("post_abbr", StandardSQLTypeName.STRING),
              Field.of("date", StandardSQLTypeName.DATE));

      // Configure time partitioning. For full list of options, see:
      // https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#TimePartitioning
      TimePartitioning partitioning =
          TimePartitioning.newBuilder(TimePartitioning.Type.DAY)
              .setField("date")
              .setExpirationMs(Duration.of(90, ChronoUnit.DAYS).toMillis())
              .build();

      LoadJobConfiguration loadJobConfig =
          LoadJobConfiguration.builder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.csv())
              .setSchema(schema)
              .setTimePartitioning(partitioning)
              .build();

      // Create a job ID so that we can safely retry.
      JobId jobId = JobId.of(UUID.randomUUID().toString());
      Job loadJob = bigquery.create(JobInfo.newBuilder(loadJobConfig).setJobId(jobId).build());

      // Load data from a GCS parquet file into the table
      // Blocks until this load table job completes its execution, either failing or succeeding.
      Job completedJob = loadJob.waitFor();

      // Check for errors
      if (completedJob == null) {
        throw new Exception("Job not executed since it no longer exists.");
      } else if (completedJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(
            "BigQuery was unable to load into the table due to an error: \n"
                + loadJob.getStatus().getError());
      }
      System.out.println("Data successfully loaded into time partitioned table during load job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println(
          "Data not loaded into time partitioned table during load job \n" + e.toString());
    }
  }
}

Parcourir les données d'une table

Vous pouvez parcourir les données d'une table partitionnée des manières suivantes :

utiliser Cloud Console ou l'UI Web classique de BigQuery ;
En utilisant la commande bq head de l'outil de ligne de commande
En appelant la méthode API tabledata.list
En utilisant les bibliothèques clientes

Autorisations requises

Pour parcourir les données d'une table ou d'une partition, vous devez disposer au minimum des autorisations bigquery.tables.getData. Les rôles Cloud IAM prédéfinis suivants incluent les autorisations bigquery.tables.getData :

bigquery.dataViewer
bigquery.dataEditor
bigquery.dataOwner
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 de parcourir les données des tables et des partitions dans l'ensemble de données.

Pour en savoir plus sur les rôles et les autorisations Cloud IAM dans BigQuery, consultez la page Rôles et autorisations prédéfinis.

Parcourir les données d'une table partitionnée

Pour parcourir les données d'une table partitionnée, procédez comme suit.

Console

Dans le volet de navigation de Cloud Console, cliquez sur l'ensemble de données pour répertorier ses tables et ses vues.
Cliquez sur une table partitionnée dans la liste.
Cliquez sur l'onglet Details (Détails).
Notez la valeur de Number of Rows (Nombre de lignes). Vous aurez peut-être besoin de cette valeur pour contrôler le point de départ de vos résultats à l'aide de l'interface de ligne de commande ou de l'API.
Cliquez sur l'onglet Preview (Aperçu). Un échantillon de données s'affiche. Notez que vous ne pouvez pas prévisualiser des partitions individuelles à l'aide de Cloud Console.

UI classique

Dans le volet de navigation de l'interface utilisateur Web de BigQuery, cliquez sur la flèche bleue située à gauche de l'ensemble de données pour le développer ou double-cliquez sur le nom de l'ensemble de données. Les tables et les vues de l'ensemble de données s'affichent.
Cliquez sur une table partitionnée dans la liste.
Cliquez sur Détails et notez la valeur indiquée pour le Nombre de lignes. Vous aurez peut-être besoin de cette valeur pour contrôler le point de départ de vos résultats à l'aide de l'interface de ligne de commande ou de l'API.
Cliquez sur Preview (Aperçu). Un échantillon de données s'affiche. Notez que vous ne pouvez pas prévisualiser des partitions individuelles à l'aide de l'UI Web de BigQuery.

CLI

Exécutez la commande bq head avec l'option --max_rows pour répertorier tous les champs présents dans un certain nombre de lignes de la table. Si l'option --max_rows n'est pas spécifiée, la valeur par défaut est 100. Spécifiez une partition à parcourir à l'aide du décorateur de partition, par exemple, $20180224.

Étant donné que la commande bq head ne crée pas de tâche de requête, elle n'apparaît pas dans l'historique des requêtes. Les commandes bq head ne vous sont donc pas facturées.

Pour parcourir un sous-ensemble de champs de la table (y compris les champs imbriqués et répétés), utilisez l'option --selected_fields et renseignez les champs sous forme de liste d'éléments séparés par une virgule.

Pour spécifier le nombre de lignes à ignorer avant d'afficher les données de la table, utilisez l'option --start_row=integer (ou le raccourci -s). La valeur par défaut est 0. Vous pouvez récupérer le nombre de lignes d'une table en utilisant la commande bq show afin de récupérer les informations sur la table.

Si la table que vous parcourez se trouve dans un projet autre que celui par défaut, ajoutez l'ID du projet à la commande, en respectant le format suivant : project_id:dataset.table.

bq head \
--max_rows integer1 \
--start_row integer2 \
--selected_fields "fields" \
project_id:dataset.table$partition

Où :

integer1 correspond au nombre de lignes à afficher.
integer2 correspond au nombre de lignes à ignorer avant d'afficher les données.
fields est une liste de champs séparés par une virgule.
project_id est l'ID du projet.
dataset est le nom de l'ensemble de données contenant la table.
table est le nom de la table à parcourir.
$partition est le décorateur de partition.

Exemples :

Saisissez la commande suivante pour répertorier tous les champs des 10 premières lignes de mydataset.mytable dans la partition "2018-02-24". mydataset se trouve dans le projet par défaut.

bq head --max_rows=10 mydataset.mytable$20180224

Saisissez la commande suivante pour répertorier tous les champs des 100 premières lignes de mydataset.mytable dans la partition "2016-09-01". mydataset se trouve dans myotherproject, et non dans votre projet par défaut.

bq head --format=prettyjson 'mydataset.mycolumntable2$20160901'

Saisissez la commande suivante pour n'afficher que field1 et field2 de mydataset.mytable dans la partition "2016-09-01". Elle utilise l'option - -start_row pour passer directement à la ligne 100. mydataset.mytable se trouve dans votre projet par défaut.

bq head \
--format=prettyjson \
--start_row 100 \
--selected_fields "state_number" \
'mydataset.mycolumntable2$20160901'

API

Vous pouvez parcourir les données d'une table en appelant tabledata.list. Indiquez le nom du décorateur de partition et de table dans le paramètre tableId.

Configurez les paramètres facultatifs ci-dessous pour contrôler la sortie.

maxResults : nombre maximal de résultats à renvoyer.
selectedFields : liste de champs à renvoyer, séparés par une virgule. Si ce paramètre n'est pas spécifié, tous les champs sont renvoyés.
startIndex : index basé sur zéro de la première ligne à lire.

Les valeurs renvoyées sont encapsulées dans un objet JSON que vous devez analyser, comme décrit dans la documentation de référence de tabledata.list.

Interroger les données d'une table partitionnée

Une fois chargées dans BigQuery, les données peuvent être interrogées dans des tables. BigQuery accepte les deux types de requêtes suivants :

Requêtes interactives
Requêtes par lot

Par défaut, BigQuery exécute des requêtes interactives, ce qui signifie que ces dernières sont exécutées dès que possible.

Le service BigQuery permet également d'exécuter des requêtes par lot. Il met en file d'attente chaque requête par lot en votre nom et en lance une dès que des ressources inactives sont disponibles, généralement en quelques minutes.

Vous pouvez exécuter des requêtes interactives et par lot en utilisant les éléments suivants :

Éditeur de requêtes dans Cloud Console
L'option Saisir une requête de l'UI Web de BigQuery
La commande bq query de l'outil de ligne de commande BigQuery
L'API REST BigQuery pour appeler de façon automatisée les méthodes jobs.query ou jobs.insert de type requête
Les bibliothèques clientes BigQuery

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

Ajouter ou écraser des données dans une table partitionnée

Vous pouvez écraser les données d'une table partitionnée en effectuant une opération de chargement ou de requête. Vous avez la possibilité d'inclure des données dans une table partitionnée existante en exécutant un chargement avec ajout ou en y ajoutant les résultats d'une requête.

Autorisations requises

Pour écraser une partition existante ou lui ajouter des données, vous devez au minimum disposer des autorisations suivantes :

bigquery.tables.create
bigquery.tables.updateData
bigquery.jobs.create

Des autorisations supplémentaires, par exemple bigquery.tables.getData, peuvent être nécessaires pour accéder aux données que vous souhaitez ajouter ou écraser.

Les rôles Cloud IAM prédéfinis suivants incluent les autorisations bigquery.tables.updateData et bigquery.tables.create :

bigquery.dataEditor
bigquery.dataOwner
bigquery.admin

Les rôles Cloud IAM prédéfinis suivants incluent les autorisations bigquery.jobs.create :

bigquery.user
bigquery.jobUser
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'ajouter et d'écraser des données dans des tables et des partitions dans l'ensemble de données.

Pour en savoir plus sur les rôles et les autorisations Cloud IAM dans BigQuery, consultez la page Rôles et autorisations prédéfinis.

Utiliser une tâche de chargement

Vous pouvez ajouter ou écraser des données dans des partitions en exécutant la commande bq load, ou en appelant la méthode jobs.insert et en configurant une tâche de chargement (load). Cloud Console et l'UI Web classique de BigQuery ne permettent pas d'ajouter ni d'écraser des données dans une partition dans le cadre d'une tâche de chargement.

Lorsque vous ajoutez ou écrasez des données dans une partition spécifique à l'aide d'une tâche de chargement, prenez en compte les points suivants.

Lorsque vous chargez des données à partir de Cloud Storage, le bucket doit se trouver dans le même emplacement que l'ensemble de données BigQuery.
Les données que vous chargez doivent être conformes au schéma de partitionnement de la table. Toutes les lignes écrites dans la partition doivent avoir des valeurs comprises dans la date de la partition.
Étant donné qu'une table partitionnée et ses partitions partagent le même schéma, le remplacement des données d'une partition n'entraînera pas le remplacement du schéma de la table. C'est le schéma des nouvelles données qui doit être compatible avec le schéma de la table. Pour plus d'informations sur la mise à jour du schéma de la table pendant l'exécution de la tâche de chargement, consultez la page Gérer des schémas de table.
Lorsque vous ajoutez des données à une table partitionnée avec date d'ingestion, si vous ne spécifiez pas de décorateur de partition, la partition actuelle est utilisée.

Pour ajouter ou écraser des données dans une table partitionnée à l'aide d'une tâche de chargement, spécifiez une table de destination et un décorateur de partition, puis définissez l'indicateur de disposition d'écriture sur l'un des paramètres suivants :

Option CLI	Option API	Description
`--noreplace`	WRITE_APPEND	Ajoute les données à une partition existante. Si aucune option de disposition d'écriture n'est spécifiée, l'action par défaut consiste à ajouter les données à la partition.
`--replace`	WRITE_TRUNCATE	Écrase (remplace) une partition.

Un décorateur de partition représente une date spécifique et se présente sous la forme suivante :

$YYYYMMDD

Par exemple, la commande suivante remplace les données de la totalité de la partition pour la date du 1er janvier 2016 (20160101) dans une table partitionnée nommée mydataset.table1. Les données JSON sont chargées à partir d'un bucket Cloud Storage.

bq load \
--replace \
--source_format=NEWLINE_DELIMITED_JSON \
'mydataset.table1$20160101' \
gs://mybucket/myfile.json

Utiliser une tâche de requête

Vous pouvez ajouter ou écraser des données dans des partitions en exécutant la commande bq query, ou en appelant la méthode jobs.insert et en configurant une tâche de requête (query). Cloud Console et l'UI Web classique de BigQuery ne permettent pas d'ajouter ni d'écraser des données dans une partition dans le cadre d'une tâche de requête.

Lorsque vous utilisez une tâche de requête pour écraser une partition, prenez en compte les points suivants.

Les tables que vous interrogez doivent être dans le même emplacement que la table dans laquelle vous ajoutez ou écrasez des données.
Lorsque vous ajoutez ou écrasez une partition dans une table partitionnée avec date d'ingestion, vous pouvez utiliser une syntaxe en ancien SQL ou en SQL standard.
Lorsque vous ajoutez ou écrasez une partition dans une table partitionnée, votre requête doit utiliser la syntaxe SQL standard. Actuellement, l'ancien langage SQL ne permet pas d'interroger les tables partitionnées ni d'écrire des résultats de requête dans des tables partitionnées.
Lorsque des résultats de requête sont écrits dans une partition, les données en cours d'écriture doivent être conformes au schéma de partitionnement de la table. Toutes les lignes écrites sur la partition doivent avoir des valeurs comprises dans la date de la partition.
Lorsque vous ajoutez des données à une table partitionnée avec date d'ingestion, si vous ne spécifiez pas de décorateur de partition, la partition actuelle est utilisée.

Pour ajouter ou écraser des données dans une partition à l'aide de résultats de requête, spécifiez une table de destination et un décorateur de partition, puis définissez la disposition d'écriture sur l'un des paramètres suivants :

Option CLI	Option API	Description
`--append_table`	WRITE_APPEND	Ajoute les résultats de la requête à une partition existante.
`--replace`	WRITE_TRUNCATE	Écrase (remplace) une partition avec les résultats de la requête.

Par exemple, la commande suivante remplace les données de la partition du 1er mars 2016 (20160301) de table1 avec les résultats de la requête.

bq query \
--use_legacy_sql=false \
--replace \
--destination_table 'mydataset.table1$20160301' \
'SELECT
  column1,
  column2
FROM
  mydataset.mytable'

Si la table de destination existe et qu'elle n'est pas partitionnée, l'erreur suivante est renvoyée : BigQuery error in query operation: Error processing job 'project_id job_id' Incompatible table partitioning specification. Expects partitioning specification interval (type:day), but input partitioning specification is none`.

Pour savoir comment utiliser des résultats de requête pour ajouter ou écraser des données, consultez la section Écrire des résultats de requête.

Modifier les données d'une table partitionnée à l'aide d'instructions LMD

Vous pouvez modifier les données d'une table partitionnée à l'aide des instructions LMD dans le dialecte SQL standard. Les instructions LMD vous permettent d'effectuer des mises à jour, des insertions et des suppressions de lignes de façon groupée. Pour voir des exemples d'utilisation des instructions LMD avec des tables partitionnées, consultez la page Mettre à jour des données de tables partitionnées à l'aide des instructions LMD.

L'ancien dialecte SQL n'accepte pas les instructions LMD. Pour mettre à jour ou supprimer des données à l'aide de l'ancien SQL, vous devez supprimer la table partitionnée, puis la recréer avec de nouvelles données. Vous pouvez également rédiger une requête qui modifie les données et écrit les résultats de la requête dans une nouvelle table partitionnée.

Copier les données d'une table partitionnée

Vous pouvez copier une table partitionnée des manières suivantes :

utiliser Cloud Console ou l'UI Web classique de BigQuery ;
En utilisant la commande bq cp de l'outil de ligne de commande
En appelant la méthode API jobs.insert et en configurant une tâche de copie
En utilisant les bibliothèques clientes.

Pour en savoir plus sur la copie de tables, consultez la section Copier une table.

Vous pouvez copier une ou plusieurs partitions en exécutant la commande bq cp de l'outil de ligne de commande, ou en appelant la méthode API jobs.insert et en configurant une tâche de copie (copy). La copie de partitions n'est actuellement pas possible dans Cloud Console ou dans l'UI Web classique de BigQuery.

Pour plus d'informations sur la copie de partitions, consultez la page Copier des partitions.

Diffuser des données en flux continu dans des tables partitionnées

Pour insérer des données en flux continu dans une partition spécifique, utilisez le décorateur de partition lorsque vous spécifiez l'élément tableId de la table dans laquelle vous insérez les données. Par exemple, la commande suivante permet d'insérer en flux continu une seule ligne de la partition pour la date du 1er janvier 2017 ($20170101) dans une table partitionnée nommée mydataset.mytable :

echo '{"a":1, "b":2}' | bq insert 'mydataset.mytable$20170101'

Cette commande permet de démontrer l'utilisation du décorateur de partition. La commande bq insert est conçue à des fins de tests uniquement. Pour insérer des données en flux continu dans BigQuery, utilisez la méthode tabledata.insertAll de l'API. Pour plus d'informations sur l'insertion de données en flux continu dans des partitions, consultez la page Insérer des données en flux continu dans des tables partitionnées.

Lors de l'insertion en flux continu avec un décorateur de partition, vous pouvez insérer des données dans des partitions dans les 30 jours qui précédent et les 5 jours qui suivent la date actuelle (heure UTC). Pour écrire dans des partitions en dehors de cette plage de dates autorisée, vous pouvez utiliser des tâches de chargement ou de requête.

Si vous spécifiez une table partitionnée par date comme table de destination lorsque vous diffusez des données en continu, chaque partition dispose d'un tampon de diffusion en continu. Ce tampon d'insertion en flux continu est conservé lorsque vous effectuez une tâche de chargement, de requête ou de copie qui écrase une partition en définissant la propriété writeDisposition sur WRITE_TRUNCATE. Si vous souhaitez supprimer le tampon d'insertion en flux continu, vérifiez qu'il est vide en appelant tables.get sur la partition.

Exporter des données de table

La procédure d'exportation de l'intégralité des données à partir d'une table partitionnée est identique à l'exportation de données à partir d'une table non partitionnée. Pour en savoir plus, consultez la page Exporter des données de table. Pour exporter les données d'une partition individuelle, ajoutez le décorateur de partition $date au nom de la table. Par exemple, mytable$20160201.

Vous pouvez également exporter les données des partitions __NULL__ et __UNPARTITIONED__ en ajoutant les noms des partitions au nom de la table. Par exemple, mytable$__NULL__ ou mytable$__UNPARTITIONED__.

Les données d'une table partitionnée peuvent être exportées au format CSV, JSON ou Avro. Actuellement, vous devez exporter les données dans un bucket Cloud Storage. Il n'est pas possible de procéder à une exportation vers une machine locale. Toutefois, vous pouvez télécharger et enregistrer les résultats de requête à l'aide de Cloud Console ou de l'UI Web classique de BigQuery.

Étapes suivantes

Pour plus d'informations sur l'utilisation des tables partitionnées, consultez les pages suivantes :