Modifier des schémas de table

Ce document décrit comment modifier les définitions de schéma pour les tables BigQuery existantes. De façon native, BigQuery permet d'effectuer les modifications de schéma suivantes :

  • Ajouter des colonnes à une définition de schéma
  • Assouplir le mode d'une colonne de REQUIRED à NULLABLE

Il est possible de créer une table sans définir de schéma initial et d'ajouter ultérieurement une définition de schéma à la table.

Les autres modifications de schéma ne peuvent pas être gérées par BigQuery et nécessitent toutes des solutions de contournement manuelles. Il s'agit, entre autres, des modifications suivantes :

  • Renommer une colonne
  • Modifier le type de données d'une colonne
  • Modifier le mode d'une colonne (hors assouplissement de REQUIRED à NULLABLE
  • Supprimer une colonne

Pour en savoir plus sur les modifications de schéma non gérées par BigQuery nécessitant des solutions de contournement, consultez l'article Modifier manuellement des schémas de table.

Ajouter des colonnes à la définition de schéma d'une table

Vous pouvez ajouter des colonnes à la définition de schéma d'une table existante :

  • manuellement, en créant une colonne vide ;
  • lorsque vous utilisez une tâche de chargement ou de requête pour écraser une table ;
  • lorsque vous ajoutez des données à une table à l'aide d'une tâche de chargement ou de requête.

Toute colonne que vous ajoutez doit respecter les règles de BigQuery pour les noms de colonnes. Pour en savoir plus sur la création de composants de schéma, consultez l'article Spécifier un schéma.

Ajouter manuellement une colonne vide

Pour ajouter une colonne vide à une table existante, vous pouvez :

  • utiliser Cloud Console ou l'UI Web classique de BigQuery ;
  • utiliser la commande bq update de l'outil de ligne de commande ;
  • appeler la méthode API tables.patch ;
  • utiliser les bibliothèques clientes.

Si vous ajoutez des colonnes à un schéma de table existant, celles-ci doivent être définies comme NULLABLE ou REPEATED. Vous ne pouvez pas ajouter une colonne REQUIRED à un schéma de table existant. Si vous tentez d'ajouter une colonne REQUIRED à un schéma de table dans l'interface de ligne de commande ou l'API, l'erreur suivante est renvoyée : BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Cannot add required columns to an existing schema. Vous ne pouvez ajouter des colonnes définies comme REQUIRED que lorsque vous créez une table lors du chargement des données ou lorsque vous créez une table vide avec une définition de schéma.

Après avoir ajouté une nouvelle colonne à la définition de schéma d'une table, vous pouvez charger des données dans la nouvelle colonne en utilisant les éléments suivants :

Pour ajouter des colonnes vides à la définition de schéma d'une table, procédez comme suit :

Console

  1. Dans le volet Ressources, sélectionnez la table.

  2. Sous l'éditeur de requête, faites défiler la page jusqu'en bas de la section Schema (Schéma), puis cliquez sur Edit schema (Modifier le schéma).

Modifier le schéma de la table

  1. Faites défiler la page jusqu'en bas du panneau, puis cliquez sur Add field (Ajouter un champ).

    • Saisissez le nom de la colonne dans le champ Name (Nom).
    • Choisissez le type de données dans le champ Type.
    • Dans le champ Mode, choisissez NULLABLE ou REPEATED.
  2. Lorsque vous avez terminé d'ajouter des colonnes, cliquez sur Save (Enregistrer).

Interface utilisateur classique

  1. Dans le volet de navigation, sélectionnez la table.

  2. Sur la page Détails de la table, cliquez sur Ajouter des champs.

  3. Dans la section New Fields (Nouveaux champs) :

    • Saisissez le nom de la colonne dans le champ Name (Nom).
    • Choisissez le type de données dans le champ Type.
    • Dans le champ Mode, choisissez NULLABLE ou REPEATED.

      Mettre à jour le schéma de la table

  4. Lorsque vous avez terminé d'ajouter des colonnes, cliquez sur Add to Table (Ajouter à la table).

CLI

Exécutez la commande bq update et fournissez un fichier de schéma JSON. Si la table que vous mettez à jour se trouve dans un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

bq update project_id:dataset.table schema

Où :

  • project_id est l'ID du projet.
  • dataset est le nom de l'ensemble de données contenant la table que vous mettez à jour.
  • table est le nom de la table que vous mettez à jour.
  • schema est le chemin d'accès du fichier de schéma JSON sur votre ordinateur local.

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 par défaut sont définis sur NULLABLE.

Si vous essayez d'ajouter des colonnes à l'aide d'une définition de schéma intégrée, vous devez fournir l'intégralité de la définition de schéma, y compris les nouvelles colonnes. Étant donné que vous ne pouvez pas indiquer de mode de colonne à l'aide d'une définition de schéma intégrée, le processus de mise à jour tente de faire passer les colonnes existantes du mode REQUIRED au mode NULLABLE. L'erreur suivante est alors renvoyée : BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Field field has changed mode from REPEATED to NULLABLE.

Pour ajouter des colonnes à une table existante à l'aide de la CLI, il est préférable de fournir un fichier de schéma JSON.

Pour ajouter des colonnes vides au schéma d'une table à l'aide d'un fichier de schéma JSON :

  1. Tout d'abord, exécutez la commande bq show avec l'option --schema et écrivez le schéma de table existant dans un fichier. Si la table que vous mettez à jour se trouve dans un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

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

    Où :

    • project_id est l'ID du projet.
    • dataset est le nom de l'ensemble de données contenant la table que vous mettez à jour.
    • table est le nom de la table que vous mettez à jour.
    • schema_file est le fichier de définition de schéma écrit sur votre ordinateur local.

    Par exemple, pour écrire la définition de schéma de mydataset.mytable dans un fichier, entrez la commande suivante. mydataset.mytable se trouve dans votre projet par défaut.

       bq show \
       --schema \
       --format=prettyjson \
       mydataset.mytable > /tmp/myschema.json
    
  2. Ouvrez le fichier de schéma dans un éditeur de texte. Le fichier doit se présenter comme suit :

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    
  3. Ajoutez les colonnes à la fin de la définition de schéma. Si vous tentez d'ajouter des colonnes ailleurs dans le tableau, l'erreur suivante est renvoyée : BigQuery error in update operation: Precondition Failed.

    Avec un fichier JSON, vous pouvez spécifier des descriptions, des modes NULLABLE ou REPEATED, et des types RECORD pour les nouvelles colonnes. Par exemple, en utilisant la définition de schéma de l'étape précédente, votre nouveau tableau JSON devrait se présenter comme suit. Dans cet exemple, une colonne NULLABLE nommée column4 est ajoutée. Cette colonne column4 comprend une description.

      [
        {
          "mode": "REQUIRED",
          "name": "column1",
          "type": "STRING"
        },
        {
          "mode": "REQUIRED",
          "name": "column2",
          "type": "FLOAT"
        },
        {
          "mode": "REPEATED",
          "name": "column3",
          "type": "STRING"
        },
        {
          "description": "my new column",
          "mode": "NULLABLE",
          "name": "column4",
          "type": "STRING"
        }
      ]
      

    Pour en savoir plus sur l'utilisation des fichiers de schéma JSON, consultez l'article Spécifier un fichier de schéma JSON.

  4. Après avoir mis à jour votre fichier de schéma, exécutez la commande suivante pour mettre à jour le schéma de la table. Si la table que vous mettez à jour se trouve dans un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

    bq update project_id:dataset.table schema
    

    Où :

    • project_id est l'ID du projet.
    • dataset est le nom de l'ensemble de données contenant la table que vous mettez à jour.
    • table est le nom de la table que vous mettez à jour.
    • schema est le chemin d'accès du fichier de schéma JSON sur votre ordinateur local.

    Par exemple, saisissez la commande suivante pour mettre à jour la définition de schéma de mydataset.mytable dans votre projet par défaut. /tmp/myschema.jsonest le chemin d'accès du fichier de schéma sur votre ordinateur local.

    bq update mydataset.mytable /tmp/myschema.json
    

API

Appelez la méthode tables.patch, puis utilisez la propriété schema pour ajouter des colonnes vides à votre définition de schéma. Étant donné que la méthode tables.update remplace l'intégralité de la ressource de table, la méthode tables.patch est préférable.

Go

import (
	"context"
	"fmt"

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

// updateTableAddColumn demonstrates modifying the schema of a table to append an additional column.
func updateTableAddColumn(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)
	}

	tableRef := client.Dataset(datasetID).Table(tableID)
	meta, err := tableRef.Metadata(ctx)
	if err != nil {
		return err
	}
	newSchema := append(meta.Schema,
		&bigquery.FieldSchema{Name: "phone", Type: bigquery.StringFieldType},
	)
	update := bigquery.TableMetadataToUpdate{
		Schema: newSchema,
	}
	if _, err := tableRef.Update(ctx, update, meta.ETag); err != nil {
		return err
	}
	return nil
}

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 sur l'utilisation des bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur 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 addEmptyColumn() {
  // Adds an empty column to the schema.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';
  const column = {name: 'size', type: 'STRING'};

  // Retrieve current table metadata
  const table = bigquery.dataset(datasetId).table(tableId);
  const [metadata] = await table.getMetadata();

  // Update table schema
  const schema = metadata.schema;
  const new_schema = schema;
  new_schema.fields.push(column);
  metadata.schema = new_schema;

  const [result] = await table.setMetadata(metadata);
  console.log(result.schema.fields);
}

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

Ajoutez un nouvel objet SchemaField à une copie de la propriété Table.schema, puis remplacez la valeur de la propriété Table.schema par le schéma mis à jour.
from google.cloud import bigquery

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

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

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

original_schema = table.schema
new_schema = original_schema[:]  # Creates a copy of the schema.
new_schema.append(bigquery.SchemaField("phone", "STRING"))

table.schema = new_schema
table = client.update_table(table, ["schema"])  # Make an API request.

if len(table.schema) == len(original_schema) + 1 == len(new_schema):
    print("A new column has been added.")
else:
    print("The column has not been added.")

Ajouter une colonne imbriquée à un type RECORD

En plus d'ajouter des colonnes au schéma d'une table, vous pouvez également ajouter des colonnes imbriquées à un type RECORD. Le processus d'ajout des colonnes imbriquées est très similaire à celui des colonnes classiques.

Console

L'ajout d'un champ imbriqué à une colonne RECORD existante n'est actuellement pas possible dans Cloud Console.

UI classique

L'ajout d'un champ imbriqué à une colonne RECORD existante n'est actuellement pas possible dans l'interface utilisateur Web de BigQuery.

CLI

Exécutez la commande bq updateet fournissez un fichier de schéma JSON qui ajoute le champ imbriqué à la définition de schéma de la colonne RECORD existante. Si la table que vous mettez à jour se trouve dans un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

bq update project_id:dataset.table schema

Où :

  • project_id est l'ID du projet.
  • dataset est le nom de l'ensemble de données contenant la table que vous mettez à jour.
  • table est le nom de la table que vous mettez à jour.
  • schema est le chemin d'accès du fichier de schéma JSON sur votre ordinateur local.

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 par défaut sont définis sur NULLABLE. Par conséquent, si vous ajoutez une nouvelle colonne imbriquée à un type RECORD, vous devez fournir un fichier de schéma JSON.

Pour ajouter une colonne imbriquée à un type RECORD en utilisant un fichier de schéma JSON, procédez comme suit :

  1. Tout d'abord, exécutez la commande bq show avec l'option --schema et écrivez le schéma de table existant dans un fichier. Si la table que vous mettez à jour se trouve dans un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.table.

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

    Où :

    • project_id est l'ID du projet.
    • dataset est le nom de l'ensemble de données contenant la table que vous mettez à jour.
    • table est le nom de la table que vous mettez à jour.
    • schema_file est le fichier de définition de schéma écrit sur votre ordinateur local.

    Par exemple, pour écrire la définition de schéma de mydataset.mytable dans un fichier, entrez la commande suivante. mydataset.mytable se trouve dans votre projet par défaut.

    bq show \
    --schema \
    --format=prettyjson \
    mydataset.mytable > /tmp/myschema.json
    
  2. Ouvrez le fichier de schéma dans un éditeur de texte. Le fichier doit se présenter comme suit. Dans cet exemple, column3 correspond à une colonne imbriquée répétée. Les colonnes imbriquées sont nested1 et nested2. Le tableau fields répertorie les champs imbriqués dans column3.

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "fields": [
          {
            "mode": "NULLABLE",
            "name": "nested1",
            "type": "STRING"
          },
          {
            "mode": "NULLABLE",
            "name": "nested2",
            "type": "STRING"
          }
        ],
        "mode": "REPEATED",
        "name": "column3",
        "type": "RECORD"
      }
    ]
    
  3. Ajoutez la colonne imbriquée à la fin du tableau fields. Dans cet exemple, nested3 correspond à la nouvelle colonne imbriquée.

      [
        {
          "mode": "REQUIRED",
          "name": "column1",
          "type": "STRING"
        },
        {
          "mode": "REQUIRED",
          "name": "column2",
          "type": "FLOAT"
        },
        {
          "fields": [
            {
              "mode": "NULLABLE",
              "name": "nested1",
              "type": "STRING"
            },
            {
              "mode": "NULLABLE",
              "name": "nested2",
              "type": "STRING"
            },
            {
              "mode": "NULLABLE",
              "name": "nested3",
              "type": "STRING"
            }
          ],
          "mode": "REPEATED",
          "name": "column3",
          "type": "RECORD"
        }
      ]
      

    Pour en savoir plus sur l'utilisation des fichiers de schéma JSON, consultez l'article Spécifier un fichier de schéma JSON.

  4. Après avoir mis à jour votre fichier de schéma, exécutez la commande suivante pour mettre à jour le schéma de la table. Si la table que vous mettez à jour se trouve dans un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

    bq update project_id:dataset.table schema
    

    Où :

    • project_id est l'ID du projet.
    • dataset est le nom de l'ensemble de données contenant la table que vous mettez à jour.
    • table est le nom de la table que vous mettez à jour.
    • schema est le chemin d'accès du fichier de schéma JSON sur votre ordinateur local.

    Par exemple, saisissez la commande suivante pour mettre à jour la définition de schéma de mydataset.mytable dans votre projet par défaut. /tmp/myschema.jsonest le chemin d'accès du fichier de schéma sur votre ordinateur local.

    bq update mydataset.mytable /tmp/myschema.json
    

API

Appelez la méthode tables.patch, puis utilisez la propriété schema pour ajouter des colonnes imbriquées à votre définition de schéma. Étant donné que la méthode tables.update remplace l'intégralité de la ressource de table, la méthode tables.patch est préférable.

Ajouter une colonne lorsque vous écrasez ou ajoutez des données

Vous pouvez ajouter des colonnes à une table existante lorsque vous y chargez des données et choisissez d'écraser cette table. Le schéma des données chargées est alors utilisé pour écraser le schéma de la table existante. Pour en savoir plus sur le remplacement d'une table à l'aide d'une tâche de chargement, consultez les sections suivantes :

Vous pouvez également ajouter des colonnes à une table existante lorsque vous y ajoutez des données à l'aide d'une tâche de chargement ou de requête.

Ajouter une colonne dans une tâche de chargement ajoutant des données

Pour ajouter des colonnes à une table lorsque vous ajoutez des données à cette dernière via une tâche de chargement, vous pouvez :

  • utiliser la commande bq load de l'outil de ligne de commande ;
  • appeler la méthode jobs.insert de l'API et configurer une tâche load ;
  • utiliser les bibliothèques clientes.

L'ajout d'une colonne à une table existante lors d'une opération d'ajout n'est actuellement pas possible dans Cloud Console ou l'interface utilisateur Web classique de BigQuery.

Lorsque vous ajoutez des colonnes à l'aide d'une opération d'ajout dans une tâche de chargement, le schéma mis à jour peut être :

  • détecté automatiquement, pour les fichiers CSV et JSON ;
  • spécifié dans un fichier de schéma JSON, pour les fichiers CSV et JSON ;
  • récupéré à partir des données source autodescriptives pour les fichiers d'exportation Avro, ORC, Parquet et Cloud Datastore.

Si vous spécifiez le schéma dans un fichier JSON, les nouvelles colonnes doivent y être définies. Si les nouvelles définitions de colonnes ne sont pas indiquées, l'erreur suivante est renvoyée lorsque vous tentez d'ajouter des données : Error while reading data, error message: parsing error in row starting at position int: No such field: field..

Lorsque vous ajoutez des colonnes lors d'une opération d'ajout, les valeurs des nouvelles colonnes sont définies sur NULL pour les lignes existantes.

Pour ajouter une colonne lorsque vous ajoutez des données à une table lors d'une tâche de chargement, procédez comme suit.

Console

Vous ne pouvez pas ajouter de colonnes à une table existante lorsque vous chargez des données en utilisant Cloud Console.

UI classique

Vous ne pouvez pas ajouter de colonnes à une table existante lorsque vous chargez des données en utilisant l'interface utilisateur Web de BigQuery.

CLI

Exécutez la commande bq load pour charger vos données et spécifiez l'option --noreplace pour indiquer que vous ajoutez les données à une table existante.

Si les données que vous ajoutez sont au format JSON délimité par un retour à la ligne ou CSV, spécifiez l'option --autodetect pour utiliser la détection automatique de schéma ou fournissez le schéma dans un fichier de schéma JSON. Les colonnes ajoutées peuvent être obtenues automatiquement à partir des fichiers d'exportation Avro ou Cloud Datastore.

Définissez l'option --schema_update_option sur ALLOW_FIELD_ADDITION pour indiquer que les données que vous ajoutez contiennent de nouvelles colonnes.

Si la table que vous ajoutez se trouve dans un ensemble de données d'un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

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

Saisissez la commande load comme suit :

bq --location=location load \
--noreplace \
--autodetect \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=format \
project_id:dataset.table \
path_to_source \
schema

Où :

  • location est le 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 est NEWLINE_DELIMITED_JSON, CSV, AVRO, PARQUET, ORC ou DATASTORE_BACKUP.
  • project_id est l'ID du projet.
  • dataset est le nom de l'ensemble de données contenant la table.
  • table est le nom de la table que vous ajoutez.
  • path_to_source correspond à un URI Cloud Storage complet, à une liste d'URI séparés par une virgule ou au chemin d'accès d'un fichier de données se trouvant sur votre ordinateur local.
  • schema correspond au chemin d'accès vers un fichier de schéma JSON local. Un fichier de schéma n'est requis que pour les fichiers CSV et JSON lorsque l'option --autodetect n'est pas spécifiée. Les schémas Avro et Cloud Datastore sont obtenus à partir des données sources.

Exemples :

Saisissez la commande suivante pour ajouter un fichier de données Avro local, /tmp/mydata.avro, à mydataset.mytable à l'aide d'une tâche de chargement. Comme les schémas peuvent être automatiquement obtenus à partir des données Avro, l'option --autodetect n'est pas nécessaire. mydataset se trouve dans votre projet par défaut.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=AVRO \
mydataset.mytable \
/tmp/mydata.avro

Saisissez la commande suivante pour ajouter un fichier de données JSON délimité par des retours à la ligne dans Cloud Storage à mydataset.mytable à l'aide d'une tâche de chargement. L'option --autodetect permet de détecter les nouvelles colonnes. mydataset se trouve dans votre projet par défaut.

bq load \
--noreplace \
--autodetect \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json

Saisissez la commande suivante pour ajouter un fichier de données JSON délimité par des retours à la ligne dans Cloud Storage à mydataset.mytable à l'aide d'une tâche de chargement. Le schéma contenant les nouvelles colonnes est spécifié dans un fichier de schéma JSON local, /tmp/myschema.json. mydataset se trouve dans myotherproject, et non dans votre projet par défaut.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=NEWLINE_DELIMITED_JSON \
myotherproject:mydataset.mytable \
gs://mybucket/mydata.json \
/tmp/myschema.json

API

Appelez la méthode jobs.insert. Configurez une tâche load et définissez les propriétés suivantes :

  • Référencez vos données dans Cloud Storage à l'aide de la propriété sourceUris.
  • Spécifiez le format de données en définissant la propriété sourceFormat.
  • Spécifiez le schéma dans la propriété schema.
  • Spécifiez l'option de mise à jour de schéma à l'aide de la propriété schemaUpdateOptions.
  • Définissez la disposition d'écriture de la table de destination sur WRITE_APPEND à l'aide de la propriété writeDisposition.

Go

import (
	"context"
	"fmt"
	"os"

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

// createTableAndWidenLoad demonstrates augmenting a table's schema to add a new column via a load job.
func createTableAndWidenLoad(projectID, datasetID, tableID, filename 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)
	}

	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType},
	}
	meta := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, meta); err != nil {
		return err
	}
	// Now, import data from a local file, but specify field additions are allowed.
	// Because the data has a second column (age), the schema is amended as part of
	// the load.
	f, err := os.Open(filename)
	if err != nil {
		return err
	}
	source := bigquery.NewReaderSource(f)
	source.AutoDetect = true   // Allow BigQuery to determine schema.
	source.SkipLeadingRows = 1 // CSV has a single header line.

	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(source)
	loader.SchemaUpdateOptions = []string{"ALLOW_FIELD_ADDITION"}
	job, err := loader.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
	}
	return nil
}

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 sur l'utilisation des bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur l'API BigQuery Node.js.

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

// Instantiate client
const bigquery = new BigQuery();

async function addColumnLoadAppend() {
  // Adds a new column to a BigQuery table while appending rows via a load job.

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

  // In this example, the existing table contains only the 'Name', 'Age',
  // & 'Weight' columns. 'REQUIRED' fields cannot  be added to an existing
  // schema, so the additional column must be 'NULLABLE'.
  const schema = 'Name:STRING, Age:INTEGER, Weight:FLOAT, IsMagic:BOOLEAN';

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

  // Set load job options
  const options = {
    schema: schema,
    schemaUpdateOptions: ['ALLOW_FIELD_ADDITION'],
    writeDisposition: 'WRITE_APPEND',
    destinationTable: destinationTableRef,
  };

  // Load data from a local file into the table
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .load(fileName, options);

  console.log(`Job ${job.id} completed.`);
  console.log(`New Schema:`);
  console.log(job.configuration.load.schema.fields);

  // Check the job's status for errors
  const errors = job.status.errors;
  if (errors && errors.length > 0) {
    throw errors;
  }
}

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

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')
# filepath = 'path/to/your_file.csv'

# Retrieves the destination table and checks the length of the schema
table_id = "my_table"
table_ref = dataset_ref.table(table_id)
table = client.get_table(table_ref)
print("Table {} contains {} columns.".format(table_id, len(table.schema)))

# Configures the load job to append the data to the destination table,
# allowing field addition
job_config = bigquery.LoadJobConfig()
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION
]
# In this example, the existing table contains only the 'full_name' column.
# 'REQUIRED' fields cannot be added to an existing schema, so the
# additional column must be 'NULLABLE'.
job_config.schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="NULLABLE"),
]
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1

with open(filepath, "rb") as source_file:
    job = client.load_table_from_file(
        source_file,
        table_ref,
        location="US",  # Must match the destination dataset location.
        job_config=job_config,
    )  # API request

job.result()  # Waits for table load to complete.
print(
    "Loaded {} rows into {}:{}.".format(
        job.output_rows, dataset_id, table_ref.table_id
    )
)

# Checks the updated length of the schema
table = client.get_table(table)
print("Table {} now contains {} columns.".format(table_id, len(table.schema)))

Ajouter une colonne dans une tâche de requête ajoutant des résultats de requête

Pour ajouter des colonnes à une table lorsque vous ajoutez à cette dernière des résultats de requête, vous pouvez :

  • utiliser la commande bq query de l'outil de ligne de commande ;
  • appeler la méthode jobs.insert de l'API et configurer une tâche query ;
  • utiliser les bibliothèques clientes.

L'ajout d'une colonne lors d'une opération d'ajout n'est actuellement pas possible dans Cloud Console ou l'interface utilisateur Web classique de BigQuery.

Lorsque vous ajoutez des colonnes à l'aide d'une opération d'ajout dans une tâche de requête, le schéma des résultats de requête est utilisé pour mettre à jour le schéma de la table de destination. Notez que vous ne pouvez pas interroger une table dans un emplacement spécifique et écrire les résultats dans une table dont l'emplacement est différent.

Pour ajouter une colonne lorsque vous ajoutez des données à une table lors d'une tâche de requête, procédez comme suit.

Console

Vous ne pouvez pas ajouter de colonnes à une table existante lorsque vous ajoutez des résultats de requête en utilisant Cloud Console.

UI classique

Vous ne pouvez pas ajouter de colonnes à une table existante lorsque vous ajoutez des résultats de requête en utilisant l'interface utilisateur Web classique de BigQuery.

CLI

Exécutez la commande bq query pour interroger vos données et spécifiez l'option --destination_table pour indiquer la table que vous ajoutez.

Pour spécifier que vous ajoutez des résultats de requête à une table de destination existante, spécifiez l'option --append_table.

Définissez l'option --schema_update_option sur ALLOW_FIELD_ADDITION pour indiquer que les résultats de requête que vous ajoutez contiennent de nouvelles colonnes.

Spécifiez l'option use_legacy_sql=false afin d'utiliser la syntaxe SQL standard pour la requête.

Si la table que vous ajoutez se trouve dans un ensemble de données d'un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset. Notez que la table que vous interrogez et la table de destination doivent se trouver au même emplacement.

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

bq --location=location query \
--destination_table project_id:dataset.table \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'query'

Où :

  • location est le 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. Notez que vous ne pouvez pas ajouter les résultats d'une requête à une table dont l'emplacement est différent.
  • project_id est l'ID du projet.
  • dataset est le nom de l'ensemble de données contenant la table que vous ajoutez.
  • table est le nom de la table que vous ajoutez.
  • query est une requête en syntaxe SQL standard.

Exemples :

Saisissez la commande suivante pour interroger mydataset.mytable dans votre projet par défaut et pour ajouter les résultats de la requête à mydataset.mytable2 (également dans votre projet par défaut).

bq query \
--destination_table mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

Saisissez la commande suivante pour interroger mydataset.mytable dans votre projet par défaut et pour ajouter les résultats de la requête à mydataset.mytable2 dans myotherproject.

bq query \
--destination_table myotherproject:mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

API

Appelez la méthode jobs.insert. Configurez une tâche query et définissez les propriétés suivantes :

  • Spécifiez la table de destination à l'aide de la propriété destinationTable.
  • Définissez la disposition d'écriture de la table de destination sur WRITE_APPEND à l'aide de la propriété writeDisposition.
  • Spécifiez l'option de mise à jour de schéma à l'aide de la propriété schemaUpdateOptions.
  • Spécifiez la requête SQL standard à l'aide de la propriété query.

Go

import (
	"context"
	"fmt"

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

// createTableAndWidenQuery demonstrates how the schema of a table can be modified to add columns by appending
// query results that include the new columns.
func createTableAndWidenQuery(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)
	}

	// First, we create a sample table.
	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	original := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, original); err != nil {
		return err
	}
	// Our table has two columns.  We'll introduce a new favorite_color column via
	// a subsequent query that appends to the table.
	q := client.Query("SELECT \"Timmy\" as full_name, 85 as age, \"Blue\" as favorite_color")
	q.SchemaUpdateOptions = []string{"ALLOW_FIELD_ADDITION"}
	q.QueryConfig.Dst = client.Dataset(datasetID).Table(tableID)
	q.WriteDisposition = bigquery.WriteAppend
	q.Location = "US"
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	_, err = job.Wait(ctx)
	if err != nil {
		return err
	}
	return nil
}

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 sur l'utilisation des bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur l'API BigQuery Node.js.

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

// Instantiate client
const bigquery = new BigQuery();

async function addColumnQueryAppend() {
  // Adds a new column to a BigQuery table while appending rows via a query job.

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

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

  // In this example, the existing table contains only the 'name' column.
  // 'REQUIRED' fields cannot  be added to an existing schema,
  // so the additional column must be 'NULLABLE'.
  const query = `SELECT name, year
    FROM \`bigquery-public-data.usa_names.usa_1910_2013\`
    WHERE state = 'TX'
    LIMIT 10`;

  // Set load job options
  const options = {
    query: query,
    schemaUpdateOptions: ['ALLOW_FIELD_ADDITION'],
    writeDisposition: 'WRITE_APPEND',
    destinationTable: destinationTableRef,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
  };

  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();
  console.log(`Job ${job.id} completed.`);

  // Print the results
  console.log('Rows:');
  rows.forEach(row => console.log(row));
}

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

from google.cloud import bigquery

# TODO(developer): 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"

# Retrieves the destination table and checks the length of the schema.
table = client.get_table(table_id)  # Make an API request.
print("Table {} contains {} columns".format(table_id, len(table.schema)))

# Configures the query to append the results to a destination table,
# allowing field addition.
job_config = bigquery.QueryJobConfig(destination=table_id)
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION
]
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND

# Start the query, passing in the extra configuration.
query_job = client.query(
    # In this example, the existing table contains only the 'full_name' and
    # 'age' columns, while the results of this query will contain an
    # additional 'favorite_color' column.
    'SELECT "Timmy" as full_name, 85 as age, "Blue" as favorite_color;',
    job_config=job_config,
)  # Make an API request.
query_job.result()  # Wait for the job to complete.

# Checks the updated length of the schema.
table = client.get_table(table_id)  # Make an API request.
print("Table {} now contains {} columns".format(table_id, len(table.schema)))

Assouplir le mode d'une colonne

Actuellement, la seule modification qu'il est possible d'apporter au mode d'une colonne consiste à la passer du mode REQUIRED à NULLABLE. Changer le mode d'une colonne de REQUIRED en NULLABLE revient à assouplir cette colonne. Vous pouvez assouplir le mode des colonnes REQUIRED :

  • manuellement ;
  • lorsque vous écrasez une table en utilisant une tâche de chargement ou de requête ;
  • lorsque vous ajoutez des données à une table en utilisant une tâche de requête.

Passer manuellement une colonne du mode REQUIRED au mode NULLABLE

Pour modifier manuellement le mode d'une colonne de REQUIRED à NULLABLE, vous pouvez :

  • utiliser Cloud Console ou l'UI Web classique de BigQuery ;
  • utiliser la commande bq update de l'outil de ligne de commande ;
  • appeler la méthode API tables.patch ;
  • utiliser les bibliothèques clientes.

Pour passer manuellement une colonne du mode REQUIRED au mode NULLABLE, procédez comme suit :

Console

Actuellement, il n'est pas possible d'assouplir le mode d'une colonne à l'aide de Cloud Console.

UI classique

  1. Développez l'ensemble de données et sélectionnez la table.

  2. Sur la page Table Details (Détails de la table), cliquez sur l'onglet Schema (Schéma).

  3. Cliquez sur la flèche vers le bas située à droite de la colonne souhaitée et choisissez l'une des options suivantes :

    • Make NULLABLE (Passer en mode NULLABLE) : assouplit le mode d'une colonne individuelle
    • All REQUIRED to NULLABLE (Passer toutes les colonnes de REQUIRED à NULLABLE) : fait passer toutes les colonnes de la définition de schéma du mode REQUIRED au mode NULLABLE

      Assouplir le mode d'une colonne

  4. Dans la boîte de dialogue Confirmer le changement de mode, cliquez sur OK pour définir le mode sur NULLABLE. Notez que cette modification est irréversible.

CLI

  1. Tout d'abord, exécutez la commande bq show avec l'option --schema et écrivez le schéma de table existant dans un fichier. Si la table que vous mettez à jour se trouve dans un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

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

    Où :

    • project_id est l'ID du projet.
    • dataset est le nom de l'ensemble de données contenant la table que vous mettez à jour.
    • table est le nom de la table que vous mettez à jour.
    • schema_file correspond au fichier de définition de schéma écrit sur votre ordinateur local.

    Par exemple, pour écrire la définition de schéma de mydataset.mytable dans un fichier, entrez la commande suivante. mydataset.mytable se trouve dans votre projet par défaut.

      bq show \
      --schema \
      --format=prettyjson \
      mydataset.mytable > /tmp/myschema.json
    
  2. Ouvrez le fichier de schéma dans un éditeur de texte. Le fichier doit se présenter comme suit :

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    
  3. Faites passer une colonne existante du mode REQUIRED au mode NULLABLE. Dans cet exemple, le mode de la colonne column1 est assoupli.

    [
      {
        "mode": "NULLABLE",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    

    Pour en savoir plus sur l'utilisation des fichiers de schéma JSON, consultez l'article Spécifier un fichier de schéma JSON.

  4. Après avoir mis à jour votre fichier de schéma, exécutez la commande suivante pour mettre à jour le schéma de la table. Si la table que vous mettez à jour se trouve dans un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

    bq update project_id:dataset.table schema
    

    Où :

    • project_id est l'ID du projet.
    • dataset est le nom de l'ensemble de données contenant la table que vous mettez à jour.
    • table est le nom de la table que vous mettez à jour.
    • schema est le chemin d'accès du fichier de schéma JSON sur votre ordinateur local.

    Par exemple, saisissez la commande suivante pour mettre à jour la définition de schéma de mydataset.mytable dans votre projet par défaut. /tmp/myschema.jsonest le chemin d'accès du fichier de schéma sur votre ordinateur local.

      bq update mydataset.mytable /tmp/myschema.json
    

API

Appelez la méthode tables.patch et utilisez la propriété schema pour faire passer une colonne du mode REQUIRED au mode NULLABLE dans votre définition de schéma. Étant donné que la méthode tables.update remplace l'intégralité de la ressource de table, la méthode tables.patch est préférable.

Go

import (
	"context"
	"fmt"

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

// relaxTableAPI demonstrates modifying the schema of a table to remove the requirement that columns allow
// no NULL values.
func relaxTableAPI(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)
	}

	// Setup: We first create a table with a schema that's restricts NULL values.
	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	original := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	if err := client.Dataset(datasetID).Table(tableID).Create(ctx, original); err != nil {
		return err
	}

	tableRef := client.Dataset(datasetID).Table(tableID)
	meta, err := tableRef.Metadata(ctx)
	if err != nil {
		return err
	}
	// Iterate through the schema to set all Required fields to false (nullable).
	var relaxed bigquery.Schema
	for _, v := range meta.Schema {
		v.Required = false
		relaxed = append(relaxed, v)
	}
	newMeta := bigquery.TableMetadataToUpdate{
		Schema: relaxed,
	}
	if _, err := tableRef.Update(ctx, newMeta, meta.ETag); err != nil {
		return err
	}
	return nil
}

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 sur l'utilisation des bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur 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 relaxColumn() {
  /**
   * Changes columns from required to nullable.
   * Assumes existing table with the following schema:
   * [{name: 'Name', type: 'STRING', mode: 'REQUIRED'},
   * {name: 'Age', type: 'INTEGER'},
   * {name: 'Weight', type: 'FLOAT'},
   * {name: 'IsMagic', type: 'BOOLEAN'}];
   */

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

  const newSchema = [
    {name: 'Name', type: 'STRING', mode: 'NULLABLE'},
    {name: 'Age', type: 'INTEGER'},
    {name: 'Weight', type: 'FLOAT'},
    {name: 'IsMagic', type: 'BOOLEAN'},
  ];

  // Retrieve current table metadata
  const table = bigquery.dataset(datasetId).table(tableId);
  const [metadata] = await table.getMetadata();

  // Update schema
  metadata.schema = newSchema;
  const [apiResponse] = await table.setMetadata(metadata);

  console.log(apiResponse.schema.fields);
}

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

Écrasez la propriété Table.schema avec une liste d'objets SchemaField dont la propriété mode est définie sur 'NULLABLE'.

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

original_schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]
table_ref = client.dataset(dataset_id).table(table_id)
table = bigquery.Table(table_ref, schema=original_schema)
table = client.create_table(table)
assert all(field.mode == "REQUIRED" for field in table.schema)

# SchemaField properties cannot be edited after initialization.
# To make changes, construct new SchemaField objects.
relaxed_schema = [
    bigquery.SchemaField("full_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("age", "INTEGER", mode="NULLABLE"),
]
table.schema = relaxed_schema
table = client.update_table(table, ["schema"])

assert all(field.mode == "NULLABLE" for field in table.schema)

Passer du mode REQUIRED au mode NULLABLE dans une tâche de chargement ou de requête

Vous pouvez assouplir une colonne du mode REQUIRED en mode NULLABLE dans le schéma d'une table existante lorsque vous y chargez des données et choisissez d'écraser cette table. Le schéma des données chargées est alors utilisé pour écraser le schéma de la table existante. Pour en savoir plus sur le remplacement d'une table à l'aide d'une tâche de chargement, consultez les sections suivantes :

Vous pouvez également assouplir une colonne du mode REQUIRED au mode NULLABLE dans le schéma d'une table existante lorsque vous ajoutez des données à l'aide d'une tâche de requête.

Passer du mode REQUIRED au mode NULLABLE dans une tâche de chargement ajoutant des données

Pour assouplir le mode d'une colonne lors de l'ajout de données à une table via une tâche de chargement, vous pouvez :

  • utiliser la commande bq load de l'outil de ligne de commande ;
  • appeler la méthode jobs.insert de l'API et configurer une tâche de chargement ;
  • utiliser les bibliothèques clientes.

La modification du mode d'une colonne lors d'une opération d'ajout n'est actuellement pas possible dans Cloud Console ou l'interface utilisateur Web classique de BigQuery.

Lorsque vous assouplissez le mode d'une colonne en utilisant une opération d'ajout dans une tâche de chargement, vous pouvez :

  • assouplir le mode des colonnes individuelles en spécifiant un fichier de schéma JSON lors de l'ajout de données à partir de fichiers CSV et JSON ;
  • assouplir les colonnes en les passant au mode null dans votre schéma Avro, ORC ou Parquet et autoriser l'inférence de schéma à détecter les colonnes dont le mode a été assoupli.

Pour assouplir une colonne du mode REQUIRED au mode NULLABLE lorsque vous ajoutez des données à une table lors d'une tâche de chargement, procédez comme suit.

Console

Actuellement, il n'est pas possible d'assouplir le mode d'une colonne à l'aide de Cloud Console.

UI classique

Vous ne pouvez pas assouplir le mode des colonnes existantes lorsque vous chargez des données en utilisant l'interface utilisateur Web classique de BigQuery.

CLI

Exécutez la commande bq load pour charger vos données et spécifiez l'option --noreplace pour indiquer que vous ajoutez les données à une table existante.

Si les données que vous ajoutez sont au format JSON délimité par des retours à la ligne ou CSV, spécifiez les colonnes dont le mode a été assoupli dans un fichier de schéma JSON local. Vous pouvez également utiliser l'option --autodetect pour procéder à la détection de schéma afin d'identifier les colonnes dont le mode a été assoupli dans les données sources. Pour savoir comment assouplir les modes de colonne à l'aide d'un fichier de schéma JSON, consultez la section Passer manuellement une colonne du mode REQUIRED au mode NULLABLE.

Les colonnes dont le mode a été assoupli peuvent être automatiquement obtenues à partir des fichiers Avro, ORC et Parquet. L'assouplissement du mode des colonnes ne peut pas être appliqué aux fichiers d'exportation Cloud Datastore ajoutés. Les colonnes des tables créées en chargeant les fichiers d'exportation Cloud Datastore sont toujours définies comme NULLABLE.

Définissez l'option --schema_update_option sur ALLOW_FIELD_RELAXATION pour indiquer que les données que vous ajoutez contiennent de nouvelles colonnes.

Si la table que vous ajoutez se trouve dans un ensemble de données d'un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

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

Saisissez la commande load comme suit :

bq --location=location load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=format \
project_id:dataset.table \
path_to_source \
schema

Où :

  • location est le 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 correspond à NEWLINE_DELIMITED_JSON, CSV, PARQUET, ORC ou AVRO. Les fichiers DATASTORE_BACKUP ne nécessitent pas que le mode des colonnes soit assoupli. Les colonnes des tables créées à partir des fichiers d'exportation Cloud Datastore sont toujours définies comme NULLABLE.
  • project_id est l'ID du projet.
  • dataset est le nom de l'ensemble de données contenant la table.
  • table est le nom de la table que vous ajoutez.
  • path_to_source correspond à un URI Cloud Storage complet, à une liste d'URI séparés par une virgule ou au chemin d'accès d'un fichier de données se trouvant sur votre ordinateur local.
  • schema correspond au chemin d'accès vers un fichier de schéma JSON local. Cette option n'est utilisée que pour les fichiers CSV et JSON. Les colonnes dont le mode a été assoupli sont automatiquement obtenues à partir des fichiers Avro.

Exemples :

Saisissez la commande suivante pour ajouter un fichier de données Avro local, /tmp/mydata.avro, à mydataset.mytable à l'aide d'une tâche de chargement. Comme les colonnes dont le mode a été assoupli peuvent être automatiquement obtenues à partir des données Avro, vous n'avez pas besoin de spécifier un fichier de schéma. mydataset se trouve dans votre projet par défaut.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=AVRO \
mydataset.mytable \
/tmp/mydata.avro

Saisissez la commande suivante pour ajouter les données d'un fichier JSON délimité par des retours à la ligne dans Cloud Storage à mydataset.mytable à l'aide d'une tâche de chargement. Le schéma contenant les colonnes assouplies se trouve dans un fichier de schéma JSON local nommé /tmp/myschema.json. mydataset se trouve dans votre projet par défaut.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json \
/tmp/myschema.json

Entrez la commande suivante pour ajouter les données d'un fichier CSV sur votre ordinateur local dans mydataset.mytable à l'aide d'une tâche de chargement. La commande utilise la détection automatique de schéma pour déterminer les colonnes dont le mode a été assoupli dans les données sources. mydataset se trouve dans myotherproject, et non dans votre projet par défaut.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=CSV \
--autodetect \
myotherproject:mydataset.mytable \
mydata.csv

API

Appelez la méthode jobs.insert. Configurez une tâche load et définissez les propriétés suivantes :

  • Référencez vos données dans Cloud Storage à l'aide de la propriété sourceUris.
  • Spécifiez le format de données en définissant la propriété sourceFormat.
  • Spécifiez le schéma dans la propriété schema.
  • Spécifiez l'option de mise à jour de schéma à l'aide de la propriété schemaUpdateOptions.
  • Définissez la disposition d'écriture de la table de destination sur WRITE_APPEND à l'aide de la propriété writeDisposition.

Go

import (
	"context"
	"fmt"
	"os"

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

// relaxTableImport demonstrates amending the schema of a table to relax columns from
// not allowing NULL values to allowing them.
func relaxTableImport(projectID, datasetID, tableID, filename 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)
	}

	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	meta := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, meta); err != nil {
		return err
	}
	// Now, import data from a local file, but specify relaxation of required
	// fields as a side effect while the data is appended.
	f, err := os.Open(filename)
	if err != nil {
		return err
	}
	source := bigquery.NewReaderSource(f)
	source.AutoDetect = true   // Allow BigQuery to determine schema.
	source.SkipLeadingRows = 1 // CSV has a single header line.

	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(source)
	loader.SchemaUpdateOptions = []string{"ALLOW_FIELD_RELAXATION"}
	job, err := loader.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
	}
	return nil
}

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 sur l'utilisation des bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur l'API BigQuery Node.js.

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

// Instantiate client
const bigquery = new BigQuery();

async function relaxColumnLoadAppend() {
  // Changes required column to nullable in load append job.

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

  // In this example, the existing table contains the 'Name'
  // column as a 'REQUIRED' field.
  const schema = 'Age:INTEGER, Weight:FLOAT, IsMagic:BOOLEAN';

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

  // Set load job options
  const options = {
    schema: schema,
    schemaUpdateOptions: ['ALLOW_FIELD_RELAXATION'],
    writeDisposition: 'WRITE_APPEND',
    destinationTable: destinationTableRef,
  };

  // Load data from a local file into the table
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .load(fileName, options);

  console.log(`Job ${job.id} completed.`);

  // Check the job's status for errors
  const errors = job.status.errors;
  if (errors && errors.length > 0) {
    throw errors;
  }
}

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

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')
# filepath = 'path/to/your_file.csv'

# Retrieves the destination table and checks the number of required fields
table_id = "my_table"
table_ref = dataset_ref.table(table_id)
table = client.get_table(table_ref)
original_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
# In this example, the existing table has 3 required fields.
print("{} fields in the schema are required.".format(original_required_fields))

# Configures the load job to append the data to a destination table,
# allowing field relaxation
job_config = bigquery.LoadJobConfig()
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION
]
# In this example, the existing table contains three required fields
# ('full_name', 'age', and 'favorite_color'), while the data to load
# contains only the first two fields.
job_config.schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1

with open(filepath, "rb") as source_file:
    job = client.load_table_from_file(
        source_file,
        table_ref,
        location="US",  # Must match the destination dataset location.
        job_config=job_config,
    )  # API request

job.result()  # Waits for table load to complete.
print(
    "Loaded {} rows into {}:{}.".format(
        job.output_rows, dataset_id, table_ref.table_id
    )
)

# Checks the updated number of required fields
table = client.get_table(table)
current_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
print("{} fields in the schema are now required.".format(current_required_fields))

Passer du mode REQUIRED au mode NULLABLE dans une tâche de requête ajoutant des résultats de requête

Pour assouplir toutes les colonnes d'une table lorsque vous ajoutez des résultats de requête, vous pouvez :

  • utiliser la commande bq query de l'outil de ligne de commande ;
  • appeler la méthode jobs.insert de l'API, puis configurer une tâche de requête ;
  • utiliser les bibliothèques clientes.

L'assouplissement des colonnes lors d'une opération d'ajout n'est actuellement pas possible dans Cloud Console ou l'interface utilisateur Web de BigQuery.

Lorsque vous assouplissez les colonnes à l'aide d'une opération d'ajout dans une tâche de requête, vous pouvez assouplir tous les champs obligatoires dans la table de destination en définissant l'option --schema_update_option sur ALLOW_FIELD_RELAXATION. Vous ne pouvez pas assouplir le mode de colonnes individuelles dans une table de destination à l'aide d'une tâche de requête ajoutant des résultats de requête.

Pour assouplir le mode de toutes les colonnes d'une table de destination lorsque vous y ajoutez des données lors d'une tâche de requête, procédez comme suit.

Console

Actuellement, il n'est pas possible d'assouplir le mode d'une colonne à l'aide de Cloud Console.

UI classique

Vous ne pouvez pas assouplir le mode des colonnes d'une table de destination lorsque vous ajoutez des résultats de requête à l'aide de l'interface utilisateur Web de BigQuery.

CLI

Exécutez la commande bq query pour interroger vos données et spécifiez l'option --destination_table pour indiquer la table que vous ajoutez.

Pour spécifier que vous ajoutez des résultats de requête à une table de destination existante, spécifiez l'option --append_table.

Définissez l'option --schema_update_option sur ALLOW_FIELD_RELAXATION pour indiquer que toutes les colonnes REQUIRED de la table que vous ajoutez doivent être remplacées par NULLABLE.

Spécifiez l'option use_legacy_sql=false afin d'utiliser la syntaxe SQL standard pour la requête.

Si la table que vous ajoutez se trouve dans un ensemble de données d'un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

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

bq --location=location query \
--destination_table project_id:dataset.table \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'query'

Où :

  • location est le 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.
  • project_id est l'ID du projet.
  • dataset est le nom de l'ensemble de données contenant la table que vous ajoutez.
  • table est le nom de la table que vous ajoutez.
  • query est une requête en syntaxe SQL standard.

Exemples :

Saisissez la commande suivante pour interroger mydataset.mytable dans votre projet par défaut et pour ajouter les résultats de la requête à mydataset.mytable2 (également dans votre projet par défaut). La commande modifie toutes les colonnes REQUIRED de la table de destination en mode NULLABLE.

bq query \
--destination_table mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

Saisissez la commande suivante pour interroger mydataset.mytable dans votre projet par défaut et pour ajouter les résultats de la requête à mydataset.mytable2 dans myotherproject. La commande modifie toutes les colonnes REQUIRED de la table de destination en mode NULLABLE.

bq query \
--destination_table myotherproject:mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

API

Appelez la méthode jobs.insert. Configurez une tâche query et définissez les propriétés suivantes :

  • Spécifiez la table de destination à l'aide de la propriété destinationTable.
  • Définissez la disposition d'écriture de la table de destination sur WRITE_APPEND à l'aide de la propriété writeDisposition.
  • Spécifiez l'option de mise à jour de schéma à l'aide de la propriété schemaUpdateOptions.
  • Spécifiez la requête SQL standard à l'aide de la propriété query.

Go

import (
	"context"
	"fmt"

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

// relaxTableQuery demonstrates relaxing the schema of a table by appending query results to
// enable the table to allow NULL values.
func relaxTableQuery(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)
	}
	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	meta := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, meta); err != nil {
		return err
	}
	// Now, append a query result that includes nulls, but allow the job to relax
	// all required columns.
	q := client.Query("SELECT \"Beyonce\" as full_name")
	q.QueryConfig.Dst = client.Dataset(datasetID).Table(tableID)
	q.SchemaUpdateOptions = []string{"ALLOW_FIELD_RELAXATION"}
	q.WriteDisposition = bigquery.WriteAppend
	q.Location = "US"
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	_, err = job.Wait(ctx)
	if err != nil {
		return err
	}
	return nil
}

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

from google.cloud import bigquery

# TODO(developer): 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"

# Retrieves the destination table and checks the number of required fields.
table = client.get_table(table_id)  # Make an API request.
original_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)

# In this example, the existing table has 2 required fields.
print("{} fields in the schema are required.".format(original_required_fields))

# Configures the query to append the results to a destination table,
# allowing field relaxation.
job_config = bigquery.QueryJobConfig(destination=table_id)
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION
]
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND

# Start the query, passing in the extra configuration.
query_job = client.query(
    # In this example, the existing table contains 'full_name' and 'age' as
    # required columns, but the query results will omit the second column.
    'SELECT "Beyonce" as full_name;',
    job_config=job_config,
)  # Make an API request.
query_job.result()  # Wait for the job to complete.

# Checks the updated number of required fields.
table = client.get_table(table_id)  # Make an API request.
current_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
print("{} fields in the schema are now required.".format(current_required_fields))

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

Envoyer des commentaires concernant…

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