This topic describes how to use the CLONE TABLE statement to copy data from a table to another. CLONE TABLE improves the efficiency of data migration.

Limits

  • The schema of a destination table must be compatible with that of a source table.
  • The CLONE TABLE operation supports partitioned and non-partitioned tables.
  • If the destination table is created before data copy, data in up to 10,000 partitions can be copied at a time.
  • If the destination table is not created before data copy, the number of partitions that you can copy at a time is unlimited, which guarantees atomicity.

Syntax

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

Description

This statement is used to copy data from src_table_name to desc_table_name.

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 it is not specified, the current project name is used by default.
  • desc_table_name: the name of the destination table.
    • If the destination table is not created before data copy, a table is created by using the CREATE TABLE LIKE statement when you perform the CLONE TABLE operation.
    • If the destination table is created before data copy and IF EXISTS OVERWRITE is selected, data in the corresponding partitions of the destination table is overwritten.
    • If the destination table is created before data copy and IF EXISTS IGNORE is selected, existing partitions in the table are skipped and data in these partitions is not overwritten.
  • dest_project_name: the name of the project to which the destination table belongs. If it is not specified, the current project name is used by default.

Example

If source tables are partitioned table srcpart_copy and non-partitioned table src_copy, the table metadata is as follows:
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         |
+------------+------------+
  • Copy all data from src_copy to destination table src_clone.
    odps@ multi>clone table src_copy to src_clone;
    ID = 2019102303024544g2540cdv2
    OK
    -- Query information in src_clone after data copy.
    odps@ multi>read src_clone;
    +------------+------------+
    | key        | value      |
    +------------+------------+
    | 1          | ok         |
    +------------+------------+
  • Copy data from a specific partition of srcpart_copy to 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 information in srcpart_clone after data copy.
    odps@ multi>read srcpart_clone;
    +------------+------------+------------+------------+
    | key        | value      | ds         | hr         |
    +------------+------------+------------+------------+
    | 1          | ok49       | 2008-04-09 | 11         |
    +------------+------------+------------+------------+
  • Copy all data from srcpart_copy to srcpart_clone and skip data in the partitions of the destination table.
    odps@ multi>clone table srcpart_copy to srcpart_clone IF EXISTS IGNORE;
    ID = 20191023030619196g5540cdv2
    OK
    -- Query information in srcpart_clone after data copy.
    odps@ multi>read srcpart_clone;
    +------------+------------+------------+------------+
    | key        | value      | ds         | hr         |
    +------------+------------+------------+------------+
    | 1          | ok49       | 2008-04-09 | 11         |
    | 1          | ok48       | 2008-04-08 | 12         |
    +------------+------------+------------+------------+
  • Copy data from srcpart_copy to destination table srcpart_clone2.
    odps@ multi>clone table srcpart_copy to srcpart_clone2;
    ID = 20191023030825186g6540cdv2
    OK
    -- Query information in srcpart_clone2 after data copy.
    odps@ multi>read srcpart_clone2;
    +------------+------------+------------+------------+
    | key        | value      | ds         | hr         |
    +------------+------------+------------+------------+
    | 1          | ok49       | 2008-04-09 | 11         |
    | 1          | ok48       | 2008-04-08 | 12         |
    +------------+------------+------------+------------+