Google BigQuery: how to create a new column with SQL
my_old_table
a,b,c
1,2,3
2,3,4
CREATE TABLE IF NOT EXISTS my_dataset.my_new_table
AS
SELECT a,b,c,
"my_string" AS d, current_timestamp() as timestamp
FROM my_dataset.my_old_table
my_new_table
a,b,c,d,timestamp
1,2,3,my_string,2020-04-22 17:09:42.987987 UTC
2,3,4,my_string,2020-04-22 17:09:42.987987 UTC
schema:
a: integer
b: integer
c: integer
d: string
timestamp: timestamp
I hope all is clear :) With this you can easily add new columns to an existing table, and also specify the data types. After that you can delete the old table, if necessary. :)
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.