Créer et utiliser des tables en cluster

Ce document décrit comment créer et utiliser des tables en cluster dans BigQuery.

Limites

Les tables en cluster dans BigQuery sont soumises aux limitations suivantes :

  • Actuellement, le clustering n'est disponible que pour les tables partitionnées.
  • Seul le langage SQL standard est compatible avec l'interrogation de tables en cluster et avec l'écriture des résultats de requête dans des tables en cluster.
  • Il n'est possible de spécifier que des colonnes de clustering lors de la création d'une table.
  • Une fois la table en cluster créée, les colonnes de clustering ne peuvent pas être modifiées.
  • Les colonnes de clustering doivent être des colonnes non répétées de premier niveau et correspondre à l'un des types suivants : INT64, STRING, DATE, TIMESTAMP, BOOL ou NUMERIC. Pour en savoir plus sur les types de données, consultez la page Types de données SQL standards.
  • Un maximum de quatre colonnes de clustering peut être spécifié.

Créer des tables en cluster

Il n'est actuellement possible de mettre en cluster que des tables partitionnées. Sont incluses les tables partitionnées par temps d'ingestion et les tables partitionnées (tables partitionnées par une colonne TIMESTAMP ou DATE).

Une table en cluster peut être créée dans BigQuery comme suit :

Lorsqu'une table en cluster est créée dans BigQuery, son nom 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 en cluster, 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 prédéfinis au niveau du projet incluent les autorisations bigquery.tables.create :

D'autre part, 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 en cluster dans tout ensemble de données qu'il crée. Par défaut, 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.

Créer une table en cluster vide avec une définition de schéma

Lors de la création d'une table dans BigQuery, des colonnes de clustering doivent être spécifiées. Une fois la table créée, les colonnes de clustering ne peuvent plus être modifiées. Il n'est actuellement possible de spécifier des colonnes de clustering que pour les tables partitionnées.

Les colonnes de clustering doivent être des colonnes non répétées de premier niveau et correspondre à l'un des types de données simples suivants : INTEGER, STRING, DATE, TIMESTAMP, BOOLEAN ou NUMERIC.

Un maximum de quatre colonnes de clustering peut être spécifié. Si plusieurs colonnes sont spécifiées, leur ordre détermine l'ordre des données. Par exemple, si la table est organisée en cluster selon les colonnes a, b et c, les données sont triées dans le même ordre : la colonne a en premier, puis la colonne b et la colonne c. Il est recommandé de faire apparaître en premier la colonne la plus fréquemment filtrée ou agrégée.

L'ordre des colonnes de clustering affecte également les performances et le tarif des requêtes. Pour en savoir plus sur les bonnes pratiques en matière d'interrogation de tables en cluster, consultez la page Interroger des tables en cluster.

Pour créer une table en cluster vide avec une définition de schéma, procédez comme suit :

Interface utilisateur classique

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

    Accéder à l'interface utilisateur Web de BigQuery

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

  3. Dans la section Source Data (Données sources) de la page Create Table (Créer une table), cliquez sur Create empty table (Créer une table vide).

  4. 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).
  5. Dans la section Schema (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 Edit as Text (Modifier sous forme de texte), puis en saisissant le schéma de la table en tant que tableau JSON.

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

  6. Dans la section Options :

    • Pour Partitioning Type (Type de partitionnement), cliquez sur None (Aucun) et sélectionnez Day (Jour).
    • Pour Partitioning Field (Champ de partitionnement), choisissez l'une des options suivantes :
      • Sélectionnez timestamp pour créer une table partitionnée par une colonne DATE ou TIMESTAMP.
      • Sélectionnez _PARTITIONTIME pour créer une table partitionnée par temps d'ingestion.
    • Pour Clustering Fields (Champs de clustering), saisissez un à quatre noms de champs.
    • Dans le champ Destination Encryption (Chiffrement de destination), 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 le contenu client stocké au repos.

      Détails des tables partitionnées

  7. Cliquez sur Create Table (Créer une table).

Une fois la table créée, vous pouvez mettre à jour la date d'expiration, la description et les libellés de la table en cluster. Vous ne pouvez pas ajouter de date d'expiration de partition après la création d'une table à l'aide de l'interface Web de BigQuery.

Ligne de commande

Utilisez la commande mk avec les indicateurs suivants :

  • --table (ou le raccourci -t).
  • --schema &mdash : vous pouvez fournir la définition du schéma de la table de façon intégrée ou fournir un fichier de schéma JSON.
  • Soit --time_partitioning_type (pour les tables partitionnées par temps d'ingestion), soit --time_partitioning_field (pour les tables partitionnées). Actuellement, DAY est la seule valeur acceptée pour --time_partitioning_type.
  • --clustering_fields pour spécifier un maximum de quatre colonnes de clustering.

Les paramètres facultatifs incluent --expiration, --description, --time_partitioning_expiration, --destination_kms_key et --label.

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 en cluster vide avec une définition de schéma :

bq mk --table --expiration [INTEGER1] --schema [SCHEMA] --time_partitioning_type=DAY --time_partitioning_field [COLUMN] --clustering_fields [COLUMNS] --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 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 intégrée au format [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE] ou le chemin d'accès au fichier de schéma JSON sur l'ordinateur local.
  • [COLUMN] est le nom de la colonne TIMESTAMP ou DATE utilisée pour créer une table partitionnée. Si vous créez une table partitionnée, vous n'avez pas besoin de spécifier l'indicateur --time_partitioning_type=DAY.
  • [COLUMNS] est une liste de quatre colonnes de clustering au maximum, séparées par des virgules.
  • [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 valeur/clé 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 un type RECORD (STRUCT) ou une description de colonne, ni spécifier 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 :

Entrez la commande suivante pour créer une table en cluster nommée myclusteredtable dans mydataset au sein de votre projet par défaut. La table est une table partitionnée (partitionnée par une colonne TIMESTAMP). 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 clustered table et le libellé sur organization:development. La commande utilise le raccourci -t au lieu de --table.

Le schéma est spécifié de façon intégrée comme suit : timestamp:timestamp,customer_id:string,transaction_amount:float. Le champ de clustering spécifié customer_id est utilisé pour mettre en cluster les partitions.

bq mk -t --expiration 2592000 --schema 'timestamp:timestamp,customer_id:string,transaction_amount:float' --time_partitioning_field timestamp --clustering_fields customer_id --time_partitioning_expiration 86400  --description "This is my clustered table" --label org:dev mydataset.myclusteredtable

Entrez la commande suivante pour créer une table en cluster nommée myclusteredtable dans myotherproject au sein de votre projet par défaut. La table est une table partitionnée par temps d'ingestion. 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. Le champ customer_id est utilisé pour mettre en cluster les partitions.

bq mk -t --expiration 2592000 --schema /tmp/myschema.json --time_partitioning_type=DAY --clustering_fields=customer_id --time_partitioning_expiration 86400  --description "This is my partitioned table" --label org:dev myotherproject:mydataset.myclusteredtable

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.

API

Appelez la méthode tables.insert avec une ressource de table définie, spécifiant les propriétés timePartitioning, clustering.fields et schema.

Go

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Go 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: "timestamp", Type: bigquery.TimestampFieldType},
	{Name: "origin", Type: bigquery.StringFieldType},
	{Name: "destination", Type: bigquery.StringFieldType},
	{Name: "amount", Type: bigquery.NumericFieldType},
}
metaData := &bigquery.TableMetadata{
	Schema: sampleSchema,
	TimePartitioning: &bigquery.TimePartitioning{
		Field:      "timestamp",
		Expiration: 90 * 24 * time.Hour,
	},
	Clustering: &bigquery.Clustering{
		Fields: []string{"origin", "destination"},
	},
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, metaData); err != nil {
	return err
}

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

Deux options s'offrent à vous pour créer une table en cluster à partir d'un résultat de requête :

  • En écrivant les résultats dans une nouvelle table de destination et en spécifiant les colonnes de clustering. Cette méthode est détaillée ci-dessous.
  • À l'aide d'une instruction DDL CREATE TABLE AS SELECT Pour en savoir plus sur cette méthode, consultez la section Créer une table en cluster à partir d'un résultat de requête de la page "Utiliser les instructions de langage de définition de données".

Il n'est pas possible de créer une table partitionnée en interrogeant une table partitionnée ou une table non partitionnée, ni de convertir une table existante en une table en cluster à l'aide de résultats de requêtes.

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

Interface utilisateur classique

Il n'est pas possible de spécifier des options de clustering pour une table de destination lors de l'interrogation de données à l'aide de l'interface utilisateur Web de BigQuery, sauf si une instruction DDL est utilisée. Pour en savoir plus, consultez la page Utiliser les instructions de langage de définition de données.

CLI

Saisissez la commande bq query et spécifiez les indicateurs suivants :

  • Spécifiez l'indicateur use_legacy_sql=false pour utiliser la syntaxe SQL standard.
  • Spécifiez l'indicateur --location et définissez la valeur correspondant à votre site.

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

    bq --location=[LOCATION] query --use_legacy_sql=false '[QUERY]'

Où :

  • [LOCATION] est le nom du site. 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 le site à l'aide du fichier .bigqueryrc.
  • [QUERY] est une requête en syntaxe SQL standard. Il n'est actuellement pas possible d'utiliser l'ancien SQL pour interroger des tables en cluster ou pour écrire des résultats de requête dans des tables en cluster. La requête peut contenir une instruction DDL CREATE TABLE spécifiant les options de création de la table en cluster. L'instruction DDL peut être utilisée à la place des indicateurs de ligne de commande individuels.

Exemples :

Entrez la commande suivante pour écrire les résultats de la requête dans une table de destination en cluster nommée myclusteredtable dans mydataset. mydataset se trouve dans votre projet par défaut. La requête extrait les données d'une table non partitionnée : mytable. La colonne customer_id est utilisée pour mettre en cluster la table. La colonne timestamp de la table sert à créer une table partitionnée.

bq --location=US query --use_legacy_sql=false 'CREATE TABLE mydataset.myclusteredtable PARTITION BY DATE(timestamp) CLUSTER BY customer_id AS SELECT * FROM mydataset.mytable'

API

Pour enregistrer des résultats de requête dans une table en cluster, appelez la méthode jobs.insert, configurez une tâche query et incluez une instruction DDL CREATE TABLE qui crée la table en cluster.

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

Créer une table en cluster lors du chargement de données

Vous pouvez créer une table en cluster en spécifiant les colonnes de clustering lorsque vous chargez des données dans une nouvelle table. Vous n'avez pas besoin de créer une table vide avant de charger des données. Vous pouvez créer la table en cluster et charger les données en même temps.

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

Pour définir le clustering lors de la définition d'une tâche de chargement, procédez comme suit :

API

Pour définir la configuration de clustering lors de la création d'une table via une tâche de chargement, vous pouvez renseigner le message configuration.load.clustering, en insérant dans la propriété configuration.load.clustering.Fields un maximum de quatre colonnes de clustering, par ordre de priorité.

Go

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Go 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")
gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/sample-transactions/transactions.csv")
gcsRef.SkipLeadingRows = 1
gcsRef.Schema = bigquery.Schema{
	{Name: "timestamp", Type: bigquery.TimestampFieldType},
	{Name: "origin", Type: bigquery.StringFieldType},
	{Name: "destination", Type: bigquery.StringFieldType},
	{Name: "amount", Type: bigquery.NumericFieldType},
}
loader := client.Dataset(destDatasetID).Table(destTableID).LoaderFrom(gcsRef)
loader.TimePartitioning = &bigquery.TimePartitioning{
	Field: "timestamp",
}
loader.Clustering = &bigquery.Clustering{
	Fields: []string{"origin", "destination"},
}
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())
}

Contrôler l'accès aux tables en cluster

Vous ne pouvez pas attribuer de contrôles d'accès directement aux tables en cluster ou aux partitions. Vous pouvez contrôler l'accès aux tables en configurant les contrôles d'accès au niveau de l'ensemble de données ou au niveau 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 :

Utiliser des tables en cluster

Obtenir des informations sur des tables en cluster

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 bq show de la CLI
  • Appel de la méthode d'API tables.get
  • Interrogation des vues INFORMATION_SCHEMA (version bêta)

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'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 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 une table en cluster

Pour afficher des informations sur une table en cluster, procédez comme suit :

Interface utilisateur 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 l'ensemble de données. Les tables et vues de ce dernier s'affichent.

  2. Cliquez sur le nom de la table.

  3. Cliquez sur Détails. La page Table Details (Détails de la table) affiche les détails de la table, y compris les colonnes de clustering.

    Détails des tables en cluster

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] correspond à l'ID de votre projet.
  • [DATASET] est le nom de l'ensemble de données.
  • [TABLE] est le nom de la table.

Exemples :

Entrez la commande suivante pour afficher toutes les informations sur myclusteredtable dans mydataset. mydataset se trouve dans le projet par défaut.

bq show --format=prettyjson mydataset.myclusteredtable

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

{
  "clustering": {
    "fields": [
      "customer_id"
    ]
  },
...
}

API

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

Obtenir des informations sur la table en cluster à l'aide de INFORMATION_SCHEMA (version bêta)

INFORMATION_SCHEMA est une série de vues offrant un accès aux métadonnées sur les ensembles de données, les tables et les vues.

Vous pouvez interroger les vues INFORMATION_SCHEMA.TABLES et INFORMATION_SCHEMA.TABLE_OPTIONS pour récupérer des métadonnées sur les tables et les vues d'un projet. Vous pouvez également interroger les vues INFORMATION_SCHEMA.COLUMNS et INFORMATION_SCHEMA.COLUMN_FIELD_PATHS pour récupérer des métadonnées sur les colonnes (champs) d'une table.

Pour les tables en cluster, vous pouvez interroger la colonne CLUSTERING_ORDINAL_POSITION dans la vue INFORMATION_SCHEMA.COLUMNS pour récupérer des informations sur vos colonnes en cluster.

Vue TABLES

Lorsque vous interrogez la vue INFORMATION_SCHEMA.TABLES, les résultats de la requête contiennent une ligne pour chaque table ou vue d'un ensemble de données.

Les requêtes exécutées sur la vue INFORMATION_SCHEMA.TABLES doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables ou les vues.

La vue INFORMATION_SCHEMA.TABLES présente le schéma suivant :

Nom de la colonne Type de données Valeur
TABLE_CATALOG STRING Nom du projet qui contient l'ensemble de données
TABLE_SCHEMA STRING Nom de l'ensemble de données contenant la table ou la vue (également appelé datasetId)
TABLE_NAME STRING Nom de la table ou de la vue (également appelé tableId)
TABLE_TYPE STRING Type de table :
IS_INSERTABLE_INTO STRING YES ou NO selon que la table accepte les instructions LMD INSERT
IS_TYPED STRING La valeur est toujours NO
CREATION_TIME TIMESTAMP Date/Heure de création de la table

Exemples

Exemple 1 :

L'exemple suivant récupère toutes les colonnes de la vue INFORMATION_SCHEMA.TABLES, à l'exception de is_typed qui est réservée en vue d'une utilisation ultérieure. Les métadonnées renvoyées concernent toutes les tables de l'ensemble de données mydataset de votre projet par défaut (myproject).

mydataset contient les tables suivantes :

  • mytable1 : table BigQuery standard
  • myview1 : vue BigQuery

Les requêtes exécutées sur la vue INFORMATION_SCHEMA.TABLES doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables.

Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet à l'ensemble de données, en respectant le format suivant : `[PROJECT_ID]`.[DATASET]..INFORMATION_SCHEMA.[VIEW] Par exemple, `myproject`.mydataset.INFORMATION_SCHEMA.TABLES.

Pour exécuter la requête, procédez comme suit :

Console

  1. Ouvrez l'interface utilisateur Web de BigQuery dans la console GCP.

    Accéder à l'UI Web de BigQuery

  2. Saisissez la requête SQL standard suivante dans la zone Éditeur de requête. INFORMATION_SCHEMA requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans la console GCP.

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.TABLES
    
  3. Cliquez sur Exécuter.

Ligne de commande

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'indicateur --nouse_legacy_sql ou --use_legacy_sql=false. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA.

Pour exécuter la requête, saisissez :

bq query --nouse_legacy_sql \
'SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES'

Les résultats doivent se présenter sous la forme suivante :

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | YES                | 2018-10-29 20:34:44 |
  | myproject      | mydataset     | myview1        | VIEW       | NO                 | 2018-12-29 00:19:20 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  

Exemple 2 :

L'exemple suivant récupère toutes les tables de type BASE TABLE de la vue INFORMATION_SCHEMA.TABLES. La colonne is_typed est exclue. Les métadonnées renvoyées concernent les tables de l'ensemble de données mydataset de votre projet par défaut (myproject).

Les requêtes exécutées sur la vue INFORMATION_SCHEMA.TABLES doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables.

Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet à l'ensemble de données, en respectant le format suivant : `[PROJECT_ID]`.[DATASET]..INFORMATION_SCHEMA.[VIEW] Par exemple, `myproject`.mydataset.INFORMATION_SCHEMA.TABLES.

Pour exécuter la requête, procédez comme suit :

Console

  1. Ouvrez l'interface utilisateur Web de BigQuery dans la console GCP.

    Accéder à l'UI Web de BigQuery

  2. Saisissez la requête SQL standard suivante dans la zone Éditeur de requête. INFORMATION_SCHEMA requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans la console GCP.

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.TABLES
    WHERE
     table_type="BASE TABLE"
    
  3. Cliquez sur Exécuter.

Ligne de commande

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'indicateur --nouse_legacy_sql ou --use_legacy_sql=false. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA.

Pour exécuter la requête, saisissez :

bq query --nouse_legacy_sql \
'SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES
WHERE table_type="BASE TABLE"'

Les résultats doivent se présenter sous la forme suivante :

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | NO                 | 2018-10-31 22:40:05 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  

Vue TABLE_OPTIONS

Lorsque vous interrogez la vue INFORMATION_SCHEMA.TABLE_OPTIONS, les résultats de la requête contiennent une ligne pour chaque table ou vue d'un ensemble de données.

Les requêtes exécutées sur la vue INFORMATION_SCHEMA.TABLE_OPTIONS doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables ou les vues.

La vue INFORMATION_SCHEMA.TABLE_OPTIONS présente le schéma suivant :

Nom de la colonne Type de données Valeur
TABLE_CATALOG STRING Nom du projet qui contient l'ensemble de données
TABLE_SCHEMA STRING Nom de l'ensemble de données contenant la table ou la vue (également appelé datasetId)
TABLE_NAME STRING Nom de la table ou de la vue (également appelé tableId)
OPTION_NAME STRING Une des valeurs de nom figurant dans la table d'options
OPTION_TYPE STRING Une des valeurs de type de données figurant dans la table d'options
OPTION_VALUE STRING Une des options de valeur figurant dans la table d'options
Table d'options
OPTION_NAME OPTION_TYPE OPTION_VALUE
partition_expiration_days FLOAT64 Durée de vie par défaut, en jours, de toutes les partitions d'une table partitionnée
expiration_timestamp FLOAT64 Durée de vie par défaut, en jours, de la table
kms_key_name STRING Nom de la clé Cloud KMS employée pour chiffrer la table
friendly_name STRING Nom descriptif de la table
description STRING Description de la table
labels ARRAY<STRUCT<STRING, STRING>> Tableau de valeurs STRUCT représentant les étiquettes de la table

Exemples

Exemple 1 :

L'exemple suivant récupère les délais d'expiration par défaut pour toutes les tables de l'ensemble de données mydataset de votre projet par défaut (myproject) en interrogeant la vue INFORMATION_SCHEMATA.TABLE_OPTIONS.

Les requêtes exécutées sur la vue INFORMATION_SCHEMA.TABLE_OPTIONS doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables.

Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet à l'ensemble de données, en respectant le format suivant : `[PROJECT_ID]`.[DATASET]..INFORMATION_SCHEMA.[VIEW] Par exemple, `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS.

Pour exécuter la requête, procédez comme suit :

Console

  1. Ouvrez l'interface utilisateur Web de BigQuery dans la console GCP.

    Accéder à l'UI Web de BigQuery

  2. Saisissez la requête SQL standard suivante dans la zone Éditeur de requête. INFORMATION_SCHEMA requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans la console GCP.

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
     option_name="expiration_timestamp"
    
  3. Cliquez sur Exécuter.

Ligne de commande

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'indicateur --nouse_legacy_sql ou --use_legacy_sql=false. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA.

Pour exécuter la requête, saisissez :

bq query --nouse_legacy_sql \
'SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE option_name="expiration_timestamp"'

Les résultats doivent se présenter sous la forme suivante :

  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | table_catalog  | table_schema  | table_name |     option_name      | option_type |             option_value             |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | myproject      | mydataset     | mytable1   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2020-01-16T21:12:28.000Z" |
  | myproject      | mydataset     | mytable2   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2021-01-01T21:12:28.000Z" |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  

Exemple 2 :

L'exemple suivant récupère les métadonnées sur toutes les tables de l'ensemble de données mydataset contenant des données de test. La requête utilise les valeurs de l'option description pour rechercher les tables dont la description contient "test". mydataset se trouve dans votre projet par défaut (myproject).

Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet à l'ensemble de données, en respectant le format suivant : `[PROJECT_ID]`.[DATASET]..INFORMATION_SCHEMA.[VIEW] Par exemple, `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS.

Pour exécuter la requête, procédez comme suit :

Console

  1. Ouvrez l'interface utilisateur Web de BigQuery dans la console GCP.

    Accéder à l'UI Web de BigQuery

  2. Saisissez la requête SQL standard suivante dans la zone Éditeur de requête. INFORMATION_SCHEMA requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans la console GCP.

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
     option_name="description" AND option_value LIKE "%test%"
    
  3. Cliquez sur Exécuter.

Ligne de commande

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'indicateur --nouse_legacy_sql ou --use_legacy_sql=false. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA.

Pour exécuter la requête, saisissez :

bq query --nouse_legacy_sql \
'SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE option_name="description" AND option_value LIKE "%test%"'

Les résultats doivent se présenter sous la forme suivante :

  +----------------+---------------+------------+-------------+-------------+--------------+
  | table_catalog  | table_schema  | table_name | option_name | option_type | option_value |
  +----------------+---------------+------------+-------------+-------------+--------------+
  | myproject      | mydataset     | mytable1   | description | STRING      | "test data"  |
  | myproject      | mydataset     | mytable2   | description | STRING      | "test data"  |
  +----------------+---------------+------------+-------------+-------------+--------------+
  

Vue COLUMNS

Lorsque vous interrogez la vue INFORMATION_SCHEMA.COLUMNS, les résultats de la requête contiennent une ligne pour chaque colonne (champ) d'une table.

Les requêtes exécutées sur la vue INFORMATION_SCHEMA.COLUMNS doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables.

La vue INFORMATION_SCHEMA.COLUMNS présente le schéma suivant :

Nom de la colonne Type de données Valeur
TABLE_CATALOG STRING Nom du projet qui contient l'ensemble de données
TABLE_SCHEMA STRING Nom de l'ensemble de données contenant la table (également appelé datasetId)
TABLE_NAME STRING Nom de la table ou de la vue (également appelé tableId)
COLUMN_NAME STRING Nom de la colonne
ORDINAL_POSITION INT64 Décalage avec un indice de 1 de la colonne dans la table. S'il s'agit d'une pseudo-colonne telle que _PARTITIONTIME ou _PARTITIONDATE, la valeur est NULL.
IS_NULLABLE STRING YES ou NO selon que le mode de la colonne autorise les valeurs NULL
DATA_TYPE STRING Type de données SQL standard de la colonne
IS_GENERATED STRING La valeur est toujours NEVER
GENERATION_EXPRESSION STRING La valeur est toujours NULL
IS_STORED STRING La valeur est toujours NULL
IS_HIDDEN STRING YES ou NO selon que la colonne est une pseudo-colonne telle que _PARTITIONTIME ou _PARTITIONDATE
IS_UPDATABLE STRING La valeur est toujours NULL
IS_SYSTEM_DEFINED STRING YES ou NO selon que la colonne est une pseudo-colonne telle que _PARTITIONTIME ou _PARTITIONDATE
IS_PARTITIONING_COLUMN STRING YES ou NO selon que la colonne est une colonne de partitionnement
CLUSTERING_ORDINAL_POSITION STRING Décalage avec un indice de 1 de la colonne dans les colonnes de clustering de la table. La valeur est NULL si la table n'est pas une table en cluster.

Exemples

L'exemple suivant récupère les métadonnées de la vue INFORMATION_SCHEMA.COLUMNS pour la table population_by_zip_2010 de l'ensemble de données census_bureau_usa. Ce dernier fait partie du programme d'ensembles de données publics de BigQuery.

Comme cette table se trouve dans le projet bigquery-public-data, ajoutez l'ID de ce dernier à l'ensemble de données, en respectant le format suivant : `[PROJECT_ID]`.[DATASET]..INFORMATION_SCHEMA.[VIEW] Par exemple, `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES.

Les colonnes suivantes sont exclues des résultats de la requête, car elles sont actuellement réservées en vue d'une utilisation ultérieure :

  • IS_GENERATED
  • GENERATION_EXPRESSION
  • IS_STORED
  • IS_UPDATABLE

Les requêtes exécutées sur la vue INFORMATION_SCHEMA.COLUMNS doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables.

Pour exécuter la requête, procédez comme suit :

Console

  1. Ouvrez l'interface utilisateur Web de BigQuery dans la console GCP.

    Accéder à l'UI Web de BigQuery

  2. Saisissez la requête SQL standard suivante dans la zone Éditeur de requête. INFORMATION_SCHEMA requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans la console GCP.

    SELECT
     * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
    FROM
     `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
    WHERE
     table_name="population_by_zip_2010"
    
  3. Cliquez sur Exécuter.

Ligne de commande

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'indicateur --nouse_legacy_sql ou --use_legacy_sql=false. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA.

Pour exécuter la requête, saisissez :

bq query --nouse_legacy_sql \
'SELECT * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
FROM `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
WHERE table_name="population_by_zip_2010"'

Les résultats doivent se présenter sous la forme suivante. Pour des raisons de lisibilité, les colonnes table_catalog et table_schema sont exclues des résultats :

+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
|       table_name       | column_name | ordinal_position | is_nullable | data_type | is_hidden | is_system_defined | is_partitioning_column | clustering_ordinal_position |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
| population_by_zip_2010 | zipcode     |                1 | NO          | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | geo_id      |                2 | YES         | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | minimum_age |                3 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | maximum_age |                4 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | gender      |                5 | YES         | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | population  |                6 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
  

Vue COLUMN_FIELD_PATHS

Lorsque vous interrogez la vue INFORMATION_SCHEMA.COLUMN_FIELD_PATHS, les résultats de la requête contiennent une ligne pour chaque colonne imbriquée dans une colonne RECORD (ou STRUCT).

Les requêtes exécutées sur la vue INFORMATION_SCHEMA.COLUMN_FIELD_PATHS doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables.

La vue INFORMATION_SCHEMA.COLUMN_FIELD_PATHS présente le schéma suivant :

Nom de la colonne Type de données Valeur
TABLE_CATALOG STRING Nom du projet qui contient l'ensemble de données
TABLE_SCHEMA STRING Nom de l'ensemble de données contenant la table (également appelé datasetId)
TABLE_NAME STRING Nom de la table ou de la vue (également appelé tableId)
COLUMN_NAME STRING Nom de la colonne
FIELD_PATH STRING Chemin d'accès à une colonne imbriquée dans une colonne RECORD (ou STRUCT)
DATA_TYPE STRING Type de données SQL standard de la colonne
DESCRIPTION STRING Description de la colonne

Exemples

L'exemple suivant récupère les métadonnées de la vue INFORMATION_SCHEMA.COLUMN_FIELD_PATHS pour la table commits de l'ensemble de données github_repos. Ce dernier fait partie du programme d'ensembles de données publics de BigQuery.

Comme cette table se trouve dans le projet bigquery-public-data, ajoutez l'ID de ce dernier à l'ensemble de données, en respectant le format suivant : `[PROJECT_ID]`.[DATASET]..INFORMATION_SCHEMA.[VIEW]Par exemple, `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS.

La table commits contient les colonnes imbriquées ainsi que les colonnes imbriquées et répétées suivantes :

  • author : colonne RECORD imbriquée
  • committer : colonne RECORD imbriquée
  • trailer : colonne RECORD imbriquée et répétée
  • difference : colonne RECORD imbriquée et répétée

La requête récupérera des métadonnées sur les colonnes author et difference.

Les requêtes exécutées sur la vue INFORMATION_SCHEMA.COLUMN_FIELD_PATHS doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables.

Pour exécuter la requête, procédez comme suit :

Console

  1. Ouvrez l'interface utilisateur Web de BigQuery dans la console GCP.

    Accéder à l'UI Web de BigQuery

  2. Saisissez la requête SQL standard suivante dans la zone Éditeur de requête. INFORMATION_SCHEMA requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans la console GCP.

    SELECT
     *
    FROM
     `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
    WHERE
     table_name="commits"
     AND column_name="author"
     OR column_name="difference"
    
  3. Cliquez sur Exécuter.

Ligne de commande

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'indicateur --nouse_legacy_sql ou --use_legacy_sql=false. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA.

Pour exécuter la requête, saisissez :

bq query --nouse_legacy_sql \
'SELECT * FROM `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE table_name="commits" AND column_name="author" OR column_name="difference"'

Les résultats doivent se présenter sous la forme suivante. Pour des raisons de lisibilité, les colonnes table_catalog et table_schema sont exclues des résultats.

  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  | table_name | column_name |     field_path      |                                                                      data_type                                                                      | description |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  | commits    | author      | author              | STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>                                                                  | NULL        |
  | commits    | author      | author.name         | STRING                                                                                                                                              | NULL        |
  | commits    | author      | author.email        | STRING                                                                                                                                              | NULL        |
  | commits    | author      | author.time_sec     | INT64                                                                                                                                               | NULL        |
  | commits    | author      | author.tz_offset    | INT64                                                                                                                                               | NULL        |
  | commits    | author      | author.date         | TIMESTAMP                                                                                                                                           | NULL        |
  | commits    | difference  | difference          | ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>> | NULL        |
  | commits    | difference  | difference.old_mode | INT64                                                                                                                                               | NULL        |
  | commits    | difference  | difference.new_mode | INT64                                                                                                                                               | NULL        |
  | commits    | difference  | difference.old_path | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_path | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.old_sha1 | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_sha1 | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.old_repo | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_repo | STRING                                                                                                                                              | NULL        |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  

Répertorier les tables en cluster dans un ensemble de données

Vous pouvez répertorier des tables en cluster dans des ensembles de données avec la console GCP, l'interface utilisateur Web classique de BigQuery, la commande CLI bq ls ou en appelant la méthode API tables.list.

Les autorisations requises pour répertorier les tables en cluster et les étapes pour y arriver sont les mêmes que pour les tables partitionnées. Pour découvrir comment répertorier des tables, consultez la section Répertorier les tables partitionnées dans un ensemble de données.

Fonctionnalités en cours de développement

Les fonctionnalités suivantes sont en cours de développement, mais ne sont pas encore disponibles dans la version alpha :

  • Prise en charge du clustering pour les tables natives (non partitionnées)
  • Réduction des coûts pour certains types de requêtes qui utilisent des filtres sur des colonnes de clustering

Étapes suivantes

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

Envoyer des commentaires concernant…

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