How to Add a New Column to an Existing Table in BigQuery?

Adding a new column to an existing table in BigQuery is a common task when evolving your data schema. BigQuery allows schema updates like adding new columns without requiring a full table rewrite.

Add a column using SQL

You can use the ALTER TABLE statement to add a new column.

ALTER TABLE `my_project.my_dataset.my_table`
ADD COLUMN new_column_name STRING;

Let’s create an employee table by executing following statement.

CREATE TABLE `your_project.your_dataset.employee` (
    employee_id INT64,
    first_name STRING,
    last_name STRING,
    email STRING,
    hire_date DATE,
    department STRING
);

 

Let’s add new column ‘salary’ of type DOUBLE to the employee table by executing following ALTER statement.

ALTER TABLE `your_project.your_dataset.employee`
ADD COLUMN salary FLOAT64;

Go back to the table schema view and click on Refresh button. You can see the updated table schema.  

 

You can even add new column by clicking on ‘Edit schema’ button.

 

Click on the button ‘Add field’. Fill the field name, type, Mode and description.  

 

Click on the Save button to reflect these changes. Now go back to schema section, you can confirm that the new column ‘age’ is added.

 

 

Previous                                                    Next                                                    Home