Google BigQuery: how to create a new column with SQL

user_1949628 0
Viewed 31791

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?

google-bigquery sql
3 Answers

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;
user_12602370

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. :)

user_113043270
Related