BigQuery - Copy Table

SQL tables can be copied or deleted as needed in the same way as a file on a desktop.

Copying a table can take two forms −

• Copying / recreating a table
• Cloning a table

Let's find out how cloning a table is different from copying a table.

Cloning a Table in BigQuery

Making a perfect copy of an existing table in BigQuery is known as cloning a table. This task can be accomplished through either the BigQuery Studio UI or through the SQL copy process.

In either case, it is important to keep in mind that any new table created, even if it is a cloned table, will still incur long-term storage and usage charges.

Copying a Table in BigQuery

Copying a table preserves all its current attributes including −

• All data stored
• Partitioning specs
• Clustering specs
• Metadata like descriptions
• Sensitive data protection policy tags

To copy a table in the BigQuery Studio UI, navigate to the query environment. Click on the table you'd like to copy. Select "copy."

It's important to note that this copying process isn't automatic. When you click "copy", you'll need to specify what dataset you're copying the new table to and provide a new table name.

Note − The default naming convention is for GCP to append "_copy" to the end of your original table name.

BigQuery does not support the "SQL COPY" command. Instead, developers can copy a table using several different methods.

Create or Replace Table

Often considered the default create table statement within BigQuery, CREATE OR REPLACE TABLE can double as a de-facto COPY.

CREATE OR REPLACE TABLE project.dataset.table

It's required to supply some kind of query using an AS keyword −

CREATE OR REPLACE TABLE project.dataset.table AS (
)

To perform a copy, you could simply "SELECT * from" an existing table.

In order to create a perfect clone, developers can use the "CREATE TABLE CLONE" keyword. This command creates a perfect copy of an existing table without needing to provide a query.

Between the UI and supported SQL syntax, BigQuery offers flexibility related to copying and cloning tables.