BigQuery - Alter Table

---

---

Throughout the course of SQL development, it will almost certainly become necessary to edit, in some form, the work you've completed already. This may mean updating a query or refining a view. Often, however, it means altering a SQL table to meet new requirements or to facilitate the transfer of new data.

Use Cases of ALTER Command

To alter an existing table, BigQuery provides an ALTER keyword that allows for powerful manipulations of table structure and metadata.

The syntax to alter any table within the SQL environment is "ALTER TABLE". Use cases for the ALTER command include −

• Adding a Column
• Dropping a Column
• Renaming a Table
• Add a Table Description
• Add Partition Expiration Days

Let's now take each of these cases one by one.

Adding a Column

Here is the original table schema prior to the modification.

This is the SQL statement to use to add a column −

Here is the table schema following the addition of the new column.

Dropping a Column

This is the schema for the existing table, prior to dropping sale_id.

This is the DML to drop sale_id −

Here is the resulting schema −

Renaming a Table

You can use the following command to rename a table −

Add a Table Description

Use the following query to add a table description −

You can see in the following screenshot that this statement successfully added a description to the table.

Add Partition Expiration Days

Use the following query to add partition expiration days −

Unlike SELECT statements, any SQL code beginning with ALTER will fundamentally change the structure or metadata of a given table.

Note − You should use these queries with utmost caution.