Use external tables to import data from RDS for MySQL - AnalyticDB

External tables let AnalyticDB for MySQL read data directly from an RDS for MySQL instance over your virtual private cloud (VPC). Use this approach to pull source data into AnalyticDB for MySQL for complex analytics, then optionally write summarized results back to RDS for MySQL.

Prerequisites

Before you begin, make sure that:

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 IP address whitelist of the RDS for MySQL instance. Without this, AnalyticDB for MySQL cannot reach the RDS for MySQL instance and queries against the external table fail.
For Enterprise Edition, Basic Edition, Data Lakehouse Edition, or Data Warehouse Edition clusters in elastic mode: the ENI switch is enabled in the Network Information section on the Cluster Information page in the AnalyticDB for MySQL console.

Important

Enabling or disabling the elastic network interface (ENI) interrupts database connectivity for approximately two minutes, during which read and write operations are unavailable. Plan this change during a maintenance window.

Sample data

The examples in this topic use an RDS for MySQL database named test_adb that contains a goods table:

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 sample rows to use throughout the steps:

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

Import data

The steps differ depending on your cluster edition.

Enterprise Edition, Basic Edition, and Data Lakehouse Edition
Data Warehouse Edition

Enterprise Edition, Basic Edition, and Data Lakehouse Edition

Steps overview: Open the SQL editor → Create an external database → Create an external table → Query the external table → Create a destination table → Import data → Verify.

Open the SQL editor.
1. Log on to the AnalyticDB for MySQL console. In the upper-left corner, select a region. In the left navigation pane, click Clusters, then click your cluster ID.
2. In the left navigation pane, click Job Development > SQL Development.
Select the XIHE engine and an interactive resource group.

Create an external database.

CREATE EXTERNAL DATABASE adb_external_db;

Create an external table that maps to the goods table in RDS for MySQL.

The external 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.

Important

Avoid hardcoding credentials in SQL statements that you save or share. Store credentials securely and substitute them at runtime where possible.

Placeholder	Description
`<mysql-address>`	The internal (VPC) endpoint hostname of your RDS for MySQL instance. Format: `<hostname>:3306`. To find your endpoint, see View or modify internal and public endpoints and ports.
`<mysql-user-name>`	The database account for the RDS for MySQL instance.
`<mysql-user-password>`	The password for the database account.

Parameter	Description
`url`	The VPC endpoint of the RDS for MySQL instance and the source database name. Format: `"jdbc:mysql://<hostname>:3306/<database-name>"`.
`tablename`	The name of the source table in RDS for MySQL.
`username`	The database account for the RDS for MySQL instance.
`password`	The password for the database account.
`charset`	The character set. Valid values: `gbk`, `utf8` (default), `utf8mb4`.

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

Replace the following placeholders: Key parameters in TABLE_PROPERTIES: For the full CREATE EXTERNAL TABLE syntax, see CREATE EXTERNAL TABLE.

Verify the external table by querying RDS for MySQL data through AnalyticDB for MySQL.

SELECT * FROM adb_external_db.goods;

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

Create a destination database and table in AnalyticDB for MySQL to store the imported data.

CREATE DATABASE adb_demo;

CREATE TABLE IF NOT EXISTS adb_demo.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);

Import data from the external table into the destination table.

INSERT INTO adb_demo.mysql_import_test
SELECT * FROM adb_external_db.goods;

Verify the import.

SELECT * FROM adb_demo.mysql_import_test;

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

Data Warehouse Edition

Steps overview: Connect to the cluster → Create a destination database → Create an external table → Create a destination table → Import data → Verify.

Connect to the AnalyticDB for MySQL cluster. For details, see Connect to a cluster.
Create a destination database. For details, see Create a database. In this example, the destination database is named adb_demo.

Create an external table in adb_demo that maps to the goods table in RDS for MySQL.

Important

Avoid hardcoding credentials in SQL statements that you save or share. Store credentials securely and substitute them at runtime where possible.

Parameter	Description
`ENGINE='mysql'`	Specifies MySQL as the storage engine for the external table.
`TABLE_PROPERTIES`	A JSON object that defines how AnalyticDB for MySQL connects to RDS for MySQL.
`url`	The VPC endpoint of the RDS for MySQL instance and the source database name. Format: `"jdbc:mysql://<hostname>:3306/<database-name>"`. To find your endpoint, see View or modify internal and public endpoints and ports.
`tablename`	The name of the source table in RDS for MySQL.
`username`	The database account for the RDS for MySQL instance.
`password`	The password for the database account.
`charset`	The character set. Valid values: `gbk`, `utf8` (default), `utf8mb4`.

CREATE TABLE IF NOT EXISTS adb_demo.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"
}';

Key parameters:

Create a destination table to store the imported data.

CREATE TABLE IF NOT EXISTS adb_demo.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);

Import data from the external table into the destination table.

REPLACE INTO adb_demo.mysql_import_test
SELECT * FROM adb_demo.goods_external_table;

Verify the import.

SELECT * FROM adb_demo.mysql_import_test LIMIT 100;

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

What's next

To write results back to RDS for MySQL, create an external table pointing to the target RDS table and use INSERT INTO ... SELECT to push data from AnalyticDB for MySQL into RDS for MySQL.
For the full external table DDL reference, see CREATE EXTERNAL TABLE.