Create table as Select, Create table Copy and Create table Like in BigQuery
Contents
Create Table As Select (CTAS) in BigQuery
The CTAS statement creates a new table by copying the schema and data from an existing table. It is a combination of CREATE TABLE statement and SELECT statement. The new table name given in the CREATE TABLE statement. The column details and source/existing table name given in the SELECT statement.
Syntax
|
1 2 3 4 5 |
CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ] table_name [OPTIONS(table_option_list)] AS SELECT <Column_1>,<Column_2>...<Column_n> FROM source_table_name; |
- OR REPLACE – optional argument. It replace the table if it is already exist. Cannot appear with IF NOT EXISTS.
- IF NOT EXISTS – optional argument. It creates the table only if it is not exist. Cannot appear with OR REPLACE.
- table_name – New table name
- OPTIONS(table_option_list) – optional argument. Table options can be specified in this.
- <Column_1>,<Column_2>.. – Either we can specify the exact column names of the source table or we can give star(*) for all the columns.
- source_table_name – Existing table name
Example
Assume that we have a table called as job_post in BigQuery. It has the records as below.
Our goal is to take the backup of this table in BigQuery. In order to copy the schema and data of this table, we are writing the Create Table As Select statement.
|
1 2 3 |
create table rc_fin_test_tables.job_post_bkup as (select * from rc_fin_test_tables.job_post); |
The name of the back up table is job_post_bkup and the source table name is job_post. Since we want to copy all the columns from the source table , we have provided the Select * statement. To copy the specific columns, we can mention the column names in the Select statement.
The CTAS statement has created the back up table. Let’s verify the records in it.
As mentioned above, the new table job_post_bkup has the same schema and records as source table job_post.
Create Table Copy in BigQuery
The Create Table Copy statement creates a new table with the same metadata and data as source table. As we mentioned earlier, CTAS statement allows to mention the specific column names whereas Create Table Copy doesn’t allow to select the column names from source table.
Also the source table can be a table , table clone or table snapshot in BigQuery. While copying the table, it inherits the table properties from source table which includes partitioning, clustering and table options. The table options can be override by using the OPTIONS clause.
Syntax
|
1 2 3 4 |
CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ] table_name COPY source_table_name ... [OPTIONS(table_option_list)] |
Example
Let’s take another back up from the source table job_post. This time , we mentioned the new table name as job_post_bkup_july02.
|
1 2 |
create table rc_fin_test_tables.job_post_bkup_july02 copy rc_fin_test_tables.job_post; |
As shown below, the back up table is created using the Create Table Copy statement in BigQuery.
The new table job_post_bkup_july02 has the same metadata and data as source table.
Create table Like in BigQuery
The Create table Like statement copies only the metadata of the source table. But we can include the as query_statement in this which copies both metadata and data of the source table.
By default, the new table inherits partitioning, clustering, and options metadata from the source table. The metadata can be customized using the OPTIONS clause.
Syntax
|
1 2 3 4 5 |
CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ] table_name LIKE [[project_name.]dataset_name.]source_table_name ... [OPTIONS(table_option_list)] |
Example
In this example, we are copying only the metadata of the table job_post to new table job_post_popular.
|
1 2 |
create table rc_fin_test_tables.job_post_popular like rc_fin_test_tables.job_post; |
Since it didn’t copy the data to the new table job_post_popular, the select statement is not returning any records.
Create table like with as select statement in BigQuery
Let’s copy the data using Create table like with as select statement. As shown below, we have added the as select * statement after the Create table like statement.
Along with this, we have mentioned the keyword or replace in the create statement to replace the existing table job_post_popular.
|
1 2 3 4 5 6 |
create or replace table rc_fin_test_tables.job_post_popular like rc_fin_test_tables.job_post as select * from rc_fin_test_tables.job_post where job_title ='Regional Sales Manager'; |
This time, we have added the where condition to select only the job post with Job title ‘Regional Sales Manager‘. So it copied only those records to the target table job_post_popular.
Recommended Articles
- How to create an external table in BigQuery?
- How to create a view in BigQuery?
- How to add a column to existing table in BigQuery?
- How to add partition to existing table in BigQuery?
