CODEWITHSUNDEEP
sqlgoogle-bigquery
scre_www
scre_www

Reputation: 2692

Google BigQuery: how to create a new column with SQL

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?

Upvotes: 27

Views: 84207

Answers (4)

Vijay Anand Pandian
Vijay Anand Pandian

Reputation: 1165

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;

Upvotes: 0

Jeff Klukas
Jeff Klukas

Reputation: 1357

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;

Upvotes: 45

Tycho
Tycho

Reputation: 79

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

Upvotes: 3

Elliott Brossard
Elliott Brossard

Reputation: 33705

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.

Upvotes: 9

Related Questions