MaxCompute:Migrate data from MaxCompute projects in different regions by using cross-project data access

Limits

This solution is suitable only for data migration between MaxCompute projects in the Chinese mainland regions.

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

Migrate table data from the source project named projectA to the destination project named projectB. The owners of the projects are the same Alibaba Cloud account.

• The Alibaba Cloud account can directly go to the destination project and create the destination table.

  Copy data during table creation

  -- Go to projectB.
  use projectB;
  
  set odps.namespace.schema=false;
  -- Create a table based on the source table in projectA and copy data from the source table to the new table. 
  create table [if not exists] <table_name> [lifecycle <days>] as select * from projectA.<table_name>; 

  Copy data after table creation

  -- Go to projectB.
  use projectB;
  
  set odps.namespace.schema=false;
  -- Create a table that has the same schema as the source table but do not copy data. 
  create table [if not exists] <table_name> like projectA.<table_name> [lifecycle <days>];
  -- Write data from the source table to the new table in the destination project.
  insert {into|overwrite} table <table_name> [partition (<pt_spec>)] [(<col_name> [,<col_name> ...)]]
  select * from projectA.<table_name> [zorder by <zcol_name> [, <zcol_name> ...]];

  For more information about how to create a table, see Table operations.

• If you perform operations as a RAM user or RAM role, make sure that the status of the source project is normal and the operator has the SELECT permission on the source table of the source project and the CreateInstance and CreateTable permissions on the destination project. For more information about how to grant permissions. You can use one of the following methods to grant the SELECT permission on the source table of a source project to a RAM user or RAM role:

  • Method 1: Add the RAM user to projectA. For more information, see User planning and management. Then, grant the RAM user permissions on the required resources. For more information, see Manage user permissions by using commands.

  • Method 2: Access projects across regions by using the package-based access control mechanism. You can use an Alibaba Cloud account to compress the resources in projectA into PackageA and allow PackageA to be installed in projectB. After you install PackageA in projectB, grant the read permission on PackageA to the operator in projectB. For more information, see Cross-project resource access based on packages.

Migrate data across MaxCompute projects within different Alibaba Cloud accounts in different regions

-- Go to projectB.
use projectB;

set odps.namespace.schema=false;
-- Create a table based on the source table in projectA and copy data from the source table to the new table. 
create table [if not exists] <table_name> [lifecycle <days>] as select * from projectA.<table_name>; 

-- Go to projectB.
use projectB;

set odps.namespace.schema=false;
-- Create a table that has the same schema as the source table but do not copy data. 
create table [if not exists] <table_name> like projectA.<table_name> [lifecycle <days>];
-- Write data from the source table to the new table in the destination project.
insert {into|overwrite} table <table_name> [partition (<pt_spec>)] [(<col_name> [,<col_name> ...)]]
select * from projectA.<table_name> [zorder by <zcol_name> [, <zcol_name> ...]];