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?

Across

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;

Whydah

I recommend that this be changed to the accepted answer as the updated release notes include this functionality and the above example does work. – Thaxter

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.

Malt

Instructions for adding columns with the API can be found on the page Modifying table schemas. – Quartering

Answer 3

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

Uncompromising

Copying the whole table just to add a column works but is a bit of an overkill. In the end, bigquery is a column storage so adding an extra column should be very easy. However the lack of an alter-table statement makes this solution a nice workaround. – Crooks

I like your answer but I having just one query. What to do if I want my_new_table to look like following one? my_new_table a,b,c,d,timestamp 1,2,3,my_string_1,2020-04-22 17:09:42.987987 UTC 2,3,4,my_string_2,2020-04-23 12:24:29.437447 UTC I want different data not the same one as you added in your. Please help what to do? – Misread

The same data I added, i.e. "my_string" and time stamp is just dummy, it has to be replaced with the actual values. In your case you need to do a merge statement after creating then new columns. However you also need a unique identifier as well in the old table. I hope this helps. (I hope you are familiar with bq sql syntax MERGE... target_table USING source_table ON condition WHEN MATCHED THEN ..... ) – Uncompromising

Answer 4

Method - 01 - Copies table without schema restriction

CREATE TABLE `project.dataset.tmp_dev_dataset_table` AS
SELECT
    *
FROM
    `project.dataset.table`
LIMIT
    0;

ALTER TABLE
    `project.dataset.tmp_dev_dataset_table`
ADD
    COLUMN IF NOT EXISTS new_uuid STRING;

Method - 02 - Copies table with schema restriction

CREATE TABLE IF NOT EXISTS 
`project.dataset.tmp_dev_dataset_table`
 LIKE `project.dataset.table`;


ALTER TABLE
    `project.dataset.tmp_dev_dataset_table`
ADD
    COLUMN IF NOT EXISTS new_uuid STRING;

Method - 03

CREATE TABLE `project.dataset.tmp_dev_dataset_table` AS
SELECT
    GENERATE_UUID() AS new_uuid,
    *
FROM
    `project.dataset.table`
LIMIT
    0;

Rail