Modifying Table Schemas

This document describes how to modify the schema definitions for existing BigQuery tables. BigQuery natively supports the following schema modifications:

Adding a schema to an empty table that doesn't have a schema definition
Adding columns to a schema definition
Relaxing a column's mode from REQUIRED to NULLABLE

All other schema modifications are unsupported and require manual workarounds, including:

Changing a column's name
Changing a column's data type
Changing a column's mode (aside from relaxing REQUIRED columns to NULLABLE)
Deleting a column

For information on unsupported schema changes that require workarounds, see Manually Changing Table Schemas.

Adding a schema to an empty table

If you create an empty table without a schema definition, you can add a schema definition later. You can specify the schema definition when you load data into the empty table, or you can add the schema before you load data.

You can add a schema definition to an empty table by using the BigQuery web UI, the command-line tool's bq update command, or by calling the tables.patch API method.

To add a schema definition to an empty table:

Web UI

In the navigation pane, select your empty table.
On the Table Details page, click Add New Fields.
In the New Fields section, enter the schema definition.
- You can enter schema information manually by:
  - Clicking Edit as text and entering the table schema as a JSON array:
    Note: You can view the schema of an existing table in JSON format by entering the following command: bq show --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE].
  - Using Add Field to manually input the schema:
When you are done adding columns, click Add to Table.

For more information on entering a schema definition, see Specifying a Schema.

CLI

Issue the bq update command and provide either an inline schema definition or a JSON schema file. If the empty 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]

Where:

[PROJECT_ID] is your project ID.
[DATASET] is the name of the dataset that contains the table you're updating.
[TABLE] is the name of the table you're updating.
[SCHEMA] is an inline schema definition in the format [COLUMN]:[DATA_TYPE],[COLUMN]:[DATA_TYPE] or the path to the JSON schema file on your local machine.

When you specify the schema on the command line, you cannot include a RECORD (STRUCT) type, you cannot include a column description, and you cannot specify the column's mode. All modes default to NULLABLE. To include descriptions, modes, and RECORD types, supply a JSON schema file instead.

Examples:

Enter the following command to add a schema to an empty table using an inline schema definition. This command updates a table named mytable in mydataset in your default project. The schema is specified inline as: qtr:STRING,sales:FLOAT,year:STRING.

bq update mydataset.mytable qtr:STRING,sales:FLOAT,year:STRING

Enter the following command to add a schema definition to an empty table using an JSON schema file. This command updates a table named mytable in mydataset in your default project. The path to the schema file is /tmp/myschema.json.

bq update mydataset.mytable /tmp/myschema.json

Enter the following command to add a schema definition to an empty table using an JSON schema file. This command updates a table named mytable in mydataset in myotherproject, not your default project. The path to the schema file is /tmp/myschema.json.

bq update myotherproject: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.

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 specify a schema, configure the Table.schema property.

View on GitHub

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

table_ref = client.dataset(dataset_id).table('my_table')
schema = [
    bigquery.SchemaField('full_name', 'STRING', mode='REQUIRED'),
    bigquery.SchemaField('age', 'INTEGER', mode='REQUIRED'),
]
table = bigquery.Table(table_ref, schema=schema)

table = client.update_table(table, ['schema'])  # API request

assert table.schema == schema

Adding columns to a table's schema definition

You can add columns to an existing table's schema definition:

Manually (creates an empty column)
When you use a load or query job to overwrite a table
When you append data to a table using 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.

Manually adding an empty column

You can add an empty column to an existing table by using the BigQuery web UI, the command-line tool's bq update command, or by calling the tables.patch API method.

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. If you attempt to add a REQUIRED column to an existing table schema in the CLI or API, the following error is returned: BigQuery error in update operation: Provided Schema does not match Table [PROJECT_ID]:[DATASET].[TABLE]. Cannot add required columns to an existing schema. 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.

After adding a new column to your table's schema definition, you can load data into the new column by using a:

DML statement to perform a bulk update on every row
Load job that overwrites the table
Query result that overwrites the table

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

Web UI

In the navigation pane, select your table.
On the Table Details page, click Add New Fields.
In the New Fields section:
- 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 Add to Table.

CLI

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]

Where:

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

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 will attempt to change any existing REQUIRED column to NULLABLE. This results in 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 CLI 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_FILE]
```
Where:
- [PROJECT_ID] is your project ID.
- [DATASET] is the name of the dataset that contains the table you're updating.
- [TABLE] is the name of the table you're updating.
- [SCHEMA_FILE] is 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]
```
Where:
- [PROJECT_ID] is your project ID.
- [DATASET] is the name of the dataset that contains the table you're updating.
- [TABLE] is the name of the table you're updating.
- [SCHEMA] is 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

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 .

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.

View on GitHub

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

table_ref = client.dataset(dataset_id).table(table_id)
table = client.get_table(table_ref)  # 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'])  # API request

assert len(table.schema) == len(original_schema) + 1 == len(new_schema)

Adding a nested column to a `RECORD`

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

Web UI

Adding a new nested field to an exising RECORD column is not currently supported by the BigQuery web UI.

CLI

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]

Where:

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

When you specify the schema on the command line, you cannot include a RECORD (STRUCT) type, you cannot include a column description, and you cannot specify the column's mode. All 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.

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].
```
bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE] > [SCHEMA_FILE]
```
Where:
- [PROJECT_ID] is your project ID.
- [DATASET] is the name of the dataset that contains the table you're updating.
- [TABLE] is the name of the table you're updating.
- [SCHEMA_FILE] is 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]
```
Where:
- [PROJECT_ID] is your project ID.
- [DATASET] is the name of the dataset that contains the table you're updating.
- [TABLE] is the name of the table you're updating.
- [SCHEMA] is 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.

Adding a column 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:

You can also add new columns to an existing table when you append data to it using a load or query job.

Adding a column in a load append job

To add columns to a table while appending data to it in a load job, you can use the command-line tool's bq load command, or you can call the API's jobs.insert method and configure a load job. Adding a column to an existing table during an append operation is not currently supported by the BigQuery web UI.

When you add columns using an append operation in a load job, the updated schema can be:

Automatically detected (for CSV and JSON files)
Specified in a JSON schema file (for CSV and JSON files)
Automatically inferred from the data for Avro and Cloud 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, the following error is returned when you attempt to append the data: Error while reading data, error message: parsing error in row starting at position [INT]: No such field: [FIELD].

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:

Web UI

You cannot add new columns to an existing table when you load data using the BigQuery web UI.

CLI

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 Cloud 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].

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]

Where:

[LOCATION] is the name of your location. The --location flag is optional if your data is in the US or the EU multi-region location. 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] is NEWLINE_DELIMITED_JSON, CSV, AVRO, or DATASTORE_BACKUP.
[PROJECT_ID] is your project ID.
[DATASET] is the name of the dataset that contains the table.
[TABLE] is the name of the table you're appending.
[PATH_TO_SOURCE] is a fully-qualified Cloud Storage URI, a comma-separated list of URIs, or the path to a data file on your local machine.
[SCHEMA] is 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 Cloud 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. Since added columns can be automatically inferred from Avro data you do not need to use the --autodetect flag. mydataset is in your default project and was created in the US multi-region location.

bq --location=US 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 and was created in the US multi-region location.

bq --location=US 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. mydataset was created in the US multi-region location.

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

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

API

Call the jobs.insert method. Specify your region in the location property in the jobReference section of the job resource.

Configure a load job and set the following properties:

Reference your data in Cloud Storage using the configuration.load.sourceUris property.
Specify the data format by setting the configuration.load.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 configuration.load.writeDisposition property.

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 .

View on GitHub Feedback

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

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

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

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

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

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

Adding a column in a query append job

To add columns to a table while appending query results to it, you can use the command-line tool's bq query command, or you can call the API's jobs.insert method and configure a query job. Adding a column during an append operation is not currently supported by the BigQuery web UI.

When you add columns using an append operation in a query job, the schema of the query results is used to update the schema of the destination table.

To add a new column when you append data to a table during a query job:

Web UI

You cannot add new columns to an existing table when you append query results using the BigQuery web UI.

CLI

Use the bq query command to query your data and specify the --destination_table flag to indicate which table you're appending.

To specify that you are appending query results to an existing destination table, specify the --append_table flag.

Set the --schema_update_option flag to ALLOW_FIELD_ADDITION to indicate that the query results you're appending contain new columns.

Specify the use_legacy_sql=false flag to use standard SQL syntax for the query.

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].

Supply the --location flag and set the value to your location.

bq --location=[LOCATION] query --destination_table [PROJECT_ID]:[DATASET].[TABLE] --append_table --schema_update_option  --use_legacy_sql=false '[QUERY]'

Where:

[LOCATION] is the name of your location. The --location flag is optional if your data is in the US or the EU multi-region location. 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.
[PROJECT_ID] is your project ID.
[DATASET] is the name of the dataset that contains the table you're appending.
[TABLE] is the name of the table you're appending.
[QUERY] is a query in standard SQL syntax.

Examples:

Enter the following command query mydataset.mytable in your default project and to append the query results to mydataset.mytable2 (also in your default project). mydataset was created in the US multi-region location.

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

Enter the following command query mydataset.mytable in your default project and to append the query results to mydataset.mytable2 in myotherproject. mydataset was created in the US multi-region location.

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

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

API

Call the jobs.insert method. Specify your region in the location property in the jobReference section of the job resource.

Configure a query job and set the following properties:

Specify the destination table using the configuration.query.destinationTable property.
Set the write disposition of the destination table to WRITE_APPEND using the configuration.query.writeDisposition property.
Specify the schema update option using the configuration.query.schemaUpdateOptions property.
Specify the standard SQL query using the configuration.query.query property.

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 .

View on GitHub Feedback

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

# 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 query to append the results to a destination table,
# allowing field addition
job_config = bigquery.QueryJobConfig()
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION,
]
job_config.destination = table_ref
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND

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;',
    # Location must match that of the dataset(s) referenced in the query
    # and of the destination table.
    location='US',
    job_config=job_config
)  # API request - starts the query

query_job.result()  # Waits for the query to finish
print("Query job {} complete.".format(query_job.job_id))

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

Relaxing a column's mode

Currently, 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. You can relax REQUIRED columns:

Manually
When you overwrite a table using a load or query job
When you append data to a table using a query job

Caution: Changing a REQUIRED column to NULLABLE cannot be undone.

Manually changing `REQUIRED` columns to `NULLABLE`

You can manually change a column's mode from REQUIRED to NULLABLE by using the BigQuery web UI, the command-line tool's bq update command, or by calling the tables.patch API method.

To manually change a column's mode from REQUIRED to NULLABLE:

Web UI

Expand your dataset and select your table.
On the Table Details page, click the Schema tab.
Click the down arrow to the right of your required column and choose either:
- Make NULLABLE — relaxes the individual column's mode
- All REQUIRED to NULLABLE — changes all REQUIRED columns in the schema definition to NULLABLE
In the Confirm Mode Change dialog, click OK to change the mode to NULLABLE. Note that this change cannot be undone.

CLI

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_FILE]
```
Where:
- [PROJECT_ID] is your project ID.
- [DATASET] is the name of the dataset that contains the table you're updating.
- [TABLE] is the name of the table you're updating.
- [SCHEMA_FILE] is 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"
  }
]

Change an existing column's mode from REQUIRED to NULLABLE. In this example, the mode for column1 is relaxed.

[
  {
    "mode": "NULLABLE",
    "name": "column1",
    "type": "STRING"
  },
  {
    "mode": "REQUIRED",
    "name": "column2",
    "type": "FLOAT"
  },
  {
    "mode": "REPEATED",
    "name": "column3",
    "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]
```
Where:
- [PROJECT_ID] is your project ID.
- [DATASET] is the name of the dataset that contains the table you're updating.
- [TABLE] is the name of the table you're updating.
- [SCHEMA] is 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 tables.patch and use the schema property to change a REQUIRED column to NULLABLE in your schema definition. Because the tables.update method replaces the entire table resource, the tables.patch method is preferred.

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 .

Overwrite the Table.schema property with a list of SchemaField objects with the mode property set to 'NULLABLE'

View on GitHub

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

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

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

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

Changing `REQUIRED` to `NULLABLE` in a load or query job

You can relax REQUIRED columns to NULLABLE in an existing table's schema 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:

You can also relax REQUIRED columns to NULLABLE in an existing table's schema when you append data to it using a query job.

Changing `REQUIRED` to `NULLABLE` in a load append job

To relax a column's mode while appending data to a table in a load job, you can use the command-line tool's bq load command, or you can call the API's jobs.insert method and configure a load job. Changing a column's mode during an append operation is not currently supported by the BigQuery web UI.

When you relax a column's mode using an append operation in a load job, you can:

Relax the mode for individual columns by specifying a JSON schema file (when appending data from CSV and JSON files)
Relax columns to null in your Avro schema and allow schema inference to detect the relaxed columns

To relax a column from REQUIRED to NULLABLEwhen you append data to a table during a load job:

Web UI

You cannot relax an existing column's mode when you append data to a table in a load job using the BigQuery web UI.

CLI

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 relaxed columns in a local JSON schema file or use the --autodetect flag to use schema detection to discover relaxed columns in the source data. For information on relaxing column modes using a JSON schema file, see Manually changing REQUIRED columns to NULLABLE.

Relaxed columns can be automatically inferred from Avro files. Column relaxation does not apply to Cloud Datastore export appends. The columns in tables created by loading Cloud Datastore export files are always NULLABLE.

Set the --schema_update_option flag to ALLOW_FIELD_RELAXATION to indicate that the data you're appending contains relaxed 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].

Supply the --location flag and set the value to your location.

Enter the load command as follows:

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

Where:

[LOCATION] is the name of your location. The --location flag is optional if your data is in the US or the EU multi-region location. 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] is NEWLINE_DELIMITED_JSON, CSV, or AVRO. DATASTORE_BACKUP files do not require column relaxation. The columns in tables created from Cloud Datastore export files are always NULLABLE.
[PROJECT_ID] is your project ID.
[DATASET] is the name of the dataset that contains the table.
[TABLE] is the name of the table you're appending.
[PATH_TO_SOURCE] is a fully-qualified Cloud Storage URI, a comma-separated list of URIs, or the path to a data file on your local machine.
[SCHEMA] is the path to a local JSON schema file. This option is used only for CSV and JSON files. Relaxed columns are automatically inferred from Avro files.

Examples:

Enter the following command to append a local Avro data file, /tmp/mydata.avro, to mydataset.mytable using a load job. Since added columns can be automatically inferred from Avro data you do not need to specify a schema file. mydataset is in your default project and was created in the US multi-region location.

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

Enter the following command append data from a newline-delimited JSON file in Cloud Storage to mydataset.mytable using a load job. The schema containing the relaxed columns is in a local JSON schema file — /tmp/myschema.json. mydataset is in your default project and was created in the US multi-region location..

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

Enter the following command append data in a CSV file on your local machine to mydataset.mytable using a load job. The command uses schema auto- detection to discover relaxed columns in the source data. mydataset is in myotherproject, not your default project. mydataset was created in the US multi-region location.

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

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

API

Call the jobs.insert method. Specify your region in the location property in the jobReference section of the job resource. Configure a load job and set the following properties:

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

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 .

View on GitHub Feedback

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

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

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

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

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

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

Changing `REQUIRED` to `NULLABLE` in a query append job

To relax all columns in a table while appending query results to it, you can use the command-line tool's bq query command, or you can call the API's jobs.insert method and configure a query job. Relaxing columns during an append operation is not currently supported by the BigQuery web UI.

When you relax columns using an append operation in a query job, you can relax all required fields in the destination table by setting the --schema_update_option flag to ALLOW_FIELD_RELAXATION. You cannot relax individual columns in a destination table using a query append.

To relax all columns in a destination table when you append data to it during a query job:

Web UI

You cannot relax columns in a destination table when you append query results using the BigQuery web UI.

CLI

Use the bq query command to query your data and specify the --destination_table flag to indicate which table you're appending.

To specify that you are appending query results to an existing destination table, specify the --append_table flag.

Set the --schema_update_option flag to ALLOW_FIELD_RELAXATION to indicate that all REQUIRED columns in the table you're appending should be changed to NULLABLE.

Specify the use_legacy_sql=false flag to use standard SQL syntax for the query.

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].

Supply the --location flag and set the value to your location.

bq --location=[LOCATION] query --destination_table [PROJECT_ID]:[DATASET].[TABLE] --append_table --schema_update_option --use_legacy_sql=false '[QUERY]'

Where:

[LOCATION] is the name of your location. The --location flag is optional if your data is in the US or the EU multi-region location. 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.
[PROJECT_ID] is your project ID.
[DATASET] is the name of the dataset that contains the table you're appending.
[TABLE] is the name of the table you're appending.
[QUERY] is a query in standard SQL syntax.

Examples:

Enter the following command query mydataset.mytable in your default project and to append the query results to mydataset.mytable2 (also in your default project). The command changes all REQUIRED columns in the destination table to NULLABLE. mydataset was created in the US multi-region location.

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

Enter the following command query mydataset.mytable in your default project and to append the query results to mydataset.mytable2 in myotherproject. The command changes all REQUIRED columns in the destination table to NULLABLE. mydataset was created in the US multi-region location.

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

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

API

Call the jobs.insert method. Specify your region in the location property in the jobReference section of the job resource.

Configure a query job and set the following properties:

Specify the destination table using the configuration.query.destinationTable property.
Set the write disposition of the destination table to WRITE_APPEND using the configuration.query.writeDisposition property.
Specify the schema update option using the configuration.query.schemaUpdateOptions property.
Specify the standard SQL query using the configuration.query.query property.

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 .

View on GitHub Feedback

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

# 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 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()
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION,
]
job_config.destination = table_ref
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND

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;',
    # Location must match that of the dataset(s) referenced in the query
    # and of the destination table.
    location='US',
    job_config=job_config
)  # API request - starts the query

query_job.result()  # Waits for the query to finish
print("Query job {} complete.".format(query_job.job_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))

Adding a schema to an empty table

Web UI

CLI

API

Python

Adding columns to a table's schema definition

Manually adding an empty column

Web UI

CLI

API

Python

Adding a nested column to a RECORD

Web UI

CLI

API

Adding a column when you overwrite or append data

Adding a column in a load append job

Web UI

CLI

API

Python

Adding a column in a query append job

Web UI

CLI

API

Python

Relaxing a column's mode

Manually changing REQUIRED columns to NULLABLE

Web UI

CLI

API

Python

Changing REQUIRED to NULLABLE in a load or query job

Changing REQUIRED to NULLABLE in a load append job

Web UI

CLI

API

Python

Changing REQUIRED to NULLABLE in a query append job

Web UI

CLI

API

Python

Send feedback about...

Adding a nested column to a `RECORD`

Manually changing `REQUIRED` columns to `NULLABLE`

Changing `REQUIRED` to `NULLABLE` in a load or query job

Changing `REQUIRED` to `NULLABLE` in a load append job

Changing `REQUIRED` to `NULLABLE` in a query append job