How to add a column to existing table in BigQuery?
Contents
ALTER TABLE ADD COLUMN statement
Alter statement allows us to modify the structure of the existing table. Using Alter Table Add Column statement, we can add one or more columns to the existing table in BigQuery.
Syntax
|
1 2 |
ALTER TABLE <table_name> ADD COLUMN [IF NOT EXISTS] <column_name> <data_type>[, ...] |
- table_name – It is a name of the existing table.
- IF NOT EXISTS – If the given column name is already exist, the statement has no effect.
- column_name – The column to add in the table.
- data_type – It is a data type of the new column.
Example 1: Add new column to a table
Let’s try to add the new columns in the existing table. We have a table loan_details as below in BigQuery. It maintains the customer’s loan details. The table has the following columns loan_no, customer_acc_no, loan_amt, interest_rate and approved_date.
Consider that we got the requirement to add the new column mode_of_payment in the existing table loan_details. The data type of this column is String. We can perform this task using Alter Table Add Column statement.
|
1 2 3 |
ALTER TABLE rc_fin_test_tables.loan_details add column mode_of_payment STRING; |
The alter statement has executed successfully and added the new column in the table as below.
By default, the value of the new column is NULL in BigQuery. Later on ,we can update the values in this column.
Example 2: Add multiple columns to a table
In this example, we will add the multiple new columns to the existing table loan_details. We don’t see the loan type and repayment period details in the table.
So we decided to two columns such as loan_type and repayment_period_in_yrs. The data type of those columns are String and Int respectively.
|
1 2 3 |
ALTER TABLE rc_fin_test_tables.loan_details add column loan_type string, add column repayment_period_in_yrs int; |
If we look at the Alter table statement, the keyword add column is mentioned for each column. Then the statement is executed successfully as below.
Example 3: Add nested columns in Struct data type
The data type Struct is a kind of container which holds the collection of fields with a field name and data type. Let’s add the new column with Struct type. Within that, we can define the set of columns.
To maintain the customer contact details, we are adding a new column as customer_contact which contains the customer’s mobile number, email_address and linked account list.
|
1 2 3 4 5 6 7 |
ALTER TABLE rc_fin_test_tables.loan_details ADD COLUMN customer_contact STRUCT< mobile_no int, email_address string, linked_account_list ARRAY<string> >; |
As mentioned above, the data type of the new column customer_contact is mentioned as Struct. Within that column, we are adding the set of columns using Alter statement.
Now all the new columns has null values. We tried to update those values to check the nested columns.
|
1 2 3 4 5 6 |
update rc_fin_test_tables.loan_details set mode_of_payment= 'Cheques', loan_type='Higher Studies', repayment_period_in_yrs=5, customer_contact = STRUCT(7593034406,'ajcooper@gmail.com' ,['personal','corporate']) where loan_no = 8210; |
Finally we have added the nested columns using Alter table add column statement in BigQuery.
Recommended Articles
- How to add partition to existing table in BigQuery?
- Create table as Select, Create table Copy and Create table Like in BigQuery
