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:
In the Google Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
CREATE TABLE myproject.myDataset_backup.myTableClone CLONE myproject.myDataset.myTable;
Click
Run.
For more information about how to run queries, see Running interactive queries.
bq
Use a bq cp command with
the --clone
flag:
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.