Créer et utiliser des tables partitionnées par temps d'ingestion

Vous trouverez dans ce document la procédure à suivre pour créer et utiliser des tables partitionnées par temps d'ingestion dans BigQuery. Pour en savoir plus sur les tables partitionnées, consultez la page Créer et utiliser des tables partitionnées.

Après avoir créé une table partitionnée par temps d'ingestion, vous pouvez effectuer les opérations suivantes :

Contrôler l'accès aux données de votre table
Obtenir des informations sur vos tables partitionnées
Répertorier les tables partitionnées dans un ensemble de données
Obtenir des métadonnées de tables partitionnées à l'aide de métatables

Pour en savoir plus sur la gestion des tables partitionnées, y compris pour mettre à jour leurs propriétés, les copier et les supprimer, consultez la page Gérer des tables partitionnées.

Créer des tables partitionnées par temps d'ingestion

Vous pouvez créer une table partitionnée par temps d'ingestion dans BigQuery :

en utilisant une instruction DDL CREATE TABLE avec une clause partition_expression ;
manuellement, à l'aide de l'UI Web de BigQuery ou de la commande bq mk de l'outil de ligne de commande ;
de façon automatisée, en appelant la méthode d'API tables.insert ;
à partir des résultats d'une requête ;
lorsque vous chargez des données ;
en convertissant les tables segmentées par date en tables partitionnées.

Lorsque vous créez une table partitionnée par temps d'ingestion dans BigQuery, le nom de la table doit être unique dans son ensemble de données. Le nom de la table peut :

contenir jusqu'à 1 024 caractères ;
contenir des lettres (majuscules ou minuscules), des chiffres et des traits de soulignement.

Autorisations requises

Pour créer une table partitionnée par date d'ingestion, vous devez disposer d'un accès WRITER au niveau de l'ensemble de données ou d'un rôle IAM au niveau du projet qui inclut les autorisations bigquery.tables.create. Les rôles IAM prédéfinis au niveau du projet incluent les autorisations bigquery.tables.create :

En outre, comme le rôle bigquery.user dispose d'autorisations bigquery.datasets.create, un utilisateur ayant le rôle bigquery.user peut créer des tables partitionnées dans tous les ensembles de données qu'il génère. Lorsqu'un utilisateur ayant le rôle bigquery.user crée un ensemble de données, il bénéficie d'un accès OWNER à cet ensemble de données. 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 en savoir plus sur les autorisations et les rôles IAM dans BigQuery, consultez la page Contrôle des accès. Si vous souhaitez obtenir davantage d'informations sur les rôles au niveau des ensembles de données, consultez la section Rôles primitifs pour les ensembles de données.

Créer une table partitionnée par temps d'ingestion vide avec une définition de schéma

Lorsque vous créez une table partitionnée par temps d'ingestion vide avec une définition de schéma, vous pouvez :

saisir le schéma à l'aide de l'UI Web ;
fournir le schéma en ligne à l'aide de l'outil de ligne de commande ;
envoyer un fichier de schéma JSON à l'aide de l'outil de ligne de commande ;
fournir le schéma dans une ressource de table lorsque vous appelez la méthode tables.insert de l'API.

Pour en savoir plus sur la spécification d'un schéma de table, consultez la page Spécifier un schéma.

Après avoir créé la table partitionnée par temps d'ingestion, vous pouvez y effectuer les opérations suivantes :

Charger des données
Écrire les résultats de requêtes
Copier des données

Pour créer une table partitionnée par temps d'ingestion vide avec une définition de schéma, procédez comme suit :

UI Web

Cliquez sur la flèche vers le bas qui est située à côté du nom de votre ensemble de données dans la barre de navigation, puis sur Créer une table.
Dans la section Données source de la page Créer une table, cliquez sur Créer une table vide.
Dans la section Table de destination de la page Créer une table, procédez comme suit :
- Dans le champ Nom de la table, sélectionnez l'ensemble de données approprié, puis saisissez le nom de la table que vous créez.
- Vérifiez que le champ Type de table est défini sur Table native.
Dans la section Schéma, saisissez manuellement la définition du schéma.
- Vous pouvez saisir les informations du schéma manuellement en utilisant les méthodes suivantes :
  - En cliquant sur Modifier sous forme de texte, puis en saisissant le schéma de la table en tant que tableau JSON
    Remarque : Vous pouvez afficher le schéma d'une table existante au format JSON en saisissant la commande suivante : bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE].
  - En utilisant l'option Ajouter un champ pour saisir le schéma
Dans la section Options, procédez comme suit :
- Dans le champ Partitionnement, cliquez sur Aucun, puis sélectionnez Jour.
- Dans le champ Type de chiffrement, conservez l'option Default. Cette propriété est destinée aux clés de chiffrement gérées par le client. Par défaut, BigQuery chiffre les contenus client stockés au repos.
Cliquez sur Créer une table.

Une fois la table créée, vous pouvez mettre à jour sa date et son heure d'expiration, sa description et ses libellés. Vous ne pouvez pas ajouter de délai d'expiration de la partition après avoir créé une table à l'aide de l'UI Web de BigQuery.

Ligne de commande

Exécutez la commande mk avec les paramètres --table (ou le raccourci -t) et --time_partitioning_type=DAY. Actuellement, DAY est la seule valeur acceptée pour --time_partitioning_type. Vous pouvez fournir la définition du schéma de table intégrée ou un fichier de schéma JSON. Les paramètres facultatifs incluent --expiration, --description, --time_partitioning_expiration, --destination_kms_key et --label. Si vous créez une table dans un projet autre que votre projet par défaut, ajoutez l'ID du projet à l'ensemble de données au format suivant : [PROJECT_ID]:[DATASET].

--destination_kms_key n'est pas présenté ici. Pour en savoir plus sur l'utilisation de ce paramètre, consultez l'article relatif aux clés de chiffrement gérées par le client.

Saisissez la commande suivante pour créer une table partitionnée par temps d'ingestion vide avec une définition de schéma :

bq mk --table --expiration [INTEGER1] --time_partitioning_type=DAY --time_partitioning_expiration [INTEGER2] --description "[DESCRIPTION]" --label [KEY:VALUE, KEY:VALUE] [PROJECT_ID]:[DATASET].[TABLE] [SCHEMA]

où :

[INTEGER1] est la durée de vie par défaut (en secondes) de la table. La valeur minimale est de 3 600 secondes (une heure). Le délai d'expiration correspond à l'heure actuelle plus la valeur entière. Si vous définissez le délai d'expiration de la table lorsque vous créez une table partitionnée, le paramètre d'expiration de la table par défaut de l'ensemble de données est ignoré. La définition de cette valeur supprime la table et toutes les partitions après le délai indiqué.
[INTEGER2] est la durée de vie par défaut (en secondes) des partitions de la table. Aucune valeur minimale n'est requise. Le délai d'expiration correspond à la date de la partition plus la valeur entière. Le délai d'expiration de la partition est indépendant du délai d'expiration de la table, mais il ne l'ignore pas. Si vous définissez un délai d'expiration de partition plus long que celui de la table, le délai d'expiration de la table est prioritaire.
[DESCRIPTION] est une description de la table entre guillemets.
[KEY:VALUE] est la paire clé/valeur qui représente un libellé. Vous pouvez entrer plusieurs libellés en utilisant une liste séparée par des virgules.
[PROJECT_ID] correspond à l'ID du projet.
[DATASET] est un ensemble de données dans votre projet.
[TABLE] est le nom de la table partitionnée par temps d'ingestion que vous créez.
[SCHEMA] est une définition de schéma intégrée au format [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE] ou le chemin d'accès au fichier de schéma JSON sur votre machine locale.

Lorsque vous spécifiez le schéma depuis la ligne de commande, vous ne pouvez pas inclure de type RECORD (STRUCT) ni de description de colonne. Il est également impossible d'indiquer le mode de la colonne. Tous les modes sont définis sur NULLABLE par défaut. Pour inclure des descriptions, des modes et des types RECORD, il est recommandé de fournir un fichier de schéma JSON.

Exemples :

Saisissez la commande suivante pour créer une table partitionnée par temps d'ingestion nommée mytable dans mydataset au sein de votre projet par défaut. Le délai d'expiration du partitionnement est défini sur 259 200 secondes (trois jours), celui de la table sur 2 592 000 secondes (un mois de 30 jours), la description sur This is my time-partitioned table et le libellé sur organization:development. La commande utilise le raccourci -t au lieu de --table. Le schéma est spécifié de manière intégrée comme suit : qtr:STRING,sales:FLOAT,year:STRING.

bq mk -t --expiration 2592000 --time_partitioning_type=DAY --time_partitioning_expiration 259200 --description "This is my time-partitioned table" --label organization:development mydataset.mytable qtr:STRING,sales:FLOAT,year:STRING

Saisissez la commande suivante pour créer une table partitionnée par temps d'ingestion nommée mytable dans myotherproject, et non dans votre projet par défaut. Le délai d'expiration du partitionnement est défini sur 259 200 secondes (trois jours), la description sur This is my time-partitioned table et le libellé sur organization:development. La commande utilise le raccourci -t au lieu de --table. Cette commande ne spécifie pas de délai d'expiration de la table. Si l'ensemble de données a un délai d'expiration de table par défaut, ce dernier est appliqué. Si l'ensemble de données n'a pas de délai d'expiration de table par défaut, la table n'expirera jamais, mais les partitions expireront dans trois jours. Le chemin d'accès au fichier de schéma est le suivant : /tmp/myschema.json.

bq mk -t --time_partitioning_type=DAY --time_partitioning_expiration 259200 --description "This is my time-partitioned table" --label organization:development myotherproject:mydataset.mytable /tmp/myschema.json

Une fois la table créée, vous pouvez mettre à jour sa date et son heure d'expiration, sa description, ses libellés, ainsi que la date et l'heure d'expiration de la partition.

API

Appelez la méthode tables.insert avec une ressource de table définie qui spécifie les propriétés timePartitioning et schema.

Créer une table partitionnée par temps d'ingestion à partir d'un résultat de requête

Pour créer une table partitionnée à partir d'un résultat de requête, écrivez les résultats dans une nouvelle table de destination. Vous pouvez créer une table partitionnée en interrogeant une table partitionnée ou une table non partitionnée. En revanche, il n'est pas possible de modifier une table existante en table partitionnée avec des résultats de requêtes.

Pour en savoir plus sur l'interrogation des tables partitionnées par temps d'ingestion, consultez la page Interroger des tables partitionnées.

UI Web

Vous ne pouvez pas spécifier les options de partitionnement d'une table de destination lorsque vous interrogez des données à l'aide de l'UI Web de BigQuery.

CLI

Saisissez la commande bq query, spécifiez le paramètre --destination_table pour créer une table permanente en fonction des résultats de requêtes, et ajoutez le paramètre --time_partitioning_type=DAY pour créer une table de destination partitionnée par temps d'ingestion. Actuellement, DAY est la seule valeur acceptée pour --time_partitioning_type.

Spécifiez le paramètre use_legacy_sql=false pour utiliser la syntaxe SQL standard. Pour écrire les résultats des requêtes dans une table qui n'est pas dans votre projet par défaut, ajoutez l'ID du projet au nom de l'ensemble de données au format suivant : [PROJECT_ID]:[DATASET].

Définissez l'indicateur --location sur la valeur correspondant à votre emplacement.

Saisissez la commande suivante pour créer une table de destination partitionnée par temps d'ingestion à partir d'un résultat de requête :

    bq --location=[LOCATION] query --destination_table [PROJECT_ID]:[DATASET].[TABLE] --time_partitioning_type=DAY --use_legacy_sql=false '[QUERY]'

où :

[LOCATION] est le nom de votre zone. L'indicateur --location est facultatif. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, définissez l'indicateur sur la valeur asia-northeast1. Vous pouvez spécifier une valeur par défaut pour l'emplacement à l'aide du fichier .bigqueryrc.
[PROJECT_ID] correspond à l'ID du projet.
[DATASET] est le nom de l'ensemble de données qui contiendra la nouvelle table partitionnée par temps d'ingestion.
[TABLE] est le nom de la table partitionnée par temps d'ingestion que vous créez à l'aide des résultats de requêtes.
[QUERY] correspond à une requête en syntaxe SQL standard.

Exemples :

Saisissez la commande suivante pour écrire les résultats de requêtes dans une table de destination partitionnée par temps d'ingestion nommée mytable dans mydataset. L'ensemble de données se trouve dans votre projet par défaut. La requête récupère les données d'une table non partitionnée dans l'ensemble de données public USA Name Data.

bq --location=US query --destination_table mydataset.mytable --time_partitioning_type=DAY --use_legacy_sql=false 'SELECT name,number FROM `bigquery-public-data.usa_names.usa_1910_current` WHERE gender = "M" ORDER BY number DESC'

Saisissez la commande suivante pour écrire les résultats de requêtes dans une table de destination partitionnée par temps d'ingestion nommée mytable dans mydataset. L'ensemble de données se trouve dans myotherproject, et non dans votre projet par défaut. La requête récupère les données d'une table non partitionnée dans l'ensemble de données public USA Name Data.

bq --location=US query --destination_table myotherproject:mydataset.mytable --time_partitioning_type=DAY --use_legacy_sql=false 'SELECT name,number FROM `bigquery-public-data.usa_names.usa_1910_current` WHERE gender = "M" ORDER BY number DESC'

API

Pour enregistrer les résultats d'une requête dans une table permanente qui est partitionnée par date d'ingestion, appelez la méthode jobs.insert, configurez une tâche de type query, et définissez une valeur pour les propriétés configuration.query.destinationTable et timePartitioning.

Spécifiez votre emplacement dans la propriété location de la section jobReference de la ressource de tâche.

Créer une table partitionnée par temps d'ingestion lors du chargement des données

Vous pouvez créer une table partitionnée par temps d'ingestion en spécifiant les options de partitionnement lorsque vous chargez des données dans une nouvelle table. Vous n'avez pas besoin de créer une table partitionnée vide avant de charger des données. Vous pouvez créer la table partitionnée et charger vos données en même temps.

Lorsque vous chargez des données dans BigQuery, vous pouvez fournir le schéma de la table ou utiliser la détection automatique des schémas pour les formats de données compatibles.

Les décorateurs de partition vous permettent de charger des données dans une partition spécifique. Pour ajuster les fuseaux horaires, utilisez un décorateur de partition afin de charger les données dans une partition basée sur le fuseau horaire de votre choix. Par exemple, si vous êtes à l'heure normale du Pacifique (PST), chargez toutes les données générées le 1er mai 2016 PST dans la partition pour cette date, avec le décorateur de partition correspondant :

[TABLE_NAME]$20160501

Pour en savoir plus sur le chargement des données, consultez la page intitulée Présentation du chargement de données dans BigQuery.

Convertir des tables segmentées par date en tables partitionnées par temps d'ingestion

Si vous avez déjà créé des tables segmentées par date, vous pouvez convertir l'ensemble des tables associées en une seule table partitionnée par temps d'ingestion à l'aide de la commande partition dans l'outil de ligne de commande bq. Les tables segmentées par date doivent respecter la convention d'attribution de noms suivante : [TABLE]_YYYYMMDD. Exemples : mytable_20160101, …, mytable_20160331.

Les paramètres facultatifs incluent --time_partitioning_expiration et --time_partitioning_type. Étant donné que --time_partitioning_type=DAY est actuellement la seule valeur acceptée, ce paramètre est facultatif. Si vos tables sources ou votre table de destination se trouvent dans un projet autre que votre projet par défaut, ajoutez l'ID du projet au nom de l'ensemble de données au format suivant : [PROJECT_ID]:[DATASET].

Définissez l'indicateur --location sur la valeur correspondant à votre emplacement.

Saisissez la commande suivante pour convertir une série de tables segmentées par date en une seule table partitionnée par temps d'ingestion :

bq --location=[LOCATION] partition --time_partitioning_type=DAY --time_partitioning_expiration [INTEGER] [PROJECT_ID]:[DATASET].[SOURCE_TABLE]_ [PROJECT_ID]:[DATASET].[DESTINATION_TABLE]

où :

[LOCATION] est le nom de votre zone. L'indicateur --location est facultatif. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, définissez la valeur de l'indicateur sur asia-northeast1. Vous pouvez spécifier une valeur par défaut pour l'emplacement à l'aide du fichier .bigqueryrc.
[INTEGER] est la durée de vie par défaut (en secondes) des partitions de la table. Aucune valeur minimale n'est requise. Le délai d'expiration correspond à la date de la partition plus la valeur entière. Le délai d'expiration de la partition est indépendant du délai d'expiration de la table, mais il ne l'ignore pas. Si vous définissez un délai d'expiration de partition plus long que celui de la table, le délai d'expiration de la table est prioritaire.
[PROJECT_ID] correspond à l'ID du projet.
[DATASET] est un ensemble de données dans votre projet.
[SOURCE_TABLE]_ est le préfixe de votre table segmentée par date.
[DESTINATION_TABLE] est le nom de la table partitionnée que vous créez.

Exemples :

Saisissez la commande suivante pour créer une table partitionnée par temps d'ingestion nommée mytable_partitioned dans mydataset au sein de votre projet par défaut. Le délai d'expiration du partitionnement est défini sur 259 200 secondes (trois jours). Le nom des tables sources segmentées par date commence par sourcetable_. Les tables sources se trouvent également dans votre projet par défaut. mydataset a été créé dans la zone multirégionale US.

bq --location=US partition --time_partitioning_type=DAY --time_partitioning_expiration 259200 mydataset.sourcetable_ mydataset.mytable_partitioned

Lorsque vous exécutez la commande, une tâche de copie est créée pour générer des partitions à partir des tables segmentées. Par exemple, si vous aviez des tables segmentées par date nommées sourcetable_20180126 et sourcetable_20180127, la tâche de copie créerait les partitions suivantes : mydataset.mytable_partitioned$20180126 et mydataset.mytable_partitioned$20180127.

Saisissez la commande suivante pour créer une table partitionnée par temps d'ingestion nommée mytable_partitioned dans mydataset. mydataset se trouve dans le projet myotherproject, et non dans votre projet par défaut. Le délai d'expiration du partitionnement est défini sur 86 400 secondes (un jour). Le nom des tables sources segmentées par date commence par sourcetable_. Les tables sources se trouvent dans votre projet par défaut. mydataset a été créé dans la zone multirégionale US.

bq --location=US partition --time_partitioning_type=DAY --time_partitioning_expiration 86400 mydataset.sourcetable_ myotherproject:mydataset.mytable_partitioned

Saisissez la commande suivante pour créer une table partitionnée par temps d'ingestion nommée mytable_partitioned dans mydataset au sein de votre projet par défaut. mydataset a été créé dans la région asia-northeast1. Le délai d'expiration du partitionnement est défini sur 259 200 secondes (trois jours). Le nom des tables sources segmentées par date commence par sourcetable_. Les tables sources se trouvent également dans votre projet par défaut.

bq --location=asia-northeast1 partition --time_partitioning_type=DAY --time_partitioning_expiration 259200 mydataset.sourcetable_ mydataset.mytable_partitioned

Contrôler l'accès aux tables partitionnées par temps d'ingestion

Vous ne pouvez pas attribuer des contrôles d'accès directement au niveau des tables ou des partitions. Vous pouvez contrôler l'accès aux tables en configurant des contrôles d'accès au niveau de l'ensemble de données ou du projet.

Les contrôles d'accès au niveau d'un ensemble de données spécifient les opérations que les utilisateurs, les groupes et les comptes de service sont autorisés à effectuer sur les tables de cet ensemble de données. Si vous n'affectez que des autorisations au niveau de l'ensemble de données, vous devez également attribuer un rôle primitif ou prédéfini au niveau du projet qui permet d'accéder au projet, par exemple, bigquery.user.

Au lieu d'accorder l'accès à des ensembles de données individuels, vous pouvez affecter des rôles IAM prédéfinis au niveau du projet qui accordent des autorisations à toutes les données des tables de tous les ensembles de données d'un projet.

Vous pouvez également créer des rôles personnalisés IAM. Si vous créez un rôle personnalisé, les autorisations que vous accordez dépendent des opérations de table que vous souhaitez que l'utilisateur, le groupe ou le compte de service puisse effectuer.

Pour plus d'informations sur les rôles et les autorisations, consultez les pages suivantes :

Comprendre les rôles dans la documentation IAM
Contrôles des accès dans BigQuery
Attribuer des contrôles d'accès aux ensembles de données

Utiliser des tables partitionnées par temps d'ingestion

Obtenir des informations sur les tables partitionnées par temps d'ingestion

Vous pouvez obtenir des informations sur les tables à l'aide de l'interface utilisateur Web de BigQuery ou de la commande bq show de l'interface de ligne de commande, ou bien en appelant la méthode d'API tables.get.

Autorisations requises

Pour obtenir des informations sur les tables, vous devez disposer du rôle READER au niveau de l'ensemble de données ou d'un rôle IAM au niveau du projet qui inclut les autorisations bigquery.tables.get. Si vous disposez des autorisations bigquery.tables.get au niveau du projet, vous pouvez obtenir des informations sur toutes les tables du projet. Tous les rôles IAM prédéfinis au niveau du projet incluent les autorisations bigquery.tables.get, à l'exception de bigquery.jobUser et bigquery.user.

En outre, comme le rôle bigquery.user dispose des autorisations bigquery.datasets.create, un utilisateur affecté au rôle bigquery.user peut obtenir des informations sur les tables des 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 à celui-ci. 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.

Obtenir des informations sur les tables partitionnées par temps d'ingestion

Pour afficher des informations sur une table partitionnée par temps d'ingestion, procédez comme suit :

UI Web

Dans le volet de navigation, cliquez sur la flèche vers le bas qui est située à gauche de votre ensemble de données pour le développer ou double-cliquez sur le nom de l'ensemble de données. Les tables et vues de ce dernier s'affichent.
Cliquez sur le nom de la table.
Cliquez sur Détails. La page Détails de la table affiche la description et les informations relatives à la table.
Cliquez sur l'onglet Schéma pour afficher la définition du schéma de la table. Vous remarquerez que la pseudo-colonne supplémentaire _PARTITIONTIME contient l'horodatage basé sur la date pour les données chargées dans la table.

Ligne de commande

Exécutez la commande bq show pour afficher toutes les informations de la table. Utilisez l'indicateur --schema pour n'afficher que les informations du schéma de table. Vous pouvez contrôler la sortie à l'aide de l'indicateur --format.

Si vous souhaitez obtenir des informations sur une table dans un projet autre que votre projet par défaut, ajoutez l'ID du projet à l'ensemble de données au format suivant : [PROJECT_ID]:[DATASET].

bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE]

Où :

[PROJECT_ID] correspond à l'ID du projet.
[DATASET] est le nom de l'ensemble de données.
[TABLE] est le nom de la table.

Exemples :

Saisissez la commande suivante pour afficher toutes les informations sur mytable dans mydataset. mydataset se trouve dans votre projet par défaut.

bq show --format=prettyjson mydataset.mytable

Saisissez la commande suivante pour afficher toutes les informations sur mytable dans mydataset. mydataset se trouve dans myotherproject, pas dans votre projet par défaut.

bq show --format=prettyjson myotherproject:mydataset.mytable

Saisissez la commande suivante pour n'afficher que les informations du schéma sur mytable dans mydataset. mydataset se trouve dans myotherproject, et non dans votre projet par défaut.

Lorsque vous affichez les informations de schéma d'une table partitionnée par temps d'ingestion, la pseudo-colonne _PARTITIONTIME n'est pas visible.

bq show --schema --format=prettyjson myotherproject:mydataset.mytable

API

Appelez la méthode bigquery.tables.get et définissez tous les paramètres pertinents.

Répertorier les tables partitionnées par temps d'ingestion dans un ensemble de données

Vous pouvez répertorier les tables des ensembles de données (y compris les tables partitionnées) à l'aide de l'interface utilisateur Web de BigQuery ou de la commande bq ls de l'interface de ligne de commande, ou bien en appelant la méthode d'API tables.list.

Autorisations requises

Pour répertorier les tables d'un ensemble de données, vous devez disposer du rôle READER au niveau de l'ensemble de données ou d'un rôle IAM au niveau du projet qui inclut les autorisations bigquery.tables.list. Si vous avez des autorisations bigquery.tables.list au niveau du projet, vous pouvez répertorier les tables dans tous les ensembles de données du projet. Tous les rôles IAM prédéfinis au niveau du projet incluent les autorisations bigquery.tables.list, à l'exception de bigquery.jobUser.

Répertorier des tables partitionnées

Pour répertorier les tables dans un ensemble de données (y compris les tables partitionnées), procédez comme suit :

UI Web

Dans le volet de navigation de l'interface utilisateur Web, cliquez sur la flèche vers le bas qui est située à gauche de votre ensemble de données pour le développer ou double-cliquez sur le nom de l'ensemble de données. Les tables et vues de ce dernier s'affichent.
Faites défiler la liste pour voir les tables de l'ensemble de données. Les tables et les vues sont identifiées par des icônes différentes.

CLI

Exécutez la commande bq ls. Vous pouvez contrôler le résultat à l'aide du paramètre --format. Si vous répertoriez des tables dans un projet autre que votre projet par défaut, ajoutez l'ID du projet à l'ensemble de données au format suivant : [PROJECT_ID]:[DATASET].

bq ls --format=pretty [PROJECT_ID]:[DATASET]

Où :

[PROJECT_ID] correspond à l'ID du projet.
[DATASET] est le nom de l'ensemble de données.

Lorsque vous exécutez la commande, le champ Type affiche TABLE ou VIEW. Pour les tables partitionnées par temps d'ingestion, le champ Time Partitioning contient DAY et le délai d'expiration de la partition exprimé en millisecondes (le cas échéant).

Exemple :

+-------------------------+-------+----------------------+--------------------------------+
|         tableId         | Type  |        Labels        | Time Partitioning              |
+-------------------------+-------+----------------------+--------------------------------+
| mytable                 | TABLE | department:shipping  |  DAY (expirationMs: 259200000) |
| myview                  | VIEW  |                      |                                |
+-------------------------+-------+----------------------+--------------------------------+

Exemples :

Saisissez la commande suivante pour répertorier les tables dans l'ensemble de données mydataset, dans votre projet par défaut.

bq ls --format=pretty mydataset

Saisissez la commande suivante pour répertorier les tables dans l'ensemble de données mydataset dans myotherproject.

bq ls --format=pretty myotherproject:mydataset

API

Pour répertorier les tables avec l'API, appelez la méthode tables.list.

Répertorier des partitions dans des tables partitionnées par temps d'ingestion

Pour répertorier les partitions dans une table partitionnée par temps d'ingestion, vous pouvez interroger la pseudo-colonne _PARTITIONTIME en SQL standard ou la métatable __PARTITIONS_SUMMARY__ en ancien SQL.

Vous pouvez exécuter la requête à l'aide de l'interface utilisateur Web de BigQuery ou de la commande bq query, ou bien en appelant la méthode jobs.insert et en configurant une tâche de type query.

Autorisations requises

Pour exécuter une tâche de requête qui utilise la métatable __PARTITIONS_SUMMARY__ ou la pseudo-colonne _PARTITIONTIME, vous devez disposer des autorisations bigquery.jobs.create. Voici les rôles IAM prédéfinis au niveau du projet qui incluent l'autorisation bigquery.jobs.create :

Vous devez également disposer du rôle READER au niveau de l'ensemble de données ou d'un rôle IAM au niveau du projet 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.

Répertorier des partitions dans une table partitionnée par temps d'ingestion

Vous pouvez répertorier des partitions dans une table partitionnée par temps d'ingestion en langage SQL standard (méthode recommandée) ou en ancien SQL. Pour répertorier des partitions, procédez comme suit :

Langage SQL standard :

UI Web

Accédez à l'interface utilisateur Web de BigQuery.
Accéder à l'interface utilisateur Web de BigQuery
Cliquez sur le bouton Saisir une requête.
Saisissez le texte suivant dans le champ Nouvelle requête pour interroger une pseudo-colonne _PARTITIONTIME de la table :
```
#standardSQL
SELECT
  _PARTITIONTIME as pt
FROM
  `[DATASET].[TABLE]`
GROUP BY 1
```
où :
- [DATASET] est l'ensemble de données qui contient la table.
- [TABLE] est le nom de la table.
Cliquez sur Afficher les options.
(Facultatif) Dans le champ Zone de traitement, cliquez sur Non spécifiée et sélectionnez l'emplacement de vos données.
Cliquez sur Exécuter la requête.

CLI

Saisissez la requête suivante à l'aide de la commande bq query :

bq --location=[LOCATION] query --use_legacy_sql=false '
SELECT
  _PARTITIONTIME as pt
FROM
  `[DATASET].[TABLE]`
GROUP BY 1'

Where:

  + `[LOCATION]` is the name of your location. The `--location` flag is
    optional. For example, if you are using BigQuery in
    the Tokyo region, you can set the flag's value to `asia-northeast1`.
    You can set a default value for the location using the
    [.bigqueryrc file](/bigquery/docs/bq-command-line-tool#setting_default_values_for_command-line_flags).
  + `[DATASET]` is the dataset that contains the table.
  + `[TABLE]` is the name of the table.

API

Appelez la méthode jobs.insert et configurez une tâche de type query qui interroge la pseudo-colonne _PARTITIONTIME de la table.

Spécifiez votre emplacement dans la propriété location de la section jobReference de la ressource de tâche.

Ancien SQL :

UI Web

Accédez à l'interface utilisateur Web de BigQuery.
Accéder à l'interface utilisateur Web de BigQuery
Cliquez sur le bouton Saisir une requête.
Saisissez le texte suivant dans le champ Nouvelle requête pour interroger la métatable __PARTITIONS_SUMMARY__ d'une table :
```
#legacySQL
SELECT
   partition_id
 FROM
   [[DATASET].[TABLE]$__PARTITIONS_SUMMARY__]
```
où :
- [DATASET] est l'ensemble de données qui contient la table.
- [TABLE] est le nom de la table.
Cliquez sur Afficher les options.
(Facultatif) Dans le champ Zone de traitement, cliquez sur Non spécifiée et sélectionnez l'emplacement de vos données.
Cliquez sur Exécuter la requête.

CLI

Saisissez la requête suivante à l'aide de la commande bq query :

bq --location=[LOCATION] query --use_legacy_sql=true '
SELECT
   partition_id
 FROM
   [[DATASET].[TABLE]$__PARTITIONS_SUMMARY__]'

Where:

  + `[LOCATION]` is the name of your location. The `--location` flag is
    optional. For example, if you are using BigQuery in
    the Tokyo region, you can set the flag's value to `asia-northeast1`.
    You can set a default value for the location using the
    [.bigqueryrc file](/bigquery/docs/bq-command-line-tool#setting_default_values_for_command-line_flags).
  + `[DATASET]` is the dataset that contains the table.
  + `[TABLE]` is the name of the table.

API

Appelez la méthode jobs.insert et configurez une tâche de type query qui interroge la métatable __PARTITIONS_SUMMARY__.

Spécifiez votre emplacement dans la propriété location de la section jobReference de la ressource de tâche.

Obtenir des métadonnées de tables à l'aide de métatables

Le processus d'obtention des métadonnées d'une table partitionnée par temps d'ingestion est identique à celui d'une table standard. Pour en savoir plus, consultez la section Obtenir des métadonnées de tables à l'aide de métatables.

Lorsque vous interrogez la métatable __TABLES_SUMMARY__, toutes les tables sont répertoriées. Aucune option type ne permet de distinguer les tables partitionnées des tables non partitionnées.

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

La métatable __PARTITIONS_SUMMARY__ est une table spéciale dont le contenu représente des métadonnées relatives aux partitions d'une table partitionnée par temps d'ingestion. La métatable __PARTITIONS_SUMMARY__ est en lecture seule.

Pour accéder aux métadonnées des partitions dans une table partitionnée par temps d'ingestion, servez-vous de la métatable __PARTITIONS_SUMMARY__ dans une instruction de requête SELECT. Vous pouvez exécuter la requête à l'aide de l'interface utilisateur Web de BigQuery ou de la commande bq query de l'outil de ligne de commande, ou bien en appelant la méthode d'API jobs.insert et en configurant une tâche de requête.

Actuellement, le langage SQL standard n'est pas compatible avec le séparateur de décorateur de partition ($). Vous ne pouvez donc pas interroger __PARTITIONS_SUMMARY__ en SQL standard. Une requête en ancien SQL utilisant la métatable __PARTITIONS_SUMMARY__ se présente sous la forme suivante :

    SELECT [COLUMN] FROM [[DATASET].[TABLE]$__PARTITIONS_SUMMARY__]

où :

[DATASET] est le nom de votre ensemble de données.
[TABLE] est le nom de la table partitionnée par temps d'ingestion.
[COLUMN] est l'une des valeurs 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 temps d'ingestion
`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

Autorisations requises pour le partitionnement des métatables

Pour exécuter une tâche de requête qui utilise la métatable __PARTITIONS_SUMMARY__, vous devez disposer des autorisations bigquery.jobs.create. Voici les rôles IAM prédéfinis au niveau du projet qui incluent l'autorisation bigquery.jobs.create :

Exemples de partitionnement de métatables

La requête suivante récupère toutes les métadonnées d'une partition pour une table partitionnée par temps d'ingestion nommée mydataset.mytable.

UI Web

#legacySQL
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

Ligne de commande

bq query --use_legacy_sql=true '
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

Le résultat doit se présenter sous la forme suivante :

+----------------+------------+----------------+--------------+---------------+--------------------+
|   project_id   | dataset_id |    table_id    | partition_id | creation_time | last_modified_time |
+----------------+------------+----------------+--------------+---------------+--------------------+
| myproject      | mydataset  | mytable        | 20160314     | 1517190224120 | 1517190224997      |
| myproject      | mydataset  | mytable        | 20160315     | 1517190224120 | 1517190224997      |
+----------------+------------+----------------+--------------+---------------+--------------------+

La requête suivante répertorie les heures auxquelles les partitions de mydataset.mytable ont été modifiées pour la dernière fois.

UI Web

#legacySQL
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

Ligne de commande

bq query --use_legacy_sql=true '
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

Le résultat doit se présenter sous la forme suivante :

+--------------+--------------------+
| partition_id | last_modified_time |
+--------------+--------------------+
| 20160102     |      1471632556179 |
| 20160101     |      1471632538142 |
| 20160103     |      1471632570463 |
+--------------+--------------------+

Pour afficher le champ last_modified_time dans un format plus lisible, utilisez la fonction FORMAT_UTC_USEC. Exemple :

UI Web

#legacySQL
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.table1$__PARTITIONS_SUMMARY__]

Ligne de commande

bq query --use_legacy_sql=true ' SELECT partition_id, FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified FROM [mydataset.mytable$PARTITIONS_SUMMARY]'

Le résultat doit se présenter sous la forme suivante :

+--------------+----------------------------+
| partition_id |       last_modified        |
+--------------+----------------------------+
| 20160103     | 2016-08-19 18:49:30.463000 |
| 20160102     | 2016-08-19 18:49:16.179000 |
| 20160101     | 2016-08-19 18:48:58.142000 |
+--------------+----------------------------+

Exemple : Créer une table partitionnée par temps d'ingestion à l'aide des résultats de requête

Dans cet exemple, vous créez une table partitionnée par temps d'ingestion avec l'outil de ligne de commande et vous ajoutez des données à trois partitions à l'aide des résultats de requête. La table va contenir des données météorologiques, partitionnées par date, pour les trois premiers jours de 2016.

Cet exemple interroge l'ensemble de données météorologiques "Global Surface Summary of the Day" de l'Agence américaine d'observation océanique et atmosphérique (NOAA) pour connaître les températures des trois premiers jours de 2016 et écrire les résultats dans une table partitionnée. Cet exemple utilise le paramètre --location=US, car vous interrogez un ensemble de données public. Les ensembles de données publics BigQuery sont stockés dans la zone multirégionale US. De ce fait, vous ne pouvez pas écrire les résultats des requêtes portant sur des données publiques dans une table d'une autre région, ni associer les tables d'ensembles de données publics aux tables d'une autre région.

Étape 1. Créez une table partitionnée par temps d'ingestion vide nommée temps dans un ensemble de données mydataset. Vous n'avez pas à spécifier un schéma de table. Le schéma obtenu à partir des résultats de requête est utilisé comme définition de schéma de la table.

bq mk --time_partitioning_type=DAY mydataset.temps

Pour afficher les paramètres de configuration, exécutez la commande bq show :

bq show --format=prettyjson mydataset.temps

Recherchez l'entrée timePartitioning dans le résultat de la commande bq show :

{
  ...
  "timePartitioning": {
    "type": "DAY"
  },
  "type": "TABLE"
}

Étape 2. Interrogez l'ensemble de données météorologiques "Global Surface Summary of the Day" de l'Agence américaine d'observation océanique et atmosphérique (NOAA) pour connaître les températures des trois premiers jours de 2016 et écrire les résultats dans les partitions correspondantes de la table temps. Les requêtes suivantes écrivent 100 lignes de résultats de requête dans une partition à l'aide de l'option --destination_table et de la syntaxe SQL standard.

Exécutez la requête suivante pour écrire les températures du 1er janvier 2016 dans la partition mydataset.temps$20160101 :

bq --location=US query --use_legacy_sql=false \
--destination_table 'mydataset.temps$20160101' \
'SELECT stn,temp from `bigquery-public-data.noaa_gsod.gsod2016` WHERE mo="01" AND da="01" limit 100'

Exécutez la requête suivante pour écrire les températures du 2 janvier 2016 dans la partition mydataset.temps$20160102 :

bq --location=US query --use_legacy_sql=false \
--destination_table 'mydataset.temps$20160102' \
'SELECT stn,temp from `bigquery-public-data.noaa_gsod.gsod2016` WHERE mo="01" AND da="02" limit 100'

Exécutez la requête suivante pour écrire les températures du 3 janvier 2016 dans la partition mydataset.temps$20160103 :

bq --location=US query --use_legacy_sql=false \
--destination_table 'mydataset.temps$20160103' \
'SELECT stn,temp from `bigquery-public-data.noaa_gsod.gsod2016` WHERE mo="01" AND da="03" limit 100'

Étape 3. Vérifiez que votre table contient 300 lignes à l'aide de la commande bq show.

bq show mydataset.temps

Les résultats affichent les colonnes "Schema" (Schéma) et "Total Rows" (Nombre total de lignes).

  Last modified        Schema       Total Rows   Total Bytes     Expiration      Time Partitioning   Labels   kmsKeyName
 ----------------- ---------------- ------------ ------------- ----------------- ------------------- -------- ------------
  28 Jan 15:03:45   |- stn: string   300          4800          29 Jan 15:00:32   DAY
                    |- temp: float

Étapes suivantes

Pour découvrir le fonctionnement des tables partitionnées dans BigQuery, consultez la page intitulée Présentation des tables partitionnées.
Pour apprendre à créer et utiliser des tables partitionnées, consultez la page Créer et utiliser des tables partitionnées.
Pour savoir comment gérer et mettre à jour des tables partitionnées, consultez la section Gérer des tables partitionnées.
Pour en savoir plus sur l'interrogation des tables partitionnées, consultez la page Interroger des tables partitionnées.

Créer des tables partitionnées par temps d'ingestion

Autorisations requises

Créer une table partitionnée par temps d'ingestion vide avec une définition de schéma

UI Web

Ligne de commande

API

Créer une table partitionnée par temps d'ingestion à partir d'un résultat de requête

UI Web

CLI

API

Créer une table partitionnée par temps d'ingestion lors du chargement des données

Convertir des tables segmentées par date en tables partitionnées par temps d'ingestion

Contrôler l'accès aux tables partitionnées par temps d'ingestion

Utiliser des tables partitionnées par temps d'ingestion

Obtenir des informations sur les tables partitionnées par temps d'ingestion

Autorisations requises

Obtenir des informations sur les tables partitionnées par temps d'ingestion

UI Web

Ligne de commande

API

Répertorier les tables partitionnées par temps d'ingestion dans un ensemble de données

Autorisations requises

Répertorier des tables partitionnées

UI Web

CLI

API

Répertorier des partitions dans des tables partitionnées par temps d'ingestion

Autorisations requises

Répertorier des partitions dans une table partitionnée par temps d'ingestion

UI Web

CLI

API

UI Web

CLI

API

Obtenir des métadonnées de tables à l'aide de métatables

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

Autorisations requises pour le partitionnement des métatables

Exemples de partitionnement de métatables

UI Web

Ligne de commande

UI Web

Ligne de commande

UI Web

Ligne de commande

Exemple : Créer une table partitionnée par temps d'ingestion à l'aide des résultats de requête

Étapes suivantes

Envoyer des commentaires concernant…