ApsaraDB RDS:Import cloud disk backup data to AnalyticDB for MySQL

Prerequisites

Before you begin, ensure that you have:

• An ApsaraDB RDS for MySQL instance that uses cloud disks and supports the advanced download feature. For details, see Download backup

• An AnalyticDB for MySQL cluster (Data Warehouse Edition or Data Lakehouse Edition). For setup instructions, see Create a Data Warehouse Edition cluster or Create a Data Lakehouse Edition cluster

• An OSS bucket in the same region as the AnalyticDB for MySQL cluster

• (If the cluster uses elastic mode) ENI enabled in the Network Information section of the Cluster Information page

  

Step 1: Download the RDS backup to OSS

Set up an OSS bucket and download the RDS backup data to it.

1. Activate OSS. For details, see Activate OSS.

2. Create a bucket in the OSS console. For details, see Create buckets.

   Note The OSS bucket and the AnalyticDB for MySQL cluster must be in the same region.

3. Create a directory in the OSS bucket to store the data. For details, see Manage directories.

4. Download the RDS backup using the advanced download feature. Set Download Destination to OSS. For details, see Download backup.

   Note If you select a different download destination, upload the backup files to the OSS bucket manually. For details, see Upload objects.

In this example, the source database is mydb with a table named mytable. The sample data is:

INSERT INTO mytable (id, name)
VALUES
    ("12", "hello_world_1"),
    ("27", "hello_world_2"),
    ("28", "hello_world_3"),
    ("33", "hello_world_4"),
    ("37", "hello_world_5"),
    ("40", "hello_world_6");

The backup for mytable is downloaded to /bucket/_export/mydb/mytable/data/0-1csv in the OSS bucket.

Step 2: Import data from OSS to the AnalyticDB for MySQL cluster

In this example, data is imported from /bucket/_export/mydb/mytable/data/0-1csv into the adb_demo database in the AnalyticDB for MySQL cluster.

1. Connect to the AnalyticDB for MySQL cluster. For details, see Connect to an AnalyticDB for MySQL cluster.

2. Create a database. For details, see Create a database. This example uses a database named adb_demo.

3. Create an OSS external table in the adb_demo database using CREATE TABLE. The table maps to the OSS path where the backup data is stored. The following example creates an unpartitioned external table in CSV format:

   Note The external table can use the same name as the source table or a different name.

   CREATE TABLE IF NOT EXISTS mytable
   (
       id string,
       name string
   )
   ENGINE='OSS'
   TABLE_PROPERTIES='{
       "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
       "url":"oss://bucket/_export/mydb/mytable/data/0-1csv",
       "accessid":"LTAI****************",
       "accesskey":"yourAccessKeySecret",
       "delimiter":",",
       "format":"csv"
   }';

   Replace the placeholder values in TABLE_PROPERTIES with your actual configuration:

   Parameter	Description	Example
   endpoint	The VPC endpoint of the OSS bucket. AnalyticDB for MySQL accesses OSS only through a virtual private cloud (VPC), so use the internal endpoint. Find it in the OSS console under the bucket's Overview page.	oss-cn-hangzhou-internal.aliyuncs.com
   url	The absolute path of the OSS object or directory. For a directory, end the path with /.	Object: oss://<bucket-name>/adb/data.csv<br>Directory: oss://<bucket-name>/adb_data/
   accessid	The AccessKey ID used to access OSS. For details, see Accounts and permissions.	LTAI5tXxx
   accesskey	The AccessKey secret used to access OSS.	—
   delimiter	The column delimiter for CSV files.	,
   format	The file format: csv (default, no need to specify), parquet for Parquet files, or orc for ORC files.	csv

   Note If the backup data is split across multiple CSV files (for example, 0-1csv and 0-2csv), set url to the directory path: "url":"oss://bucket/_export/mydb/mytable/data/". AnalyticDB for MySQL merges all files in the directory into the external table automatically.

   For the full CREATE TABLE syntax, see Use external tables to import data to Data Warehouse Edition.

Step 3: Verify the imported data

Run the following query to confirm the data was imported correctly:

-- mytable is the name of the external table in the AnalyticDB for MySQL cluster.
SELECT * FROM mytable;

The returned data matches the original mytable table on the RDS instance.

What's next

After the data of the RDS instance that uses cloud disks is imported to the AnalyticDB for MySQL cluster, use AnalyticDB for MySQL to run business analytics on the data. If multiple tables are needed, follow the preceding steps to download and import each additional table into the adb_demo database.