Copy table data and partitions with CLONE TABLE - 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: Partitioned tables, non-partitioned tables, and clustered tables.
Foreign tables: CLONE TABLE cannot copy data from foreign tables.
Cross-region: CLONE TABLE cannot copy data between MaxCompute projects in different regions.
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).
You can run the CLONE TABLE command a maximum of six times on the same non-partitioned table or the same partition of a partitioned table.

Tip: To migrate data between projects with different storage classes, use the following alternatives: - Non-partitioned tables: Use CREATE TABLE AS. - Partitioned tables: Use CREATE TABLE LIKE to create the table, then use INSERT OVERWRITE to migrate the data.

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 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;

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 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;

Expected output:

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

Examples

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       |
+------------+-------------+-------------+

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      |
+------------+-------------+-------------+------------+------------+

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   |
+------------+-------------+-------------+------------+------------+

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   |
+------------+-------------+-------------+------------+------------+