Modifier des schémas de table

Ce document décrit comment modifier les définitions de schéma pour les tables BigQuery existantes.

Vous pouvez effectuer la plupart des modifications de schéma décrites dans ce document à l'aide d'instructions LDD (langage de définition de données) SQL. Ces instructions n'entraînent aucuns frais.

Vous pouvez modifier le schéma d'une table de toutes les manières décrites sur cette page :exportation de vos données de table dans Cloud Storage, puis chargement des données dans une nouvelle table avec la définition de schéma modifiée Les jobs de chargement et d'exportation BigQuery sont gratuits, mais des frais s'appliquent pour le stockage des données exportées dans Cloud Storage. Les sections suivantes décrivent d'autres façons d'effectuer différents types de modifications de schéma.

Ajouter une colonne

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

Ajoutez une colonne vide.
Écrasez une table avec une tâche de chargement ou de requête.
Ajoutez des données à une table avec 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 une colonne vide

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. L'ajout d'une colonne REQUIRED à un schéma de table existant dans l'API ou l'outil de ligne de commande bq génère une erreur. Toutefois, vous pouvez créer une colonne REQUIRED imbriquée dans un nouveau champ RECORD. 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.

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

Console

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

Accéder à BigQuery
Dans le panneau Explorer, développez votre projet et votre ensemble de données, puis sélectionnez la table.
Dans le panneau des détails, cliquez sur l'onglet Preview (Aperçu).
Cliquez sur Modifier le schéma. Vous devrez peut-être faire défiler la page pour voir ce bouton.
Sur la page Current schema (Schéma actuel), sous New fields (Nouveaux champs), 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.
Lorsque vous avez terminé d'ajouter des colonnes, cliquez sur Enregistrer.

SQL

Utilisez l'instruction LDD ALTER TABLE ADD COLUMN :

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

Accéder à BigQuery
Dans l'éditeur de requête, saisissez l'instruction suivante :
```
ALTER TABLE mydataset.mytable
ADD COLUMN new_column STRING;
```
Cliquez sur Exécuter.

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

bq

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

Remplacez les éléments suivants :

PROJECT_ID : ID de votre projet.
DATASET : nom de l'ensemble de données contenant la table que vous mettez à jour.
TABLE : nom de la table que vous mettez à jour.
SCHEMA : chemin d'accès au fichier de schéma JSON sur votre ordinateur local.

Lorsque vous spécifiez un schéma intégré, vous ne pouvez pas spécifier la description, le mode, ni le type RECORD (STRUCT) de la colonne. Tous les modes de colonne sont définis par défaut 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.

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 modifie les colonnes REPEATED existantes en NULLABLE, ce qui génère l'erreur suivante : 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 l'outil de ligne de commande bq, 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 :

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
```
Remplacez les éléments suivants :
- PROJECT_ID : ID de votre projet.
- DATASET : nom de l'ensemble de données contenant la table que vous mettez à jour.
- TABLE : nom de la table que vous mettez à jour.
- SCHEMA : 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
```

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"
  }
]

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.
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
```
Remplacez les éléments suivants :
- PROJECT_ID : ID de votre projet.
- DATASET : nom de l'ensemble de données contenant la table que vous mettez à jour.
- TABLE : nom de la table que vous mettez à jour.
- SCHEMA : fichier de définition de schéma écrit 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.json est 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.

Accéder

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

// 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)
	}
	defer client.Close()

	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
}

Java

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.

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.FieldList;
import com.google.cloud.bigquery.LegacySQLTypeName;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.Table;
import java.util.ArrayList;
import java.util.List;

public class AddEmptyColumn {

  public static void runAddEmptyColumn() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableId = "MY_TABLE_NAME";
    String newColumnName = "NEW_COLUMN_NAME";
    addEmptyColumn(newColumnName, datasetName, tableId);
  }

  public static void addEmptyColumn(String newColumnName, String datasetName, String tableId) {
    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();

      Table table = bigquery.getTable(datasetName, tableId);
      Schema schema = table.getDefinition().getSchema();
      FieldList fields = schema.getFields();

      // Create the new field/column
      Field newField = Field.of(newColumnName, LegacySQLTypeName.STRING);

      // Create a new schema adding the current fields, plus the new one
      List<Field> fieldList = new ArrayList<Field>();
      fields.forEach(fieldList::add);
      fieldList.add(newField);
      Schema newSchema = Schema.of(fieldList);

      // Update the table with the new schema
      Table updatedTable =
          table.toBuilder().setDefinition(StandardTableDefinition.of(newSchema)).build();
      updatedTable.update();
      System.out.println("Empty column successfully added to table");
    } catch (BigQueryException e) {
      System.out.println("Empty column was not added. \n" + e.toString());
    }
  }
}

Node.js

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

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

# 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 à une colonne `RECORD`

En plus d'ajouter des colonnes au schéma d'une table, vous pouvez également ajouter des colonnes imbriquées à une colonne 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 pas possible dans la console Google Cloud.

SQL

Il n'est pas possible d'ajouter un nouveau champ imbriqué à une colonne RECORD existante en utilisant une instruction LDD SQL.

bq

Exécutez la commande bq update et 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

Remplacez les éléments suivants :

PROJECT_ID : ID de votre projet.
DATASET : nom de l'ensemble de données contenant la table que vous mettez à jour.
TABLE : nom de la table que vous mettez à jour.
SCHEMA : chemin d'accès au fichier de schéma JSON sur votre ordinateur local.

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

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
```
Remplacez les éléments suivants :
- PROJECT_ID : ID de votre projet.
- DATASET : nom de l'ensemble de données contenant la table que vous mettez à jour.
- TABLE : nom de la table que vous mettez à jour.
- SCHEMA : 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
```

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"
  }
]

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.

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
```
Remplacez les éléments suivants :
- PROJECT_ID : ID de votre projet.
- DATASET : nom de l'ensemble de données contenant la table que vous mettez à jour.
- TABLE : nom de la table que vous mettez à jour.
- SCHEMA : chemin d'accès au 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.json est 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 des colonnes 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 l'écrasement d'une table avec une tâche de chargement, consultez le document correspondant au format de vos données :

Ajouter des colonnes dans un job de chargement ajoutant des données

Vous pouvez ajouter des colonnes à une table lorsque vous y ajoutez des données via un job de chargement. Le nouveau schéma est déterminé par l'un des éléments suivants :

Détection automatique (pour les fichiers CSV et JSON)
Un schéma spécifié dans un fichier de schéma JSON (pour les fichiers CSV et JSON)
Les données sources autodescriptives pour les fichiers d'exportation Avro, ORC, Parquet et 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, une erreur est renvoyée lorsque vous tentez d'ajouter des données.

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'un job de chargement, utilisez l'une des options suivantes :

bq

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.

(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

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 : format du schéma. NEWLINE_DELIMITED_JSON, CSV, AVRO, PARQUET, ORC ou DATASTORE_BACKUP.
PROJECT_ID : ID de votre projet.
DATASET : nom de l'ensemble de données contenant la table.
TABLE : nom de la table que vous ajoutez.
PATH_TO_SOURCE : URI Cloud Storage complet, liste d'URI séparés par une virgule ou chemin d'accès d'un fichier de données se trouvant sur votre machine locale.
SCHEMA : chemin d'accès à 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.

Par exemple :

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.

Accéder

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)
	}
	defer client.Close()

	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
}

Java

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.JobInfo.SchemaUpdateOption;
import com.google.cloud.bigquery.JobInfo.WriteDisposition;
import com.google.cloud.bigquery.LegacySQLTypeName;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.TableId;
import com.google.common.collect.ImmutableList;
import java.util.UUID;

public class AddColumnLoadAppend {

  public static void runAddColumnLoadAppend() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "/path/to/file.csv";
    addColumnLoadAppend(datasetName, tableName, sourceUri);
  }

  public static void addColumnLoadAppend(String datasetName, String tableName, String sourceUri)
      throws Exception {
    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);

      // Add a new column to a BigQuery table while appending rows via a load job.
      // 'REQUIRED' fields cannot  be added to an existing schema, so the additional column must be
      // 'NULLABLE'.
      Schema newSchema =
          Schema.of(
              Field.newBuilder("name", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.REQUIRED)
                  .build(),
              // Adding below additional column during the load job
              Field.newBuilder("post_abbr", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build());

      LoadJobConfiguration loadJobConfig =
          LoadJobConfiguration.builder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.csv())
              .setWriteDisposition(WriteDisposition.WRITE_APPEND)
              .setSchema(newSchema)
              .setSchemaUpdateOptions(ImmutableList.of(SchemaUpdateOption.ALLOW_FIELD_ADDITION))
              .build();

      // Create a job ID so that we can safely retry.
      JobId jobId = JobId.of(UUID.randomUUID().toString());
      Job loadJob = bigquery.create(JobInfo.newBuilder(loadJobConfig).setJobId(jobId).build());

      // Load data from a GCS parquet file into the table
      // Blocks until this load table job completes its execution, either failing or succeeding.
      Job completedJob = loadJob.waitFor();

      // Check for errors
      if (completedJob == null) {
        throw new Exception("Job not executed since it no longer exists.");
      } else if (completedJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(
            "BigQuery was unable to load into the table due to an error: \n"
                + loadJob.getStatus().getError());
      }
      System.out.println("Column successfully added during load append job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Column not added during load append \n" + e.toString());
    }
  }
}

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

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, '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 des colonnes dans un job de requête ajoutant des résultats de requête

Vous pouvez ajouter des colonnes à une table lorsque vous y ajoutez des résultats de requête.

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'un job de requête, sélectionnez l'une des options suivantes :

bq

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 GoogleSQL 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'

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. Notez que vous ne pouvez pas ajouter les résultats d'une requête à une table dont l'emplacement est différent.
PROJECT_ID : ID de votre projet.
dataset : nom de l'ensemble de données contenant la table que vous ajoutez.
TABLE : nom de la table que vous ajoutez.
QUERY : requête en syntaxe GoogleSQL.

Exemples :

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 GoogleSQL à l'aide de la propriété query.

Accéder

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)
	}
	defer client.Close()

	// 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
}

Java

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.JobInfo.SchemaUpdateOption;
import com.google.cloud.bigquery.JobInfo.WriteDisposition;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableList;

public class RelaxTableQuery {

  public static void runRelaxTableQuery() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    relaxTableQuery(projectId, datasetName, tableName);
  }

  // To relax all columns in a destination table when you append data to it during a query job
  public static void relaxTableQuery(String projectId, String datasetName, String tableName)
      throws Exception {
    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);

      String sourceTable = "`" + projectId + "." + datasetName + "." + tableName + "`";
      String query = "SELECT word FROM " + sourceTable + " WHERE word like '%is%'";

      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              // Use standard SQL syntax for queries.
              // See: https://cloud.google.com/bigquery/sql-reference/
              .setUseLegacySql(false)
              .setSchemaUpdateOptions(ImmutableList.of(SchemaUpdateOption.ALLOW_FIELD_RELAXATION))
              .setWriteDisposition(WriteDisposition.WRITE_APPEND)
              .setDestinationTable(tableId)
              .build();

      Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).build());

      queryJob = queryJob.waitFor();

      // Check for errors
      if (queryJob == null) {
        throw new Exception("Job no longer exists");
      } else if (queryJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(queryJob.getStatus().getError().toString());
      }

      // Get the results.
      TableResult results = queryJob.getQueryResults();

      // Print all pages of the results.
      results
          .iterateAll()
          .forEach(
              rows -> {
                rows.forEach(row -> System.out.println("row: " + row.toString()));
              });

      System.out.println("Successfully relaxed all columns in destination table during query job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Columns not relaxed during query job \n" + e.toString());
    }
  }
}

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

from google.cloud import bigquery

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

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

# 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,
    schema_update_options=[bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION],
    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)))

Renommer une colonne

Pour renommer une colonne d'une table, utilisez l'instruction LDD ALTER TABLE RENAME COLUMN. L'exemple suivant renomme la colonne old_name en new_name sur mytable :

ALTER TABLE mydataset.mytable
  RENAME COLUMN old_name TO new_name;

Pour en savoir plus sur les instructions ALTER TABLE RENAME COLUMN, consultez la section Détails des instructions LDD.

Modifier le type de données d'une colonne

Vous ne pouvez pas modifier le type de données d'une colonne à l'aide de la console, de l'outil de ligne de commande bq ou de l'API BigQuery. Si vous essayez de mettre à jour une table en appliquant un schéma qui spécifie un nouveau type de données pour une colonne, une erreur est renvoyée.

Modifier le type de données d'une colonne avec une instruction LDD

Vous pouvez utiliser le langage GoogleSQL pour apporter certaines modifications au type de données d'une colonne. Pour en savoir plus et obtenir la liste complète des conversions de types de données compatibles, consultez la section Instruction LDD ALTER COLUMN SET DATA TYPE.

L'exemple suivant crée une table avec une colonne de type INT64, puis met à jour le type sur NUMERIC :

CREATE TABLE mydataset.mytable(c1 INT64);

ALTER TABLE mydataset.mytable
ALTER COLUMN c1 SET DATA TYPE NUMERIC;

L'exemple suivant crée une table avec une colonne imbriquée avec deux champs, puis met à jour le type de l'une des colonnes de INT à NUMERIC :

CREATE TABLE mydataset.mytable(s1 STRUCT<a INT64, b STRING>);

ALTER TABLE mydataset.mytable ALTER COLUMN s1
SET DATA TYPE STRUCT<a NUMERIC, b STRING>;

Caster le type de données d'une colonne

Pour modifier le type de données d'une colonne en type castable, exécutez une requête SQL pour sélectionner les données de la table, puis castez la colonne concernée et écrasez la table. Le casting et l'écrasement ne sont pas recommandés pour les très grandes tables, car ils nécessitent une analyse complète de la table.

L'exemple suivant montre une requête SQL qui sélectionne toutes les données de column_two et column_three dans mydataset.mytable, et caste column_one de DATE en STRING. Le résultat de la requête permet d'écraser la table existante. La table écrasée stocke column_one en tant que type de données STRING.

En cas d'utilisation de CAST, une requête peut échouer si BigQuery est incapable d'effectuer le casting. Pour en savoir plus sur le casting de règles dans GoogleSQL, consultez la page Casting.

Console

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

Accéder à BigQuery
Dans l'éditeur de requête, saisissez la requête suivante pour sélectionner toutes les données de column_two et column_three dans mydataset.mytable, ainsi que pour caster column_one de DATE en STRING. La requête utilise un alias pour caster column_one avec le même nom. mydataset.mytable se trouve dans votre projet par défaut.
```
SELECT
 column_two,
 column_three,
 CAST(column_one AS STRING) AS column_one
FROM
 mydataset.mytable;
```
Cliquez sur Plus et sélectionnez Paramètres de requête.
Dans la section Destination, procédez comme suit :
1. Cochez la case Set a destination table for query results (Définir une table de destination pour les résultats de la requête).
2. Dans le champ Nom du projet, laissez la valeur définie sur votre projet par défaut. Il s'agit du projet contenant mydataset.mytable.
3. Dans le champ Ensemble de données, choisissez mydataset.
4. Dans le champ ID de la table, saisissez mytable.
5. Dans la section Destination table write preference (Préférence d'écriture pour la table de destination), choisissez Overwrite table (Écraser la table). Cette option écrase mytable à l'aide des résultats de la requête.
(Facultatif) Choisissez l'emplacement de vos données.
Pour mettre à jour les paramètres, cliquez sur Enregistrer.
Cliquez sur Exécuter.

Une fois la tâche de requête terminée, le type de données de column_one devient STRING.

bq

Saisissez la commande bq query suivante pour sélectionner toutes les données de column_two et column_three dans mydataset.mytable, ainsi que pour caster column_one de DATE en STRING. La requête utilise un alias pour caster column_one avec le même nom. mydataset.mytable se trouve dans votre projet par défaut.

Les résultats de la requête sont écrits dans mydataset.mytable à l'aide de l'option --destination_table. L'option --replace permet quant à elle d'écraser mytable. Spécifiez l'option use_legacy_sql=false pour utiliser la syntaxe GoogleSQL.

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

bq query \
    --destination_table mydataset.mytable \
    --replace \
    --use_legacy_sql=false \
'SELECT
  column_two,
  column_three,
  CAST(column_one AS STRING) AS column_one
FROM
  mydataset.mytable'

API

Pour sélectionner toutes les données de column_two et column_three dans mydataset.mytable, et caster column_one de DATE en STRING, appelez la méthode jobs.insert et configurez une tâche de requête (query). (Facultatif) Spécifiez l'emplacement dans la propriété location de la section jobReference.

La requête SQL utilisée dans la tâche de requête devrait ressembler à ceci : SELECT column_two, column_three, CAST(column_one AS STRING) AS column_one FROM mydataset.mytable. La requête utilise un alias pour caster column_one avec le même nom.

Pour écraser mytable avec les résultats de la requête, incluez mydataset.mytable dans la propriété configuration.query.destinationTable, puis spécifiez WRITE_TRUNCATE dans la propriété configuration.query.writeDisposition.

Modifier le mode d'une colonne

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 également assouplir une colonne lorsque vous chargez des données pour écraser une table existante ou lorsque vous ajoutez des données à une table existante. Vous ne pouvez pas modifier le mode d'une colonne de NULLABLE à REQUIRED.

Rendre une colonne `NULLABLE` dans une table existante

Pour passer le mode d'une colonne de REQUIRED à NULLABLE, sélectionnez l'une des options suivantes :

Console

Accédez à la page BigQuery.

Accéder à BigQuery
Dans le panneau Explorer, développez votre projet et votre ensemble de données, puis sélectionnez la table.
Dans le panneau des détails, cliquez sur l'onglet Preview (Aperçu).
Cliquez sur Modifier le schéma. Vous devrez peut-être faire défiler la page pour voir ce bouton.
Sur la page Schéma actuel, repérez le champ que vous souhaitez modifier.
Dans la liste déroulante Mode de ce champ, sélectionnez NULLABLE.
Pour mettre à jour les paramètres, cliquez sur Enregistrer.

SQL

Utilisez l'instruction LDD ALTER COLUMN DROP NOT NULL : L'exemple suivant modifie le mode de la colonne mycolumn de REQUIRED à NULLABLE :

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

Accéder à BigQuery

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

ALTER TABLE mydataset.mytable
ALTER COLUMN mycolumn
DROP NOT NULL;

Cliquez sur Exécuter.

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

bq

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
```
Remplacez les éléments suivants :
- PROJECT_ID : ID de votre projet.
- DATASET : nom de l'ensemble de données contenant la table que vous mettez à jour.
- TABLE : nom de la table que vous mettez à jour.
- SCHEMA_FILE : 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
```

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"
  }
]

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.
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
```
Remplacez les éléments suivants :
- PROJECT_ID : ID de votre projet.
- DATASET : nom de l'ensemble de données contenant la table que vous mettez à jour.
- TABLE : nom de la table que vous mettez à jour.
- SCHEMA : chemin d'accès au 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.json est 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.

Accéder

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)
	}
	defer client.Close()

	// 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
}

Java

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.LegacySQLTypeName;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.Table;

public class RelaxColumnMode {

  public static void runRelaxColumnMode() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableId = "MY_TABLE_NAME";
    relaxColumnMode(datasetName, tableId);
  }

  public static void relaxColumnMode(String datasetName, String tableId) {
    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();

      Table table = bigquery.getTable(datasetName, tableId);

      // Create new relaxed schema based on the existing table schema
      Schema relaxedSchema =
          Schema.of(
              // The only supported modification you can make to a column's mode is changing it from
              // REQUIRED to NULLABLE
              // Changing a column's mode from REQUIRED to NULLABLE is also called column relaxation
              // INFO: LegacySQLTypeName will be updated to StandardSQLTypeName in release 1.103.0
              Field.newBuilder("word", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build(),
              Field.newBuilder("word_count", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build(),
              Field.newBuilder("corpus", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build(),
              Field.newBuilder("corpus_date", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build());

      // Update the table with the new schema
      Table updatedTable =
          table.toBuilder().setDefinition(StandardTableDefinition.of(relaxedSchema)).build();
      updatedTable.update();
      System.out.println("Table schema successfully relaxed.");
    } catch (BigQueryException e) {
      System.out.println("Table schema not relaxed \n" + e.toString());
    }
  }
}

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

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

# TODO(dev): Change table_id to full name of the table you want to create.
table_id = "your-project.your_dataset.your_table"

table = client.get_table(table_id)
new_schema = []
for field in table.schema:
    if field.mode != "REQUIRED":
        new_schema.append(field)
    else:
        # SchemaField properties cannot be edited after initialization.
        # To make changes, construct new SchemaField objects.
        new_field = field.to_api_repr()
        new_field["mode"] = "NULLABLE"
        relaxed_field = bigquery.SchemaField.from_api_repr(new_field)
        new_schema.append(relaxed_field)

table.schema = new_schema
table = client.update_table(table, ["schema"])

print(f"Updated {table_id} schema: {table.schema}.")

Rendre une colonne `NULLABLE` avec un job de chargement par ajout

Vous pouvez assouplir le mode d'une colonne lorsque vous ajoutez des données à une table via un job de chargement. Sélectionnez l'une des options suivantes en fonction du type de fichier :

Lorsque vous ajoutez des données à partir de fichiers CSV et JSON, assouplissez le mode de colonnes individuelles en spécifiant un fichier de schéma JSON.
Lorsque vous ajoutez des données à partir de fichiers Avro, ORC ou Parquet, assouplissez les colonnes sur NULL dans votre schéma et laissez 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'un job de chargement, sélectionnez l'une des options suivantes :

Console

Il n'est pas possible d'assouplir le mode d'une colonne à l'aide de la console Google Cloud.

bq

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.

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.

(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

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, 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 : ID de votre projet.
dataset : nom de l'ensemble de données contenant la table.
TABLE : nom de la table que vous ajoutez.
PATH_TO_SOURCE : URI Cloud Storage complet, liste d'URI séparés par une virgule ou chemin d'accès d'un fichier de données se trouvant sur votre machine locale.
SCHEMA : chemin d'accès à 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.

Par exemple :

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.

Accéder

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)
	}
	defer client.Close()

	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
}

Java

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.Field;
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.Table;
import com.google.cloud.bigquery.TableId;
import com.google.common.collect.ImmutableList;

// Sample to append relax column in a table.
public class RelaxColumnLoadAppend {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    relaxColumnLoadAppend(datasetName, tableName, sourceUri);
  }

  public static void relaxColumnLoadAppend(String datasetName, String tableName, String sourceUri) {
    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();

      // Retrieve destination table reference
      Table table = bigquery.getTable(TableId.of(datasetName, tableName));

      // column as a 'REQUIRED' field.
      Field name =
          Field.newBuilder("name", StandardSQLTypeName.STRING).setMode(Field.Mode.REQUIRED).build();
      Field postAbbr =
          Field.newBuilder("post_abbr", StandardSQLTypeName.STRING)
              .setMode(Field.Mode.REQUIRED)
              .build();
      Schema schema = Schema.of(name, postAbbr);

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      // Set job options
      LoadJobConfiguration loadConfig =
          LoadJobConfiguration.newBuilder(table.getTableId(), sourceUri)
              .setSchema(schema)
              .setFormatOptions(csvOptions)
              .setSchemaUpdateOptions(
                  ImmutableList.of(JobInfo.SchemaUpdateOption.ALLOW_FIELD_RELAXATION))
              .setWriteDisposition(JobInfo.WriteDisposition.WRITE_APPEND)
              .build();

      // Create a load job and wait for it to complete.
      Job job = bigquery.create(JobInfo.of(loadConfig));
      job = job.waitFor();
      // Check the job's status for errors
      if (job.isDone() && job.getStatus().getError() == null) {
        System.out.println("Relax column append 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());
    }
  }
}

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

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, '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))

Rendre toutes les colonnes `NULLABLE` avec un job d'ajout

Vous pouvez assouplir toutes les colonnes d'une table lorsque vous y ajoutez des résultats 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 des colonnes individuelles dans une table de destination à l'aide d'un job de requête ajoutant des résultats de requête. Pour assouplir le mode de colonnes individuelles avec un job de chargement ajoutant des données, consultez la section Rendre une colonne NULLABLE avec un job d'ajout.

Pour assouplir toutes les colonnes lorsque vous ajoutez des résultats de requête à une table de destination, sélectionnez l'une des options suivantes :

Console

Il n'est pas possible d'assouplir le mode d'une colonne à l'aide de la console Google Cloud.

bq

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 GoogleSQL pour la requête.

(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'

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 à l'aide du fichier .bigqueryrc.
PROJECT_ID : ID de votre projet.
DATASET : nom de l'ensemble de données contenant la table que vous ajoutez.
TABLE : nom de la table que vous ajoutez.
QUERY : requête en syntaxe GoogleSQL.

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 GoogleSQL à l'aide de la propriété query.

Accéder

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)
	}
	defer client.Close()

	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
}

Java

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.JobInfo.SchemaUpdateOption;
import com.google.cloud.bigquery.JobInfo.WriteDisposition;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableList;

public class RelaxTableQuery {

  public static void runRelaxTableQuery() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    relaxTableQuery(projectId, datasetName, tableName);
  }

  // To relax all columns in a destination table when you append data to it during a query job
  public static void relaxTableQuery(String projectId, String datasetName, String tableName)
      throws Exception {
    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);

      String sourceTable = "`" + projectId + "." + datasetName + "." + tableName + "`";
      String query = "SELECT word FROM " + sourceTable + " WHERE word like '%is%'";

      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              // Use standard SQL syntax for queries.
              // See: https://cloud.google.com/bigquery/sql-reference/
              .setUseLegacySql(false)
              .setSchemaUpdateOptions(ImmutableList.of(SchemaUpdateOption.ALLOW_FIELD_RELAXATION))
              .setWriteDisposition(WriteDisposition.WRITE_APPEND)
              .setDestinationTable(tableId)
              .build();

      Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).build());

      queryJob = queryJob.waitFor();

      // Check for errors
      if (queryJob == null) {
        throw new Exception("Job no longer exists");
      } else if (queryJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(queryJob.getStatus().getError().toString());
      }

      // Get the results.
      TableResult results = queryJob.getQueryResults();

      // Print all pages of the results.
      results
          .iterateAll()
          .forEach(
              rows -> {
                rows.forEach(row -> System.out.println("row: " + row.toString()));
              });

      System.out.println("Successfully relaxed all columns in destination table during query job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Columns not relaxed during query job \n" + e.toString());
    }
  }
}

Python

from google.cloud import bigquery

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

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

# 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,
    schema_update_options=[bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION],
    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))

Modifier la valeur par défaut d'une colonne

Pour modifier la valeur par défaut d'une colonne, sélectionnez l'une des options suivantes :

Console

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

Accéder à BigQuery
Dans le panneau Explorer, développez votre projet et votre ensemble de données, puis sélectionnez la table.
Dans le panneau des détails, cliquez sur l'onglet Preview (Aperçu).
Cliquez sur Modifier le schéma. Vous devrez peut-être faire défiler la page pour voir ce bouton.
Sur la page Schéma actuel, localisez le champ de premier niveau que vous souhaitez modifier.
Saisissez la valeur par défaut pour ce champ.
Cliquez sur Enregistrer.

SQL

Utilisez l'instruction LDD ALTER COLUMN SET DEFAULT :

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

Accéder à BigQuery

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

ALTER TABLE mydataset.mytable
ALTER COLUMN column_name SET DEFAULT default_expression;

Cliquez sur Exécuter.

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

Supprimer une colonne

Vous pouvez supprimer une colonne d'une table existante à l'aide de l'instruction LDD ALTER TABLE DROP COLUMN.

L'instruction ne libère pas immédiatement l'espace de stockage associé à la colonne supprimée. Pour en savoir plus sur l'impact de la suppression d'une colonne sur le stockage, consultez les détails de l'instruction ALTER TABLE DROP COLUMN. Deux options sont possibles afin de récupérer immédiatement l'espace de stockage :

Écraser une table avec une requête SELECT * EXCEPT.
Exporter les données vers Cloud Storage, supprimer les colonnes indésirables, puis charger les données dans une nouvelle table avec le schéma approprié.

Modifier des schémas de table

Ajouter une colonne

Ajouter une colonne vide

Console

SQL

bq

API

Accéder

Java

Node.js

Python

Ajouter une colonne imbriquée à une colonne RECORD

Console

SQL

bq

API

Ajouter des colonnes lorsque vous écrasez ou ajoutez des données

Ajouter des colonnes dans un job de chargement ajoutant des données

bq

API

Accéder

Java

Node.js

Python

Ajouter des colonnes dans un job de requête ajoutant des résultats de requête

bq

API

Accéder

Java

Node.js

Python

Renommer une colonne

Modifier le type de données d'une colonne

Modifier le type de données d'une colonne avec une instruction LDD

Caster le type de données d'une colonne

Console

bq

API

Modifier le mode d'une colonne

Rendre une colonne NULLABLE dans une table existante

Console

SQL

bq

API

Accéder

Java

Node.js

Python

Rendre une colonne NULLABLE avec un job de chargement par ajout

Console

bq

API

Accéder

Java

Node.js

Python

Rendre toutes les colonnes NULLABLE avec un job d'ajout

Console

bq

API

Accéder

Java

Python

Modifier la valeur par défaut d'une colonne

Console

SQL

Supprimer une colonne

Ajouter une colonne imbriquée à une colonne `RECORD`

Rendre une colonne `NULLABLE` dans une table existante

Rendre une colonne `NULLABLE` avec un job de chargement par ajout

Rendre toutes les colonnes `NULLABLE` avec un job d'ajout