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

Prerequisites

• The RDS for MySQL instance must be a cloud disk instance and support the Advanced Download feature. For more information about supported instances, see Prerequisites.

• You have created a destination cloud-native data warehouse AnalyticDB for MySQL cluster. For more information, see Create a Data Warehouse Edition cluster or Create a Data Lakehouse Edition cluster.

• If the AnalyticDB for MySQL cluster is in elastic mode, you must turn on ENI in the Network Information section of the Cluster Information page.

  Important

  When you enable or disable ENI, database connections may be interrupted for approximately 2 minutes. During this period, you cannot perform read or write operations. Proceed with caution when you enable or disable ENI.

Notes

AnalyticDB for MySQL supports importing data files only in CSV or Parquet format. When you use the backup download feature of RDS for MySQL, you must select CSV or Parquet as the download format.

Preparations

Follow these steps to use the backup download feature to move your AnalyticDB for MySQL data to a folder in Object Storage Service (OSS).

1. Activate OSS. For more information, see Activate OSS.

2. Create an OSS bucket in the OSS console. For more information, see Create a bucket in the console.

   Note

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

3. Create a folder in the OSS console. For more information, see Create a folder.

4. You can use the Download Backup feature to download backup data from a cloud disk instance to OSS.

   Note

   If you select a different download destination, you must manually upload the backup data to OSS. For more information, see Upload data.

   In this example, the source RDS for MySQL database is named mydb and contains a table named mytable. The sample data in the table is as follows:

   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");

   Use the backup download feature to save the data from the mytable table to the OSS path /bucket/_export/mydb/mytable/data/0-1csv.

Import OSS data into AnalyticDB for MySQL

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

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

2. Create a database. For more information, see Create a database.

   In this example, a database named adb_demo is created in the AnalyticDB for MySQL cluster.

3. Create an external mapping table.

   Note

   When you create an external mapping table, you can use the same name as the source table or specify a different name.

   AnalyticDB for MySQL supports only table-level analysis and does not support full-database analysis. Therefore, you must create a mapping table in the AnalyticDB for MySQL database before you can perform analysis. Use the CREATE TABLE statement to create an OSS external mapping table in CSV or Parquet format in the destination database adb_demo. This example shows how to create an OSS external mapping table in CSV format for data files that do not have partitions. For more information about the syntax, see Creating an OSS external table from data files without partitions.

   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"
   }';

   Parameter	Description
   ENGINE='OSS'	The table engine. Set this parameter to OSS.
   TABLE_PROPERTIES	Specifies how AnalyticDB for MySQL accesses data in OSS.
   endpoint	The Endpoint of the OSS bucket. AnalyticDB for MySQL can access OSS only over a VPC. Note Log on to the OSS console. Click the name of the destination bucket. On the Overview page of the bucket, view the Endpoint.
   url	The absolute path to the source data file or folder in OSS. We recommend ending folder paths with a forward slash (/). Examples: File: oss://<bucket-name>/adb/oss_import_test_data.csv. Folder: oss://<bucket-name>/adb_data/. Note If you specify a folder path, the external table includes all data under that folder after creation.
   accessid	The AccessKey ID of the Alibaba Cloud account or a RAM user that has the management permissions on OSS. To obtain your AccessKey ID and AccessKey secret, see Accounts and Permissions.
   accesskey	Your AccessKey secret for accessing files or folders in OSS.
   delimiter	Defines the column delimiter for the CSV data file.
   format	The data file format. To create an external table for a Parquet file, set this parameter to parquet. To create an external table for an ORC file, set this parameter to orc. Note If you do not specify a format, the default is CSV.

   Note

   If the downloaded table is split into multiple CSV files, such as 0-1csv and 0-2csv, set the url parameter to the parent directory of the data files: "url":"oss://bucket/_export/mydb/mytable/data/". AnalyticDB for MySQL merges all files in this path into the mytable table.

   At this point, the mytable table is created in the AnalyticDB for MySQL database. The content of the table is identical to the content of the source mytable table. You can now query the table.

4. Run the following statement to query the imported data.

   -- mytable is the table name in the database
   SELECT * FROM mytable;

   The query returns a table whose content is identical to the content of the source mytable table.

   The table has two columns, id and name, with six records. The name values range from hello_world_1 to hello_world_6.