Schema changes in BigQuery tables

What ALTER TABLE can change in a BigQuery table: add columns at the end, relax REQUIRED to NULLABLE, widen a few types, rename and drop columns. What it can't: add REQUIRED columns, add nested fields with SQL, narrow a type, or reorder.

BigQuery changes a schema without rewriting data, so the changes it allows are the ones that need no rewrite: add a nullable column at the end, loosen a rule, widen a type, rename or drop a column. Anything that would need existing rows to change or move, such as a new REQUIRED column, a narrower type or a different column order, is refused, and you rebuild the table with CREATE OR REPLACE TABLE ... AS SELECT. The DDL statements are free.

The examples here run on temporary tables in a script. ALTER TABLE works on them the same way as on stored tables.

Add a column

New columns are NULLABLE or REPEATED, and always go at the end. Existing rows get NULL, or an empty array for a repeated column:

CREATE TEMP TABLE orders (order_id INT64, status STRING);
INSERT INTO orders VALUES (1, 'paid'), (2, 'open');

ALTER TABLE orders
  ADD COLUMN coupon STRING,
  ADD COLUMN tags ARRAY<STRING>;

SELECT order_id, coupon, tags, ARRAY_LENGTH(tags) AS tag_count
FROM orders
ORDER BY order_id

ADD COLUMN IF NOT EXISTS skips a column that is already there. You can add several columns in one statement.

REQUIRED columns can’t be added

A REQUIRED column would need a value for every existing row, so BigQuery refuses:

CREATE TEMP TABLE orders (order_id INT64, status STRING);
INSERT INTO orders VALUES (1, 'paid');

ALTER TABLE orders ADD COLUMN coupon STRING NOT NULL

REQUIRED columns can only be created together with the table. The workaround is a nullable column that you fill with an UPDATE, or a new table.

Nested fields can’t be added with SQL

ALTER TABLE ... ADD COLUMN shipping.country STRING is a syntax error, because the statement has no way to name a field inside a STRUCT. A new field in an existing STRUCT or RECORD is added by sending the whole schema: edit the JSON schema file and run bq update, or call tables.patch. Google’s guide says the console can’t do it either. In Querylab.io, Copy schema as JSON on a table’s Schema tab copies the whole schema in BigQuery’s JSON format (table information).

bq show --schema --format=prettyjson mydataset.orders > schema.json
# add {"name": "country", "type": "STRING", "mode": "NULLABLE"} to the shipping fields
bq update mydataset.orders schema.json

In our test this added country to a struct holding city and zip, and existing rows read {"city":"Oslo","zip":"0150","country":null}. The new field is NULLABLE or REPEATED like any added column.

The same file can’t remove a nested field. Leaving zip out of the schema failed with Field shipping.zip is missing in new schema. A field inside a struct can’t be dropped or renamed in place at all. Rebuild the column: see STRUCT.

Column order

You can’t insert a column in the middle. We put a new field second in a schema file and ran bq update, and the column still ended up last (ordinal_position 11 of 11). Google’s guide says this case returns Precondition Failed. Our test returned success and appended it. Either way, the position you ask for is not what you get. To reorder columns, create a new table.

Relax REQUIRED to NULLABLE

Going from REQUIRED to NULLABLE is the only mode change BigQuery allows. It loosens the rule, so every existing row still fits:

CREATE TEMP TABLE customers (customer_id INT64 NOT NULL, email STRING NOT NULL);
INSERT INTO customers VALUES (1, '[email protected]');

ALTER TABLE customers ALTER COLUMN email DROP NOT NULL;
INSERT INTO customers VALUES (2, NULL);

SELECT customer_id, email
FROM customers
ORDER BY customer_id

Before the change the same insert fails:

CREATE TEMP TABLE customers (customer_id INT64 NOT NULL, email STRING NOT NULL);

INSERT INTO customers VALUES (2, NULL)

There is no SET NOT NULL. ALTER COLUMN order_id SET NOT NULL returns a syntax error: after SET the parser expects DATA, DEFAULT, GENERATED or OPTIONS. NULLABLE to REQUIRED and REPEATED to NULLABLE aren’t supported. Rebuild the table. DROP NOT NULL on a column that isn’t NOT NULL is an error, not a no-op.

Change a column’s type

ALTER COLUMN ... SET DATA TYPE only goes to a less restrictive type. The supported conversions are:

The last two rows are parameterized types, where a bigger length, precision or scale is a widening. NUMERIC to BIGNUMERIC(40, 20) is allowed too.

CREATE TEMP TABLE payments (payment_id INT64, amount INT64);
INSERT INTO payments VALUES (1, 40), (2, 15);

ALTER TABLE payments ALTER COLUMN amount SET DATA TYPE NUMERIC;
INSERT INTO payments VALUES (3, NUMERIC '12.34');

SELECT payment_id, amount
FROM payments
ORDER BY payment_id

Going back is refused:

CREATE TEMP TABLE payments (payment_id INT64, amount INT64);

ALTER TABLE payments ALTER COLUMN amount SET DATA TYPE NUMERIC;
ALTER TABLE payments ALTER COLUMN amount SET DATA TYPE INT64

We tried the other pairs on a test table. These all failed with the same “is assignable to the new type” error: BIGNUMERIC to NUMERIC, FLOAT64 to NUMERIC, STRING to INT64, DATE to TIMESTAMP, BOOL to INT64. STRING to STRING(3) failed with Narrowing type parameters is not compatible, and DATE to DATETIME with Field d has changed type from DATE to DATETIME.

Two cases the guide leaves unclear:

• A field inside a struct can be widened by restating the struct: SET DATA TYPE STRUCT<a NUMERIC, b STRING> changed a from INT64 to NUMERIC. The reference page also says “Modifying subfields is not supported”, which means you can’t name the subfield on its own.
• An array of a widenable type isn’t widenable. ARRAY<INT64> to ARRAY<NUMERIC> failed, even though INT64 to NUMERIC works. Fields inside an array of structs aren’t supported either, and Google’s workaround is CREATE OR REPLACE TABLE ... AS SELECT with an UNNEST over the array.

For any other change, cast and rewrite the table:

CREATE OR REPLACE TABLE mydataset.orders AS
SELECT * REPLACE (CAST(order_date AS STRING) AS order_date)
FROM mydataset.orders;

This scans the whole table, so it costs the same as reading it, and it isn’t a good choice for very large tables.

Rename a column

ALTER TABLE mydataset.orders RENAME COLUMN amount TO total;

Several renames can go in one statement and are applied in order, so a swap is possible with a temporary name:

CREATE TEMP TABLE route (origin STRING, destination STRING);
INSERT INTO route VALUES ('Oslo', 'Bergen');

ALTER TABLE route
  RENAME COLUMN origin TO temp_col,
  RENAME COLUMN destination TO origin,
  RENAME COLUMN temp_col TO destination;

SELECT origin, destination FROM route

The statement has restrictions:

• It can’t be combined with other ALTER TABLE actions:

  RENAME COLUMN together with ADD COLUMNBigQuery rejects this query

  12345

  CREATE TEMP TABLE orders (order_id INT64, amount INT64);
  
  ALTER TABLE orders
    RENAME COLUMN amount TO total,
    ADD COLUMN coupon STRING

  Error in BigQuery

  Query error: ALTER TABLE does not support combining ADD COLUMN with RENAME COLUMN at [5:3]

• It can’t rename a partitioning column, a clustering column, a key column, a nested field, or any column of a table with row access policies. On a table partitioned by d and clustered by status, both renames failed with Columns used in table partitioning or clustering cannot be renamed.

• After a rename, the table can’t be queried with legacy SQL or as a wildcard table. Renaming back to the original names lifts that.

The main trap is views. A rename does not touch views, materialized views or saved queries that use the column. A view over order_id, amount kept working until amount was renamed, then the next SELECT * FROM the_view failed with:

Unrecognized name: amount; failed to parse view 'orders_view'

The error comes from the view when it is queried, not from the ALTER. Recreate every dependent view after a rename.

Drop a column

ALTER TABLE mydataset.orders DROP COLUMN internal_note;

Dropping is a metadata change. It doesn’t free the storage, and it doesn’t make the table smaller. We dropped the only big column of a 300,000-row table (41.4 MB logical). The table still reported 41,400,000 bytes afterwards. After a DELETE of one row, which writes the table, it reported 2,399,992. Google says the space comes back the next time the table is written to or when a background optimization runs, with no guaranteed time. To free it at once, rewrite the table:

CREATE OR REPLACE TABLE mydataset.orders AS
SELECT * EXCEPT (internal_note) FROM mydataset.orders;

A dropped column comes back with time travel: a read from before the drop returns the old schema and the old values, even after a new column with the same name is added. In Querylab.io the TT picker on a table’s Preview tab shows the table as it was within the time travel window (time travel). You can’t drop a partitioning or clustering column, a key column, a nested field, or a column of a table with row access policies. A table whose columns were dropped can’t be queried with legacy SQL or as a wildcard table, as with renames.

Add columns from load and query jobs

A load job that appends to a table can add columns, and relax REQUIRED columns, through schemaUpdateOptions (ALLOW_FIELD_ADDITION, ALLOW_FIELD_RELAXATION). A query job that appends to a destination table can do the same. This is how a schema evolves during ingestion with no ALTER. New columns are NULL in the old rows. See Loading data.