MaxCompute CLONE TABLE cross-project data migration same region - MaxCompute

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

After cloning, verify the destination table. Run SELECT to inspect rows or DESC to check the 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]];

Full syntax and examples: 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)	Up to 10,000 partitions per operation when the destination table exists.
Partition limit (new table)	Unlimited, which ensures atomicity.
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

A RAM user needs these permissions to run CLONE TABLE:

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

With these permissions, a RAM user runs CLONE TABLE the same way as an Alibaba Cloud account. .

Migrate data across different Alibaba Cloud accounts

Cross-account cloning requires adding the destination project owner to the source project with the required permissions. Only Alibaba Cloud accounts (not RAM users) can perform this operation.

Prerequisites

Ensure that you have:

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

Procedure

Grant permissions in the source project. Log in as the source project owner and run these commands. Add an Alibaba Cloud account (project-level). 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 as the destination project owner, who already has full permissions on the destination project.

   -- 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 rows or DESC to check the 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 migration, remove the destination project owner from the source project to maintain data security. Remove an Alibaba Cloud account (project-level).