Créer et utiliser des tables partitionnées

Ce document décrit comment créer et utiliser des tables partitionnées dans BigQuery. 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 également ê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 d'une instruction LDD CREATE TABLE avec une clause PARTITION BY contenant une expression partition expression
• Manuellement avec l'outil de ligne de commande bq mk
• 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

Lorsque vous créez une table en colonnes, partitionnée par date 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, 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 avec des autorisations bigquery.tables.create. Les rôles IAM ci-dessous prédéfinis au niveau du projet incluent les autorisations bigquery.tables.create :

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

Comme le rôle bigquery.user dispose d'autorisations bigquery.datasets.create, un utilisateur affecté au rôle bigquery.user peut créer des tables partitionnées 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 à 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.

Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la section 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.

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 l'outil de ligne de commande ;
• spécifier un fichier de schéma JSON avec l'outil de ligne de commande ;
• 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. Ouvrez l'interface utilisateur Web de BigQuery dans la console GCP.
   Accéder à l'interface utilisateur Web de BigQuery

2. Cliquez sur Compose a new query (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.

    #standardSQL
    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 More (Plus) et sélectionnez Query settings (Paramètres de requête).

5. Dans le champ Processing location (Zone de traitement), cliquez sur Auto-select (Sélection automatique) et choisissez l'emplacement de vos données. Si vos données se trouvent dans l'emplacement multirégional US ou EU, vous pouvez laisser la zone de traitement définie sur "Non spécifiée". Lorsque vos données se trouvent dans l'emplacement US ou EU, la zone de traitement est automatiquement détectée.

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

UI classique

1. Accédez à l'UI Web de BigQuery.

   Accéder à l'interface utilisateur Web de BigQuery

2. Cliquez sur Saisir une requête.

3. Saisissez votre instruction LDD CREATE TABLE dans la zone de texte New Query (Nouvelle 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.

    #standardSQL
    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 Afficher les options.

5. Dans le champ Processing Location (Zone de traitement), cliquez sur Unspecified (Non spécifiée) et sélectionnez l'emplacement de vos données. Si vos données se trouvent dans l'emplacement multirégional US ou EU, vous pouvez laisser la zone de traitement définie sur "Non spécifiée". Lorsque vos données se trouvent dans l'emplacement US ou EU, la zone de traitement est automatiquement détectée.

6. Cliquez sur Exécuter la requête. Lorsque la requête a été exécutée, la table apparaît dans le volet de navigation.

Ligne de commande

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 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 section Clés de chiffrement gérées par le client.

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] --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 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 date 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 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 ordinateur local.
• [COLUMN] est le nom de la colonne TIMESTAMP ou DATE utilisée pour 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 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 de votre 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 le type RECORD (STRUCT), ni la description et le mode de 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 à la place.

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.

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  --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, pas 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 d'expiration de table. Si l'ensemble de données possède une expiration de table par défaut, celle-ci est appliquée. Si l'ensemble de données n'a pas 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 le délai d'expiration de la table, le délai d'expiration des partitions, la description et les libellés de la table partitionnée.

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 sur l'API BigQuery Go.

Afficher sur GitHub Commentaires

// 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 : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur l'API BigQuery Python.

Afficher sur GitHub Commentaires

# 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. Pour ajuster les fuseaux horaires, utilisez un décorateur de partition pour écrire les données sur une partition basée dans votre fuseau horaire préféré. Par exemple, si vous êtes à l'heure normale du Pacifique (PST), écrivez 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

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 écrit des données dans la partition du 1er février 2018 de la table T. La table contient deux colonnes : une colonne TIMESTAMP nommée TS et une colonne INT64 nommée a. Comme la requête écrit l'horodatage 2018-02-01 sur la partition $20180201, la commande peut s'exécuter. La requête est exécutée dans la zone multirégionale US.

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

La requête suivante tente également d'écrire des données dans la table T, mais écrit l'horodatage 2018-01-31 sur la partition $20180201. Cette requête échoue car la valeur que vous essayez d'écrire ne correspond pas à la date de la partition.

bq --location=US 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 Interroger des tables partitionnées.

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 l'interface Web BigQuery.

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'interface Web 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 l'indicateur 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].

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 zone. L'indicateur --location est facultatif si vos données se trouvent dans la zone multirégionale US ou EU. 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] correspond à l'ID de votre 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 :

Remarque : Les exemples qui suivent utilisent l'indicateur --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. Les ensembles de données publics étant stockés aux États-Unis, vous ne pouvez pas écrire les résultats d'une requête de données publique dans la table d'une autre région, ni joindre des tables d'ensembles de données publics aux tables d'une autre région.

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 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 --location=US 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 --location=US 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 votre 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. Pour ajuster les fuseaux horaires, utilisez un décorateur de partition pour charger les données dans une partition en fonction de votre fuseau horaire préféré. 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

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.

API

Pour définir la configuration du partitionnement lorsque vous créez une table via une tâche de chargement, vous pouvez insérer dans le message configuration.load.timePartitioning la configuration de partitionnement associée, y compris configuration.load.timePartitioning.expirationMs pour les partitions qui expirent au fil du temps. L'insertion de configuration.load.timePartitioning.field détermine si cette table est partitionnée par une pseudo-colonne ou une colonne d'informations sur l'utilisateur.

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 sur l'API BigQuery Go.

Afficher sur GitHub Commentaires

// 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")
gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states-by-date.csv")
gcsRef.SkipLeadingRows = 1
gcsRef.Schema = bigquery.Schema{
	{Name: "name", Type: bigquery.StringFieldType},
	{Name: "post_abbr", Type: bigquery.StringFieldType},
	{Name: "date", Type: bigquery.DateFieldType},
}
loader := client.Dataset(destDatasetID).Table(destTableID).LoaderFrom(gcsRef)
loader.TimePartitioning = &bigquery.TimePartitioning{
	Field:      "date",
	Expiration: 90 * 24 * time.Hour,
}
loader.WriteDisposition = bigquery.WriteEmpty

job, err := loader.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}

if status.Err() != nil {
	return fmt.Errorf("Job completed with error: %v", status.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 : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur l'API BigQuery Python.

Afficher sur GitHub Commentaires

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'
table_id = "us_states_by_date"

dataset_ref = client.dataset(dataset_id)
job_config = bigquery.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
    bigquery.SchemaField("date", "DATE"),
]
job_config.skip_leading_rows = 1
job_config.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.DAY,
    field="date",  # name of column to use for partitioning
    expiration_ms=7776000000,
)  # 90 days
uri = "gs://cloud-samples-data/bigquery/us-states/us-states-by-date.csv"

load_job = client.load_table_from_uri(
    uri, dataset_ref.table(table_id), job_config=job_config
)  # API request

assert load_job.job_type == "load"

load_job.result()  # Waits for table load to complete.

table = client.get_table(dataset_ref.table(table_id))
print("Loaded {} rows to table {}".format(table.num_rows, table_id))

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

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 des ensembles 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 l'ensemble de données spécifique. Si vous n'attribuez 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 permettant 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 autorisent l'accès aux données de toutes les 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 attribuez dépendent des opérations sur les tables que vous souhaitez que l'utilisateur, le groupe ou le compte de service puisse effectuer.

Pour en savoir plus 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
• Contrôler l'accès aux ensembles de données

Utiliser des tables partitionnées

Obtenir des informations sur les tables partitionnées

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

Autorisations requises

Pour obtenir des informations sur des tables, vous devez disposer du rôle READER au niveau de l'ensemble de données ou détenir au niveau du projet un rôle IAM qui comprend 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.user et bigquery.jobUser.

En outre, comme le rôle bigquery.user dispose des autorisations bigquery.datasets.create, un utilisateur ayant le rôle bigquery.user peut obtenir des informations sur 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 à 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.

Pour en savoir plus 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.

Obtenir des informations sur la table partitionnée

Pour afficher des informations sur une table partitionnée :

Console

1. Ouvrez l'interface utilisateur 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.

   

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  à gauche de l'ensemble de données ou double-cliquez sur son nom pour le développer. Les tables et vues de l'ensemble s'affichent.

2. Cliquez sur le nom de la table.

3. Cliquez sur Details (Détails). La page Table Details (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.

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 le résultat à l'aide de l'indicateur --format.

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 de votre 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, pas dans votre projet par défaut.

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

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 (y compris des tables partitionnées) à l'aide de la console GCP, de l'interface utilisateur Web classique de BigQuery, de la commande bq ls de la CLI, ou en appelant la méthode 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 disposez d'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.

Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la section 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.

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'interface utilisateur 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  à gauche de votre ensemble de données ou double-cliquez sur son nom pour le développer. Les tables et vues de l'ensemble 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.

   

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 de votre 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 pour créer les partitions et la date/heure d'expiration de la partition est exprimée en millisecondes (le cas échéant).

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 disposer des autorisations bigquery.jobs.create. Les rôles IAM suivants, prédéfinis au niveau du projet, incluent les autorisations bigquery.jobs.create :

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

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 des rôles bigquery.user, bigquery.jobUser et bigquery.metadataViewer.

Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la section 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.

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'interface utilisateur Web de BigQuery dans la console GCP.
   Accéder à l'interface utilisateur Web de BigQuery

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

3. Saisissez le texte suivant dans l'é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 More (Plus) et sélectionnez Query settings (Paramètres de requête).

   

5. Dans le champ Processing location (Zone de traitement), cliquez sur Auto-select (Sélection automatique) et choisissez l'emplacement de vos données. Si vos données se trouvent dans l'emplacement multirégional US ou EU, vous pouvez conserver la sélection automatique de la zone de traitement. Lorsque vos données se trouvent dans l'emplacement US ou EU, la zone de traitement est automatiquement détectée.

   

6. Cliquez sur Exécuter (Run).

UI classique

1. Accédez à l'UI Web de BigQuery.

   Accéder à l'UI Web de BigQuery

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

3. Saisissez le texte suivant dans la zone New Query (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. Dans le champ Processing Location (Zone de traitement), cliquez sur Unspecified (Non spécifiée) et sélectionnez l'emplacement de vos données. Si vos données se trouvent dans l'emplacement multirégional US ou EU, vous pouvez laisser la zone de traitement définie sur "Non spécifiée". Lorsque vos données se trouvent dans l'emplacement US ou EU, la zone de traitement est automatiquement détectée.

6. 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__]'

Where:

  + `[LOCATION]` is the name of your location. The `--location` flag is
    optional if your data is in the `US` or the `EU` multi-region
    location. For example, if you are using BigQuery in
    the Tokyo region, 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 query qui interroge la métatable __PARTITIONS_SUMMARY__ de la table.

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

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.

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 à l'aide de la console, de l'interface utilisateur Web classique de BigQuery, de la commande bq query de l'outil de ligne de commande, ou en appelant la méthode API jobs.insert et en configurant une tâche de requête.

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 disposer des autorisations bigquery.jobs.create. Les rôles IAM suivants, prédéfinis au niveau du projet, incluent les autorisations bigquery.jobs.create :

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

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 des rôles bigquery.user, bigquery.jobUser et bigquery.metadataViewer.

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__]

Ligne de commande

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__]

Ligne de commande

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__]

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

Étapes suivantes

• Pour une présentation de la compatibilité des tables partitionnées dans BigQuery, consultez la page Présentation des tables partitionnées.
• Pour découvrir comment créer et utiliser des tables partitionnées avec date d'ingestion, consultez la page Créer et utiliser des tables partitionnées avec date d'ingestion.
• Pour savoir comment gérer et mettre à jour des tables partitionnées, consultez la page 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.