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 utiliser la détection automatique de schéma pour les formats de données acceptés, ou vous pouvez spécifier le schéma.

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

Parcourir les données d'une table

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

  • En utilisant la console GCP ou l'interface utilisateur Web classique de BigQuery
  • En utilisant la commande bq head de l'outil de ligne de commande
  • En appelant la méthode d'API tabledata.list

Autorisations requises

Pour parcourir les données d'une table au niveau de l'ensemble de données, il est nécessaire de disposer d'un accès READER à l'ensemble de données contenant la table partitionnée en question.

Au lieu des autorisations définies au niveau de l'ensemble de données, vous pouvez utiliser un rôle IAM qui inclut les autorisations bigquery.tables.getData. 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.

En outre, étant donné que le rôle bigquery.user possède les autorisations bigquery.datasets.create, un utilisateur disposant du rôle bigquery.user peut lire les données de toutes les tables dans tous les ensembles de données qu'il crée. Lorsqu'un utilisateur détenant le rôle bigquery.user crée un ensemble de données, il bénéficie d'un accès OWNER à cet ensemble si aucun autre propriétaire n'est spécifié. Un accès OWNER à un ensemble de données confère à l'utilisateur un contrôle total sur cet ensemble et sur toutes les tables qu'il contient.

Pour plus d'informations sur les rôles et les autorisations IAM dans BigQuery, consultez la page 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.

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

Pour parcourir les données d'une table partitionnée, procédez comme décrit ci-dessous.

Interface utilisateur classique

  1. 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 cet ensemble de données s'affichent.

  2. Cliquez sur une table partitionnée dans la liste. Vous ne pouvez pas prévisualiser des partitions individuelles à l'aide de l'interface Web de BigQuery.

  3. Cliquez sur Details (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.

  4. Cliquez sur Preview (Aperçu). Un exemple d'ensemble de données s'affiche.

    Aperçu d'une table

Ligne de commande

Exécutez la commande bq head avec l'indicateur --max_rows pour répertorier tous les champs présents dans un certain nombre de lignes de la table. Si --max_rows n'est pas spécifié, 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'indicateur --selected_fields et spécifiez les champs sous forme de liste en les séparant par une virgule.

Pour spécifier le nombre de lignes à ignorer avant d'afficher les données de la table, utilisez l'indicateur --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 d'extraire les informations de 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 utilisant 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 de votre 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 ci-dessous pour répertorier tous les champs des 10 premières lignes de mydataset.mytable dans la partition "2018-02-24". mydataset se trouve dans votre projet par défaut.

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

Saisissez la commande ci-dessous 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 ci-dessous pour afficher uniquement les champs field1 et field2 de mydataset.mytable dans la partition "2016-09-01". La commande utilise l'indicateur --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

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

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

  • maxResults : nombre maximum 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

Après avoir chargé vos données dans BigQuery, vous pouvez interroger les données de vos tables. BigQuery accepte les deux types de requêtes suivants :

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 :

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 y ajouter des données, vous devez disposer d'un accès WRITER au niveau de l'ensemble de données ou détenir un rôle IAM au niveau du projet qui comprend les autorisations bigquery.tables.updateData. Les rôles IAM prédéfinis au niveau du projet qui sont présentés ci-dessous incluent les autorisations bigquery.tables.updateData.

En outre, comme le rôle bigquery.user dispose d'autorisations bigquery.datasets.create, un utilisateur ayant le rôle bigquery.user peut écraser ou ajouter des données dans toutes les partitions de toutes les tables qu'il crée dans l'ensemble de données. Lorsqu'un utilisateur détenant le rôle bigquery.user crée un ensemble de données, il bénéficie d'un accès OWNER à cet ensemble si aucun autre propriétaire n'est spécifié. L'accès OWNER à un ensemble de données confère à l'utilisateur un contrôle total sur l'ensemble de données, sur toutes les tables qu'il contient et sur toutes les partitions de ces tables.

Vous devez également disposer d'autorisations bigquery.jobs.create pour pouvoir exécuter des tâches de chargement ou de requête. Les rôles IAM prédéfinis au niveau du projet qui sont présentés ci-dessous incluent les autorisations bigquery.jobs.create.

Pour plus d'informations sur les rôles et les autorisations IAM dans BigQuery, consultez la page 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.

Utiliser une tâche de chargement

Vous pouvez ajouter ou écraser des partitions à l'aide de la commande bq load ou en appelant la méthode jobs.insert et en configurant une tâche de chargement. La console GCP et l'interface utilisateur Web classique de BigQuery n'acceptent pas l'ajout ni l'écrasement d'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 sur 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. Le bucket Cloud Storage et l'ensemble de données BigQuery ont été créés dans la région asia-northeast1.

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

Pour savoir comment ajouter ou écraser des données à l'aide d'une tâche de chargement, consultez la documentation correspondant au format de vos données sources :

Utiliser une tâche de requête

Vous pouvez ajouter ou écraser des partitions à l'aide de la commande bq query ou en appelant la méthode jobs.insert et en configurant une tâche de requête. La console GCP et l'interface utilisateur Web classique de BigQuery n'acceptent pas l'ajout ni l'écrasement d'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 la table1 avec les résultats de la requête. La table de destination et la table interrogée se trouvent toutes les deux dans la zone multirégionale US :

bq --location=US 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][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 :

  • En utilisant la console GCP ou l'interface utilisateur Web classique de BigQuery
  • En utilisant la commande bq cp de l'outil de ligne de commande
  • En appelant la méthode d'API jobs.insert et en configurant une tâche de copie

Pour plus d'informations sur la copie de tables, consultez la page Copier une table.

Vous pouvez copier une ou plusieurs partitions à l'aide de la commande bq cp de l'outil de ligne de commande, ou en appelant la méthode d'API jobs.insert et en configurant une tâche de copie. La copie de partitions n'est actuellement pas possible dans la console GCP ou dans l'interface utilisateur 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 diffuser 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 diffusez les données. Par exemple, la commande ci-dessous permet de diffuser 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 est utilisée pour 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 d'API tabledata.insertAll. Pour plus d'informations sur l'insertion de données en flux continu dans des partitions, consultez la section 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 ces plages de dates autorisées, 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 insérez des données en flux continu, chaque partition dispose d'un tampon d'insertion. Ce tampon d'insertion 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, vérifiez qu'il est vide en appelant tables.get dans la partition.

Exporter des données de table

La procédure d'exportation de données à partir d'une table partitionnée est identique à l'exportation de données à partir d'une table non partitionnée. Actuellement, il n'est pas possible d'exporter des partitions individuelles. Pour en savoir plus, consultez la page Exporter des données de table.

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 un ordinateur local. Toutefois, vous pouvez télécharger et enregistrer les résultats de requête à l'aide de la console GCP ou de l'interface utilisateur Web classique de BigQuery.

Étapes suivantes

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

Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

Cette page
Commentaires sur la documentation
BigQuery
Commentaires sur le produit
Besoin d'aide ? Consultez notre page d'assistance.