All Products
Search

This Product

    AnalyticDB:Import RDS MySQL data by using an external table

    Document Center

    AnalyticDB:Import RDS MySQL data by using an external table

    Last Updated:Aug 24, 2026

    You can import data from RDS for MySQL into AnalyticDB for MySQL. This lets you integrate data from multiple sources and run complex analytical queries in AnalyticDB for MySQL. You can also write aggregated key metrics back to RDS for MySQL.

    Prerequisites

    • Your RDS for MySQL instance and AnalyticDB for MySQL cluster are in the same VPC.

    • You have added the AnalyticDB for MySQL RDS for MySQL to the IP address whitelist of the RDS for MySQL instance.

    • If your AnalyticDB for MySQL cluster is Enterprise Edition, Basic Edition, Data Lakehouse Edition, or Data Warehouse Edition in elastic mode, enable the ENI switch in the Network Information section of the Cluster Information page in the AnalyticDB for MySQL.

      Important

      Enabling or disabling the elastic network interface (ENI) interrupts database connectivity for approximately 2 minutes, during which read and write operations are unavailable. Assess the potential impact before you perform this action.

    Prepare the data

    The examples in this topic use an RDS for MySQL database named test_adb. In this database, a table named goods is created. The following code provides an example:

    CREATE TABLE goods (
   goods_id bigint(20) NOT NULL,
   price double NOT NULL,
   class bigint(20) NOT NULL,
   name varchar(32) NOT NULL,
   update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (goods_id)
);

    Insert data into the goods table. The following code provides an example:

    INSERT INTO test_adb.goods 
VALUES
  (1, 50, 1, 'Book', '2024-08-07 09:56:53'),
  (2, 80, 2, 'Basketball', '2024-08-08 10:00:55'),
  (3, 150, 3, 'Watch', '2024-08-06 11:00:25'),
  (4, 30, 1, 'Magazine', '2024-08-08 12:25:55'),
  (5, 80, 2, 'Football', '2024-08-07 08:50:35'),
  (6, 25, 4, 'Tea', '2024-08-05 09:25:30'),
  (7, 30, 4, 'Coffee', '2024-08-07 10:20:40'),
  (8, 300, 3, 'Computer', '2024-08-06 10:55:35'),
  (9, 100, 2, 'Baseball', '2024-08-08 11:35:50'),
  (10, 200, 3, 'Phone', '2024-08-07 11:30:25');

    Procedure

    Enterprise, Basic, and Lakehouse

    1. Go to the SQL editor.

      1. Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. Find the cluster that you want to manage and click the cluster ID.

      2. In the left-side navigation pane, choose Job Development > SQL Development.

    2. Select the XIHE engine and an interactive resource group.

    3. Run the following statement to create an external database. The following code provides an example:

      CREATE EXTERNAL DATABASE adb_external_db;

    4. Run the following statement to create an external table. The following code provides an example:

      Note

      • The AnalyticDB for MySQL in AnalyticDB for MySQL must match the column names, count, order, and data types of the source table in RDS for MySQL.

      • For more information about the parameters for creating an AnalyticDB for MySQL external table, see CREATE EXTERNAL TABLE.

      CREATE EXTERNAL TABLE IF NOT EXISTS  adb_external_db.goods (
    goods_id bigint(20) NOT NULL,
    price double NOT NULL,
    class bigint(20) NOT NULL,
    name varchar(32) NOT NULL,
    update_time timestamp,
    PRIMARY KEY (goods_id)
 )ENGINE = 'MYSQL' TABLE_PROPERTIES = '{
   "url":"jdbc:mysql://<mysql-address>:3306/test_adb",
   "tablename":"goods",
   "username":"<mysql-user-name>",
   "password":"<mysql-user-password>",
   "charset":"utf8"
}';

    5. Query data.

      After you create the external table, you can run a SELECT statement in AnalyticDB for MySQL to query data from the goods table in RDS for MySQL.

      SELECT * FROM adb_external_db.goods;

      The following result is returned:

      +----------+-------+-------+------------+---------------------+
| goods_id | price | class | name       | update_time         |
+----------+-------+-------+------------+---------------------+
|        2 |  80.0 |     2 | Basketball | 2024-08-08 10:00:55 |
|       10 | 200.0 |     3 | Phone      | 2024-08-07 11:30:25 |
|        1 |  50.0 |     1 | Book       | 2024-08-07 09:56:53 |
|        6 |  25.0 |     4 | Tea        | 2024-08-05 09:25:30 |
|        9 | 100.0 |     2 | Baseball   | 2024-08-08 11:35:50 |
|        3 | 150.0 |     3 | Watch      | 2024-08-06 11:00:25 |
|        5 |  80.0 |     2 | Football   | 2024-08-07 08:50:35 |
|        7 |  30.0 |     4 | Coffee     | 2024-08-07 10:20:40 |
|        8 | 300.0 |     3 | Computer   | 2024-08-06 10:55:35 |
|        4 |  30.0 |     1 | Magazine   | 2024-08-08 12:25:55 |
+----------+-------+-------+------------+---------------------+

    6. Run the following statement to create a destination database:

      CREATE DATABASE adb_demo;

    7. Run the following statement to create a destination table named mysql_import_test in the adb_demo destination database. This table stores data imported from RDS for MySQL.

      CREATE TABLE IF NOT EXISTS  mysql_import_test (
        goods_id bigint(20) NOT NULL,
        price double NOT NULL,
        class bigint(20) NOT NULL,
        name varchar(32) NOT NULL,
        update_time timestamp,
        PRIMARY KEY (goods_id)
 )
DISTRIBUTED BY HASH(goods_id);

    8. Run the following statement to import data from the RDS for MySQL instance into the destination AnalyticDB for MySQL cluster.

      INSERT INTO mysql_import_test
SELECT * FROM adb_external_db.goods;

    9. Run the following statement to query data from the mysql_import_test table in AnalyticDB for MySQL:

      SELECT * FROM mysql_import_test;

      The following result is returned:

      +----------+-------+-------+------------+---------------------+
| goods_id | price | class | name       | update_time         |
+----------+-------+-------+------------+---------------------+
|        2 |  80.0 |     2 | Basketball | 2024-08-08 10:00:55 |
|       10 | 200.0 |     3 | Phone      | 2024-08-07 11:30:25 |
|        1 |  50.0 |     1 | Book       | 2024-08-07 09:56:53 |
|        6 |  25.0 |     4 | Tea        | 2024-08-05 09:25:30 |
|        9 | 100.0 |     2 | Baseball   | 2024-08-08 11:35:50 |
|        3 | 150.0 |     3 | Watch      | 2024-08-06 11:00:25 |
|        5 |  80.0 |     2 | Football   | 2024-08-07 08:50:35 |
|        7 |  30.0 |     4 | Coffee     | 2024-08-07 10:20:40 |
|        8 | 300.0 |     3 | Computer   | 2024-08-06 10:55:35 |
|        4 |  30.0 |     1 | Magazine   | 2024-08-08 12:25:55 |
+----------+-------+-------+------------+---------------------+

    Data warehouse edition

    1. Connect to the destination AnalyticDB for MySQL cluster. For detailed steps, see Connect to a cluster.

    2. Create a destination database. For detailed steps, see Create a database.

      This example uses a destination database named adb_demo in the AnalyticDB for MySQL cluster.

    3. Create an external table.

      Run the following statement to create an external table named goods_external_table in the adb_demo destination database.

      CREATE TABLE IF NOT EXISTS goods_external_table (
        goods_id bigint(20) NOT NULL,
        price double NOT NULL,
        class bigint(20) NOT NULL,
        name varchar(32) NOT NULL,
        update_time timestamp,
        PRIMARY KEY (goods_id)
 )
        ENGINE='mysql'  
        TABLE_PROPERTIES='{  
        "url":"jdbc:mysql://<mysql-vpc-address>:3306/test_adb",  
        "tablename":"goods",  
        "username":"<mysql-user-name>",  
        "password":"<mysql-user-password>",
        "charset":"utf8"
  }';

      Parameter

      Description

      ENGINE='mysql'

      Specifies MySQL as the storage engine for the external table.

      TABLE_PROPERTIES

      Specifies how AnalyticDB for MySQL accesses data in RDS for MySQL.

      url

      The JDBC connection URL for the source RDS for MySQL instance. The URL must contain the RDS for MySQL of the instance and the name of the source database. In this example, the source database is test_adb. For information about how to view the RDS for MySQL endpoint, see View or modify internal and public endpoints and ports.

      Format: "jdbc:mysql://<mysql-vpc-address>:3306/<rds-database-name>".

      Example: jdbc:mysql://rm-bp1hem632****.mysql.rds.aliyuncs.com:3306/test_adb.

      tablename

      The name of the source table in RDS for MySQL. In this example, the table name is goods.

      username

      The username of the RDS for MySQL database account.

      password

      The password of the RDS for MySQL database account.

      charset

      The MySQL character set. Valid values:

      • gbk

      • UTF8 (default)

      • utf8mb4

    4. Create a destination table.

      Run the following statement to create a destination table named mysql_import_test in the adb_demo destination database. This table stores data imported from RDS for MySQL.

      CREATE TABLE IF NOT EXISTS  mysql_import_test (
        goods_id bigint(20) NOT NULL,
        price double NOT NULL,
        class bigint(20) NOT NULL,
        name varchar(32) NOT NULL,
        update_time timestamp,
        PRIMARY KEY (goods_id)
 )
DISTRIBUTED BY HASH(goods_id);

    5. Import data from the source RDS for MySQL instance into the destination AnalyticDB for MySQL cluster.

      REPLACE INTO mysql_import_test
SELECT * FROM goods_external_table;

    6. After the import is complete, log on to the adb_demo database in AnalyticDB for MySQL and run the following statement to verify that the source data was successfully imported into the mysql_import_test table:

      SELECT * FROM mysql_import_test LIMIT 100;

      The following result is returned:

      +----------+-------+-------+------------+---------------------+
| goods_id | price | class | name       | update_time         |
+----------+-------+-------+------------+---------------------+
|        2 |  80.0 |     2 | Basketball | 2024-08-08 10:00:55 |
|       10 | 200.0 |     3 | Phone      | 2024-08-07 11:30:25 |
|        1 |  50.0 |     1 | Book       | 2024-08-07 09:56:53 |
|        6 |  25.0 |     4 | Tea        | 2024-08-05 09:25:30 |
|        9 | 100.0 |     2 | Baseball   | 2024-08-08 11:35:50 |
|        3 | 150.0 |     3 | Watch      | 2024-08-06 11:00:25 |
|        5 |  80.0 |     2 | Football   | 2024-08-07 08:50:35 |
|        7 |  30.0 |     4 | Coffee     | 2024-08-07 10:20:40 |
|        8 | 300.0 |     3 | Computer   | 2024-08-06 10:55:35 |
|        4 |  30.0 |     1 | Magazine   | 2024-08-08 12:25:55 |
+----------+-------+-------+------------+---------------------+