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 acceptés. Lorsque vous chargez des fichiers d'exportation Avro, Parquet, ORC, Cloud Firestore ou Cloud Datastore, le schéma est automatiquement extrait des données sources auto-descriptives.

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

Spécifiez le schéma manuellement :
- à l'aide de la console GCP ;
- à l'aide de l'UI Web classique de BigQuery ;
- de façon intégrée à l'aide de l'interface de ligne de commande.
Créez un fichier de schéma au format JSON.
Appelez la méthode jobs.insert et configurez la propriété configuration.load.schema.
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 éventuellement fournir la description et le mode d'une colonne.

Noms de colonne

Un nom de colonne ne doit contenir que des lettres (a-z, AZ), des chiffres (0-9) et des traits de soulignement (_), et il doit commencer par une lettre ou un trait de soulignement. Sa longueur maximale est de 128 caractères. Un nom de colonne ne peut utiliser aucun des préfixes suivants :

_TABLE_
_FILE_
_PARTITION

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.

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.

Types de données du langage SQL standard

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

Type de données	Description
Entier	Valeurs numériques sans composants fractionnaires
Virgule flottante	Valeurs numériques approximatives avec composants fractionnaires
Numérique	Valeurs numériques exactes avec composants fractionnaires
Booléen	TRUE ou FALSE (non sensible à la casse)
Chaîne	Données de type caractères de longueur variable (Unicode)
Octets	Données binaires de longueur variable
Date	Date de calendrier logique
Date/Heure	Année, mois, jour, heure, minute, seconde et milliseconde
Heure	Heure indépendante d'une date précise
Horodatage	Point absolu dans le temps avec spécification de la microseconde
Type "Struct (Record)"	Conteneur de champs triés ayant chacun un type (obligatoire) et un nom de champ (facultatif)
Géographie	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)

Pour plus d'informations sur les types de données en langage SQL standard, consultez la page Types de données du langage SQL standard.

Vous pouvez également spécifier un type de tableau lorsque vous interrogez des données. Pour plus d'informations, 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 plus d'informations sur les modes, consultez la section schema.fields.mode dans la documentation de référence sur l'API.

Spécifier les schémas manuellement

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

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

Interface utilisateur classique

Dans l'interface utilisateur Web classique de BigQuery, vous pouvez spécifier un schéma à l'aide de l'option Add Field (Ajouter un champ) ou de l'option Edit as Text (Modifier sous forme de texte).

Accédez à l'UI Web de BigQuery.

Accéder à l'UI Web de BigQuery
Dans le volet de navigation, cliquez sur la flèche vers le bas située à côté du nom de l'ensemble de données, puis sur Créer une table. Le processus de chargement des données est identique à celui permettant de créer une table vide.
Sur la page Créer une table :
- Dans la section Données sources, cliquez sur Créer à partir de la source si vous chargez des données ou cliquez sur Créer une table vide.
- Dans la section Table de destination, choisissez votre ensemble de données et saisissez le nom de la table dans le champ Nom de la table de destination.
- Dans la section Schema (Schéma), choisissez l'une des options suivantes pour spécifier un schéma manuellement :
  - Option 1 : Utilisez Add Field (Ajouter un champ) et définissez les valeurs Name (Nom), Type et Mode de chaque champ. Dans l'interface utilisateur Web de BigQuery, vous ne pouvez pas ajouter de description de champ lorsque vous utilisez l'option Add Field (Ajouter un champ). Toutefois, vous pouvez ajouter des descriptions de champ manuellement dans l'interface utilisateur une fois vos données chargées.
  - 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 la création d'un fichier de schéma JSON.
- Cliquez sur Créer une table.

Ligne de commande

Fournissez manuellement le schéma intégré au format [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE] à l'aide des commandes suivantes :

Commande load si vous chargez des données
Commande mk si vous créez une table vide

Lorsque vous spécifiez le schéma sur la ligne de commande, vous ne pouvez pas inclure le type RECORD (STRUCT) ni la description et le mode de colonne. Tous les modes par défaut sont NULLABLE. Pour inclure des descriptions, des modes et des types RECORD, 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'indicateur --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 en utilisant le format suivant : [PROJECT_ID]:[DATASET].[TABLE_NAME].

Définissez l'indicateur --location sur la valeur correspondant à votre emplacement.

bq --location=[LOCATION] load --source_format=[FORMAT] [PROJECT_ID]:[DATASET].[TABLE_NAME] [PATH_TO_SOURCE] [SCHEMA]

Où :

[LOCATION] est le nom de votre zone. L'indicateur --location est facultatif. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, définissez la valeur de l'indicateur sur asia-northeast1. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc.
[FORMAT] est NEWLINE_DELIMITED_JSON ou CSV.
[PROJECT_ID] est l'ID de votre projet.
[DATASET] est l'ensemble de données contenant la table dans laquelle vous chargez les données.
[TABLE] est le nom de la table dans laquelle vous chargez des données.
[PATH_TO_SOURCE] est l'emplacement du fichier de données CSV ou JSON sur votre ordinateur local ou dans Cloud Storage.
[SCHEMA] est la définition de schéma intégrée.

Exemples :

Entrez 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é manuellement de façon intégrée. mydataset est créé dans la zone multirégionale US.

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

Entrez 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. mydataset est créé dans la région asia-northeast1. Le schéma est spécifié manuellement de façon intégrée :

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

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

Pour spécifier une définition de schéma intégrée lorsque vous créez une table vide, entrez la commande mk avec l'indicateur --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]

Où :

[PROJECT_ID] est l'ID de votre projet.
[DATASET] correspond à un ensemble de données dans votre projet.
[TABLE] correspond au nom de la table que vous créez.
[SCHEMA] correspond à 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é manuellement de façon intégrée.

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

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

C#

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

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

Afficher sur GitHub


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.PollUntilCompleted();  // 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 :

Afficher sur GitHub


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 dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery Go.

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

Afficher sur GitHub

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states.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())
}

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

Afficher sur GitHub

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
sampleSchema := bigquery.Schema{
	{Name: "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
}

Python

Avant de tester cet exemple, suivez la procédure de configuration de Python dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur l'API BigQuery Python.

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

Afficher sur GitHub

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

dataset_ref = client.dataset(dataset_id)
job_config = bigquery.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING')
]
job_config.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,
    dataset_ref.table('us_states'),
    location='US',  # Location must match that of the destination dataset.
    job_config=job_config)  # API request
print('Starting job {}'.format(load_job.job_id))

load_job.result()  # Waits for table load to complete.
print('Job finished.')

destination_table = client.get_table(dataset_ref.table('us_states'))
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.

Afficher sur GitHub

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

schema = [
    bigquery.SchemaField('full_name', 'STRING', mode='REQUIRED'),
    bigquery.SchemaField('age', 'INTEGER', mode='REQUIRED'),
]
table_ref = dataset_ref.table('my_table')
table = bigquery.Table(table_ref, schema=schema)
table = client.create_table(table)  # API request

assert table.table_id == 'my_table'

Spécifier un fichier de schéma JSON

Si vous préférez ne pas spécifier le schéma manuellement, vous pouvez créer un fichier de schéma JSON à utiliser avec la CLI. Un fichier de schéma JSON consiste en un tableau JSON contenant les éléments suivants :

(Facultatif) Description de la colonne
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)

Créer un fichier de schéma JSON

Pour créer un fichier de schéma JSON, entrez les informations suivantes à l'aide d'un éditeur de texte approprié :

[
 {
   "description": "[DESCRIPTION]",
   "name": "[NAME]",
   "type": "[TYPE]",
   "mode": "[MODE]"
 },
 {
   "description": "[DESCRIPTION]",
   "name": "[NAME]",
   "type": "[TYPE]",
   "mode": "[MODE]"
 }
]

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 "},".

Vous pouvez écrire un schéma de table existant dans un fichier local en entrant la commande suivante :

bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE] > [PATH_TO_FILE]

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 3 colonnes : qtr (REQUIRED STRING), rep (NULLABLE STRING) et sales (NULLABLE FLOAT).

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

Utiliser un fichier de schéma JSON

Une fois le fichier de schéma JSON créé, vous pouvez le spécifier sur la ligne de commande. Vous ne pouvez pas utiliser un fichier de schéma avec la console GCP, l'interface utilisateur Web classique de BigQuery ou l'API.

Fournissez le fichier de schéma manuellement à l'aide des éléments suivants :

Commande load si vous chargez des données
Commande mk si vous créez une table vide

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

La commande suivante charge des données dans une table à l'aide de la définition de schéma du fichier JSON :

bq --location=[LOCATION] load --source_format=[FORMAT] [PROJECT_ID]:[DATASET].[TABLE] [PATH_TO_DATA_FILE] [PATH_TO_SCHEMA_FILE]

Où :

[LOCATION] est le nom de votre zone. L'indicateur --location est facultatif. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, définissez la valeur de l'indicateur sur asia-northeast1. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc.
[FORMAT] est NEWLINE_DELIMITED_JSON ou CSV.
[PROJECT_ID] est l'ID de votre projet.
[DATASET] est l'ensemble de données contenant la table dans laquelle vous chargez les données.
[TABLE] est le nom de la table dans laquelle vous chargez des données.
[PATH_TO_DATA_FILE] est l'emplacement du fichier de données CSV ou JSON sur votre ordinateur local ou dans Google Cloud Storage.
[SCHEMA_FILE] est le chemin d'accès du fichier de schéma sur votre ordinateur local.

Exemples :

Entrez 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. mydataset est créé dans la zone multirégionale US. Le schéma est spécifié dans myschema.json.

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

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

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

La commande suivante crée une table vide dans un jeu de données existant en utilisant la définition de schéma d'un fichier JSON :

bq mk --table [PROJECT_ID]:[DATASET].[TABLE] [PATH_TO_SCHEMA_FILE]

Où :

[PROJECT_ID] est l'ID de votre projet.
[DATASET] correspond à un ensemble de données dans votre projet.
[TABLE] correspond au nom de la table que vous créez.
[PATH_TO_SCHEMA_FILE] correspond au 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 :

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