All Products
Search

This Product

    MaxCompute:CLONE TABLE

    Document Center

    MaxCompute:CLONE TABLE

    Last Updated:Jan 30, 2026

    The CLONE TABLE statement supports efficiently copying source table data to a target table, making it suitable for table data migration scenarios. This topic provides examples on how to use the CLONE TABLE statement.

    Limits

    • Schema Compatibility: The schema of the destination table must be compatible with that of the source table.

    • Table Types: Supports partitioned tables, non-partitioned tables, and clustered tables.

    • Partition Limit:

      • If the destination table exists: Maximum 10,000 partitions per operation.

      • If the destination table does not exist: No partition limit (atomic operation).

    • Cross-Region/Cluster: Data replication between MaxCompute projects across different regions or clusters is not supported.

    • External Tables: Not supported.

    • Schema Evolution: Not supported for tables that have undergone schema evolution (such as adding or dropping columns).

    Syntax

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

    Parameter

    Required

    Description

    src_project_name

    No

    Name of the source MaxCompute project. Defaults to the current project if unspecified. Required if source and destination tables are in different projects.

    src_table_name

    Yes

    Name of the source table.

    pt_spec

    No

    Partition specification of the source table. Format: (partition_col1 = value1, partition_col2 = value2, ...).

    dest_project_name

    No

    Name of the destination MaxCompute project. Defaults to the current project if unspecified. Required if source and destination tables are in different projects.

    dest_table_name

    Yes

    Name of the destination table.

    • If not exists: Creates the table using CREATE TABLE LIKE semantics.

    • If exists + OVERWRITE: Overwrites the table or specified partitions.

    • If exists + IGNORE: Skips existing partitions and keeps their data.

    Sample data

    Sample source data is provided for you to better understand the examples in this topic. The following statements show how to create a partitioned table named sale_detail and a non-partitioned table named sale_detail_np and insert data into the tables.

    • sale_detail table

      -- Create a partitioned table named 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);

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

-- Insert data into the sale_detail table.
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 data in the sale_detail table. Sample statement:

      -- Enable a full table scan only for the current session. Execute the SELECT statement to view data in the sale_detail table.
SET odps.sql.allow.fullscan=true; 
SELECT * FROM sale_detail;

      The following result is returned:

      -- The following result is returned: 
+------------+-------------+-------------+------------+------------+
| 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   |
+------------+-------------+-------------+------------+------------+

    • sale_detail_np table

      -- Create a non-partitioned table named sale_detail_np. 
CREATE TABLE IF NOT EXISTS sale_detail_np
(
shop_name     string,
customer_id   string,
total_price   double
);

-- Insert data into the sale_detail_np table. 
INSERT INTO sale_detail_np VALUES ('s4','c4',100.4);

      Query data in the sale_detail_np table. Sample statement:

      SELECT * FROM sale_detail_np;

      The following result is returned:

      -- The following result is returned: 
+------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s4         | c4          | 100.4       |
+------------+-------------+-------------+

    Examples

    This section provides examples on how to use the CLONE TABLE statement based on the sample data.

    • Example 1: Clone a non-partitioned table. Clone all data from sale_detail_np to sale_detail_np_clone.

      -- Clone full data from the sale_detail_np table to the sale_detail_np_clone table. 
CLONE TABLE sale_detail_np TO sale_detail_np_clone;
-- Query data in the sale_detail_np_clone table to verify the accuracy of the data. 
SELECT * FROM sale_detail_np_clone;

      The following result is returned:

      -- The following result is returned: 
+------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s4         | c4          | 100.4       |
+------------+-------------+-------------+

    • Example 2: Clone a specific partition (Overwrite). Clone partition sale_date='2013', region='china' from sale_detail to sale_detail_clone, overwriting if it exists.

      -- Clone data from a specified partition of the sale_detail table to the sale_detail_clone table. 
CLONE TABLE sale_detail PARTITION (sale_date='2013', region='china') TO sale_detail_clone IF EXISTS OVERWRITE;
-- Query data in the sale_detail_clone table to verify the accuracy of the data. 
SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail_clone;

      The following result is returned:

      -- The following result is returned: 
+------------+-------------+-------------+------------+------------+
| 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 all partitions (Ignore existing). Clone all data from sale_detail to sale_detail_clone, skipping partitions that already exist in the destination.

      -- Clone full data from the sale_detail table to the sale_detail_clone table. 
CLONE TABLE sale_detail TO sale_detail_clone IF EXISTS IGNORE;
-- Query data in the sale_detail_clone table to verify the accuracy of the data. 
-- Enable a full table scan only for the current session. Execute the SELECT statement to view data in the sale_detail table. 
SET odps.sql.allow.fullscan=true; 
SELECT * FROM sale_detail_clone;

      The following result is returned:

      -- The following result is returned: 
+------------+-------------+-------------+------------+------------+
| 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 to a new table. Clone all data from sale_detail to a new table sale_detail_clone1.

      -- Clone full data from the sale_detail table to the sale_detail_clone1 table. 
CLONE TABLE sale_detail TO sale_detail_clone1;
-- Query data in the sale_detail_clone1 table to verify the accuracy of the data. 
SET odps.sql.allow.fullscan=true; 
SELECT * FROM sale_detail_clone1;

      The following result is returned:

      -- The following result is returned: 
+------------+-------------+-------------+------------+------------+
| 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.

      • Non-partitioned Delta Table:

        CLONE TABLE mf_dt TO new_table;

      • Partitioned Delta Table:

        CLONE TABLE mf_dt2 PARTITION (dd='01', hh='01') TO new_table;

    Best practices

    For more information about how to migrate data across MaxCompute projects in the same region, see Use CLONE TABLE to migrate data between MaxCompute projects in the same region.