How to use CLONE TABLE to copy data from a source table to a destination table-MaxCompute(MaxCompute)-阿里云帮助中心

CLONE TABLE copies data from a source table to a destination table. Use it to migrate data between tables in the same or different MaxCompute projects within the same region and storage class.

Limitations

Schema compatibility: The destination table's schema must be compatible with the source table's schema.
Partition copy limit:
- When copying to an existing destination table, up to 10,000 partitions can be copied at a time.
- When the destination table does not exist, there is no partition limit, and the operation is atomic.
Supported table types:
- PK/Append Delta Table:
  Existing Delta Tables cannot be overwritten. You can only use CLONE TABLE to create a new Delta Table.
- Regular tables (partitioned tables, non-partitioned tables, and clustered tables).
- Transactional tables:
  Existing Transactional tables cannot be overwritten. You can only use CLONE TABLE to create a new Transactional table.
Foreign tables: CLONE TABLE cannot copy data from foreign tables.
Row-level access control or data masking:
CLONE TABLE cannot be used on tables that have a Row Access Policy or Data Masking Policy applied.
Cross-region:
CLONE TABLE cannot copy data between MaxCompute projects in different regions or clusters (including during project O&M migrations).
Cross-storage class:
CLONE TABLE cannot copy data between projects that use different storage classes (for example, from a multi-AZ storage project to a single-AZ storage project, or vice versa).
To migrate data between projects with different storage classes, use the following alternatives:
- Non-partitioned tables: Use CREATE TABLE AS to migrate data.
- Partitioned tables: Use CREATE TABLE LIKE to create the table, then use INSERT OVERWRITE to migrate the data.
Schema Evolution:
CLONE TABLE cannot be used on tables that have undergone Schema Evolution (such as adding or dropping columns).

Billing

CLONE TABLE is a metadata-based operation and does not incur compute costs. The cloned destination table is billed based on its actual storage usage.

Syntax

CLONE TABLE [<src_project_name>.]<src_table_name> [PARTITION (<pt_spec>), ...]
TO [<dest_project_name>.]<dest_table_name> [IF EXISTS [OVERWRITE | IGNORE]];

Parameters:

Parameter	Required	Description
`src_project_name`	No	The MaxCompute project that contains the source table. Defaults to the current project. Required when the source and destination tables are in different projects.
`src_table_name`	Yes	The name of the source table.
`pt_spec`	No	The partition to copy. Format: `partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...`
`dest_project_name`	No	The MaxCompute project that contains the destination table. Defaults to the current project. Required when the source and destination tables are in different projects.
`dest_table_name`	Yes	The name of the destination table. See the behavior table below.

Destination table behavior:

Condition	Behavior
Destination table does not exist	Creates the table using `CREATE TABLE LIKE` semantics, then copies the data. For details, see Create a table.
Destination table exists + `IF EXISTS OVERWRITE`	Overwrites the data in the destination table or the specified partition.
Destination table exists + `IF EXISTS IGNORE`	Skips existing partitions without overwriting their data.

Sample data

The following examples use two sample tables. Run the commands below to create and populate them.

Partitioned table: `sale_detail`

Create a partitioned table.

-- Create a partitioned table
CREATE TABLE IF NOT EXISTS sale_detail
(
  shop_name     STRING,
  customer_id   STRING,
  total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

-- Add partitions
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china') PARTITION (sale_date='2014', region='shanghai');

-- Insert data
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
INSERT INTO sale_detail PARTITION (sale_date='2014', region='shanghai') VALUES ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);

Query the table to verify:

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail;

View results

Expected output:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

Non-partitioned table: `sale_detail_np`

Create a non-partitioned table

-- Create a non-partitioned table
CREATE TABLE IF NOT EXISTS sale_detail_np
(
  shop_name     STRING,
  customer_id   STRING,
  total_price   DOUBLE
);

-- Insert data
INSERT INTO sale_detail_np VALUES ('s4','c4',100.4);

Query the table to verify:

SELECT * FROM sale_detail_np;

View results

Expected output:

+------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s4         | c4          | 100.4       |
+------------+-------------+-------------+

Examples

Example 1:Copy a non-partitioned table to a new table

CLONE TABLE sale_detail_np TO sale_detail_np_clone; -- Creates sale_detail_np_clone and copies all data

SELECT * FROM sale_detail_np_clone;

Expected output:

+------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s4         | c4          | 100.4       |
+------------+-------------+-------------+

Example 2:Copy a specific partition to an existing table

CLONE TABLE sale_detail PARTITION (sale_date='2013', region='china')
TO sale_detail_clone IF EXISTS OVERWRITE; -- Copies only the 2013/china partition; overwrites if the partition exists

SELECT * FROM sale_detail_clone;

Expected output:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

Example 3:Copy all partitions while skipping existing ones

This example continues from the previous one. sale_detail_clone already contains the 2013/china partition. The IF EXISTS IGNORE option copies the remaining partitions without touching the existing one.

CLONE TABLE sale_detail TO sale_detail_clone IF EXISTS IGNORE; -- Adds new partitions; skips partitions that already exist

SELECT * FROM sale_detail_clone;

Expected output:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

Example 4:Copy all partitions to a new table

CLONE TABLE sale_detail TO sale_detail_clone1; -- Creates sale_detail_clone1 and copies all partitions atomically

SELECT * FROM sale_detail_clone1;

Expected output:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

Example 5:Clone a Delta Table

Clone a non-partitioned Delta Table

-- Create a non-partitioned Delta Table
CREATE TABLE IF NOT EXISTS sale_detail_delta
(
  shop_name     STRING,
  customer_id   STRING,
  total_price   DOUBLE
)
TBLPROPERTIES ("table.format.version"="2");

INSERT INTO sale_detail_delta VALUES ('s1','c1',100.1),('s2','c2',100.2);

-- Clone example
CLONE TABLE sale_detail_delta TO sale_detail_delta_clone;

Clone a partitioned Delta Table

-- Create a partitioned Delta Table
CREATE TABLE IF NOT EXISTS sale_detail_delta_pt
(
  shop_name     STRING,
  customer_id   STRING,
  total_price   DOUBLE
)
PARTITIONED BY (dd STRING, hh STRING)
TBLPROPERTIES ("table.format.version"="2");

INSERT INTO sale_detail_delta_pt PARTITION (dd='01', hh='01') VALUES ('s3','c3',100.3);

-- Clone example
CLONE TABLE sale_detail_delta_pt PARTITION (dd='01', hh='01') TO sale_detail_delta_pt_clone;

Best practices

For information about migrating data between MaxCompute projects in the same region, see Migrate data across MaxCompute projects in the same region by using CLONE TABLE.