All Products
Search

This Product

    MaxCompute:CLONE TABLE

    Document Center

    MaxCompute:CLONE TABLE

    Last Updated:Dec 09, 2025

    The CLONE TABLE statement efficiently copies data from a source table to a destination table. This feature is useful for data migration scenarios. This topic provides examples of how to use the CLONE TABLE command.

    Limits

    • Destination table limits:

      • The schema of the destination table must be compatible with the schema of the source table.

      • If the destination table exists, you can copy data from a maximum of 10,000 partitions at a time.

      • If the destination table does not exist, there is no limit on the number of partitions. This ensures atomicity.

    • Foreign table limits: You cannot use the CLONE TABLE command to copy data from foreign tables.

    • You can use the CLONE TABLE command to copy data for partitioned tables, non-partitioned tables, and clustered tables.

    • Cross-region limits: You cannot use the CLONE TABLE command to copy data between MaxCompute projects in different regions.

    • Cross-storage class limits

      You cannot use the CLONE TABLE command to copy data between projects that use different storage classes. For example, you cannot use the CLONE TABLE command to copy data from a table in a project that uses multi-AZ storage to a project that uses single-AZ storage, or vice versa.

    • Recommended method for copying tables between projects with different storage classes:

      • For non-partitioned tables, you can migrate data using the CREATE TABLE AS statement.

      • For partitioned tables, you can first create a table using the CREATE TABLE LIKE statement and then migrate data by running the INSERT OVERWRITE statement.

    Command format

    clone table <[<src_project_name>.]<src_table_name>> [partition(<pt_spec>), ...]
 to <[<dest_project_name>.]<dest_table_name>> [if exists [overwrite | ignore]] ;

    • src_project_name: Optional. The name of the MaxCompute project that contains the source table. If you do not specify this parameter, the current project is used. You must specify this parameter if the source table and destination table are in different MaxCompute projects.

    • src_table_name: Required. The name of the source table.

    • pt_spec: Optional. The partition of the source table. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col is the partition field, and partition_col_value is the partition value.

    • dest_project_name: Optional. The name of the MaxCompute project that contains the destination table. If you do not specify this parameter, the current project is used. You must specify this parameter if the destination table and source table are in different MaxCompute projects.

    • dest_table_name: Required. The name of the destination table.

      • If the destination table does not exist, the clone table command creates the table using create table like semantics. For more information about create table like, see Create a table.

      • If the destination table exists and you specify if exists overwrite, the CLONE TABLE command overwrites the data in the destination table or the specified partition.

      • If the destination table exists and you specify if exists ignore, the CLONE TABLE command skips existing partitions and does not overwrite their data.

    Sample data

    To help you understand the examples, this topic provides sample data. You can run the following commands to create a partitioned table named sale_detail and a non-partitioned table named sale_detail_np, and then insert data into them.

    • Partitioned table sale_detail

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

      To query the data in the partitioned table sale_detail, run the following command:

      set odps.sql.allow.fullscan=true;
select * from sale_detail;
-- 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   |
+------------+-------------+-------------+------------+------------+

    • Non-partitioned table sale_detail_np

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

-- Append data to the source table.
insert into sale_detail_np values ('s4','c4',100.4);

      To query the data in the non-partitioned table sale_detail_np, run the following command:

      select * from sale_detail_np;
-- The following result is returned.
+------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s4         | c4          | 100.4       |
+------------+-------------+-------------+

    Usage examples

    The following examples show how to use the CLONE TABLE command based on the sample data:

    • Example 1: Copy all data from the non-partitioned table sale_detail_np to the destination table sale_detail_np_clone.

      -- Copy the table data.
clone table sale_detail_np to sale_detail_np_clone;
-- Query the destination table sale_detail_np_clone to verify the data.
select * from sale_detail_np_clone;
-- The following result is returned.
+------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s4         | c4          | 100.4       |
+------------+-------------+-------------+

    • Example 2: Copy data from a specified partition of the partitioned table sale_detail to the destination table sale_detail_clone.

      -- Copy the table data.
clone table sale_detail partition (sale_date='2013', region='china') to sale_detail_clone if exists overwrite;
-- Query the destination table sale_detail_clone to verify the data.
select * from sale_detail_clone;
-- 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: Copy all data from the partitioned table sale_detail to the destination table sale_detail_clone (the table generated in Example 2) and skip existing partitions in the destination table.

      -- Copy the table data.
clone table sale_detail to sale_detail_clone if exists ignore;
-- Query the destination table sale_detail_clone to verify the data.
select * from sale_detail_clone;
-- 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: Copy all data from the partitioned table sale_detail to the destination table sale_detail_clone1.

      -- Copy the table data.
clone table sale_detail to sale_detail_clone1;
-- Query the destination table sale_detail_clone1 to verify the data.
select * from sale_detail_clone1;
-- 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   |
+------------+-------------+-------------+------------+------------+