All Products
Search
Document Center

MaxCompute:CLONE TABLE

Last Updated:Feb 28, 2024

If you want to clone data from one table to another, you can use the CLONE TABLE statement of MaxCompute. The CLONE TABLE statement can improve the efficiency of data cloning. This topic provides examples on how to use the CLONE TABLE statement.

Features

The CLONE TABLE statement is used to clone data from a source table to a destination table in an efficient manner. After you clone the data to the destination table, we recommend that you verify the cloned data to ensure data accuracy. For example, you can execute the SELECT statement to query the data in the destination table and execute the DESC statement to query the size of the destination table.

Limits

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

  • You can execute the CLONE TABLE statement for partitioned tables, non-partitioned tables, and clustered tables.

  • If you have created the destination table before the CLONE TABLE statement is executed, you can execute the CLONE TABLE statement to clone data from a maximum of 10,000 partitions at the same time.

  • If you have not created the destination table before the CLONE TABLE statement is executed, the number of partitions from which you can clone data at the same time is unlimited. This way, the atomicity of the cloning operation is ensured.

  • You cannot execute the CLONE TABLE statement for projects across multiple regions.

  • You cannot execute the CLONE TABLE statement for external tables.

Syntax

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 to which the source table belongs. If you do not configure this parameter, the name of the current project is used. This parameter is required if the source table and destination table do not belong to the same MaxCompute project.

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

  • pt_spec: optional. The partition information of the source table. The value of this parameter is in the (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...) format. partition_col specifies the column name, and partition_col_value specifies the column value.

  • dest_project_name: optional. The name of the MaxCompute project to which the destination table belongs. If you do not configure this parameter, the name of the current project is used. This parameter is required if the source table and destination table do not belong to the same MaxCompute project.

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

    • If you have not created the destination table, the CLONE TABLE statement creates the destination table by using the syntax of the CREATE TABLE LIKE statement. For more information about the CREATE TABLE LIKE statement, see Create a table.

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

    • If you have created the destination table and you specify if exists ignore, the CLONE TABLE statement skips the existing partitions and does not overwrite the data in the existing partitions of the destination table.

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: 
    +------------+-------------+-------------+------------+------------+
    | 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: 
    +------------+-------------+-------------+
    | 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 full data from the sale_detail_np table to the sale_detail_np_clone table. Sample statements:

    -- 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: 
    +------------+-------------+-------------+
    | shop_name  | customer_id | total_price |
    +------------+-------------+-------------+
    | s4         | c4          | 100.4       |
    +------------+-------------+-------------+
  • Example 2: Clone data from a specified partition of the sale_detail table to the sale_detail_clone table. Sample statements:

    -- 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. 
    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: Clone full data from the sale_detail table to the sale_detail_clone table that is created in Example 2 and skip the existing partitions in the sale_detail_clone table. Sample statements:

    -- 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: 
    +------------+-------------+-------------+------------+------------+
    | 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 full data from the sale_detail table to the sale_detail_clone1 table. Sample statements:

    -- 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. 
    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   |
    +------------+-------------+-------------+------------+------------+
  • Example 5: Clone data from a Transaction Table 2.0 table

    -- Clone data from a Transaction Table 2.0 non-partitioned table.
    clone table mf_tt3 to new_table;
    
    -- Clone data from a Transaction Table 2.0 partitioned table.
    clone table mf_tt2 partition (dd='01', hh='01') to new_table;
    Note

    The Transaction Table 2.0 feature is in invitational preview. By default, this feature cannot be used. If you want to use the Transaction Table 2.0 feature, submit a ticket on the trail application page of MaxCompute. For more information about Transaction Table 2.0, see Overview of Transaction Table 2.0.

Best practices

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