This topic describes how to use the CLONE TABLE statement. The CLONE TABLE statement is used to clone data from one table to another. This statement improves efficiency of data migration.

Limits

  • The schema of the destination table must be compatible with that of the source table.
  • Both partitioned and non-partitioned tables are supported. The CLONE TABLE command cannot be used to clone clustered tables.
  • If a destination table is created before the CLONE TABLE statement is executed, data in a maximum of 10,000 partitions can be cloned at a time.
  • If a destination table is not created before the CLONE TABLE statement is executed, the number of partitions from which you can clone data at a time is not limited. This ensures atomicity.
  • You can execute the CLONE TABLE statement for up to seven times in the same non-partitioned table or in the same partition of a partitioned table.
  • You cannot execute the CLONE TABLE statement for projects across regions.
  • The CLONE TABLE command cannot be used to clone external tables.

Syntax

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

Description

The CLONE TABLE statement is used to clone data from the src_table_name table to the desc_table_name table.
Note After you clone data to a destination table, we recommend that you verify accuracy of the data. For example, you can execute the SELECT COUNT command to view the number of rows in the destination table or execute the DESC command to view the table size.

Parameters

  • src_table_name: the name of the source table.
  • src_project_name: the name of the project to which the source table belongs. If this parameter is not specified, the name of the current project is used by default.
  • desc_table_name: the name of the destination table.
    • If a destination table is not created, the table is created by using the CREATE TABLE LIKE statement when you execute the CLONE TABLE statement.
    • If a destination table is created and IF EXISTS OVERWRITE is specified, data in the partitions of the destination table is overwritten when you execute the CLONE TABLE statement.
    • If a destination table is created and IF EXISTS IGNORE is specified, existing partitions in the table are skipped and data in these partitions is not overwritten when you execute the CLONE TABLE statement.
  • dest_project_name: the name of the project to which the destination table belongs. If this parameter is not specified, the current project name is used by default.

Export more than 10,000 data entries by using SQLTask and Tunnel

Assume that partitioned table srcpart_copy and non-partitioned table src_copy are source tables. The two tables have the following metadata:
odps@ multi>READ srcpart_copy;
+------------+------------+------------+------------+
| key        | value      | ds         | hr         |
+------------+------------+------------+------------+
| 1          | ok49       | 2008-04-09 | 11         |
| 1          | ok48       | 2008-04-08 | 12         |
+------------+------------+------------+------------+
odps@ multi>READ src_copy;
+------------+------------+
| key        | value      |
+------------+------------+
| 1          | ok         |
+------------+------------+
  • Clone all data from non-partitioned table src_copy to destination table src_clone.
    odps@ multi>CLONE TABLE src_copy TO src_clone;
    ID = 2019102303024544g2540cdv2
    OK
    -- Query data in destination table src_clone after the data clone and verify the accuracy of the data.
    odps@ multi>SELECT * FROM src_clone;
  • Clone data from a specified partition of partitioned table srcpart_copy to destination table srcpart_clone.
    odps@ multi>CLONE TABLE srcpart_copy PARTITION(ds="2008-04-09", hr='11') TO srcpart_clone IF EXISTS OVERWRITE;
    ID = 20191023030534986g4540cdv2
    OK
    -- Query data in destination table srcpart_clone after the data clone and verify the accuracy of the data.
    odps@ multi>SELECT * FROM srcpart_clone;
  • Clone all data from partitioned table srcpart_copy to destination table srcpart_clone and skip data in existing partitions of the destination table.
    odps@ multi>CLONE TABLE srcpart_copy TO srcpart_clone IF EXISTS IGNORE;
    ID = 20191023030619196g5540cdv2
    OK
    -- Query data in destination table srcpart_clone after the data clone and verify accuracy of the data.
    odps@ multi>SELECT * FROM srcpart_clone;
  • Clone all data from partitioned table srcpart_copy to destination table srcpart_clone2.
    odps@ multi>CLONE TABLE srcpart_copy TO srcpart_clone2;
    ID = 20191023030825186g6540cdv2
    OK
    -- Query data in destination table srcpart_clone2 after the data clone and verify the accuracy of the data.
    odps@ multi>SELECT * FROM srcpart_clone2;