All Products
Search

This Product

    MaxCompute:CLONE TABLE

    Document Center

    MaxCompute:CLONE TABLE

    Last Updated:Dec 12, 2024

    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

    • 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:

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

      -- 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. 
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 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:

      -- 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. 
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 data from a Delta table.

      • Clone data from a non-partitioned Delta table

        CLONE TABLE mf_dt TO new_table;

      • Clone data from a 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 Migrate data across MaxCompute projects in the same region by using CLONE TABLE.