Spécifier un schéma

BigQuery vous permet de spécifier le schéma d'une table lorsque vous chargez des données dans une table et lorsque vous créez une table vide. Vous pouvez également utiliser la détection automatique de schéma pour les formats de données compatibles.

Lorsque vous chargez des fichiers d'exportation Avro, Parquet, ORC, Firestore ou Datastore, le schéma est automatiquement extrait des données sources auto-descriptives.

Vous pouvez spécifier le schéma d'une table des manières suivantes :

• Utilisez la console Google Cloud.
• Utilisez l'instruction SQL CREATE TABLE.
• De manière intégrée avec l'outil de ligne de commande bq
• Créez un fichier de schéma au format JSON.
• Appelez la méthode jobs.insert et configurez la propriété schema dans la configuration de la tâche load.
• Appelez la méthode tables.insert et configurez le schéma dans la ressource de table à l'aide de la propriété schema.

Une fois les données chargées ou une table vide créée, vous pouvez modifier la définition de schéma de la table.

Composants du schéma

Lorsque vous spécifiez un schéma de table, vous devez fournir le nom et le type de données de chaque colonne. Vous pouvez également fournir la description, le mode et la valeur par défaut d'une colonne.

Noms de colonne

Un nom de colonne peut contenir des lettres (a-z, A-Z), des chiffres (0-9) ou des traits de soulignement (_), et doit commencer par une lettre ou un trait de soulignement. Si vous utilisez des noms de colonnes flexibles, BigQuery vous autorise à commencer un nom de colonne par un chiffre. Soyez prudent lorsque vous commencez des noms de colonnes par un chiffre, car l'utilisation de noms de colonnes flexibles avec l'API BigQuery Storage Read ou l'API BigQuery Storage Write nécessite un traitement particulier. Pour en savoir plus sur la compatibilité des noms de colonnes flexibles, consultez Noms de colonnes flexibles.

Les noms de colonne ne doivent pas comporter plus de 300 caractères. Les noms de colonnes ne peuvent utiliser aucun des préfixes suivants :

• _TABLE_
• _FILE_
• _PARTITION
• _ROW_TIMESTAMP
• __ROOT__
• _COLIDENTIFIER

Les noms de colonnes en double ne sont pas autorisés, même si la casse est différente. Par exemple, la colonne Column1 est considérée comme identique à la colonne column1. Pour en savoir plus sur les règles de dénomination des colonnes, consultez Noms de colonnes dans la documentation de référence GoogleSQL.

Si un nom de table (par exemple, test) est identique à l'un de ses noms de colonne (par exemple, test), l'expression SELECT interprète la colonne test comme un élément STRUCT contenant toutes les autres colonnes de la table. Pour éviter ce conflit, utilisez l'une des méthodes suivantes :

• Évitez d'utiliser le même nom pour une table et ses colonnes.

• Attribuez un autre alias à la table. Par exemple, la requête suivante attribue un alias de table t à la table project1.dataset.test :

  SELECT test FROM project1.dataset.test AS t;
  

• Incluez le nom de la table lorsque vous référencez une colonne. Exemple :

  SELECT test.test FROM project1.dataset.test;
  

Noms de colonne flexibles

Vous disposez d'une plus grande flexibilité dans vos noms de colonnes, y compris d'un accès étendu aux caractères dans d'autres langues que l'anglais, ainsi qu'à d'autres symboles.

Les noms de colonnes flexibles acceptent les caractères suivants :

• N'importe quelle lettre dans n'importe quelle langue comme représenté par l'expression régulière Unicode \p{L}
• Tout caractère numérique dans n'importe quelle langue comme représenté par l'expression régulière Unicode \p{N}.
• Tout caractère de ponctuation du connecteur, y compris les traits de soulignement, comme représenté par l'expression régulière Unicode \p{Pc}.
• Trait d'union ou tiret comme représenté par l'expression régulière Unicode \p{Pd}.
• Toute marque destinée à accompagner un autre caractère comme représenté par l'expression régulière Unicode \p{M}. Par exemple, les accents, les tréma ou les cadres de délimitation.
• Les caractères spéciaux suivants :
  • Esperluette (&) comme représenté par l'expression régulière Unicode \u0026.
  • Signe du pourcentage (%) comme représenté par l'expression régulière Unicode \u0025.
  • Signe "égal à" (=) comme représenté par l'expression régulière Unicode \u003D.
  • Signe plus (+) comme représenté par l'expression régulière Unicode \u002B.
  • Signe deux-points (:) comme représenté par l'expression régulière Unicode \u003A.
  • Apostrophe (') comme représenté par l'expression régulière Unicode \u0027.
  • Signe "inférieur à" (<) comme représenté par l'expression régulière Unicode \u003C.
  • Signe "supérieur à" (>) comme représenté par l'expression régulière Unicode \u003E.
  • Signe numérique (#) comme représenté par l'expression régulière Unicode \u0023.
  • Ligne verticale (|) comme représenté par l'expression régulière Unicode \u007c
  • Whitespace.

Les noms de colonnes flexibles n'acceptent pas les caractères spéciaux suivants :

• Point d'exclamation (!) comme représenté par l'expression régulière Unicode \u0021.
• Guillemets (") comme représenté par l'expression régulière Unicode \u0022.
• Signe dollar ($) comme représenté par l'expression régulière Unicode \u0024.
• Parenthèse gauche (() comme représenté par l'expression régulière Unicode \u0028
• Parenthèse droite ()) comme représenté par l'expression régulière Unicode \u0029.
• Astérisque (*) comme représenté par l'expression régulière Unicode \u002A.
• Virgule (,) comme représenté par l'expression régulière Unicode \u002C.
• Point (.) comme représenté par l'expression régulière Unicode \u002E.
• Barre oblique (/) comme représenté par l'expression régulière Unicode \u002F.
• Point-virgule (;) comme représenté par l'expression régulière Unicode \u003B.
• Point d'interrogation (?) comme représenté par l'expression régulière Unicode \u003F.
• Signe arobase (@) comme représenté par l'expression régulière Unicode \u0040.
• Crochet gauche ([) comme représenté par l'expression régulière Unicode \u005B.
• Barre oblique inverse (\) comme représenté par l'expression régulière Unicode \u005C.
• Crochet droit (]) comme représenté par l'expression régulière Unicode \u005D.
• Accent circonflexe (^) comme représenté par l'expression régulière Unicode \u005E.
• Accent grave (`) comme représenté par l'expression régulière Unicode \u0060.
• Accolade gauche {{) comme représenté par l'expression régulière Unicode \u007B
• Accolade droite (}) comme représenté par l'expression régulière Unicode \u007D
• Tilde (~) comme représenté par l'expression régulière Unicode \u007E.

Pour obtenir des consignes supplémentaires, consultez la section Noms de colonne.

Les caractères de colonne étendus sont compatibles avec l'API BigQuery Storage Read et l'API BigQuery Storage Write. Pour utiliser la liste étendue des caractères Unicode avec l'API BigQuery Storage Read, vous devez définir une option. Vous pouvez utiliser l'attribut displayName pour récupérer le nom de la colonne. L'exemple suivant montre comment définir une option avec le client Python :

from google.cloud.bigquery_storage import types
requested_session = types.ReadSession()

#set avro serialization options for flexible column.
options = types.AvroSerializationOptions()
options.enable_display_name_attribute = True
requested_session.read_options.avro_serialization_options = options

Pour utiliser la liste étendue de caractères Unicode avec l'API BigQuery Storage Write, vous devez fournir le schéma avec la notation column_name, sauf si vous utilisez l'objet rédacteur JsonStreamWriter. L'exemple suivant montre comment fournir le schéma :

syntax = "proto2";
package mypackage;
// Source protos located in github.com/googleapis/googleapis
import "google/cloud/bigquery/storage/v1/annotations.proto";

message FlexibleSchema {
  optional string item_name_column = 1
  [(.google.cloud.bigquery.storage.v1.column_name) = "name-列"];
  optional string item_description_column = 2
  [(.google.cloud.bigquery.storage.v1.column_name) = "description-列"];
}

Dans cet exemple, item_name_column et item_description_column sont des noms d'espaces réservés qui doivent être conformes à la convention de dénomination du tampon de protocole. Notez que les annotations column_name ont toujours priorité sur les noms d'espaces réservés.

Limites

Les noms de colonnes flexibles ne sont pas compatibles avec les tables externes.

Descriptions de colonne

Chaque colonne peut inclure une description facultative. La description est une chaîne qui ne doit pas contenir plus de 1 024 caractères.

Valeurs par défaut

L'expression de valeur par défaut d'une colonne doit être un littéral ou l'une des fonctions suivantes :

• CURRENT_DATE
• CURRENT_DATETIME
• CURRENT_TIME
• CURRENT_TIMESTAMP
• GENERATE_UUID
• RAND
• SESSION_USER
• ST_GEOGPOINT

Types de données GoogleSQL

GoogleSQL vous permet de spécifier les types de données suivants dans votre schéma. Le type de données est un champ obligatoire.

Nom	Type de données	Description
Entier	INT64	Valeurs numériques sans composants fractionnaires
Virgule flottante	FLOAT64	Valeurs numériques approximatives avec composants fractionnaires
Numérique	NUMERIC	Valeurs numériques exactes avec composants fractionnaires
BigNumeric	BIGNUMERIC	Valeurs numériques exactes avec composants fractionnaires
Booléen	BOOL	TRUE ou FALSE (non sensible à la casse)
Chaîne	STRING	Données de type caractères de longueur variable (Unicode)
Octets	BYTES	Données binaires de longueur variable
Date	DATE	Date de calendrier logique
Date/Heure	DATETIME	Année, mois, jour, heure, minute, seconde et milliseconde
Heure	TIME	Heure indépendante d'une date précise
Temporel	TIMESTAMP	Point absolu dans le temps avec spécification de la microseconde
Type "Struct (Record)"	STRUCT	Conteneur de champs triés ayant chacun un type (obligatoire) et un nom de champ (facultatif)
Géographie	GEOGRAPHY	Ensemble de points sur la surface de la Terre (ensemble de points, de lignes et de polygones sur le sphéroïde de référence WGS84 avec des arêtes géodésiques)
JSON	JSON	Représente JSON, un format d'échange de données léger
RANGE	RANGE	Une plage de valeurs DATE, DATETIME ou TIMESTAMP

Pour en savoir plus sur les types de données en GoogleSQL, consultez la section Types de données GoogleSQL.

Vous pouvez également spécifier un type de tableau lorsque vous interrogez des données. Pour en savoir plus, consultez la section Utiliser des tableaux.

Modes

BigQuery accepte les modes suivants pour les colonnes. Le mode est un champ facultatif. Si le mode n'est pas spécifié, la valeur par défaut de la colonne est NULLABLE.

Mode	Description
Nullable	Colonne autorisant les valeurs NULL (paramètre par défaut)
Obligatoire	Valeurs NULL non autorisées
Répété	Colonne contenant un tableau de valeurs du type spécifié

Pour en savoir plus sur les modes, consultez la ligne mode de la section TableFieldSchema.

Mode d'arrondi

Lorsqu'une colonne est de type NUMERIC ou BIGNUMERIC, vous pouvez définir l'option de colonne rounding_mode qui détermine la manière dont les valeurs de cette colonne sont arrondies lorsqu'elles sont écrites dans la table. Vous pouvez définir l'option rounding_mode sur une colonne de premier niveau ou un champ STRUCT. Les modes d'arrondi suivants sont acceptés :

• "ROUND_HALF_AWAY_FROM_ZERO" : ce mode (par défaut) arrondit les cas à mi-chemin à la valeur la plus éloignée de zéro.
• "ROUND_HALF_EVEN" : ce mode arrondit les cas à mi-chemin au chiffre pair le plus proche.

Vous ne pouvez pas définir l'option rounding_mode pour une colonne qui n'est pas de type NUMERIC ou BIGNUMERIC. Pour en savoir plus sur ces types, consultez la section Types décimaux.

L'exemple suivant crée une table et insère des valeurs arrondies en fonction du mode d'arrondi de la colonne :

CREATE TABLE mydataset.mytable (
  x NUMERIC(5,2) OPTIONS (rounding_mode='ROUND_HALF_EVEN'),
  y NUMERIC(5,2) OPTIONS (rounding_mode='ROUND_HALF_AWAY_FROM_ZERO')
);
INSERT mydataset.mytable (x, y)
VALUES (NUMERIC "1.025", NUMERIC "1.025"),
       (NUMERIC "1.0251", NUMERIC "1.0251"),
       (NUMERIC "1.035", NUMERIC "1.035"),
       (NUMERIC "-1.025", NUMERIC "-1.025");

La table mytable ressemble à ceci :

+-------+-------+
| x     | y     |
+-------+-------+
| 1.02  | 1.03  |
| 1.03  | 1.03  |
| 1.04  | 1.04  |
| -1.02 | -1.03 |
+-------+-------+

Pour en savoir plus, consultez la description de roundingMode dans TableFieldSchema.

Spécifier des schémas

Lorsque vous chargez des données ou créez une table vide, vous pouvez spécifier le schéma de la table à l'aide de la console Google Cloud ou de l'outil de ligne de commande bq. La spécification d'un schéma est acceptée pour le chargement des fichiers CSV et JSON (délimités par un retour à la ligne). Lorsque vous chargez des données d'exportation Avro, Parquet, ORC, Firestore ou Datastore, le schéma est automatiquement extrait des données sources auto-descriptives.

Pour spécifier un schéma de table, procédez comme suit :

Console

Dans la console Google Cloud, vous pouvez spécifier un schéma à l'aide de l'option Ajouter un champ ou de l'option Modifier sous forme de texte.

1. Dans la console Google Cloud, ouvrez la page "BigQuery".

   Accéder à BigQuery

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

3. Développez l'option Actions puis cliquez sur Ouvrir.

4. Dans le panneau de détails, cliquez sur Créer une table.

5. Dans la section Source de la page Créer une table, sélectionnez Table vide.

6. Dans la section Destination de la page Créer une table :

   • Sous Nom de l'ensemble de données, sélectionnez l'ensemble de données approprié.

     

   • Dans le champ Nom de la table, saisissez le nom de la table que vous créez.

   • Vérifiez que le champ Table type (Type de table) est défini sur Native table (Table native).

7. Dans la section Schéma, saisissez la définition du schéma.

   • Option 1 : Cliquez sur Ajouter un champ, puis définissez les valeurs type et mode de chaque champ.
   • 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.

8. Cliquez sur Create table.

SQL

Utilisez l'instruction CREATE TABLE. Spécifiez le schéma en utilisant l'option column. L'instruction suivante crée une table nommée newtable avec des colonnes x, y et z dont les types sont respectivement "integer" (entier), "string" (chaîne) et "boolean" (booléen) :

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

   Accéder à BigQuery

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

   CREATE TABLE IF NOT EXISTS mydataset.newtable (x INT64, y STRING, z BOOL)
     OPTIONS(
       description = 'My example table');

3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

bq

Fournissez le schéma intégré au format field:data_type,field:data_type en utilisant l'une des commandes suivantes :

• Si vous chargez des données, utilisez la commande bq load.
• Si vous créez une table vide, utilisez la commande bq mk.

Lorsque vous spécifiez le schéma sur la ligne de commande, vous ne pouvez pas inclure les types RECORD (STRUCT) ou RANGE, vous ne pouvez pas inclure de description de colonne et vous ne pouvez pas 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, ainsi que des types RANGE, fournissez plutôt un fichier de schéma JSON.

Pour charger des données dans une table à l'aide d'une définition de schéma intégrée, entrez la commande load et spécifiez le format de données à l'aide de l'option --source_format. Si vous chargez des données dans une table d'un projet autre que votre projet par défaut, ajoutez l'ID du projet au format suivant : project_id:dataset.table_name.

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

bq --location=location load \
--source_format=format \
project_id:dataset.table_name \
path_to_source \
schema

Remplacez l'élément suivant :

• location : nom de votre emplacement. 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 définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc.
• format : NEWLINE_DELIMITED_JSON ou CSV.
• project_id : ID de votre projet.
• dataset : ensemble de données contenant la table dans laquelle vous chargez des données.
• table_name : nom de la table dans laquelle vous chargez des données.
• path_to_source : emplacement du fichier de données CSV ou JSON sur votre ordinateur local ou dans Cloud Storage.
• schema : définition de schéma intégrée.

Exemple :

Saisissez la commande suivante pour charger des données à partir d'un fichier CSV local nommé myfile.csv dans mydataset.mytable dans le projet par défaut. Le schéma est spécifié de manière intégrée.

bq load \
--source_format=CSV \
mydataset.mytable \
./myfile.csv \
qtr:STRING,sales:FLOAT,year:STRING

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

Pour spécifier une définition de schéma intégrée lorsque vous créez une table vide, saisissez la commande bq mk avec l'option --table ou -t. Si vous créez une table dans un projet autre que votre projet par défaut, ajoutez l'ID du projet à la commande au format suivant : project_id:dataset.table.

bq mk --table project_id:dataset.table schema

Remplacez les éléments suivants :

• project_id : ID de votre projet.
• dataset : un ensemble de données de votre projet.
• table : nom de la table que vous créez.
• schema : une définition de schéma intégrée.

Par exemple, la commande suivante crée une table vide nommée mytable dans votre projet par défaut. Le schéma est spécifié de manière intégrée.

bq mk --table mydataset.mytable qtr:STRING,sales:FLOAT,year:STRING

Pour en savoir plus sur la création d'une table vide, consultez la section Créer une table vide avec une définition de schéma.

C#

Pour spécifier le schéma d'une table lorsque vous chargez des données dans une table :

Avant d'essayer cet exemple, suivez les instructions de configuration pour C# du 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 pour C#.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

using Google.Apis.Bigquery.v2.Data;
using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryLoadTableGcsJson
{
    public void LoadTableGcsJson(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        var gcsURI = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
        var dataset = client.GetDataset(datasetId);
        var schema = new TableSchemaBuilder {
            { "name", BigQueryDbType.String },
            { "post_abbr", BigQueryDbType.String }
        }.Build();
        TableReference destinationTableRef = dataset.GetTableReference(
            tableId: "us_states");
        // Create job configuration
        var jobOptions = new CreateLoadJobOptions()
        {
            SourceFormat = FileFormat.NewlineDelimitedJson
        };
        // Create and run job
        BigQueryJob loadJob = client.CreateLoadJob(
            sourceUri: gcsURI, destination: destinationTableRef,
            schema: schema, options: jobOptions);
        loadJob = loadJob.PollUntilCompleted().ThrowOnAnyError();  // Waits for the job to complete.
        // Display the number of rows uploaded
        BigQueryTable table = client.GetTable(destinationTableRef);
        Console.WriteLine(
            $"Loaded {table.Resource.NumRows} rows to {table.FullyQualifiedId}");
    }
}

Pour spécifier un schéma lorsque vous créez une table vide :

using Google.Cloud.BigQuery.V2;

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

Pour spécifier le schéma d'une table lorsque vous chargez des données dans une table :

Avant d'essayer cet exemple, suivez les instructions de configuration pour Go du 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 pour Go.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

import (
	"context"
	"fmt"

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

// importJSONExplicitSchema demonstrates loading newline-delimited JSON data from Cloud Storage
// into a BigQuery table and providing an explicit schema for the data.
func importJSONExplicitSchema(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()

	gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states.json")
	gcsRef.SourceFormat = bigquery.JSON
	gcsRef.Schema = bigquery.Schema{
		{Name: "name", Type: bigquery.StringFieldType},
		{Name: "post_abbr", Type: bigquery.StringFieldType},
	}
	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(gcsRef)
	loader.WriteDisposition = bigquery.WriteEmpty

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

	if status.Err() != nil {
		return fmt.Errorf("job completed with error: %v", status.Err())
	}
	return nil
}

Pour spécifier un schéma lorsque vous créez une table vide :

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

Pour spécifier le schéma d'une table lorsque vous chargez des données dans une table :

Avant d'essayer cet exemple, suivez les instructions de configuration pour Java du 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 pour Java.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

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.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;

// Sample to load JSON data from Cloud Storage into a new BigQuery table
public class LoadJsonFromGCS {

  public static void runLoadJsonFromGCS() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    loadJsonFromGCS(datasetName, tableName, sourceUri, schema);
  }

  public static void loadJsonFromGCS(
      String datasetName, String tableName, String sourceUri, 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);
      LoadJobConfiguration loadConfig =
          LoadJobConfiguration.newBuilder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.json())
              .setSchema(schema)
              .build();

      // Load data from a GCS JSON file into the table
      Job job = bigquery.create(JobInfo.of(loadConfig));
      // Blocks until this load table job completes its execution, either failing or succeeding.
      job = job.waitFor();
      if (job.isDone()) {
        System.out.println("Json from GCS successfully loaded in a table");
      } else {
        System.out.println(
            "BigQuery was unable to load into the table due to an error:"
                + job.getStatus().getError());
      }
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Column not added during load append \n" + e.toString());
    }
  }
}

Pour spécifier un schéma lorsque vous créez une table vide :

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());
    }
  }
}

Python

Pour spécifier le schéma d'une table lorsque vous chargez des données dans une table, configurez la propriété LoadJobConfig.schema.

Avant d'essayer cet exemple, suivez les instructions de configuration pour Python du 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 pour Python.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

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"

job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("name", "STRING"),
        bigquery.SchemaField("post_abbr", "STRING"),
    ],
    source_format=bigquery.SourceFormat.NEWLINE_DELIMITED_JSON,
)
uri = "gs://cloud-samples-data/bigquery/us-states/us-states.json"

load_job = client.load_table_from_uri(
    uri,
    table_id,
    location="US",  # Must match the destination dataset location.
    job_config=job_config,
)  # Make an API request.

load_job.result()  # Waits for the job to complete.

destination_table = client.get_table(table_id)
print("Loaded {} rows.".format(destination_table.num_rows))

Pour spécifier un schéma lorsque vous créez une table vide, configurez la propriété Table.schema.

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

Spécifier un fichier de schéma JSON

Si vous le souhaitez, vous pouvez spécifier le schéma à l'aide d'un fichier de schéma JSON au lieu d'utiliser une définition de schéma intégrée. Un fichier de schéma JSON consiste en un tableau JSON contenant les éléments suivants :

• Nom de la colonne
• Type de données de la colonne
• Facultatif : Mode de la colonne (si aucune valeur n'est spécifiée, le mode par défaut est NULLABLE)
• Facultatif : Champs de la colonne s'il s'agit d'un type STRUCT
• Facultatif : Description de la colonne
• Facultatif : Tags avec stratégie de la colonne, utilisés pour le contrôle des accès au niveau des champs
• Facultatif : Longueur maximale des valeurs de la colonne pour les types STRING ou BYTES
• Facultatif : Précision de la colonne pour les types NUMERIC ou BIGNUMERIC
• Facultatif : Échelle de la colonne pour les types NUMERIC ou BIGNUMERIC
• Facultatif : Classement de la colonne pour les types STRING
• Facultatif : Valeur par défaut de la colonne
• Facultatif : Mode d'arrondi de la colonne, si elle est de type NUMERIC ou BIGNUMERIC

Remarque : Vous pouvez également spécifier le tableau JSON que vous créez dans votre fichier de schéma à l'aide de l'option de la console Google Cloud Modifier sous forme de texte. Cette option peut également servir de point de départ pour configurer la propriété schema dans l'API.

Créer un fichier de schéma JSON

Pour créer un fichier de schéma JSON, saisissez un TableFieldSchema pour chaque colonne. Les champs name et type sont obligatoires. Tous les autres champs sont facultatifs.

[
  {
    "name": string,
    "type": string,
    "mode": string,
    "fields": [
      {
        object (TableFieldSchema)
      }
    ],
    "description": string,
    "policyTags": {
      "names": [
        string
      ]
    },
    "maxLength": string,
    "precision": string,
    "scale": string,
    "collation": string,
    "defaultValueExpression": string,
    "roundingMode": string
  },
  {
    "name": string,
    "type": string,
    ...
  }
]

Si la colonne est un type RANGE<T>, utilisez le champ rangeElementType pour décrire T, où T doit être l'une des DATE, DATETIME ou TIMESTAMP.

[
  {
    "name": "duration",
    "type": "RANGE",
    "mode": "NULLABLE",
    "rangeElementType": {
      "type": "DATE"
    }
  }
]

Le tableau JSON est indiqué par les crochets de début et de fin []. Chaque entrée de colonne doit être séparée par une virgule : },.

Pour écrire un schéma de table existant dans un fichier local, procédez comme suit :

bq

bq show \
--schema \
--format=prettyjson \
project_id:dataset.table > path_to_file

Remplacez les éléments suivants :

• project_id : ID de votre projet.
• dataset : un ensemble de données de votre projet.
• table : nom d'un schéma de table existant.
• path_to_file : emplacement du fichier local dans lequel vous souhaitez écrire le schéma de la table.

Python

Avant d'essayer cet exemple, suivez les instructions de configuration pour Python du 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 pour Python.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

Pour écrire un fichier JSON de schéma à partir d'une table à l'aide de la bibliothèque cliente Python, appelez la méthode Client.schema_to_json.

from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change the table_id variable to the full name of the
# table you want to get schema from.
table_id = "your-project.your_dataset.your_table_name"

# TODO(dev): Change schema_path variable to the path
# of your schema file.
schema_path = "path/to/schema.json"
table = client.get_table(table_id)  # Make an API request.

# Write a schema file to schema_path with the schema_to_json method.
client.schema_to_json(table.schema, schema_path)

with open(schema_path, "r", encoding="utf-8") as schema_file:
    schema_contents = schema_file.read()

# View table properties
print(f"Got table '{table.project}.{table.dataset_id}.{table.table_id}'.")
print(f"Table schema: {schema_contents}")

Vous pouvez utiliser le fichier de sortie comme point de départ pour votre propre fichier de schéma JSON. Si vous utilisez cette approche, assurez-vous que le fichier ne contient que le tableau JSON qui représente le schéma de la table.

Par exemple, le tableau JSON suivant représente un schéma de table de base. Ce schéma comporte trois colonnes : qtr (REQUIRED STRING), rep (NULLABLE STRING) et sales (NULLABLE FLOAT).

[
  {
    "name": "qtr",
    "type": "STRING",
    "mode": "REQUIRED",
    "description": "quarter"
  },
  {
    "name": "rep",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "sales representative"
  },
  {
    "name": "sales",
    "type": "FLOAT",
    "mode": "NULLABLE",
    "defaultValueExpression": "2.55"
  }
]

Utiliser un fichier de schéma JSON

Une fois le fichier de schéma JSON créé, vous pouvez le spécifier à l'aide de l'outil de ligne de commande bq. Vous ne pouvez pas utiliser de fichier de schéma avec la console Google Cloud ou l'API.

Fournissez le fichier de schéma :

• Si vous chargez des données, utilisez la commande bq load.
• Si vous créez une table vide, utilisez la commande bq mk.

Lorsque vous fournissez un fichier de schéma JSON, il doit être stocké dans un emplacement local accessible en lecture. Vous ne pouvez pas spécifier un fichier de schéma JSON stocké dans Cloud Storage ou Google Drive.

Spécifier un fichier de schéma lors du chargement de données

Pour charger des données dans une table à l'aide d'une définition de schéma JSON, procédez comme suit :

bq

bq --location=location load \
--source_format=format \
project_id:dataset.table \
path_to_data_file \
path_to_schema_file

Remplacez les éléments suivants :

• location : nom de votre emplacement. 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 définir une valeur par défaut correspondant à l'emplacement à l'aide du fichier .bigqueryrc.
• format : NEWLINE_DELIMITED_JSON ou CSV.
• project_id : ID de votre projet.
• dataset : ensemble de données contenant la table dans laquelle vous chargez des données.
• table : nom de la table dans laquelle vous chargez des données.
• path_to_data_file : emplacement du fichier de données CSV ou JSON sur votre ordinateur local ou dans Cloud Storage.
• path_to_schema_file : chemin d'accès du fichier de schéma sur votre ordinateur local.

Exemple :

Saisissez la commande suivante pour charger des données à partir d'un fichier CSV local nommé myfile.csv dans mydataset.mytable dans le projet par défaut. Le schéma est spécifié dans myschema.json dans le répertoire actuel.

bq load --source_format=CSV mydataset.mytable ./myfile.csv ./myschema.json

Python

Avant d'essayer cet exemple, suivez les instructions de configuration pour Python du 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 pour Python.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

Pour charger un schéma de table à partir d'un fichier JSON à l'aide de la bibliothèque cliente Python, appelez la méthode schema_from_json.

from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change uri variable to the path of your data file.
uri = "gs://your-bucket/path/to/your-file.csv"
# TODO(dev): Change table_id to the full name of the table you want to create.
table_id = "your-project.your_dataset.your_table"
# TODO(dev): Change schema_path variable to the path of your schema file.
schema_path = "path/to/schema.json"
# To load a schema file use the schema_from_json method.
schema = client.schema_from_json(schema_path)

job_config = bigquery.LoadJobConfig(
    # To use the schema you loaded pass it into the
    # LoadJobConfig constructor.
    schema=schema,
    skip_leading_rows=1,
)

# Pass the job_config object to the load_table_from_file,
# load_table_from_json, or load_table_from_uri method
# to use the schema on a new table.
load_job = client.load_table_from_uri(
    uri, table_id, job_config=job_config
)  # Make an API request.

load_job.result()  # Waits for the job to complete.

destination_table = client.get_table(table_id)  # Make an API request.
print(f"Loaded {destination_table.num_rows} rows to {table_id}.")

Spécifier un fichier de schéma lors de la création d'une table

Pour créer une table vide dans un ensemble de données existant à l'aide d'un fichier de schéma JSON, procédez comme suit :

bq

bq mk --table project_id:dataset.table path_to_schema_file

Remplacez les éléments suivants :

• project_id : ID de votre projet.
• dataset : un ensemble de données de votre projet.
• table : nom de la table que vous créez.
• path_to_schema_file : chemin d'accès du fichier de schéma sur votre ordinateur local.

Par exemple, la commande suivante crée une table nommée mytable dans mydataset dans votre projet par défaut. Le schéma est spécifié dans myschema.json dans le répertoire actuel :

bq mk --table mydataset.mytable ./myschema.json

Python

Avant d'essayer cet exemple, suivez les instructions de configuration pour Python du 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 pour Python.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

Pour charger un schéma de table à partir d'un fichier JSON à l'aide de la bibliothèque cliente Python, appelez la méthode schema_from_json.

from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change table_id to the full name of the table you want to create.
table_id = "your-project.your_dataset.your_table_name"
# TODO(dev): Change schema_path variable to the path of your schema file.
schema_path = "path/to/schema.json"
# To load a schema file use the schema_from_json method.
schema = client.schema_from_json(schema_path)

table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)  # API request
print(f"Created table {table_id}.")

Spécifier un schéma dans l'API

Spécifiez un schéma de table à l'aide de l'API :

• Pour spécifier un schéma lorsque vous chargez des données, appelez la méthode jobs.insert et configurez la propriété schema dans la ressource JobConfigurationLoad.

• Pour spécifier un schéma lorsque vous créez une table, appelez la méthode tables.insert et configurez la propriété schema dans la ressource Table.

La spécification d'un schéma à l'aide de l'API est similaire au processus de création d'un fichier de schéma JSON.

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

• Découvrez comment spécifier des colonnes imbriquées et répétées dans une définition de schéma.
• Consultez la section sur la détection automatique de schéma.
• Découvrez comment charger des données dans BigQuery.
• Découvrez comment créer et utiliser des tables.