All Products
Search

This Product

    MaxCompute:CLONE TABLE

    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 to improve the efficiency of data cloning. This topic describes the features, limits, and syntax of the CLONE TABLE statement. This topic also provides examples on how to use the CLONE TABLE statement.

    Description

    The CLONE TABLE statement is used to clone data from a source table to a destination table. 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 to clone data between partitioned tables and non-partitioned tables, or between 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 up to 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 indicates the column name, and partition_col_value indicates 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.

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

      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 and 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. 
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   |
+------------+-------------+-------------+------------+------------+