Google BigQuery: how to create a new column with SQL

Question

I would like to add an column to an already existing table without using legacy SQL.

The basic SQL syntax for this is:

ALTER TABLE table_name
ADD column_name datatype;

I formatted the query for Google BigQuery:

ALTER TABLE `projectID.datasetID.fooTable`
ADD (barColumn date);

But than the syntax is incorrect with this error:

Error: Syntax error: Expected "." or keyword SET but got identifier "ADD" at [1:63]

So how do I format the SQL properly for Google BigQuery?

Answer 1

Support for ALTER TABLE ADD COLUMN was released on 2020-10-14 per BigQuery Release Notes.

So the statement as originally proposed should now work with minimal modification:

ALTER TABLE `projectID.datasetID.fooTable`
ADD COLUMN barColumn DATE;

Answer 2

BigQuery does not support ALTER TABLE or other DDL statements, but you could consider submitting a feature request. For now, you either need to open in the table in the BigQuery UI and then add the column with the "Add New Field" button, or if you are using the API, you can use tables.update.