Modifying table schemas

This document describes how to modify the schema definitions for existing BigQuery tables.

You can make most schema modifications described in this document by using SQL data definition language (DDL) statements. These statements don't incur charges.

You can modify a table schema in all the ways described on this page by exporting your table data to Cloud Storage, and then loading the data into a new table with the modified schema definition. BigQuery load and extract jobs are free, but you incur costs for storing the exported data in Cloud Storage. The following sections describe other ways of performing various types of schema modifications.

Add a column

You can add columns to an existing table's schema definition by using one of the following options:

Add a new empty column.
Overwrite a table with a load or query job.
Append data to a table with a load or query job.

Any column you add must adhere to BigQuery's rules for column names. For more information on creating schema components, see Specifying a schema.

Add an empty column

If you add new columns to an existing table schema, the columns must be NULLABLE or REPEATED. You cannot add a REQUIRED column to an existing table schema. Adding a REQUIRED column to an existing table schema in the API or bq command-line tool causes an error. However, you can create a nested REQUIRED column as part of a new RECORD field. REQUIRED columns can be added only when you create a table while loading data, or when you create an empty table with a schema definition.

To add empty columns to a table's schema definition:

Console

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the left pane, click Explorer:

If you don't see the left pane, click Expand left pane to open the pane.
In the Explorer pane, expand your project, click Datasets, and then select a dataset.
Click Overview > Tables, and then select the table.
In the details pane, click the Schema tab.
Click Edit schema. You might need to scroll to see this button.
In the Current schema page, under New fields, click Add field.
- For Name, type the column name.
- For Type, choose the data type.
- For Mode, choose NULLABLE or REPEATED.
When you are done adding columns, click Save.

SQL

Use the ALTER TABLE ADD COLUMN DDL statement:

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery

In the query editor, enter the following statement:

ALTER TABLE mydataset.mytable
ADD COLUMN new_column STRING;

Click Run.

For more information about how to run queries, see Run an interactive query.

bq

Issue the bq update command and provide a JSON schema file. If the table you're updating is in a project other than your default project, add the project ID to the dataset name in the following format: PROJECT_ID:DATASET.

bq update PROJECT_ID:DATASET.TABLE SCHEMA

Replace the following:

PROJECT_ID: your project ID.
DATASET: the name of the dataset that contains the table you're updating.
TABLE: the name of the table you're updating.
SCHEMA: the path to the JSON schema file on your local machine.

When you specify an inline schema, you cannot specify the column description, mode, and RECORD (STRUCT) type. All column modes default to NULLABLE. As a result, if you are adding a new nested column to a RECORD, you must supply a JSON schema file.

If you attempt to add columns using an inline schema definition, you must supply the entire schema definition including the new columns. Because you cannot specify column modes using an inline schema definition, the update changes any existing REPEATED column to NULLABLE, which produces the following error: BigQuery error in update operation: Provided Schema does not match Table PROJECT_ID:dataset.table. Field field has changed mode from REPEATED to NULLABLE.

The preferred method of adding columns to an existing table using the bq command-line tool is to supply a JSON schema file.

To add empty columns to a table's schema using a JSON schema file:

First, issue the bq show command with the --schema flag and write the existing table schema to a file. If the table you're updating is in a project other than your default project, add the project ID to the dataset name in the following format: PROJECT_ID:DATASET.
```
bq show \
--schema \
--format=prettyjson \
PROJECT_ID:DATASET.TABLE > SCHEMA
```
Replace the following:
- PROJECT_ID: your project ID.
- DATASET: the name of the dataset that contains the table you're updating.
- TABLE: the name of the table you're updating.
- SCHEMA: the schema definition file written to your local machine.
For example, to write the schema definition of mydataset.mytable to a file, enter the following command. mydataset.mytable is in your default project.
```
   bq show \
   --schema \
   --format=prettyjson \
   mydataset.mytable > /tmp/myschema.json
```

Open the schema file in a text editor. The schema should look like the following:

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

Add the new columns to the end of the schema definition. If you attempt to add new columns elsewhere in the array, the following error is returned: BigQuery error in update operation: Precondition Failed.

Using a JSON file, you can specify descriptions, NULLABLE or REPEATED modes, and RECORD types for new columns. For example, using the schema definition from the previous step, your new JSON array would look like the following. In this example, a new NULLABLE column is added named column4. column4 includes a 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"
    }
  ]
  
```
For more information on working with JSON schema files, see Specifying a JSON schema file.
After updating your schema file, issue the following command to update the table's schema. If the table you're updating is in a project other than your default project, add the project ID to the dataset name in the following format: PROJECT_ID:DATASET.
```
bq update PROJECT_ID:DATASET.TABLE SCHEMA
```
Replace the following:
- PROJECT_ID: your project ID.
- DATASET: the name of the dataset that contains the table you're updating.
- TABLE: the name of the table you're updating.
- SCHEMA: the schema definition file written to your local machine.
For example, enter the following command to update the schema definition of mydataset.mytable in your default project. The path to the schema file on your local machine is /tmp/myschema.json.
```
bq update mydataset.mytable /tmp/myschema.json
```

API

Call the tables.patch method and use the schema property to add empty columns to your schema definition. Because the tables.update method replaces the entire table resource, the tables.patch method is preferred.

Go

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.


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

Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Append a new SchemaField object to a copy of the Table.schema and then replace the value of the Table.schema property with the updated schema.

from google.cloud import bigquery

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

# TODO(developer): Set table_id to the ID of the table
#                  to 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.")

Add a nested column to a `RECORD` column

In addition to adding new columns to a table's schema, you can also add new nested columns to a RECORD column. The process for adding a new nested column is similar to the process for adding a new column.

Console

Adding a new nested field to an existing RECORD column is not supported by the Google Cloud console.

SQL

Adding a new nested field to an existing RECORD column by using a SQL DDL statement is not supported.

bq

Issue the bq update command and provide a JSON schema file that adds the nested field to the existing RECORD column's schema definition. If the table you're updating is in a project other than your default project, add the project ID to the dataset name in the following format: PROJECT_ID:DATASET.

bq update PROJECT_ID:DATASET.TABLE SCHEMA

Replace the following:

PROJECT_ID: your project ID.
DATASET: the name of the dataset that contains the table you're updating.
TABLE: the name of the table you're updating.
SCHEMA : the path to the JSON schema file on your local machine.

To add a nested column to a RECORD using a JSON schema file:

First, issue the bq show command with the --schema flag and write the existing table schema to a file. If the table you're updating is in a project other than your default project, add the project ID to the dataset name in the following format: PROJECT_ID:DATASET.TABLE.
```
bq show \
--schema \
--format=prettyjson \
PROJECT_ID:DATASET.TABLE > SCHEMA
```
Replace the following:
- PROJECT_ID: your project ID.
- DATASET: the name of the dataset that contains the table you're updating.
- TABLE: the name of the table you're updating.
- SCHEMA: the schema definition file written to your local machine.
For example, to write the schema definition of mydataset.mytable to a file, enter the following command. mydataset.mytable is in your default project.
```
bq show \
--schema \
--format=prettyjson \
mydataset.mytable > /tmp/myschema.json
```

Open the schema file in a text editor. The schema should look like the following. In this example, column3 is a nested repeated column. The nested columns are nested1 and nested2. The fields array lists the fields nested within 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"
  }
]

Add the new nested column to the end of the fields array. In this example, nested3 is the new nested column.

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

For more information on working with JSON schema files, see Specifying a JSON schema file.

After updating your schema file, issue the following command to update the table's schema. If the table you're updating is in a project other than your default project, add the project ID to the dataset name in the following format: PROJECT_ID:DATASET.
```
bq update PROJECT_ID:DATASET.TABLE SCHEMA
```
Replace the following:
- PROJECT_ID: your project ID.
- DATASET: the name of the dataset that contains the table you're updating.
- TABLE: the name of the table you're updating.
- SCHEMA: the path to the JSON schema file on your local machine.
For example, enter the following command to update the schema definition of mydataset.mytable in your default project. The path to the schema file on your local machine is /tmp/myschema.json.
```
bq update mydataset.mytable /tmp/myschema.json
```

API

Call the tables.patch method and use the schema property to add the nested columns to your schema definition. Because the tables.update method replaces the entire table resource, the tables.patch method is preferred.

Add columns when you overwrite or append data

You can add new columns to an existing table when you load data into it and choose to overwrite the existing table. When you overwrite an existing table, the schema of the data you're loading is used to overwrite the existing table's schema. For information on overwriting a table using a load job, see the document for your data's format:

Add columns in a load append job

You can add columns to a table when you append data to it in a load job. The new schema is determined by one of the following:

Autodetection (for CSV and JSON files)
A schema specified in a JSON schema file (for CSV and JSON files)
The self-describing source data for Avro, ORC, Parquet and Datastore export files

If you specify the schema in a JSON file, the new columns must be defined in it. If the new column definitions are missing, an error is returned when you attempt to append the data.

When you add new columns during an append operation, the values in the new columns are set to NULL for existing rows.

To add a new column when you append data to a table during a load job, use one of the following options:

bq

Use the bq load command to load your data and specify the --noreplace flag to indicate that you are appending the data to an existing table.

If the data you're appending is in CSV or newline-delimited JSON format, specify the --autodetect flag to use schema auto-detection or supply the schema in a JSON schema file. The added columns can be automatically inferred from Avro or Datastore export files.

Set the --schema_update_option flag to ALLOW_FIELD_ADDITION to indicate that the data you're appending contains new columns.

If the table you're appending is in a dataset in a project other than your default project, add the project ID to the dataset name in the following format: PROJECT_ID:DATASET.

(Optional) Supply the --location flag and set the value to your location.

Enter the load command as follows:

bq --location=LOCATION load \
--noreplace \
--autodetect \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=FORMAT \
PROJECT_ID:DATASET.TABLE \
PATH_TO_SOURCE \
SCHEMA

Replace the following:

LOCATION: the name of your location. The --location flag is optional. For example, if you are using BigQuery in the Tokyo region, set the flag's value to asia-northeast1. You can set a default value for the location using the .bigqueryrc file.
FORMAT: the format of the schema. NEWLINE_DELIMITED_JSON, CSV, AVRO, PARQUET, ORC, or DATASTORE_BACKUP.
PROJECT_ID: your project ID.
DATASET: the name of the dataset that contains the table.
TABLE: the name of the table you're appending.
PATH_TO_SOURCE: a fully-qualified Cloud Storage URI, a comma-separated list of URIs, or the path to a data file on your local machine.
SCHEMA: the path to a local JSON schema file. A schema file is required only for CSV and JSON files when --autodetect is unspecified. Avro and Datastore schemas are inferred from the source data.

Examples:

Enter the following command to append a local Avro data file, /tmp/mydata.avro, to mydataset.mytable using a load job. Because schemas can be automatically inferred from Avro data you don't need to use the --autodetect flag. mydataset is in your default project.

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

Enter the following command append a newline-delimited JSON data file in Cloud Storage to mydataset.mytable using a load job. The --autodetect flag is used to detect the new columns. mydataset is in your default project.

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

Enter the following command append a newline-delimited JSON data file in Cloud Storage to mydataset.mytable using a load job. The schema containing the new columns is specified in a local JSON schema file, /tmp/myschema.json. mydataset is in myotherproject, not your default project.

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

Call the jobs.insert method. Configure a load job and set the following properties:

Reference your data in Cloud Storage using the sourceUris property.
Specify the data format by setting the sourceFormat property.
Specify the schema in the schema property.
Specify the schema update option using the schemaUpdateOptions property.
Set the write disposition of the destination table to WRITE_APPEND using the writeDisposition property.

Go

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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

Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the

Modifying table schemas

Add a column

Add an empty column

Console

SQL

bq

API

Go

Java

Node.js

Python

Add a nested column to a RECORD column

Console

SQL

bq

API

Add columns when you overwrite or append data

Add columns in a load append job

bq

API

Go

Java

Node.js

Python

Add a nested column to a `RECORD` column