All Products
Search

This Product

    AnalyticDB:Use foreign tables to import data from RDS for MySQL

    Document Center

    AnalyticDB:Use foreign tables to import data from RDS for MySQL

    Last Updated:Oct 17, 2025

    You can import data from RDS for MySQL to AnalyticDB for MySQL to integrate data from multiple sources and perform complex query analysis in AnalyticDB for MySQL. You can also write summarized key metrics back to RDS for MySQL.

    Prerequisites

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

    • The CIDR block of the VPC where the AnalyticDB for MySQL cluster resides is added to the whitelist of the RDS for MySQL instance.

    • For AnalyticDB for MySQL Enterprise Edition, Basic Edition, Data Lakehouse Edition, or Data Warehouse Edition (Elastic Mode) clusters, the Elastic Network Interface (ENI) Network switch is enabled in the Network Information section on the Cluster Information page in the AnalyticDB for MySQL console.

      Important

      Enabling or disabling the ENI network interrupts database connections for about 2 minutes. During this period, read and write operations are unavailable. Evaluate the impact before you enable or disable the ENI network.

    Data preparation

    In the example for this topic, an RDS for MySQL database named test_adb contains a table named person:

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

    The following example inserts data into the person table:

    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 Edition, Basic Edition, and Data Lakehouse Edition

    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 navigation pane on the left, click Job Development > SQL Development.

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

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

      CREATE EXTERNAL DATABASE adb_external_db;

    4. Execute the following statement to create a foreign table. The following code provides an example:

      Note

      • The foreign table in AnalyticDB for MySQL must have the same column names, number of columns, column order, and data types as the source table in RDS for MySQL.

      • For more information about the parameters for creating a foreign table in AnalyticDB for MySQL, 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 the foreign table is created, you can execute a SELECT statement in AnalyticDB for MySQL to query the data in 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. Execute the following statement to create a destination database.

      CREATE DATABASE adb_demo;

    7. Execute the following statement to create a destination table named mysql_import_test in the adb_demo database. This table is used to store the 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. Execute the following statement to import data from the RDS for MySQL instance to the destination AnalyticDB for MySQL cluster.

      INSERT INTO mysql_import_test
SELECT * FROM adb_external_db.goods;

    9. Execute the following statement to query data in the mysql_import_test table of 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 more information, see Connect to a cluster.

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

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

    3. Create a foreign table.

      Execute the following statement to create a foreign table named goods_external_table in the destination database adb_demo.

      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'

      The storage engine for the foreign table. This topic uses MySQL.

      TABLE_PROPERTIES

      AnalyticDB for MySQL uses this method to access data in RDS for MySQL.

      url

      The internal endpoint (VPC endpoint) of the RDS for MySQL instance and the name of the source database. In this example, the source database is test_adb. For more information about how to view the endpoint of an RDS for MySQL instance, 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 source table is goods.

      username

      The database account for the RDS for MySQL instance.

      password

      The password for the RDS for MySQL database account.

      charset

      The character set for MySQL. Valid values:

      • gbk

      • utf8 (default)

      • utf8mb4

    4. Create the destination table.

      In the adb_demo destination database, execute the following statement to create a table named mysql_import_test. This table is used to store 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 AnalyticDB for MySQL cluster.

      REPLACE INTO mysql_import_test
SELECT * FROM goods_external_table;

    6. After the import is complete, you can log on to the AnalyticDB for MySQL destination database adb_demo and execute the following command to verify that the data from the source table has been imported into the mysql_import_test table:

      SELECT * FROM mysql_import_test LIMIT 100;

      The command returns the following output:

      +----------+-------+-------+------------+---------------------+
| 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 |
+----------+-------+-------+------------+---------------------+