Créer et utiliser des tables partitionnées

Ce document explique comment créer et utiliser des tables partitionnées par une colonne DATE ou TIMESTAMP. Pour plus d'informations sur les tables partitionnées par date d'ingestion, consultez Créer et utiliser des tables partitionnées par date d'ingestion.

Après avoir créé une table partitionnée, vous pouvez :

  • contrôler l'accès à vos données de 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 plus d'informations sur la gestion des tables partitionnées, telle que la mise à jour de leurs propriétés, leur copie et leur suppression, consultez la page Gérer des tables partitionnées.

Limites

Les tables partitionnées sont soumises aux limites suivantes :

  • La colonne de partitionnement doit être une colonne scalaire de type DATE ou TIMESTAMP. Le mode de la colonne peut être de type REQUIRED ou NULLABLE, mais pas REPEATED (basé sur des tableaux).
  • La colonne de partitionnement doit être un champ de niveau supérieur. Vous ne pouvez pas utiliser un champ feuille d'un élément RECORD (STRUCT) comme colonne de partitionnement.
  • Vous ne pouvez pas utiliser l'ancien SQL pour interroger des tables partitionnées ou pour écrire des résultats de requêtes dans des tables partitionnées.

Créer des tables partitionnées

Vous pouvez créer une table partitionnée dans BigQuery à l'aide des méthodes suivantes :

  • En utilisant la console GCP ou l'interface utilisateur Web classique
  • À l'aide d'une instruction LDD CREATE TABLE avec une clause PARTITION BY contenant une expression partition expression
  • En utilisant la commande bq mk de l'outil de ligne de commande
  • De façon automatisée, en appelant la méthode API tables.insert
  • À partir des résultats de requêtes
  • Lorsque vous chargez des données

Nommer les tables

Lorsque vous créez une table dans BigQuery, le nom de la table doit être unique pour chaque 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, vous devez disposer au minimum des autorisations suivantes :

  • Autorisations bigquery.tables.create pour créer la table
  • Autorisations bigquery.tables.updateData pour écrire des données dans la table à l'aide d'une tâche de chargement, d'une tâche de requête ou d'une tâche de copie
  • Autorisations bigquery.jobs.create pour exécuter une tâche de requête, une tâche de chargement ou une tâche de copie qui écrit des données dans la table

Des autorisations supplémentaires, telles que bigquery.tables.getData, peuvent être nécessaires pour accéder aux données que vous écrivez dans la table.

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

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

Vous trouverez ci-dessous les rôles Cloud IAM prédéfinis qui incluent les autorisations bigquery.jobs.create :

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

En outre, si un utilisateur dispose des autorisations bigquery.datasets.create, lorsqu'il crée un ensemble de données, il obtient également le rôle bigquery.dataOwner qui lui permet d'y accéder. L'accès bigquery.dataOwner donne à l'utilisateur la possibilité de créer et de mettre à jour des tables 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.

Créer une table partitionnée vide, avec une définition de schéma

Vous ne pouvez pas créer une table partitionnée vide sans définir un schéma. Le schéma est requis pour identifier la colonne utilisée afin de créer les partitions.

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

  • fournir le schéma en ligne avec la CLI ;
  • spécifier un fichier de schéma JSON avec la CLI ;
  • fournir le schéma dans une ressource de table lorsque vous appelez la méthode API tables.insert.

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

Une fois la table partitionnée créée, vous pouvez y :

  • charger des données ;
  • écrire les résultats de requêtes ;
  • copier des données.

Pour créer une table partitionnée vide avec une définition de schéma :

Console

  1. Dans la section Resources (Ressources) du panneau de navigation, développez votre projet et sélectionnez un ensemble de données.

  2. À droite de la fenêtre, dans le panneau de détails, cliquez sur Create table (Créer une table).

  3. Dans la section Source du panneau Create table (Créer une table) :

    • Pour Create table from (Créer une table à partir de), sélectionnez Empty table (Table vide).
  4. Dans la section Destination :

    • Pour le champ Dataset name (Nom de l'ensemble de données), sélectionnez l'ensemble de données approprié, puis saisissez le nom de la table que vous créez dans le champ Table name (Nom de la table).
    • Vérifiez que le champ Table type (Type de table) est défini sur Native table (Table native).
  5. Dans la section Schéma, saisissez la définition du schéma.

    • Indiquez manuellement les informations de schéma de l'une des manières suivantes :

      • En activant l'option Modifier sous forme de texte, puis en saisissant le schéma de la table en tant que tableau JSON.

      • Utilisez l'option Ajouter un champ pour saisir manuellement le schéma.

  6. Pour le champ Paramètres de partitionnement et de clustering, cliquez sur Aucun partitionnement, sélectionnez Partitionner par champ, puis choisissez une colonne DATE ou TIMESTAMP. Cette option n'est pas disponible si le schéma ne contient pas de colonne DATE, ni TIMESTAMP.

  7. (Facultatif) Pour le champ Filtre de partitionnement, cochez la case Demander un filtre de partitionnement pour obliger les utilisateurs à inclure une clause WHERE spécifiant les partitions à interroger. Ce type de filtre peut contribuer à réduire les coûts et à améliorer les performances. Pour en savoir plus, consultez la page Interroger des tables partitionnées.

  8. (Facultatif) Cliquez sur Options avancées et, pour le champ Chiffrement, cliquez sur Clé gérée par le client pour utiliser une clé de service Cloud Key Management. Si vous conservez l'option Clé gérée par Google, BigQuery chiffre les données au repos.

  9. Cliquez sur Create table (Créer une table).

LDD

Les instructions LDD (langage de définition de données) vous permettent de créer et de modifier des tables et des vues à l'aide de la syntaxe de requête SQL standard.

En savoir plus sur l'utilisation des instructions de langage de définition de données

Pour créer une table partitionnée à l'aide d'une instruction LDD dans la console GCP, procédez comme suit :

  1. Ouvrez l'interface utilisateur Web de BigQuery dans la console GCP.
    Accéder à la console GCP

  2. Cliquez sur Saisir une nouvelle requête.

  3. Tapez votre instruction LDD CREATE TABLE dans la zone de texte de l'éditeur de requête.

    La requête suivante crée une table nommée newtable qui est partitionnée par la colonne transaction_date DATE et dont la partition expire au bout de trois jours.

     CREATE TABLE
       mydataset.newtable (transaction_id INT64,
         transaction_date DATE)
     PARTITION BY
       transaction_date
     OPTIONS
       ( partition_expiration_days=3,
         description="a table partitioned by transaction_date" )

  4. Cliquez sur Exécuter. Lorsque la requête est terminée, la table apparaît dans le volet Ressources.

UI classique

  1. Dans le volet de navigation, cliquez sur la flèche vers le bas flèche vers le bas à côté du nom de l'ensemble de données, puis sur Créer une table.

  2. Dans la section Données sources de la page Créer une table, cliquez sur Créer une table vide.

  3. Dans la section Destination Table (Table de destination) de la page Create Table (Créer une table) :

    • Dans le champ Table name (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 Table type (Type de table) est défini sur Native table (Table native).
  4. 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 :

      • En utilisant l'option Ajouter un champ pour saisir le schéma.

  5. Dans la section Options :

    • Pour Partitioning Type (Type de partitionnement), cliquez sur None (Aucun) et sélectionnez Day (Jour).
    • Pour Champ de partitionnement, choisissez une colonne TIMESTAMP ou DATE. La valeur par défaut est _PARTITIONTIME, qui crée une table partitionnée par date d'ingestion.
    • (Facultatif) Cliquez sur la case Demander un filtre de partitionnement pour obliger les utilisateurs à inclure une clause WHERE spécifiant les partitions à interroger. L'utilisation d'un filtre de partitionnement peut contribuer à réduire les coûts et à améliorer les performances. Pour en savoir plus, consultez la page Interroger des tables partitionnées.
    • (Facultatif) Pour le champ Chiffrement de la destination, sélectionnez Chiffrement géré par le client pour utiliser une clé Cloud Key Management Service afin de chiffrer la table. Si vous conservez l'option Default, BigQuery chiffre les données au repos à l'aide d'une clé gérée par Google.
  6. Cliquez sur Create table (Créer une table).

CLI

Utilisez la commande mk avec l'indicateur --table (ou le raccourci -t), l'indicateur --schema et l'indicateur --time_partitioning_field. Vous pouvez fournir la définition du schéma de la table en ligne ou fournir un fichier de schéma JSON.

Les paramètres facultatifs incluent --expiration, --description, --time_partitioning_expiration, --destination_kms_key, --require_partition_filter et --label. Actuellement, DAY est la seule valeur autorisée pour --time_partitioning_type, cet indicateur n'est donc pas nécessaire.

En cas de création d'une table dans un projet différent du projet par défaut, l'ID du projet doit être ajouté à l'ensemble de données au format suivant : project_id:dataset.

La clé --destination_kms_key n'est pas présentée ici. Pour en savoir plus sur l'utilisation de cet indicateur, consultez la page Protéger des données avec des clés Cloud KMS.

Entrez la commande suivante pour créer une table partitionnée vide, avec une définition de schéma :

bq mk --table \
--expiration integer1 \
--schema schema \
--time_partitioning_field column \
--time_partitioning_expiration integer2 \
--[no]require_partition_filter \
--description "description" \
--label key:value, key:value \
project_id:dataset.table

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 UTC 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 par temps d'ingestion, 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é.
  • schema est une définition de schéma en ligne au format field:data_type, field:data_type ou le chemin d'accès au fichier de schéma JSON sur votre machine locale.
  • column est le nom de la colonne TIMESTAMP ou DATE permettant de créer les partitions.
  • 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 la description de la table entre guillemets.
  • key:value est la paire key:value (clé/valeur) qui représente un libellé. Vous pouvez renseigner plusieurs étiquettes en utilisant une liste séparée par des virgules.
  • project_id est l'ID du projet.
  • dataset est un ensemble de données dans votre projet.
  • table est le nom de la table partitionnée que vous créez.

Lorsque vous spécifiez le schéma sur la ligne de commande, vous ne pouvez pas inclure de type RECORD (STRUCT), ni de description de colonne. Vous ne pouvez pas non plus spécifier le mode de la colonne. Tous les modes par défaut sont NULLABLE. Pour inclure des descriptions, des modes et des types RECORD, fournissez plutôt un fichier de schéma JSON.

Exemples :

Entrez la commande suivante pour créer une table partitionnée nommée mypartitionedtable dans mydataset au sein de votre projet par défaut. L'expiration du partitionnement est définie sur 86 400 secondes (un jour), l'expiration de la table sur 2 592 000 secondes (un mois de 30 jours), la description sur This is my partitioned table et le libellé sur organization:development. La commande utilise le raccourci -t au lieu de --table.

L'indicateur --require_partition_filter oblige les utilisateurs à inclure une clause WHERE spécifiant les partitions à interroger. L'utilisation d'un filtre de partitionnement peut contribuer à réduire les coûts et à améliorer les performances. Pour en savoir plus, consultez la page Interroger des tables partitionnées.

Le schéma est spécifié en ligne comme : ts:TIMESTAMP,column1:STRING,column2:INTEGER,coumn4:STRING. Le champ d'horodatage TIMESTAMP spécifié par ts sert à créer les partitions.

bq mk -t \
--expiration 2592000 \
--schema 'ts:TIMESTAMP,column1:STRING,column2:INTEGER,coumn4:STRING' \
--time_partitioning_field ts \
--time_partitioning_expiration 86400  \
--require_partition_filter \
--description "This is my partitioned table" \
--label org:dev \
mydataset.mypartitionedtable

Entrez la commande suivante pour créer une table partitionnée nommée mypartitionedtable dans myotherproject, et non dans votre projet par défaut. La valeur d'expiration du partitionnement est de 259 200 secondes (trois jours), la description est définie sur This is my 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 table. Si l'ensemble de données possède un délai d'expiration de table par défaut, celui-ci 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 schéma est spécifié dans un fichier JSON local : /tmp/myschema.json. La définition de schéma inclut un champ TIMESTAMP nommé ts servant à créer les partitions.

bq mk -t \
--expiration 2592000 \
--schema /tmp/myschema.json \
--time_partitioning_field ts \
--time_partitioning_expiration 86400  \
--description "This is my partitioned table" \
--label org:dev \
myotherproject:mydataset.mypartitionedtable

Une fois la table créée, vous pouvez mettre à jour l'expiration de la table, l'expiration des partitions, la description et les libellés de la table partitionnée à l'aide de la CLI.

API

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

Go

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Go 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 Go.

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
sampleSchema := bigquery.Schema{
	{Name: "name", Type: bigquery.StringFieldType},
	{Name: "post_abbr", Type: bigquery.IntegerFieldType},
	{Name: "date", Type: bigquery.DateFieldType},
}
metadata := &bigquery.TableMetadata{
	TimePartitioning: &bigquery.TimePartitioning{
		Field:      "date",
		Expiration: 90 * 24 * time.Hour,
	},
	Schema: sampleSchema,
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, metadata); err != nil {
	return err
}

Python

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Python décrite dans le guide de démarrage rapide de BigQuery relatif à l'utilisation des bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur l'API BigQuery Python.

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')

table_ref = dataset_ref.table("my_partitioned_table")
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
    bigquery.SchemaField("date", "DATE"),
]
table = bigquery.Table(table_ref, schema=schema)
table.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.DAY,
    field="date",  # name of column to use for partitioning
    expiration_ms=7776000000,
)  # 90 days

table = client.create_table(table)

print(
    "Created table {}, partitioned on column {}".format(
        table.table_id, table.time_partitioning.field
    )
)

Créer une table partitionnée à 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. Vous ne pouvez pas modifier une table standard existante en une table partitionnée avec des résultats de requêtes.

Lorsque vous créez une table partitionnée à partir d'un résultat de requête, vous devez utiliser le SQL standard. Actuellement, l'ancien SQL ne permet pas d'interroger les tables partitionnées ou d'écrire les résultats de requêtes dans des tables partitionnées.

Les décorateurs de partition vous permettent d'écrire les résultats de requêtes sur une partition spécifique. Par exemple, pour écrire des résultats sur la partition du 1er mai 2016, servez-vous du décorateur de partition suivant :

table_name$20160501

Lors de l'écriture des résultats de requêtes sur une partition spécifique à l'aide d'un décorateur de partition, les données en cours d'écriture sur la partition 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.

Exemple :

La requête suivante récupère des données datant du 1er février 2018, et écrit les données dans la partition $20180201 de la table mytable. La table contient deux colonnes : une colonne TIMESTAMP nommée TS et une colonne INT64 nommée a.

bq query \
--nouse_legacy_sql  \
--destination_table=mytable$20180201 \
'SELECT
   TIMESTAMP("2018-02-01") AS TS,
   2 AS a'

La requête suivante récupère des données datant du 31 janvier 2018, et tente d'écrire les données sur la partition $20180201 de la table mytable. Cette requête échoue, car les données que vous tentez d'écrire ne correspondent pas à la date de la partition.

bq query \
--nouse_legacy_sql  \
--destination_table=T$20180201 \
'SELECT
   TIMESTAMP("2018-01-31") as TS,
   2 as a'

Pour plus d'informations sur la façon d'ajouter ou de reformuler (remplacer) des données dans des tables partitionnées, consultez Ajouter et écraser des données de tables partitionnées par date. Pour plus d'informations sur la façon d'interroger des tables partitionnées, consultez la page Interroger des tables partitionnées.

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

Pour créer une table partitionnée à partir d'un résultat de requête, procédez comme suit :

Console

Vous ne pouvez pas spécifier des options de partitionnement pour une table de destination lorsque vous interrogez des données à l'aide de la console GCP.

UI classique

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

CLI

Entrez la commande bq query, spécifiez l'indicateur --destination_table pour créer une table permanente en fonction des résultats des requêtes, puis spécifiez l'indicateur --time_partitioning_field pour créer une table de destination partitionnée. Actuellement, DAY est la seule valeur autorisée pour --time_partitioning_type, cet indicateur n'est donc pas nécessaire.

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.

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

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

bq --location=location query \
--destination_table project_id:dataset.table \
--time_partitioning_field column \
--use_legacy_sql=false \
'query'

Où :

  • location est le nom de votre emplacement. 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 définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc.
  • project_id est l'ID du projet.
  • dataset est le nom de l'ensemble de données qui contiendra la nouvelle table partitionnée.
  • table est le nom de la table partitionnée que vous créez en utilisant les résultats de la requête.
  • query est une requête en syntaxe SQL standard. Actuellement, vous ne pouvez pas utiliser l'ancien SQL pour interroger des tables partitionnées ou pour écrire des résultats de requêtes dans des tables partitionnées.

Exemples :

Entrez la commande suivante pour écrire les résultats de la requête dans une table de destination partitionnée nommée mypartitionedtable dans mydataset. L'élément mydataset 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 NHTSA Traffic Fatality. La colonne timestamp_of_crash de la table TIMESTAMP sert à créer les partitions.

bq query \
--destination_table mydataset.mypartitionedtable \
--time_partitioning_field timestamp_of_crash \
--use_legacy_sql=false \
'SELECT
   state_number,
   state_name,
   day_of_crash,
   month_of_crash,
   year_of_crash,
   latitude,
   longitude,
   manner_of_collision,
   number_of_fatalities,
   timestamp_of_crash
 FROM
   `bigquery-public-data`.nhtsa_traffic_fatalities.accident_2016
 LIMIT
   100'

Entrez la commande suivante pour écrire les résultats de la requête dans une table de destination partitionnée nommée mypartitionedtable dans mydataset. mydataset se trouve dans myotherproject, pas 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 NHTSA Traffic Fatality. La colonne timestamp_of_crash de la table TIMESTAMP sert à créer les partitions.

bq query \
--destination_table myotherproject:mydataset.mypartitionedtable \
--time_partitioning_field timestamp_of_crash \
--use_legacy_sql=false \
'SELECT
   state_number,
   state_name,
   day_of_crash,
   month_of_crash,
   year_of_crash,
   latitude,
   longitude,
   manner_of_collision,
   number_of_fatalities,
   timestamp_of_crash
 FROM
   `bigquery-public-data`.nhtsa_traffic_fatalities.accident_2016
 LIMIT
   100'

API

Pour enregistrer les résultats d'une requête dans une table partitionnée permanente, appelez la méthode jobs.insert, configurez une tâche query, et incluez une valeur pour les propriétés destinationTable et timePartitioning.

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

Créer une table partitionnée lorsque vous chargez des données

Vous pouvez créer une table partitionnée 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. Par exemple, pour charger toutes les données générées le 1er mai 2016 dans la partition 20160501, utilisez le décorateur de partition suivant :

table_name$20160501

Lors du chargement de données dans une partition spécifique avec un décorateur de partition, les données en cours de chargement dans la partition 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.

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

Contrôler l'accès aux tables partitionnées

Vous ne pouvez pas attribuer de contrôles d'accès directement au niveau des tables ou des vues. Le niveau le plus bas des ressources BigQuery auquel vous pouvez accorder l'accès se situe au niveau de l'ensemble de données. Pour configurer l'accès aux tables et aux vues, vous attribuez un rôle Cloud IAM à une entité située au niveau de l'ensemble de données ou à un niveau supérieur.

L'attribution d'un rôle au niveau de l'ensemble de données spécifie les opérations qu'une entité est autorisée à effectuer sur les tables et les vues de cet ensemble de données spécifique. Pour en savoir plus sur la configuration des contrôles d'accès aux ensembles de données, consultez la page Contrôler l'accès aux ensembles de données.

Vous pouvez également attribuer des rôles Cloud IAM à un niveau supérieur dans la hiérarchie des ressources de Google Cloud Platform (par exemple, au niveau du projet, du dossier ou de l'organisation). L'attribution de rôles à un niveau supérieur accorde à l'entité un accès à un ensemble de ressources plus large. Par exemple, si vous attribuez un rôle à une entité au niveau du projet, elle obtient des autorisations qui s'appliquent à tous les ensembles de données du projet. Pour en savoir plus sur l'attribution de droits d'accès aux ressources, consultez la page Accorder, modifier et révoquer les accès à des ressources dans la documentation Cloud IAM.

Vous pouvez également créer des rôles personnalisés Cloud IAM. Si vous créez un rôle personnalisé, les autorisations que vous accordez dépendent des opérations spécifiques que vous souhaitez autoriser l'entité à effectuer.

Pour en savoir plus sur les rôles et les autorisations, consultez les pages suivantes :

Utiliser des tables partitionnées

Obtenir des informations sur les tables partitionnées

Vous pouvez obtenir des informations sur les tables à l'aide des opérations suivantes :

  • Utilisation de la console GCP ou de l'interface utilisateur Web classique de BigQuery
  • Utilisation de la commande CLI bq show
  • Utilisation de la méthode API tables.get
  • Utilisation des bibliothèques clientes

Autorisations requises

Pour obtenir des informations sur les tables, vous devez au minimum disposer des autorisations bigquery.tables.get. Les rôles Cloud IAM prédéfinis suivants incluent les autorisations bigquery.tables.get :

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

En outre, si un utilisateur dispose des autorisations bigquery.datasets.create, lorsqu'il crée un ensemble de données, il obtient également le rôle bigquery.dataOwner qui lui permet d'y accéder. L'accès bigquery.dataOwner donne à l'utilisateur la possibilité de récupérer les métadonnées d'une table.

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

Obtenir des informations sur la table partitionnée

Pour afficher des informations sur une table partitionnée :

Console

  1. Ouvrez l'UI Web de BigQuery dans la console GCP.
    Accéder à l'interface utilisateur Web de BigQuery

  2. Dans la section Resources (Ressources) du panneau de navigation, développez votre projet et sélectionnez un ensemble de données pour répertorier les tables et les vues qu'il contient.

  3. Cliquez sur Details (Détails) sous l'éditeur de requête. Cet onglet affiche la description de la table et ses informations.

    Détails de la table

  4. Cliquez sur l'onglet Schema (Schéma) pour afficher la définition du schéma de la table. Remarque : Les tables partitionnées n'incluent pas la pseudo-colonne _PARTITIONTIME.

UI classique

  1. Dans le volet de navigation, cliquez sur la flèche vers le bas flèche vers le bas située à gauche de l'ensemble de données pour le développer, ou double-cliquez sur le nom de ce dernier. Les tables et vues de ce dernier s'affichent.

  2. Cliquez sur le nom de la table.

  3. Cliquez sur Détails. La page Détails de la table affiche la description et les informations de la table.

  4. Cliquez sur l'onglet Schéma pour afficher la définition du schéma de la table. Remarque : Les tables partitionnées n'incluent pas la pseudo-colonne _PARTITIONTIME.

CLI

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. L'indicateur --format peut être utilisé pour contrôler le résultat.

Si vous souhaitez obtenir des informations sur une table dans un projet différent du 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 est 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

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

{
  "creationTime": "1563236533535",
  "description": "This is my partitioned table",
  "etag": "/ABcDEo7f8GHijKL2mnOpQr==",
  "expirationTime": "1565828533000",
  "id": "myproject:mydataset.mypartitionedtable",
  "kind": "bigquery#table",
  "labels": {
    "org": "dev"
  },
  "lastModifiedTime": "1563236533576",
  "location": "US",
  "numBytes": "0",
  "numLongTermBytes": "0",
  "numRows": "0",
  "requirePartitionFilter": true,
  "schema": {
    "fields": [
      {
        "name": "ts",
        "type": "TIMESTAMP"
      },
      {
        "name": "column1",
        "type": "STRING"
      },
      {
        "name": "column2",
        "type": "INTEGER"
      },
      {
        "name": "column3",
        "type": "STRING"
      }
    ]
  },
  "selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/myproject/datasets/mydataset/tables/mypartitionedtable",
  "tableReference": {
    "datasetId": "mydataset",
    "projectId": "myproject",
    "tableId": "mypartitionedtable"
  },
  "timePartitioning": {
    "expirationMs": "86400000",
    "field": "ts",
    "requirePartitionFilter": true,
    "type": "DAY"
  },
  "type": "TABLE"
}

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

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

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

[
  {
    "name": "ts",
    "type": "TIMESTAMP"
  },
  {
    "name": "column1",
    "type": "STRING"
  },
  {
    "name": "column2",
    "type": "INTEGER"
  },
  {
    "name": "column3",
    "type": "STRING"
  }
]

API

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

Répertorier les tables partitionnées dans un ensemble de données

Vous pouvez répertorier des tables dans des ensembles de données :

  • en utilisant la console GCP ou l'interface utilisateur Web classique de BigQuery ;
  • en utilisant la commande CLI bq ls ;
  • en appelant la méthode API tables.list ;
  • en utilisant les bibliothèques clientes.

Autorisations requises

Pour répertorier les tables d'un ensemble de données, vous devez au minimum disposer des autorisations bigquery.tables.list. Les rôles Cloud IAM prédéfinis suivants incluent les autorisations bigquery.tables.list :

  • bigquery.user
  • bigquery.metadataViewer
  • bigquery.dataViewer
  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

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

Répertorier des tables partitionnées

Pour répertorier les tables d'un ensemble de données (y compris les tables partitionnées) :

Console

  1. Ouvrez l'UI Web de BigQuery dans la console GCP.
    Accéder à l'interface utilisateur Web de BigQuery

  2. Dans la section Ressources du panneau de navigation, développez votre projet et sélectionnez votre ensemble de données.

  3. Faites défiler la liste pour voir les tables de l'ensemble de données. Les tables, les tables partitionnées, les modèles et les vues sont identifiés par des icônes différentes.

UI classique

  1. Dans le volet de navigation de l'interface utilisateur Web, cliquez sur la flèche vers le bas flèche vers le bas à gauche de votre ensemble de données ou double-cliquez sur son nom pour le développer. Les tables et vues de ce dernier s'affichent.

  2. 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.

    Afficher des tables

CLI

Exécutez la commande bq ls. L'indicateur --format peut être utilisé pour contrôler la sortie. 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 est 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, le champ Time Partitioning affiche DAY, la colonne utilisée afin de créer les partitions. La date/heure d'expiration de la partition est exprimée en millisecondes, si l'expiration de la partition est spécifiée.

Exemple :

+-------------------------+-------+----------------------+---------------------------------------------------+
|         tableId         | Type  |        Labels        | Time Partitioning                                 |
+-------------------------+-------+----------------------+---------------------------------------------------+
| mytable                 | TABLE | department:shipping  |  DAY (field: source_date, expirationMs: 86400000) |
| myview                  | VIEW  |                      |                                                   |
+-------------------------+-------+----------------------+---------------------------------------------------+

Exemples :

Entrez 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

Entrez 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

Vous pouvez répertorier les partitions dans une table partitionnée en interrogeant la métatable __PARTITIONS_SUMMARY__ en ancien SQL.

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

Autorisations requises

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. Vous trouverez ci-dessous les rôles Cloud IAM prédéfinis qui incluent les autorisations bigquery.jobs.create :

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

Vous devez également disposer des autorisations bigquery.tables.getData. Les rôles Cloud IAM prédéfinis suivants incluent des autorisations bigquery.tables.getData :

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

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

Répertorier les partitions dans une table partitionnée

Vous pouvez répertorier des partitions dans une table partitionnée avec l'ancien SQL. Pour répertorier des partitions dans une table partitionnée :

Console

  1. Ouvrez l'UI Web de BigQuery dans la console GCP.
    Accéder à la console GCP

  2. Cliquez sur le bouton Saisir une nouvelle requête.

  3. Saisissez le texte suivant dans la zone Éditeur de requête pour interroger la métatable __PARTITIONS_SUMMARY__ :

    #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.
  4. Cliquez sur Exécuter.

UI classique

  1. Accédez à l'interface utilisateur Web de BigQuery.

    Accéder à l'interface utilisateur Web de BigQuery

  2. Cliquez sur le bouton Saisir une requête.

  3. Saisissez le texte suivant dans la zone Nouvelle requête pour interroger la métatable __PARTITIONS_SUMMARY__ :

    #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.
  4. Cliquez sur Afficher les options.

  5. Cliquez sur Exécuter la requête.

CLI

Entrez la requête suivante avec la commande bq query :

bq --location=location query \
--use_legacy_sql=true \
'SELECT
  partition_id
FROM
  [dataset.table$__PARTITIONS_SUMMARY__]'

Où :

  • location est le nom de votre emplacement. 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 définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc.
  • dataset.table est l'ensemble de données qui contient la table.
  • dataset.table est le nom de la table.

API

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

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

Vous pouvez obtenir des informations concernant les tables partitionnées en utilisant des tables spéciales appelées métatables. Les métatables contiennent des métadonnées telles que la liste des tables et des vues d'un ensemble de données. Les métatables sont en lecture seule.

Actuellement, le service INFORMATION_SCHEMA ne permet pas d'obtenir des métadonnées de tables partitionnées.

Obtenir des métadonnées de partitions avec des 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 date. La métatable __PARTITIONS_SUMMARY__ est en lecture seule.

Pour accéder aux métadonnées contenues dans les partitions d'une table partitionnée par date, utilisez la métatable __PARTITIONS_SUMMARY__ dans une instruction de requête SELECT. Vous pouvez exécuter la requête :

  • en utilisant la console GCP ou l'UI Web classique de BigQuery ;
  • en utilisant la commande bq query de l'outil de ligne de commande ;
  • en appelant la méthode API jobs.insert et en configurant une tâche query ;
  • en utilisant les bibliothèques clientes.

Actuellement, le 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__ ressemble à ceci :

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 date.
  • 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 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.

Autorisations de partitionnement de métatables

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. Vous trouverez ci-dessous les rôles Cloud IAM prédéfinis qui incluent les autorisations bigquery.jobs.create :

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

Vous devez également disposer des autorisations bigquery.tables.getData. Les rôles Cloud IAM prédéfinis suivants incluent des autorisations bigquery.tables.getData :

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

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

Exemples de partitionnement de métatables

La requête suivante récupère toutes les métadonnées de partition pour une table partitionnée par date appelée mydataset.mytable.

Console

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

UI classique

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

CLI

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

Le résultat ressemble à ceci :

+----------------+------------+----------------+--------------+---------------+--------------------+
|   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.

Console

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

UI classique

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

CLI

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

Le résultat ressemble à ceci :

+--------------+--------------------+
| 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 :

Console

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

UI classique

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

CLI

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 ressemble à ceci :

+--------------+----------------------------+
| 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 |
+--------------+----------------------------+

Étapes suivantes

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

Envoyer des commentaires concernant…

Besoin d'aide ? Consultez notre page d'assistance.