Copy table data with CLONE TABLE in MaxCompute-MaxCompute(MaxCompute)-阿里云帮助中心

Limits

Schema compatibility: The destination table must be compatible with the source table schema.
Partition limits:
- If the destination table exists, a maximum of 10,000 partitions can be copied per operation.
- If the destination table does not exist, there is no partition limit and the operation is atomic.
Supported table types
- PK/Append Delta Table
  
  You cannot overwrite an existing Delta Table. You can only create a new Delta Table by using CLONE TABLE.
- Standard tables (partitioned, non-partitioned, and clustered)
- Transactional tables
  
  You cannot overwrite an existing Transactional table. You can only create a new Transactional table by using CLONE TABLE.
External tables: CLONE TABLE does not support external tables.
Row-level access or data masking

CLONE TABLE does not support tables that have a Row Access Policy or Data Masking Policy applied.
Cross-region: CLONE TABLE does not support copying data between MaxCompute projects in different regions or clusters (during O&M migration).
Cross-storage type

CLONE TABLE does not support copying data between projects that use different storage types, such as between multi-AZ and single-AZ storage projects.
- Recommended alternatives for copying tables between projects with different storage types:
  - For non-partitioned tables, use CREATE TABLE AS to migrate data.
  - For partitioned tables, create the table with CREATE TABLE LIKE, then run INSERT OVERWRITE to migrate data.
Tables that have undergone Schema Evolution (such as adding or dropping columns) do not support CLONE operations.

Billing

CLONE TABLE is a metadata-only operation and incurs no 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 name of the MaxCompute project that the source table belongs to. Default: the current project. Required when the source and destination tables are in different projects.
src_table_name	Yes	The source table name.
pt_spec	No	The partition specification of the source table. Format: `(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)`. partition_col is the partition column and partition_col_value is the partition value.
dest_project_name	No	The name of the MaxCompute project that the destination table belongs to. Default: the current project. Required when the source and destination tables are in different projects.
dest_table_name	Yes	The destination table name. If the destination table does not exist, `CLONE TABLE` creates it using `CREATE TABLE LIKE` semantics. `CREATE TABLE LIKE` Create and drop tables. If the destination table exists and `IF EXISTS OVERWRITE` is specified, `CLONE TABLE` overwrites the data in the destination table or specified partitions. If the destination table exists and `IF EXISTS IGNORE` is specified, `CLONE TABLE` skips existing partitions without overwriting their data.

Sample data

The following examples use two tables: a partitioned table sale_detail and a non-partitioned table sale_detail_np. Create them and add data as follows:

Partitioned table sale_detail

Create the partitioned table

-- 创建一张分区表sale_detail。
CREATE TABLE IF NOT EXISTS sale_detail
(
shop_name     string,
customer_id   string,
total_price   double
)
PARTITIONED BY (sale_date string, region string);

-- 向源表增加分区。
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china') PARTITION (sale_date='2014', region='shanghai');

-- 向源表追加数据。
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 data in the partitioned table sale_detail:

-- 开启全表扫描，仅此Session有效。执行select语句查看表sale_detail中的数据。
SET odps.sql.allow.fullscan=true; 
SELECT * FROM sale_detail;

Show 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 the non-partitioned table

-- 创建一张非分区表sale_detail_np。
CREATE TABLE IF NOT EXISTS sale_detail_np
(
shop_name     string,
customer_id   string,
total_price   double
);

-- 向源表追加数据。
INSERT INTO sale_detail_np VALUES ('s4','c4',100.4);

Query the data in the non-partitioned table sale_detail_np:

SELECT * FROM sale_detail_np;

Show expected output

-- 返回结果如下：
+------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s4         | c4          | 100.4       |
+------------+-------------+-------------+

Examples

The following examples use the sample data above and demonstrate CLONE TABLE in different scenarios:

Example 1: Clone a non-partitioned table

Clone all data from the non-partitioned table sale_detail_np to sale_detail_np_clone.

-- 复制表数据。
CLONE TABLE sale_detail_np TO sale_detail_np_clone;
-- 查看复制后目标表sale_detail_np_clone的信息，验证数据准确性。
SELECT * FROM sale_detail_np_clone;

Show expected output

-- 返回结果如下：
+------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s4         | c4          | 100.4       |
+------------+-------------+-------------+

Example 2: Clone specific partitions to a destination table

Clone data from a specific partition of the partitioned table sale_detail to sale_detail_clone.

-- 复制表数据
CLONE TABLE sale_detail PARTITION (sale_date='2013', region='china') TO sale_detail_clone IF EXISTS OVERWRITE;

-- 开启全表扫描，执行select语句查看复制后目标表sale_detail_clone的信息，验证数据准确性。
SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail_clone;

Show 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: Clone a partitioned table and skip existing partitions

Clone all data from the partitioned table sale_detail to sale_detail_clone (created in Example 2) and skip partitions that already exist in the destination table.

-- 复制表数据。
CLONE TABLE sale_detail TO sale_detail_clone IF EXISTS IGNORE;

-- 查看复制后目标表sale_detail_clone的信息，验证数据准确性。
-- 开启全表扫描，仅此Session有效。执行select语句查看表sale_detail_clone中的数据。
SET odps.sql.allow.fullscan=true; 
SELECT * FROM sale_detail_clone;

Show 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: Clone all partitions to a new table

Clone all data from the partitioned table sale_detail to sale_detail_clone1.

-- 复制表数据。
CLONE TABLE sale_detail TO sale_detail_clone1;

-- 开启全表扫描，执行select语句查看复制后目标表sale_detail_clone1的信息，验证数据准确性。
SET odps.sql.allow.fullscan=true; 
SELECT * FROM sale_detail_clone1;

Show 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 Delta Tables

Clone a non-partitioned Delta Table

-- 新建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 TABLE sale_detail_delta TO sale_detail_delta_clone;

Clone a partitioned Delta Table

-- 新建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 TABLE sale_detail_delta_pt PARTITION (dd='01', hh='01') TO sale_detail_delta_pt_clone;

Best practices

To migrate data between MaxCompute projects in the same region, Migrate data across MaxCompute projects in the same region by using CLONE TABLE.