Créer et utiliser des tables

Ce document explique comment créer et utiliser des tables standards (intégrées) dans BigQuery. Pour en savoir plus sur la création d'autres types de tables, consultez les pages suivantes :

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

  • contrôler l'accès aux données de votre table ;
  • obtenir des informations sur vos tables ;
  • répertorier les tables dans un ensemble de données ;
  • obtenir les métadonnées d'une table.

Pour en savoir plus sur la gestion des tables, y compris la mise à jour des propriétés de tables, la copie et la suppression de tables, consultez la section Gérer des tables.

Avant de commencer

Avant de créer une table dans BigQuery, procédez comme suit :

Nommer les tables

Lorsque vous créez une table dans BigQuery, le nom de la table doit être unique pour chaque ensemble de données. Le nom de la table peut :

  • contenir jusqu'à 1 024 caractères ;
  • contenir des caractères Unicode des catégories L (lettre), M (marque), N (nombre), Pc (ponctuation de type connecteur, y compris trait de soulignement), Pd (ponctuation de type tiret), Zs (espace). Pour en savoir plus, consultez la section décrivant les catégories générales.

Voici des exemples de noms de table valides : table 01, ग्राहक, 00_お客様, étudiant-01.

Mises en garde :

  • Certains noms de tables et préfixes de noms de tables sont réservés. Si vous recevez une erreur indiquant que le nom ou le préfixe de votre table est réservé, sélectionnez-en un autre et réessayez.
  • Si vous incluez plusieurs opérateurs point (.) dans une séquence, les opérateurs en double sont implicitement supprimés.

    Par exemple, ceci :project_name....datasest_name..table_name

    Devient : project_name.dataset_name.table_name

Créer des tables

Vous pouvez créer une table dans BigQuery de différentes manières :

  • Manuellement à l'aide de la console ou de la commande bq mk de l'outil de ligne de commande bq
  • De manière automatisée, en appelant la méthode API tables.insert
  • En utilisant les bibliothèques clientes
  • À partir des résultats de requête
  • En définissant une table qui référence une source de données externe
  • Lorsque vous chargez des données
  • Via une instruction LDD (langage de définition de données) CREATE TABLE

Autorisations requises

Pour créer une table, vous devez disposer des autorisations IAM suivantes :

  • bigquery.tables.create
  • bigquery.tables.updateData
  • bigquery.jobs.create

En outre, vous pouvez avoir besoin de l'autorisation bigquery.tables.getData pour accéder aux données que vous écrivez dans la table.

Chacun des rôles IAM prédéfinis suivants inclut les autorisations dont vous avez besoin pour créer une table :

  • roles/bigquery.dataEditor
  • roles/bigquery.dataOwner
  • roles/bigquery.admin (inclut l'autorisation bigquery.jobs.create)
  • roles/bigquery.user (inclut l'autorisation bigquery.jobs.create)
  • roles/bigquery.jobUser (inclut l'autorisation bigquery.jobs.create)

En outre, si vous disposez de l'autorisation bigquery.datasets.create, vous pouvez créer et mettre à jour des tables dans les ensembles de données que vous créez.

Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la page Rôles prédéfinis et autorisations.

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

Vous pouvez créer une table vide avec une définition de schéma de différentes manières :

  • En saisissant le schéma dans la console
  • Fournir le schéma en ligne avec l'outil de ligne de commande bq
  • En envoyant un fichier de schéma JSON à l'aide de l'outil de ligne de commande bq
  • En fournissant 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 créée, vous pouvez charger des données ou insérer des données dans celle-ci en y écrivant les résultats d'une requête.

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

Console

  1. Dans la console, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans le volet Explorateur, développez votre projet, puis sélectionnez un ensemble de données.
  3. Dans la section Informations sur l'ensemble de données, cliquez sur Créer une table.
  4. Dans le panneau Créer une table, spécifiez les détails suivants :
    1. Dans la section Source, sélectionnez Table vide dans la liste Créer une table à partir de.
    2. Dans la section Destination, spécifiez les détails suivants :
      1. Pour Ensemble de données, sélectionnez l'ensemble de données dans lequel vous souhaitez créer la table.
      2. Dans le champ Table, saisissez le nom de la table que vous souhaitez créer.
      3. Vérifiez que le champ Type de table est défini sur Table native.
    3. Dans la section Schéma, saisissez la définition du schéma. Vous pouvez saisir les informations de schéma manuellement à l'aide de l'une des méthodes suivantes :
      • Option 2 : Cliquez sur Modifier sous forme de texte et collez le schéma sous la forme d'un tableau JSON. Lorsque vous utilisez un tableau JSON, vous générez le schéma en utilisant le même processus que pour la création d'un fichier de schéma JSON. Vous pouvez afficher le schéma d'une table existante au format JSON en saisissant la commande suivante :
            bq show --format=prettyjson dataset.table
            
      • Option 2 : Cliquez sur  Ajouter un champ et saisissez le schéma de la table. Spécifiez le nom, le type et le mode de chaque champ.
    4. Facultatif : spécifiez les paramètres de partitionnement et de clustering. Pour en savoir plus, consultez les pages Créer des tables partitionnées et Créer et utiliser des tables en cluster.
    5. Facultatif: dans la section Options avancées si vous souhaitez utiliser une clé de chiffrement gérée par le client, sélectionnezUtiliser une clé de chiffrement gérée par le client (CMEK). Par défaut, BigQuery chiffre le contenu client stocké au repos à l'aide d'une clé gérée par Google.
    6. Cliquez sur Créer une table.
    7. Cliquez sur Créer une table.

SQL

L'exemple suivant crée une table nommée newtable qui expire le 1er janvier 2023 :

  1. Dans la console, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez l'instruction suivante :

    CREATE TABLE mydataset.newtable (
      x INT64 OPTIONS (description = 'An optional INTEGER field'),
      y STRUCT <
        a ARRAY <STRING> OPTIONS (description = 'A repeated STRING field'),
        b BOOL
      >
    ) OPTIONS (
        expiration_timestamp = TIMESTAMP '2023-01-01 00:00:00 UTC',
        description = 'a table that expires in 2023',
        labels = [('org_unit', 'development')]);
    

  3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez la page Exécuter des requêtes interactives.

bq

Exécutez la commande bq mk avec l'option --table ou -t. Vous pouvez fournir des informations de schéma de table en ligne ou via un fichier de schéma JSON. Les paramètres facultatifs incluent :

  • --expiration
  • --description
  • --time_partitioning_field
  • --time_partitioning_type
  • --range_partitioning
  • --clustering_fields
  • --destination_kms_key
  • --label

--time_partitioning_field, --time_partitioning_type, --range_partitioning, --clustering_fields, et --destination_kms_key ne sont pas illustrés ici. Reportez-vous aux liens suivants pour plus d'informations sur ces paramètres facultatifs :

Si vous créez une table dans un projet autre que votre projet par défaut, ajoutez l'ID du projet au nom de l'ensemble de données, en respectant le format suivant : project_id:dataset.

Pour créer une table vide dans un ensemble de données existant avec une définition de schéma, saisissez le contenu suivant :

bq mk \
--table \
--expiration integer \
--description description \
--label key_1:value_1 \
--label key_2:value_2 \
project_id:dataset.table \
schema

Remplacez les éléments suivants :

  • integer est la durée de vie par défaut (en secondes) de la table. La valeur minimale est de 3 600 secondes (une heure). L'heure d'expiration correspond à l'heure actuelle plus la valeur entière. Si vous définissez le délai d'expiration lorsque vous créez une table, le paramètre d'expiration de la table par défaut de l'ensemble de données est ignoré.
  • description est la description de la table entre guillemets.
  • key_1:value_1 et key_2:value_2 sont des paires clé/valeur qui spécifient des étiquettes.
  • project_id est l'ID de votre projet.
  • dataset est un ensemble de données dans votre projet.
  • table est le nom de la table que vous créez.
  • schema est une définition de schéma intégrée au format field:data_type,field:data_type ou le chemin d'accès au fichier de schéma JSON sur votre machine locale.

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

Exemples :

Saisissez la commande suivante pour créer une table à l'aide d'une définition de schéma intégrée. Cette commande crée une table nommée mytable dans l'ensemble de données mydataset au sein de votre projet par défaut. Le délai d'expiration de la table est défini sur 3 600 secondes (1 heure), la description est définie sur This is my table et le libellé est défini sur organization:development. La commande utilise le raccourci -t au lieu de --table. Le schéma est indiqué comme suit : qtr:STRING,sales:FLOAT,year:STRING.

bq mk \
  -t \
  --expiration 3600 \
  --description "This is my table" \
  --label organization:development \
  mydataset.mytable \
  qtr:STRING,sales:FLOAT,year:STRING

Saisissez la commande ci-dessous pour créer une table à l'aide d'un fichier de schéma JSON. Cette commande crée une table nommée mytable dans l'ensemble de données mydataset au sein de votre projet par défaut. Le délai d'expiration de la table est défini sur 3 600 secondes (1 heure), la description est définie sur This is my table et le libellé est défini sur organization:development. Le chemin d'accès au fichier de schéma est le suivant : /tmp/myschema.json.

bq mk \
  --table \
  --expiration 3600 \
  --description "This is my table" \
  --label organization:development \
  mydataset.mytable \
  /tmp/myschema.json

Saisissez la commande suivante pour créer une table à l'aide d'un fichier de schéma JSON. Cette commande crée une table nommée mytable dans l'ensemble de données mydataset au sein du projet myotherproject. Le délai d'expiration de la table est défini sur 3 600 secondes (1 heure), la description est définie sur This is my table et le libellé est défini sur organization:development. Le chemin d'accès au fichier de schéma est le suivant : /tmp/myschema.json.

bq mk \
  --table \
  --expiration 3600 \
  --description "This is my table" \
  --label organization:development \
  myotherproject:mydataset.mytable \
  /tmp/myschema.json

Une fois la table créée, vous pouvez mettre à jour son délai d'expiration, sa description et ses libellés. Vous pouvez également modifier la définition du schéma.

API

Appelez la méthode tables.insert avec une ressource de table définie.

C#

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour C# décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery C#.


using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryCreateTable
{
    public BigQueryTable CreateTable(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        var dataset = client.GetDataset(datasetId);
        // Create schema for new table.
        var schema = new TableSchemaBuilder
        {
            { "full_name", BigQueryDbType.String },
            { "age", BigQueryDbType.Int64 }
        }.Build();
        // Create the table
        return dataset.CreateTable(tableId: "your_table_id", schema: 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 en langage Go.

import (
	"context"
	"fmt"
	"time"

	"cloud.google.com/go/bigquery"
)

// createTableExplicitSchema demonstrates creating a new BigQuery table and specifying a schema.
func createTableExplicitSchema(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType},
		{Name: "age", Type: bigquery.IntegerFieldType},
	}

	metaData := &bigquery.TableMetadata{
		Schema:         sampleSchema,
		ExpirationTime: time.Now().AddDate(1, 0, 0), // Table will be automatically deleted in 1 year.
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, metaData); err != nil {
		return err
	}
	return nil
}

Java

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

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

public class CreateTable {

  public static void runCreateTable() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    Schema schema =
        Schema.of(
            Field.of("stringField", StandardSQLTypeName.STRING),
            Field.of("booleanField", StandardSQLTypeName.BOOL));
    createTable(datasetName, tableName, schema);
  }

  public static void createTable(String datasetName, String tableName, Schema schema) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);
      TableDefinition tableDefinition = StandardTableDefinition.of(schema);
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Table was not created. \n" + e.toString());
    }
  }
}

Node.js

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Node.js décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery Node.js.

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function createTable() {
  // Creates a new table named "my_table" in "my_dataset".

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";
  // const schema = 'Name:string, Age:integer, Weight:float, IsMagic:boolean';

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    location: 'US',
  };

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);
}

PHP

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

use Google\Cloud\BigQuery\BigQueryClient;

/** Uncomment and populate these variables in your code */
// $projectId = 'The Google project ID';
// $datasetId = 'The BigQuery dataset ID';
// $tableId = 'The BigQuery table ID';
// $fields = [
//    [
//        'name' => 'field1',
//        'type' => 'string',
//        'mode' => 'required'
//    ],
//    [
//        'name' => 'field2',
//        'type' => 'integer'
//    ],
//];

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$schema = ['fields' => $fields];
$table = $dataset->createTable($tableId, ['schema' => $schema]);
printf('Created table %s' . PHP_EOL, $tableId);

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 de l'API BigQuery Python.

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
# table_id = "your-project.your_dataset.your_table_name"

schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]

table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)  # Make an API request.
print(
    "Created table {}.{}.{}".format(table.project, table.dataset_id, table.table_id)
)

Ruby

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Ruby décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery en langage Ruby.

require "google/cloud/bigquery"

def create_table dataset_id = "my_dataset"
  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id
  table_id = "my_table"

  table = dataset.create_table table_id do |updater|
    updater.string  "full_name", mode: :required
    updater.integer "age",       mode: :required
  end

  puts "Created table: #{table_id}"
end

Créer une table vide sans définition de schéma

Java

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

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create a table without schema
public class CreateTableWithoutSchema {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    createTableWithoutSchema(datasetName, tableName);
  }

  public static void createTableWithoutSchema(String datasetName, String tableName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);
      TableDefinition tableDefinition = StandardTableDefinition.of(Schema.of());
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Table was not created. \n" + e.toString());
    }
  }
}

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

Pour créer une table à partir d'un résultat de requête, écrivez les résultats dans une table de destination.

Console

  1. Ouvrez la page "BigQuery" dans la console.

    Accéder à la page "BigQuery"

  2. Dans le panneau Explorateur, développez votre projet et sélectionnez un ensemble de données.

  3. Saisissez une requête SQL valide.

  4. Cliquez sur More (Plus), puis sélectionnez Query settings (Paramètres de requête).

    Paramètres de requête

  5. Sélectionnez l'option Définir une table de destination pour les résultats de la requête.

    Définir la destination

  6. Dans la section Destination, sélectionnez l'ensemble de données dans lequel vous souhaitez créer la table, puis choisissez un ID de table.

  7. Dans la section Préférence d'écriture pour la table de destination, choisissez l'une des options suivantes :

    • Écrire si la table est vide : n'écrit les résultats de requête dans la table que si celle-ci est vide.
    • Append to table (Ajouter à la table) : ajoute les résultats de requête à une table existante.
    • Écraser la table : écrase une table existante portant le même nom à l'aide des résultats de requête.
  8. Facultatif: Dans le champ Emplacement des données, sélectionnez votre emplacement.

  9. Pour mettre à jour les paramètres de la requête, cliquez sur Enregistrer.

  10. Cliquez sur Exécuter. Cette action crée une tâche de requête qui écrit les résultats dans la table spécifiée.

Si vous oubliez de spécifier une table de destination avant d'exécuter la requête, vous pouvez également copier la table de résultats mise en cache dans une table permanente en cliquant sur le bouton Enregistrer les résultats situé au-dessus de l'éditeur.

SQL

L'exemple suivant utilise l'instruction CREATE TABLE pour créer la table trips à partir de données figurant dans la table bikeshare_trips publique :

  1. Dans la console, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez l'instruction suivante :

    CREATE TABLE mydataset.trips AS (
      SELECT
        bikeid,
        start_time,
        duration_minutes
      FROM
        bigquery-public-data.austin_bikeshare.bikeshare_trips
    );
    

  3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez la page Exécuter des requêtes interactives.

Pour en savoir plus, consultez la section Créer une table à partir d'une table existante.

bq

Saisissez la commande bq query en spécifiant l'option --destination_table pour créer une table permanente basée sur les résultats de la requête. Spécifiez l'option 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 en respectant le format suivant : project_id:dataset.

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

Pour contrôler la disposition en écriture d'une table de destination existante, fournissez l'une des options facultatives suivantes :

  • --append_table : si la table de destination existe, les résultats de requête y sont ajoutés.
  • --replace : si la table de destination existe, elle est remplacée par les résultats de requête.
bq --location=location query \
--destination_table project_id:dataset.table \
--use_legacy_sql=false 'query'

Remplacez les éléments suivants :

  • location représente le nom de l'emplacement utilisé pour traiter la requête. L'option --location est facultative. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, vous pouvez définir la valeur de l'option sur asia-northeast1. Vous pouvez spécifier une valeur par défaut pour l'emplacement à l'aide du fichier .bigqueryrc.
  • project_id est l'ID de votre projet.
  • dataset représente le nom de l'ensemble de données contenant la table dans laquelle vous écrivez les résultats de requête.
  • table représente le nom de la table dans laquelle vous écrivez les résultats de requête.
  • query est une requête en syntaxe SQL standard.

Si aucune option de disposition en écriture n'est spécifiée, le comportement par défaut consiste à écrire les résultats dans la table uniquement si elle est vide. Si la table existe et qu'elle n'est pas vide, l'erreur suivante est renvoyée : `BigQuery error in query operation: Error processing jobproject_id:bqjob_123abc456789_00000e1234f_1': Already Exists: Table project_id:dataset.table.

Exemples :

Saisissez la commande suivante pour écrire les résultats de la requête dans une table de destination nommée mytable au sein de l'ensemble de données mydataset. L'ensemble de données se trouve dans votre projet par défaut. Comme aucune option de disposition en écriture n'est spécifiée dans la commande, la table doit être nouvelle ou vide. Dans le cas contraire, une erreur Already exists est renvoyée. La requête récupère les données de l'ensemble de données public USA Name Data.

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

Saisissez la commande suivante pour que les résultats de requête écrasent une table de destination nommée mytable se trouvant dans l'ensemble de données mydataset. L'ensemble de données se trouve dans votre projet par défaut. La commande utilise l'option --replace pour écraser la table de destination.

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

Saisissez la commande suivante pour ajouter les résultats de requête à une table de destination nommée mytable au sein de l'ensemble de données mydataset. L'ensemble de données se trouve dans le projet my-other-project, et non dans votre projet par défaut. La commande utilise l'option --append_table pour ajouter les résultats de requête à la table de destination.

bq query \
--append_table \
--use_legacy_sql=false \
--destination_table my-other-project:mydataset.mytable \
'SELECT
   name,
   number
 FROM
   `bigquery-public-data`.usa_names.usa_1910_current
 WHERE
   gender = "M"
 ORDER BY
   number DESC'
 

Le résultat de chacun de ces exemples ressemble à ce qui suit. Pour des raisons de lisibilité, certains résultats sont tronqués.

Waiting on bqjob_r123abc456_000001234567_1 ... (2s) Current status: DONE
+---------+--------+
|  name   | number |
+---------+--------+
| Robert  |  10021 |
| John    |   9636 |
| Robert  |   9297 |
| ...              |
+---------+--------+

API

Pour enregistrer les résultats de requête dans une table permanente, appelez la méthode jobs.insert, configurez une tâche query et ajoutez une valeur pour la propriété destinationTable. Pour contrôler la disposition en écriture d'une table de destination existante, configurez la propriété writeDisposition.

Pour contrôler la zone de traitement de la tâche de requête, spécifiez la propriété location dans la section jobReference de la ressource de tâche.

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 en langage Go.

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

// queryWithDestination demonstrates saving the results of a query to a specific table by setting the destination
// via the API properties.
func queryWithDestination(w io.Writer, projectID, destDatasetID, destTableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	q := client.Query("SELECT 17 as my_col")
	q.Location = "US" // Location must match the dataset(s) referenced in query.
	q.QueryConfig.Dst = client.Dataset(destDatasetID).Table(destTableID)
	// Run the query and print results when the query job is completed.
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	it, err := job.Read(ctx)
	for {
		var row []bigquery.Value
		err := it.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintln(w, row)
	}
	return nil
}

Java

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

Pour enregistrer des résultats de requête dans une table permanente, définissez la table de destination sur l'identifiant TableId souhaité dans une configuration QueryJobConfiguration.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableId;

public class SaveQueryToTable {

  public static void runSaveQueryToTable() {
    // TODO(developer): Replace these variables before running the sample.
    String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
    String destinationTable = "MY_TABLE";
    String destinationDataset = "MY_DATASET";

    saveQueryToTable(destinationDataset, destinationTable, query);
  }

  public static void saveQueryToTable(
      String destinationDataset, String destinationTableId, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Identify the destination table
      TableId destinationTable = TableId.of(destinationDataset, destinationTableId);

      // Build the query job
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query).setDestinationTable(destinationTable).build();

      // Execute the query.
      bigquery.query(queryConfig);

      // The results are now saved in the destination table.

      System.out.println("Saved query ran successfully");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Saved query did not run \n" + e.toString());
    }
  }
}

Node.js

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Node.js décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery Node.js.

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryDestinationTable() {
  // Queries the U.S. given names dataset for the state of Texas
  // and saves results to permanent table.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  // Create destination table reference
  const dataset = bigquery.dataset(datasetId);
  const destinationTable = dataset.table(tableId);

  const query = `SELECT name
    FROM \`bigquery-public-data.usa_names.usa_1910_2013\`
    WHERE state = 'TX'
    LIMIT 100`;

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    query: query,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
    destination: destinationTable,
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);

  console.log(`Job ${job.id} started.`);
  console.log(`Query results loaded to table ${destinationTable.id}`);
}

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 de l'API BigQuery Python.

Pour enregistrer des résultats de requête dans une table permanente, créez une configuration QueryJobConfig et définissez la destination sur la valeur TableReference souhaitée. Transmettez la configuration de la tâche à la méthode de requête.
from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the destination table.
# table_id = "your-project.your_dataset.your_table_name"

job_config = bigquery.QueryJobConfig(destination=table_id)

sql = """
    SELECT corpus
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY corpus;
"""

# Start the query, passing in the extra configuration.
query_job = client.query(sql, job_config=job_config)  # Make an API request.
query_job.result()  # Wait for the job to complete.

print("Query results loaded to the table {}".format(table_id))

Créer une table qui référence une source de données externe

Une source de données externe peut être interrogée directement depuis BigQuery, même si les données ne sont pas stockées dans un stockage BigQuery.

BigQuery accepte les sources de données externes suivantes :

Pour en savoir plus, consultez la page Présentation des sources de données externes.

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

Lorsque vous chargez des données dans BigQuery, vous pouvez charger des données dans une nouvelle table ou partition, les ajouter à une table ou une partition existante, ou bien les utiliser pour écraser une table ou une partition. Vous n'avez pas besoin de créer une table vide avant de charger des données. Vous pouvez créer la table et charger vos données en même temps.

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

Pour en savoir plus sur le chargement des données, consultez la page Introduction au chargement de données dans BigQuery.

Contrôler l'accès aux tables

Pour configurer l'accès aux tables et aux vues, vous pouvez attribuer un rôle IAM à une entité située aux niveaux suivants, classés par ordre de ressources autorisées (de la plus grande à la plus petite) :

  • À un niveau élevé dans la hiérarchie des ressources Google Cloud, tel qu'au niveau du projet, du dossier ou de l'organisation
  • Au niveau de l'ensemble de données
  • Au niveau de la table ou de la vue

Vous pouvez également restreindre l'accès aux données dans les tables à l'aide des méthodes suivantes :

L'accès avec n'importe quelle ressource protégée par IAM est cumulatif. Par exemple, si une entité n'a pas accès au niveau supérieur (un projet, par exemple), vous pouvez lui accorder l'accès au niveau de l'ensemble de données afin qu'elle ait accès aux tables et aux vues de l'ensemble de données. De même, si l'entité n'a pas accès au niveau supérieur ou au niveau de l'ensemble de données, vous pouvez lui accorder l'accès au niveau de la table ou de la vue.

L'attribution de rôles IAM à un niveau supérieur dans la hiérarchie des ressources Google Cloud (au niveau du projet, du dossier ou de l'organisation) permet à l'entité d'accéder à un vaste ensemble de ressources. Par exemple, si vous attribuez un rôle à une entité au niveau du projet, elle obtient des autorisations qui s'appliquent à tous les ensembles de données du projet.

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

L'attribution d'un rôle au niveau de la table ou de la vue spécifie les opérations qu'une entité est autorisée à effectuer sur des tables et des vues spécifiques, même si cette entité n'a pas accès à un niveau supérieur. Pour en savoir plus sur la configuration des contrôles d'accès au niveau des tables, consultez la page Contrôler l'accès aux tables et aux vues.

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

Vous ne pouvez pas définir une autorisation "deny" sur une ressource protégée par IAM.

Pour en savoir plus sur les rôles et les autorisations, consultez la page Comprendre les rôles dans la documentation IAM, ainsi que les rôles et autorisations IAM de BigQuery.

Obtenir des informations sur les tables

Vous pouvez obtenir des informations ou des métadonnées sur les tables de différentes manières :

  • Utiliser la console
  • À l'aide de la commande bq show de l'outil de ligne de commande bq
  • En appelant la méthode API tables.get
  • En utilisant les bibliothèques clientes
  • En interrogeant les vues INFORMATION_SCHEMA (version bêta)

Autorisations requises

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

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

En outre, si un utilisateur possède les autorisations bigquery.datasets.create, il obtient également un accès bigquery.dataOwner à l'ensemble de données qu'il crée. L'accès bigquery.dataOwner permet à l'utilisateur de récupérer les métadonnées d'une table.

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

Obtenir des informations sur la table

Pour obtenir des informations sur les tables :

Console

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

  2. Cliquez sur le nom de l'ensemble de données pour le développer. Les tables et les vues de l'ensemble de données s'affichent.

  3. Cliquez sur le nom de la table.

  4. Dans le panneau Details (Détails), cliquez sur Details (Détails) pour afficher la description et les informations de la table.

  5. Si vous le souhaitez, accédez à l'onglet Schema (Schéma) pour afficher la définition du schéma de la table.

bq

Exécutez la commande bq show pour afficher toutes les informations sur la table. L'option --schema permet de n'afficher que les informations de schéma de table. Vous pouvez contrôler le résultat à l'aide de l'option --format.

Si vous souhaitez obtenir des informations sur une table se trouvant dans un projet autre que celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données, en respectant le 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 la table 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 la table mytable dans mydataset. mydataset se trouve dans le projet myotherproject, et non dans votre projet par défaut.

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

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

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

API

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

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 en langage Go.

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
)

// printTableInfo demonstrates fetching metadata from a table and printing some basic information
// to an io.Writer.
func printTableInfo(w io.Writer, projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	meta, err := client.Dataset(datasetID).Table(tableID).Metadata(ctx)
	if err != nil {
		return err
	}
	// Print basic information about the table.
	fmt.Fprintf(w, "Schema has %d top-level fields\n", len(meta.Schema))
	fmt.Fprintf(w, "Description: %s\n", meta.Description)
	fmt.Fprintf(w, "Rows in managed storage: %d\n", meta.NumRows)
	return nil
}

Java

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

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Table;
import com.google.cloud.bigquery.TableId;

public class GetTable {

  public static void runGetTable() {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "bigquery_public_data";
    String datasetName = "samples";
    String tableName = "shakespeare";
    getTable(projectId, datasetName, tableName);
  }

  public static void getTable(String projectId, String datasetName, String tableName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(projectId, datasetName, tableName);
      Table table = bigquery.getTable(tableId);
      System.out.println("Table info: " + table.getDescription());
    } catch (BigQueryException e) {
      System.out.println("Table not retrieved. \n" + e.toString());
    }
  }
}

Node.js

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Node.js décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery Node.js.

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function getTable() {
  // Retrieves table named "my_table" in "my_dataset".

  /**
   * TODO(developer): Uncomment the following lines before running the sample
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";

  // Retrieve table reference
  const dataset = bigquery.dataset(datasetId);
  const [table] = await dataset.table(tableId).get();

  console.log('Table:');
  console.log(table.metadata.tableReference);
}
getTable();

PHP

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

use Google\Cloud\BigQuery\BigQueryClient;

/** Uncomment and populate these variables in your code */
//$projectId = 'The Google project ID';
//$datasetId = 'The BigQuery dataset ID';
//$tableId   = 'The BigQuery table ID';

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$table = $dataset->table($tableId);

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 de l'API BigQuery Python.


from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the model to fetch.
# table_id = 'your-project.your_dataset.your_table'

table = client.get_table(table_id)  # Make an API request.

# View table properties
print(
    "Got table '{}.{}.{}'.".format(table.project, table.dataset_id, table.table_id)
)
print("Table schema: {}".format(table.schema))
print("Table description: {}".format(table.description))
print("Table has {} rows".format(table.num_rows))

Obtenir des informations sur la table à l'aide de INFORMATION_SCHEMA

INFORMATION_SCHEMA est une série de vues donnant accès aux métadonnées sur des ensembles de données, des routines, des tables, des vues, des tâches, des réservations et des données de streaming.

Vous pouvez interroger les vues suivantes pour obtenir des informations sur la table:

  • Utilisez les vues INFORMATION_SCHEMA.TABLES et INFORMATION_SCHEMA.TABLE_OPTIONS pour extraire les métadonnées des tables et vues d'un projet.
  • Utilisez les vues INFORMATION_SCHEMA.COLUMNS et INFORMATION_SCHEMA.COLUMN_FIELD_PATHS pour extraire les métadonnées des colonnes (champs) d'une table.
  • Utilisez les vues INFORMATION_SCHEMA.TABLE_STORAGE et INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_* pour extraire les métadonnées concernant l'utilisation actuelle et historique de l'espace de stockage par une table.

Les vues TABLES et TABLE_OPTIONS contiennent également des informations générales sur les vues. Pour obtenir des informations détaillées, interrogez plutôt la vue INFORMATION_SCHEMA.VIEWS.

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. Pour obtenir des informations détaillées sur les vues, interrogez plutôt la vue INFORMATION_SCHEMA.VIEWS.

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

Nom de la colonne Type de données Value
table_catalog STRING ID 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 (l'un des éléments suivants) :
is_insertable_into STRING YES ou NO, suivant que la table accepte ou non les instructions LMD INSERT
is_typed STRING La valeur est toujours NO
creation_time TIMESTAMP Date/Heure de création de la table
ddl STRING L'Instruction LDD peut être utilisée pour recréer la table, par exemple CREATE TABLE ou CREATE VIEW
clone_time TIMESTAMP Pour les clones de table (bêta), heure à laquelle la table de base a étéclonée pour créer cette table. Si la fonctionnalité temporelle a été utilisée, ce champ contient l'horodatage de la fonctionnalité temporelle. Dans le cas contraire, le champ clone_time est identique au champ creation_time. Applicable uniquement aux tables dont la valeur table_type est définie sur CLONE.
base_table_catalog STRING Pour les clones de table (bêta), projet de la table de base. Applicable uniquement aux tables dont la valeur table_type est définie sur CLONE.
base_table_schema STRING Pour les clones de table (bêta), ensemble de données de la table de base. Applicable uniquement aux tables dont la valeur table_type est définie sur CLONE.
base_table_name STRING Pour les clones de table (bêta), nom de la table de base. Applicable uniquement aux tables dont la valeur table_type est définie sur CLONE.
default_collation_name STRING Nom de la spécification de classement par défaut, le cas échéant. Dans le cas contraire, cette valeur est définie sur NULL.

Examples

Exemple 1 :

L'exemple suivant récupère les métadonnées de toutes les tables de l'ensemble de données nommé mydataset. La requête sélectionne 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 tous les types de tables de mydataset dans votre projet par défaut.

mydataset contient les tables suivantes :

  • mytable1 : une table BigQuery standard
  • myview1 : une vue BigQuery

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.

  SELECT
    * EXCEPT(is_typed)
  FROM
    mydataset.INFORMATION_SCHEMA.TABLES;

Le résultat ressemble à ce qui suit. Pour des raisons de lisibilité, certaines colonnes sont exclues des résultats.

+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
| table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |                     ddl                     |
+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
| myproject      | mydataset     | mytable1       | BASE TABLE | YES                | 2018-10-29 20:34:44 | CREATE TABLE `myproject.mydataset.mytable1` |
|                |               |                |            |                    |                     | (                                           |
|                |               |                |            |                    |                     |   id INT64                                  |
|                |               |                |            |                    |                     | );                                          |
| myproject      | mydataset     | myview1        | VIEW       | NO                 | 2018-12-29 00:19:20 | CREATE VIEW `myproject.mydataset.myview1`   |
|                |               |                |            |                    |                     | AS SELECT 100 as id;                        |
+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
Exemple 2 :

L'exemple suivant récupère toutes les tables de type BASE TABLE à partir 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.

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.

  SELECT
    * EXCEPT(is_typed)
  FROM
    mydataset.INFORMATION_SCHEMA.TABLES
  WHERE
    table_type = 'BASE TABLE';

Le résultat ressemble à ce qui suit. Pour des raisons de lisibilité, certaines colonnes sont exclues des résultats.

  +----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |                     ddl                     |
  +----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | YES                | 2018-10-31 22:40:05 | CREATE TABLE myproject.mydataset.mytable1 |
  |                |               |                |            |                    |                     | (                                           |
  |                |               |                |            |                    |                     |   id INT64                                  |
  |                |               |                |            |                    |                     | );                                          |
  +----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
  

Exemple 3 :

L'exemple suivant récupère les colonnes table_name et ddl de la vue INFORMATION_SCHEMA.TABLES pour la table population_by_zip_2010 de l'ensemble de données census_bureau_usa. Celui-ci fait partie du programme d'ensembles de données publics de BigQuery.

Comme la table que vous interrogez se trouve dans un autre projet, ajoutez l'ID du projet à l'ensemble de données en utilisant le format suivant : `project_id`.dataset.INFORMATION_SCHEMA.view. Dans cet exemple, la valeur est `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES.

SELECT
  table_name, ddl
FROM
  `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES
WHERE
  table_name = 'population_by_zip_2010';

Le résultat ressemble à ce qui suit :

+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|       table_name       |                                                                                                            ddl                                                                                                             |
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| population_by_zip_2010 | CREATE TABLE `bigquery-public-data.census_bureau_usa.population_by_zip_2010`                                                                                                                                               |
|                        | (                                                                                                                                                                                                                          |
|                        |   geo_id STRING OPTIONS(description="Geo code"),                                                                                                                                                                           |
|                        |   zipcode STRING NOT NULL OPTIONS(description="Five digit ZIP Code Tabulation Area Census Code"),                                                                                                                          |
|                        |   population INT64 OPTIONS(description="The total count of the population for this segment."),                                                                                                                             |
|                        |   minimum_age INT64 OPTIONS(description="The minimum age in the age range. If null, this indicates the row as a total for male, female, or overall population."),                                                          |
|                        |   maximum_age INT64 OPTIONS(description="The maximum age in the age range. If null, this indicates the row as having no maximum (such as 85 and over) or the row is a total of the male, female, or overall population."), |
|                        |   gender STRING OPTIONS(description="male or female. If empty, the row is a total population summary.")                                                                                                                    |
|                        | )                                                                                                                                                                                                                          |
|                        | OPTIONS(                                                                                                                                                                                                                   |
|                        |   labels=[("freebqcovid", "")]                                                                                                                                                                                             |
|                        | );                                                                                                                                                                                                                         |
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  

Vue TABLE_OPTIONS

Lorsque vous interrogez la vue INFORMATION_SCHEMA.TABLE_OPTIONS, les résultats de la requête contiennent une ligne pour chaque option, pour chaque table ou vue d'un ensemble de données. Pour obtenir des informations détaillées sur les vues, interrogez plutôt la vue INFORMATION_SCHEMA.VIEWS.

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

Nom de la colonne Type de données Valeur
TABLE_CATALOG STRING ID 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 Heure d'expiration de cette 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
require_partition_filter BOOL Filtre de partition nécessaire ou non pour les requêtes sur la table
enable_refresh BOOL Indique si l'actualisation automatique est activée pour une vue matérialisée.
refresh_interval_minutes FLOAT64 Fréquence d'actualisation d'une vue matérialisée

Pour les tables externes, les options suivantes sont également possibles :

Options
allow_jagged_rows

BOOL

Si la valeur est true, autorise les lignes auxquelles il manque des colonnes finales facultatives.

S'applique aux données CSV.

allow_quoted_newlines

BOOL

Si la valeur est true, autorise les sections de données entre guillemets contenant des caractères de retour à la ligne dans le fichier.

S'applique aux données CSV.

compression

STRING

Type de compression de la source de données. Valeur autorisée : GZIP. Si cette option n'est pas spécifiée, la source de données n'est pas compressée.

S'applique aux données CSV et JSON.

description

STRING

Description de cette table.

enable_logical_types

BOOL

Si la valeur est true, convertit les types logiques Avro en types SQL correspondants. Pour en savoir plus, consultez la section Types logiques.

S'applique aux données Avro.

enum_as_string

BOOL

Si la valeur est définie sur true, déduit le type logique ENUM Parquet en tant que STRING au lieu de BYTES par défaut.

S'applique aux données Parquet.

enable_list_inference

BOOL

Si la valeur est définie sur true, utilise l'inférence de schéma spécifiquement pour le type logique LIST Parquet.

S'applique aux données Parquet.

encoding

STRING

Encodage des caractères des données. Valeurs autorisées : UTF8 (ou UTF-8), ISO_8859_1 (ou ISO-8859-1).

S'applique aux données CSV.

expiration_timestamp

TIMESTAMP

Date et heure d'expiration de cette table. Si cette option n'est pas spécifiée, la table n'expire pas.

Exemple : "2025-01-01 00:00:00 UTC".

field_delimiter

STRING

Séparateur des champs dans un fichier CSV.

S'applique aux données CSV.

format

STRING

Format des données externes. Les valeurs acceptées pour CREATE EXTERNAL TABLE incluent les valeurs suivantes : AVRO, CSV ,DATASTORE_BACKUP, GOOGLE_SHEETS ,NEWLINE_DELIMITED_JSON (ou JSON), ORC, PARQUET.

Les valeurs acceptées pour LOAD DATA incluent les valeurs suivantes : AVRO, CSV, NEWLINE_DELIMITED_JSON (ou JSON), ORC, PARQUET.

La valeur JSON est équivalente à NEWLINE_DELIMITED_JSON.

decimal_target_types

ARRAY<STRING>

Détermine comment convertir un type Decimal. Équivaut à ExternalDataConfiguration.decimal_target_types.

Exemple : ["NUMERIC", "BIGNUMERIC"].

json_extension

STRING

Pour les données JSON, indique un format d'échange JSON particulier. Si ce n'est pas le cas, BigQuery lit les données sous forme d'enregistrements JSON génériques.

Les valeurs acceptées sont les suivantes :
GEOJSON. Données GeoJSON délimitées par des retours à la ligne. Pour en savoir plus, consultez la section Créer une table externe à partir d'un fichier GeoJSON délimité par un retour à la ligne.

hive_partition_uri_prefix

STRING

Préfixe commun à tous les URI sources avant le début de l'encodage de la clé de partition. Ne s'applique qu'aux tables externes partitionnées avec Hive.

S'applique aux données Avro, CSV, JSON, Parquet et ORC.

Exemple : "gs://bucket/path".

ignore_unknown_values

BOOL

Si la valeur est true, ignore les valeurs supplémentaires qui ne sont pas représentées dans le schéma de la table, sans renvoyer d'erreur.

S'applique aux données CSV et JSON.

max_bad_records

INT64

Nombre maximal d'enregistrements incorrects à ignorer lors de la lecture des données.

S'applique aux données CSV, JSON et Sheets.

null_marker

STRING

Chaîne représentant les valeurs NULL dans un fichier CSV.

S'applique aux données CSV.

preserve_ascii_control_characters

BOOL

Si la valeur est true, les caractères de contrôle ASCII intégrés qui sont les 32 premiers caractères de la table ASCII, allant de "\x00" à "\x1F", sont conservés.

S'applique aux données CSV.

projection_fields

STRING

Liste des propriétés d'entité à charger.

S'applique aux données Datastore.

quote

STRING

Chaîne utilisée pour citer des sections de données dans un fichier CSV. Si vos données contiennent des caractères de retour à la ligne entre guillemets, définit également la propriété allow_quoted_newlines sur true.

S'applique aux données CSV.

require_hive_partition_filter

BOOL

Si la valeur est true, toutes les requêtes sur cette table nécessitent un filtre de partition pouvant être utilisé pour éliminer les partitions lors de la lecture des données. Ne s'applique qu'aux tables externes partitionnées avec Hive.

S'applique aux données Avro, CSV, JSON, Parquet et ORC.

sheet_range

STRING

Plage d'une feuille de calcul Sheets à interroger.

S'applique aux données Sheets.

Exemple : “sheet1!A1:B20”.

skip_leading_rows

INT64

Nombre de lignes en haut d'un fichier à ignorer lors de la lecture des données.

S'applique aux données CSV et Sheets.

uris

ARRAY<STRING>

Tableau d'URI complets pour les emplacements de données externes.

Exemple : ["gs://bucket/path/*"].

Examples

Exemple 1 :

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

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.

  SELECT
    *
  FROM
    mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
  WHERE
    option_name = 'expiration_timestamp';

Le résultat ressemble à ce qui suit :

  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | 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 de toutes les tables de 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 votre projet par défaut, ajoutez l'ID du projet à l'ensemble de données au format suivant : `project_id`.dataset.INFORMATION_SCHEMA.view. Par exemple, `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS.

  SELECT
    *
  FROM
    mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
  WHERE
    option_name = 'description'
    AND option_value LIKE '%test%';

Le résultat ressemble à ce qui suit :

  +----------------+---------------+------------+-------------+-------------+--------------+
  | 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 lancez une requête sur la vue INFORMATION_SCHEMA.COLUMNS, les résultats de la requête contiennent une ligne pour chaque colonne (champ) d'une table.

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

Nom de la colonne Type de données Valeur
TABLE_CATALOG STRING ID 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 ou non 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 qu'il s'agit ou non d'une colonne de partitionnement
CLUSTERING_ORDINAL_POSITION INT64 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.
COLLATION_NAME STRING Nom de la spécification de classement si elle existe. Dans le cas contraire, NULL

Si une valeur STRING ou ARRAY<STRING> est transmise, la spécification de classement est renvoyée si elle existe. Sinon, NULL est renvoyé.

Examples

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. Celui-ci fait partie du programme d'ensembles de données publics de BigQuery.

Comme la table que vous interrogez se trouve dans un autre projet (bigquery-public-data), vous ajoutez l'ID de projet à l'ensemble de données, en respectant le format `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
  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';

Le résultat ressemble à ce qui suit. Pour des raisons de lisibilité, certaines colonnes 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).

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 ID 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
COLLATION_NAME STRING Nom de la spécification de classement si elle existe. Dans le cas contraire, NULL

Si un champ STRING, ARRAY<STRING> ou STRING d'un élément STRUCT est transmis, la spécification de classement est renvoyée si elle existe. Sinon, NULL est renvoyé.

Examples

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. Celui-ci fait partie du programme d'ensembles de données publics de BigQuery.

Comme la table que vous interrogez se trouve dans un autre projet (bigquery-public-data), vous ajoutez l'ID de projet à l'ensemble de données, en respectant le format `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 imbriquée RECORD
  • committer : colonne imbriquée RECORD
  • trailer : colonne imbriquée et répétée RECORD
  • difference : colonne imbriquée et répétée RECORD

Pour afficher les métadonnées des colonnes author et difference, exécutez la requête suivante.

SELECT
  *
FROM
  `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE
  table_name = 'commits'
  AND (column_name = 'author' OR column_name = 'difference');

Le résultat ressemble à ce qui suit. Pour des raisons de lisibilité, certaines colonnes 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        |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  

Vue TABLE_STORAGE

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

Nom de la colonne Type de données Valeur
PROJECT_ID STRING ID du projet qui contient l'ensemble de données
PROJECT_NUMBER INT64 Numéro du projet contenant l'ensemble de données
TABLE_SCHEMA STRING Nom de l'ensemble de données contenant la table ou la vue matérialisée, également appelé datasetId
TABLE_NAME STRING Nom de la table ou de la vue matérialisée, également appelé tableId
CREATION_TIME TIMESTAMP Date/Heure de création de la table
TOTAL_ROWS INT64 Nombre total de lignes dans la table ou la vue matérialisée
TOTAL_PARTITIONS INT64 Nombre de partitions présentes dans la table ou la vue matérialisée. Les tables non partitionnées renvoient la valeur 0.
TOTAL_LOGICAL_BYTES INT64 Nombre total d'octets (non compressés) logiques dans la table ou la vue matérialisée
ACTIVE_LOGICAL_BYTES INT64 Nombre d'octets (non compressés) logiques datant de moins de 90 jours
LONG_TERM_LOGICAL_BYTES INT64 Nombre d'octets (non compressés) logiques datant de plus de 90 jours
TOTAL_PHYSICAL_BYTES INT64 Nombre total d'octets (compressés) physiques utilisés pour le stockage, y compris les octets actifs, à long terme et de fonctionnalité temporelle (pour les tables supprimées)
ACTIVE_PHYSICAL_BYTES INT64 Nombre d'octets (compressés) physiques datant de moins de 90 jours
LONG_TERM_PHYSICAL_BYTES INT64 Nombre d'octets (compressés) physiques datant de plus de 90 jours
TIME_TRAVEL_PHYSICAL_BYTES INT64 Nombre d'octets (compressés) physiques utilisés par le stockage temporel (données supprimées ou modifiées)

Examples

L'exemple suivant montre quels projets de votre organisation utilisent le plus d'espace de stockage.

SELECT
  project_id,
  SUM(total_logical_bytes) AS total_logical_bytes
FROM
  `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE
GROUP BY
  project_id
ORDER BY
  total_logical_bytes DESC;

Le résultat ressemble à ce qui suit :

+---------------------+---------------------+
|     project_id      | total_logical_bytes |
+---------------------+---------------------+
| projecta            |     971329178274633 |
+---------------------+---------------------+
| projectb            |     834638211024843 |
+---------------------+---------------------+
| projectc            |     562910385625126 |
+---------------------+---------------------+

TABLE_STORAGE_TIMELINE_BY_* vues

Les vues de chronologie de stockage de table renvoient une ligne pour chaque événement déclenchant une modification de stockage de la table, telle que l'écriture, la mise à jour ou la suppression d'une ligne. Par conséquent, il peut y avoir plusieurs lignes par table pour une même journée. Lorsque vous interrogez une vue pour une période donnée, utilisez l'horodatage le plus récent du jour qui vous intéresse.

Cette vue présente le schéma suivant:

Column name Type de données Value
TIMESTAMP TIMESTAMP Horodatage du dernier recalcul du stockage. Le nouveau calcul est déclenché par les modifications apportées aux données de la table.
DELETED BOOLEAN Indique si la table est supprimée ou non.
PROJECT_ID STRING ID du projet qui contient l'ensemble de données
PROJECT_NUMBER INT64 Numéro du projet contenant l'ensemble de données
TABLE_SCHEMA STRING Nom de l'ensemble de données contenant la table ou la vue matérialisée, également appelé datasetId
TABLE_NAME STRING Nom de la table ou de la vue matérialisée, également appelé tableId
CREATION_TIME TIMESTAMP Date/Heure de création de la table
TOTAL_ROWS INT64 Nombre total de lignes dans la table ou la vue matérialisée
TOTAL_PARTITIONS INT64 Nombre de partitions pour la table ou la vue matérialisée. Les tables non partitionnées renvoient la valeur 0.
TOTAL_LOGICAL_BYTES INT64 Nombre total d'octets logiques dans la table ou la vue matérialisée
ACTIVE_LOGICAL_BYTES INT64 Nombre d'octets logiques datant de moins de 90 jours
LONG_TERM_LOGICAL_BYTES INT64 Nombre d'octets logiques datant de plus de 90 jours
TOTAL_PHYSICAL_BYTES INT64 Nombre total d'octets physiques utilisés pour le stockage, y compris les octets actifs, à long terme et de fonctionnalité temporelle (pour les tables supprimées)
ACTIVE_PHYSICAL_BYTES INT64 Nombre d'octets physiques datant de moins de 90 jours
LONG_TERM_PHYSICAL_BYTES INT64 Nombre d'octets physiques datant de plus de 90 jours
TIME_TRAVEL_PHYSICAL_BYTES INT64 Nombre d'octets physiques utilisés par le stockage temporel (données supprimées ou modifiées)

Examples

Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet en respectant le format suivant :

PROJECT_ID.DATASET_ID.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION
Remplacez les éléments suivants :

  • PROJECT_ID : ID du projet
  • DATASET_ID : ID de l'ensemble de données.

Par exemple, myproject.mydataset.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION.

L'exemple suivant montre la somme de l'espace de stockage physique utilisé par chaque projet de votre organisation à un moment donné :

WITH most_recent_records as (
  SELECT
    project_id,
    table_schema,
    table_name,
    MAX(timestamp) as max_timestamp
  FROM
    `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION
  WHERE
    timestamp <= 'TIMESTAMP'
  GROUP BY
    project_id, table_schema, table_name
  )
  SELECT
    i_s.project_id,
    SUM(i_s.total_physical_bytes) AS TotalPhysicalBytes
  FROM
    `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION as i_s
  JOIN
    most_recent_records
  ON
    i_s.project_id = most_recent_records.project_id
    AND i_s.table_schema = most_recent_records.table_schema
    AND i_s.table_name = most_recent_records.table_name
    AND i_s.timestamp = most_recent_records.max_timestamp
  GROUP BY
    project_id;

Le résultat ressemble à ce qui suit :

-----------------+------------------------+
|  project_id    |  TotalPhysicalBytes    |
+----------------+------------------------+
| projecta       | 3844                   |
| projectb       | 16022778               |
| projectc       | 8934009                |
+----------------+------------------------+

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

Vous pouvez répertorier des tables dans des ensembles de données de plusieurs manières :

  • Utiliser la console
  • À l'aide de la commande bq ls de l'outil de ligne de commande bq
  • En appelant la méthode API tables.list
  • En utilisant les bibliothèques clientes

Autorisations requises

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

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

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

Répertorier des tables

Pour répertorier les tables dans un ensemble de données :

Console

  1. Dans le volet de navigation de la console, cliquez sur l'ensemble de données pour le développer. Les tables et vues de ce dernier s'affichent.

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

bq

Exécutez la commande bq ls. Vous pouvez contrôler le résultat à l'aide de l'option --format. Si vous répertoriez des tables dans un projet autre que votre projet par défaut, ajoutez l'ID du projet à l'ensemble de données, en respectant le format suivant : project_id:dataset.

Les options supplémentaires comprennent les éléments suivants :

  • --max_results ou -n : entier indiquant le nombre maximal de résultats. La valeur par défaut est 50.
bq ls \
--format=pretty \
--max_results integer \
project_id:dataset

Où :

  • integer est un entier représentant le nombre de tables à répertorier.
  • 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. Par exemple :

+-------------------------+-------+----------------------+-------------------+
|         tableId         | Type  |        Labels        | Time Partitioning |
+-------------------------+-------+----------------------+-------------------+
| mytable                 | TABLE | department:shipping  |                   |
| myview                  | VIEW  |                      |                   |
+-------------------------+-------+----------------------+-------------------+

Exemples :

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

    bq ls --format=pretty mydataset

Saisissez la commande suivante pour renvoyer plus de 50 tables (sortie par défaut) de l'ensemble de données mydataset. mydataset se trouve dans votre projet par défaut.

    bq ls --format=pretty --max_results 60 mydataset

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

    bq ls --format=pretty myotherproject:mydataset

API

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

C#

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour C# décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery C#.


using Google.Cloud.BigQuery.V2;
using System;
using System.Collections.Generic;
using System.Linq;

public class BigQueryListTables
{
    public void ListTables(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        // Retrieve list of tables in the dataset
        List<BigQueryTable> tables = client.ListTables(datasetId).ToList();
        // Display the results
        if (tables.Count > 0)
        {
            Console.WriteLine($"Tables in dataset {datasetId}:");
            foreach (var table in tables)
            {
                Console.WriteLine($"\t{table.Reference.TableId}");
            }
        }
        else
        {
            Console.WriteLine($"{datasetId} does not contain any tables.");
        }
    }
}

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 en langage Go.

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

// listTables demonstrates iterating through the collection of tables in a given dataset.
func listTables(w io.Writer, projectID, datasetID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	ts := client.Dataset(datasetID).Tables(ctx)
	for {
		t, err := ts.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "Table: %q\n", t.TableID)
	}
	return nil
}

Java

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

import com.google.api.gax.paging.Page;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQuery.TableListOption;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.DatasetId;
import com.google.cloud.bigquery.Table;

public class ListTables {

  public static void runListTables() {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "bigquery-public-data";
    String datasetName = "samples";
    listTables(projectId, datasetName);
  }

  public static void listTables(String projectId, String datasetName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      DatasetId datasetId = DatasetId.of(projectId, datasetName);
      Page<Table> tables = bigquery.listTables(datasetId, TableListOption.pageSize(100));
      tables.iterateAll().forEach(table -> System.out.print(table.getTableId().getTable() + "\n"));

      System.out.println("Tables listed successfully.");
    } catch (BigQueryException e) {
      System.out.println("Tables were not listed. Error occurred: " + e.toString());
    }
  }
}

Node.js

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Node.js décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery Node.js.

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function listTables() {
  // Lists tables in 'my_dataset'.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset';

  // List all tables in the dataset
  const [tables] = await bigquery.dataset(datasetId).getTables();

  console.log('Tables:');
  tables.forEach(table => console.log(table.id));
}

PHP

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

use Google\Cloud\BigQuery\BigQueryClient;

/** Uncomment and populate these variables in your code */
// $projectId  = 'The Google project ID';
// $datasetId  = 'The BigQuery dataset ID';

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$tables = $dataset->tables();
foreach ($tables as $table) {
    print($table->id() . PHP_EOL);
}

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 de l'API BigQuery Python.


from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set dataset_id to the ID of the dataset that contains
#                  the tables you are listing.
# dataset_id = 'your-project.your_dataset'

tables = client.list_tables(dataset_id)  # Make an API request.

print("Tables contained in '{}':".format(dataset_id))
for table in tables:
    print("{}.{}.{}".format(table.project, table.dataset_id, table.table_id))

Ruby

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Ruby décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery en langage Ruby.

require "google/cloud/bigquery"

def list_tables dataset_id = "your_dataset_id"
  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id

  puts "Tables in dataset #{dataset_id}:"
  dataset.tables.each do |table|
    puts "\t#{table.table_id}"
  end
end

Sécurité des tables

Pour savoir comment contrôler l'accès aux tables dans BigQuery, consultez la page Présentation des contrôles d'accès aux tables.

Étapes suivantes

Faites l'essai

Si vous débutez sur Google Cloud, créez un compte pour évaluer les performances de BigQuery en conditions réelles. Les nouveaux clients bénéficient également de 300 $ de crédits offerts pour exécuter, tester et déployer des charges de travail.

Profiter d'un essai gratuit de BigQuery