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?
BigQuery supports the Google Standard SQL dialect, but a legacy SQL dialect is also available. If you are new to BigQuery, you should use Google Standard SQL as it supports the broadest range of functionality. For example, features such as DDL and DML statements are only supported using Google Standard SQL.
To create a column with nested data, set the data type of the column to RECORD in the schema. A RECORD can be accessed as a STRUCT type in Google Standard SQL. A STRUCT is a container of ordered fields. To create a column with repeated data, set the mode of the column to REPEATED in the schema.
You can add multiple columns to an SQL table using the ALTER TABLE syntax. To do so, specify multiple columns to add after the ADD keyword. Separate each column you want to add using a comma.
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;
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With