MaxCompute:Migrate data across MaxCompute projects in the same region by using CLONE 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 can execute the CLONE TABLE statement up to six times for the same non-partitioned table or for the same partition of a partitioned table.

• 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]] ;

For more information about the parameters and examples, see CLONE TABLE.

Migrate data across MaxCompute projects within the same Alibaba Cloud account in the same region

• If you migrate data by using an Alibaba Cloud account, you can directly execute the CLONE TABLE statement in the source project.

  set odps.namespace.schema=false;
  
  -- Clone the table from projectA to projectB.
  clone TABLE projectA.<tablename> to projectB.<tablename> IF EXISTS OVERWRITE;

• If you migrate data as a RAM user, check whether the RAM user has the SELECT permission on the source table in the source project and the CreateTable and CreateInstance permissions on the destination project. The RAM user that has the required permissions can execute the CLONE TABLE statement.

Migrate data across MaxCompute projects within different Alibaba Cloud accounts in the same region

To synchronize data across MaxCompute projects within different Alibaba Cloud accounts, you must add the Alibaba Cloud account that is the owner of the destination project to the source project and grant the CreateTable and CreateInstance permissions on the source project to the account. Then, you can execute the CLONE TABLE statement by using the Alibaba Cloud account to migrate data across projects.

1. Add the Alibaba Cloud account that is the owner of the destination project to the source project and grant permissions to the account.

   -- Go to the source project named projectA.
   use projectA;
   -- Add the Alibaba Cloud account projectB_owner@aliyunid.com, which is the owner of the destination project named projectB, to projectA.
   add user ALIYUN$projectB_owner@aliyunid.com;
   -- Grant the CreateTable and CreateInstance permissions on the source project to the Alibaba Cloud account.
   grant CreateTable,CreateInstance on project projectA to user ALIYUN$projectB_owner@aliyunid.com;
   -- Grant the SELECT permission on the source table to the Alibaba Cloud account.
   grant Select on table <tablename> to user ALIYUN$projectB_owner@aliyunid.com;

   For more information about how to add an Alibaba Cloud account to a project, see Add an Alibaba Cloud account (project-level). For more information about how to grant permissions to an account, see Grant permissions on a specified project to a user.

2. Execute the CLONE TABLE statement in the destination project.

   Only Alibaba Cloud accounts can perform cross-account operations. The Alibaba Cloud account that is the owner of the destination project has all permissions on the data in the destination project. You do not need to grant permissions to the Alibaba Cloud account.

   use projectB;
   -- Clone the table from projectA to projectB.
   clone TABLE projectA.<tablename> to projectB.<tablename> IF EXISTS OVERWRITE;

3. Check whether the table data is migrated to the destination project.

   select * from <tablename> limit 2;

   Important

   To ensure data security, we recommend that you remove the Alibaba Cloud account that is the owner of the destination project from the source project after data migration is complete. For more information, see Remove an Alibaba Cloud account (project-level).