Stay organized with collections Save and categorize content based on your preferences.

Create table clones

This document describes how to copy a table to a table clone by using a CREATE TABLE CLONE SQL statement, a bq cp command, or a jobs.insert API call. This document is intended for users who are familiar with table clones.

Permissions and roles

This section describes the Identity and Access Management (IAM) permissions that you need to create a table clone, and the predefined IAM roles that grant those permissions.

Permissions

To create a table clone, you need the following permissions:

Permission Resource
All of the following:

bigquery.tables.get
bigquery.tables.getData
 The table that you want to make a clone of.
bigquery.tables.create The dataset that contains the table clone.

Roles

The predefined BigQuery roles that provide the required permissions are as follows:

Role Resource
Any of the following:

bigquery.dataViewer
bigquery.dataEditor
bigquery.dataOwner
bigquery.admin 		The table that you want to make a clone of.
Any of the following:

bigquery.dataEditor
bigquery.dataOwner
bigquery.admin 		The dataset that contains the new table clone.

Limitations

For information about table clone limitations, see table clones limitations.

In addition, table clone creation is subject to the following limitations, which apply to all table copy jobs:

  • When you create a table clone, its name must adhere to the same naming rules as when you create a table.
  • Table clone creation is subject to BigQuery limits on copy jobs.

  • The table clone dataset must be in the same region, and under the same organization, as the dataset that contains the table you are making a clone of. For example, you cannot create a table clone in a US-based dataset of a table that is located in an EU-based dataset.

  • The time that BigQuery takes to create table clones might vary significantly across different runs because the underlying storage is managed dynamically.

Create a table clone

To create a table clone, specify the table you want to clone and a unique name for the table clone.

For example, in the myProject project, create a table clone, myTableClone, of the myDataset.myTable table, and place the table clone in the myDataset_backup dataset as follows:

SQL

Use a CREATE TABLE CLONE statement:

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the query editor, enter the following statement:

    CREATE TABLE
myproject.myDataset_backup.myTableClone
CLONE myproject.myDataset.myTable;

  3. Click Run.

For more information about how to run queries, see Running interactive queries.

bq

Use a bq cp command with the --clone flag:

Go to Cloud Shell

The following example clones a table into a dataset in the same project:

bq cp --clone -n myDataset.myTable myDataset_backup.myTableClone

To clone a table into a dataset in another project, specify the project name:

bq cp --clone -n project1:myDataset.myTable project2:myDataset_backup.myTableClone

API

Call the jobs.insert method with the operationType field set to CLONE:

Parameter Value
projectId myProject
Request body 
{
  "configuration": {
    "copy": {
      "sourceTables": [
        {
          "projectId": "myProject",
          "datasetId": "myDataset",
          "tableId": "myTable"
        }
      ],
      "destinationTable": {
        "projectId": "myProject",
        "datasetId": "myDataset_backup",
        "tableId": "myTableClone"
      },
      "operationType": "CLONE",
      "writeDisposition": "WRITE_EMPTY",
    }
  }
}

Access control

When you create a table clone, access to the table clone is set as follows:

  • Row-level access policies are copied from the base table to the table clone.
  • Column-level access policies are copied from the base table to the table clone.

  • Table-level access is determined as follows:

    • If the table clone overwrites an existing table, then the table-level access for the existing table is maintained.
    • If the table clone is a new resource, then the table-level access for the table clone is determined by the access policies of the dataset in which the table clone is created.

What's next

  • After you create a table clone, you can use it like you use standard tables. For more information, see Manage tables.