Migrate data across MaxCompute projects in the same region by using CLONE TABLE - MaxCompute

The CLONE TABLE statement copies data from a source table to a destination table within the same region. Use it to migrate data between MaxCompute projects under the same Alibaba Cloud account or across different accounts.

After cloning, verify the data in the destination table. Run SELECT to inspect the data or DESC to check the table schema and size.

Syntax

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

For the full syntax reference and additional examples, see CLONE TABLE.

Limitations

Limitation	Description
Schema compatibility	The destination table schema must be compatible with the source table schema.
Supported table types	Partitioned tables, non-partitioned tables, and clustered tables.
Partition limit (existing table)	A maximum of 10,000 partitions can be cloned at once if the destination table already exists.
Partition limit (new table)	Unlimited. This ensures atomicity of the cloning operation.
Cross-region	Not supported.
External tables	Not supported.

Migrate data within the same Alibaba Cloud account

Use an Alibaba Cloud account

Run the CLONE TABLE statement directly in the source project.

-- Run in the source project
SET odps.namespace.schema=false;

-- Clone the sales_data table from project_analytics to project_reporting
CLONE TABLE project_analytics.sales_data TO project_reporting.sales_data IF EXISTS OVERWRITE;

Use a RAM user

Before running CLONE TABLE as a RAM user, confirm that the RAM user has the following permissions:

Permission	Scope
`SELECT`	Source table in the source project
`CreateTable`	Destination project
`CreateInstance`	Destination project

A RAM user with these permissions can run the CLONE TABLE statement the same way as an Alibaba Cloud account. For more information about authorization, see .

Migrate data across different Alibaba Cloud accounts

To clone data between projects owned by different Alibaba Cloud accounts, add the destination project owner to the source project and grant the required permissions. Only Alibaba Cloud accounts (not RAM users) can perform cross-account operations.

Prerequisites

Before you begin, make sure that you have:

The source and destination projects in the same region
The Alibaba Cloud account credentials for both the source and destination project owners

Procedure

Grant permissions in the source project. Log in with the source project owner account and run the following commands. For more information about adding accounts, see Add an Alibaba Cloud account (project-level). For more information about granting permissions, see Grant permissions on a specified project to a user.

   -- Run in the source project (project_analytics)
   USE project_analytics;

   -- Add the destination project owner to the source project
   ADD USER ALIYUN$destination_owner@aliyunid.com;

   -- Grant project-level permissions
   GRANT CreateTable, CreateInstance ON PROJECT project_analytics TO USER ALIYUN$destination_owner@aliyunid.com;

   -- Grant read access to the source table
   GRANT Select ON TABLE sales_data TO USER ALIYUN$destination_owner@aliyunid.com;

Run CLONE TABLE in the destination project. Log in with the destination project owner account. The destination project owner already has full permissions on the destination project, so no additional grants are needed.

   -- Run in the destination project (project_reporting)
   USE project_reporting;

   -- Clone the table from project_analytics to project_reporting
   CLONE TABLE project_analytics.sales_data TO project_reporting.sales_data IF EXISTS OVERWRITE;

Verify the cloned data. Run SELECT to inspect the data or DESC to check the table schema and size. Expected output (example): You can also verify the table schema and size:

   -- Check a sample of rows
   SELECT * FROM sales_data LIMIT 2;

   +----+------------+--------+
   | id | order_date | amount |
   +----+------------+--------+
   |  1 | 2024-01-15 | 299.00 |
   |  2 | 2024-01-16 | 450.50 |
   +----+------------+--------+

   -- Check the table structure and size
   DESC sales_data;

Clean up cross-account access

Important

After the migration is complete, remove the destination project owner from the source project to maintain data security. For more information, see Remove an Alibaba Cloud account (project-level).