BigQuery allows you to analyze large datasets using SQL queries easily. BigQuery tables can be created and modified as needed to evolve with your analysis requirements. This blog post provides an overview of BigQuery ALTER TABLE commands, which allow you to modify existing table schemas without having to create a new table. We’ll cover adding and removing columns, changing a column’s data type, adding table partitions, and more. Read on to learn how to update your BigQuery table schemas with ALTER TABLE.
Understanding Google BigQuery Alter Table Command
Your database requirements will also change as the times change and your needs change. Occasionally, you may have to alter an existing table’s definition. In some cases, you may need to rename a column, add a new column, or change the column’s datatype column. You can make these changes using DDL statements.
A BigQuery ALTER TABLE is a DDL statement. The following Google BigQuery ALTER TABLE commands are:
Hevo Data with its strong integration with 150+ data sources (including 40+ free sources) allows you to not only export data from your desired data sources & load it to the destination of your choice such as Google BigQuery, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools.
Start for free now!
Get Started with Hevo for Free
1) ALTER TABLE SET OPTIONS Statement
Comma-separated lists can be used to include multiple options. You can set the options for a table using the ALTER TABLE SET OPTIONS statement. Among the choices you can set are a label and an expiration date for each table.
A) Syntax
ALTER TABLE [IF EXISTS] table_name
SET OPTIONS(table_set_options_list)
IF EXISTS checks if the table exists. If no such table exists, the statement doesn’t have any effect. There are two parts to this query: table_name and table_set_options_list. Table_name specifies the name and table_set_options_list specifies the options to set.
The following format can be used to specify a table option list:
NAME=VALUE, …
2) ALTER TABLE ADD COLUMN Statement
You may have to add an entirely new column to your database in certain situations. For example, your product manager may decide that all users record the last time logged in. You can accomplish this using the BigQuery ALTER TABLE ADD COLUMN command. Using this command, one or more columns can be added to an existing table.
A) Syntax
ALTER TABLE table_name
ADD COLUMN [IF NOT EXISTS] column_name column_schema [, ...]
Adding a new column to an existing table requires the column_name, the name of the new column, and column_schema, which is its schema.
It is important to note that this statement cannot create a partition, clustered, or nested columns inside existing RECORD fields. The same is true for REQUIRED columns in existing schemas. You can, however, create nested REQUIRED columns as part of a new RECORD field. When the IF NOT EXISTS clause is used, the statement returns an error if that column already exists in the table. However, if that column already exists, no error is returned, and no action is taken.
B) Example
In the following example, the following columns are added to the table all_users_table:
- Column Name of type STRING.
- Column Location of type GEOGRAPHY.
- Column Salary of type NUMERIC with REPEATED mode.
- Column DOB of type DATE with a description.
Code Snippet
ALTER TABLE mydataset.all_users_table
ADD COLUMN Name STRING,
ADD COLUMN IF NOT EXISTS Location GEOGRAPHY,
ADD COLUMN Salary ARRAY<NUMERIC>,
ADD COLUMN DOB DATE OPTIONS(description="my description")
This statement fails if any of the columns named Name, Salary, or DOB exist. Due to the IF NOT EXISTS clause.
3) ALTER TABLE RENAME TO Statement
A table can be renamed using this statement. However, you should note that table snapshots cannot be changed by using the BigQuery ALTER TABLE RENAME statement. In addition, you should note that renaming a table will delete all tags associated with it or its columns in Data Catalog.
A) Syntax
ALTER TABLE [IF EXISTS] table_name
RENAME TO new_table_name
The name of the new table is new_table_name. It cannot be the same name as an existing table. In addition, if you rename a table and change its policies or row-level access policies, the changes may not take effect. You will need to wait for BigQuery to indicate that streaming is not in use before renaming a table that is presently streaming data into it.
B) Example
The following example renames the table mydataset.all_users_table to mydataset.new_allusers_table:
ALTER TABLE mydataset.all_users_table
RENAME TO new_allusers_table
Limitations of renaming tables
- If you want to rename a table that is currently streaming data, you must first pause the streaming and allow BigQuery to notify that streaming is no longer in use.
- A table can normally be renamed within 72 hours after the latest streaming operation, though this may take longer.
- Table ACLs and row access restrictions are not always retained when a table is renamed.
- You cannot rename a table while still running a DML statement on it.
- Renaming a table removes all Data Catalogue tags from the table.
- You cannot rename external tables.
4) ALTER TABLE DROP COLUMN statement
This statement drops one or more columns from an existing table schema. The dropped column doesn’t automatically free up the associated storage when the statement runs. Columns are stored in the background for seven days from the day they are dropped. You can immediately regain space by deleting the column from a table schema.
A) Syntax
ALTER TABLE table_name
DROP COLUMN [IF EXISTS] column_name [, ...]
Columns can only be dropped from an already existing table and schema. It is impossible to drop partitioned, clustered, or nested columns within existing RECORD columns with the ALTER TABLE DROP COLUMN statement. In the absence of the IF EXISTS clause, the statement fails if no column with the specified name exists in the table.
B) Example
We will drop the following columns from a table called all_users_table in this example:
- Column Name
- Column Location
ALTER TABLE mydataset.all_users_table
DROP COLUMN Name,
DROP COLUMN IF EXISTS Location
The statement fails if the column name does not exist. The statement succeeds even if Location doesn’t exist since the IF EXISTS clause applies.
5) ALTER COLUMN SET OPTIONS Statement
You can set options on columns in a table with this statement, including the column description.
A) Syntax
ALTER TABLE [IF EXISTS] table_name
ALTER COLUMN [IF EXISTS] column_name SET OPTIONS(column_set_options_list)
When ALTER COLUMN [IF EXISTS] is used, it implies no effect if the specified column does not exist. A column_name refers to the top-level column you want to make changes to. However, you cannot modify nested columns in a STRUCT. In column_set_options_list, you specify the options you want to set for the column.
The column_set_options_list must be specified in the following format:
NAME=VALUE, …
VALUE and NAME must match any of the following combinations:
| NAME | VALUE | Details |
| description | STRING | Example: description=”a table that expires in 2025″ |
Value contains only literals, query parameters, and scalar functions. NAME is removed if the constant expression evaluates to null.
It is important to note that the constant expression cannot refer to a table, subqueries, SQL statements (SELECT, CREATE, UPDATE), user-defined functions, aggregate functions, analytic functions, or scalar functions such as ARRAY_TO_STRING, RAND, and SESSION_USER.
If the column already had a value, setting VALUE will replace it. By selecting the VALUE to NULL, the column will be cleared.
B) Example
In the example below, a new description is added to a column called price:
ALTER TABLE mydataset.all_users_table
ALTER COLUMN price
SET OPTIONS (
description="Price per unit"
)
6) ALTER COLUMN DROP NOT NULL Statement
Using this statement, a NOT NULL constraint is removed from a column in a table in BigQuery. The NOT NULL constraint is regarded as the column constraint. An error is returned if a column does not have the NOT NULL constraint.
A) Syntax
ALTER TABLE [IF EXISTS] table_name
ALTER COLUMN [IF EXISTS] column DROP NOT NULL
B) Example
The following example removes the NOT NULL constraint from a column called user_column:
ALTER TABLE mydataset.all_users_table
ALTER COLUMN user_column
DROP NOT NULL
7) ALTER COLUMN SET DATA TYPE Statement
You can modify the data type in a table with the ALTER COLUMN SET DATA TYPE statement in BigQuery. For example, an INT64 data type can be changed into a FLOAT64 type, but not the other way around.
A) Syntax
ALTER TABLE [IF EXISTS] table_name
ALTER COLUMN [IF EXISTS] column_name SET DATA TYPE data_type
B) Example
The following example changes the data type of column d1 from an INT64 to FLOAT64:
CREATE TABLE dataset.table(d1 INT64);
ALTER TABLE dataset.all_users_table ALTER COLUMN c1 SET DATA TYPE FLOAT64;
8) ALTER VIEW SET OPTIONS Statement
This statement lets you set the options for a view. You can customize the options in the options list, such as the label and expiration date. A comma-separated list is an excellent way to include multiple options.
A) Syntax
ALTER VIEW [IF EXISTS] view_name
SET OPTIONS(view_set_options_list)
The format of the view option list is as follows:
NAME=VALUE, …
There are only literals, query parameters, and scalar functions in VALUE. In cases where NAME evaluates to null, the constant expression is ignored.
It should be noted that the constant expression cannot contain references to tables, subqueries, or SQL commands such as SELECT, CREATE, and UPDATE, user-defined functions, aggregate functions, and analytic functions such as REGEXP_REPLACE, GENERATE_ARRAY, REPEAT, etc.
If the view already had a value for that option, setting the VALUE will replace it. The view’s value for the option is cleared when the VALUE is set to NULL.
B) Example
In the following example, we set the views expiration time to seven days, and we also set the description:
ALTER VIEW mydataset.users_view
SET OPTIONS (
expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
description="View that expires seven days from now."
)
9) ALTER MATERIALIZED VIEW SET OPTIONS Statement
This statement allows you to set the options on a materialized view.
A) Syntax
ALTER MATERIALIZED VIEW [IF EXISTS] materialized_view_name
SET OPTIONS(materialized_view_set_options_list)
A materialized_view’s_set_options_list lets you set options such as whether to enable refresh or the refresh interval, a label, and expiration date. Multiple options can be included by using commas at the beginning and end of the list.
The following is the format for specifying a materialized view option list:
NAME=VALUE, …
If the materialized view had an existing value, changing VALUE replaces that value. When set to NULL, the materialized view’s value is cleared.
B) Example
The following example enables refresh and sets the refresh interval to 10 minutes on a materialized view:
ALTER MATERIALIZED VIEW mydataset.users_mv
SET OPTIONS (
enable_refresh=true,
refresh_interval_minutes=10
)
10) ALTER TABLE RENAME COLUMN
To change a column’s name on a table, use the ALTER TABLE RENAME COLUMN DDL command. The following query is an example to rename the column old_name to new_name on mytable:
ALTER TABLE mydataset.mytable
RENAME COLUMN old_name TO new_name;
For further information on ALTER TABLE RENAME COLUMN commands, check DDL details.
11) ALTER TABLE DROP COLUMN
The ALTER TABLE DROP COLUMN DDL statement allows you to remove a column from an existing table.
The statement does not immediately free up the storage space associated with the discarded column. Learn more about how dropping a column affects storage. There are two ways to immediately regain storage:
- Use the SELECT * EXCEPT query to overwrite a table.
- Transfer the data to Cloud Storage, remove any unwanted columns, and then import it into a new table with the suitable schema.
12) ALTER COLUMN SET DEFAULT
To modify a column’s default value, run the following command in Google Cloud Console’s Query Editor:
ALTER TABLE mydataset.mytable
ALTER COLUMN column_name SET DEFAULT default_expression;
You can also use BigQuery Alter Table to change column type, to read more about using BigQuery to change column type, click here.
Conclusion
In this article, you have learned about the types of BigQuery Alter Table Commands. This article also provided information on Google BigQuery, its key features, and Google BigQuery Alter Table Commands.
Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.
Visit our Website to Explore Hevo
Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing Hevo price, which will assist you in selecting the best plan for your requirements.
Share your experience of understanding Google BigQuery Alter Table Commands in the comment section below! We would love to hear your thoughts.
Samuel specializes in freelance writing within the data industry, adeptly crafting informative and engaging content centered on data science by merging his problem-solving skills.