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

This topic describes how to import data from ApsaraDB RDS for MySQL to AnalyticDB for MySQL to implement multi-source data integration and complex query analysis. You can also write the summarized metrics to ApsaraDB RDS for MySQL.

Prerequisites

An AnalyticDB for MySQL Data Lakehouse Edition cluster is created.
An ApsaraDB RDS for MySQL instance is created in the same virtual private cloud (VPC) as the AnalyticDB for MySQL cluster.
Elastic Network Interface (ENI) is turned on in the Network Information section of the Cluster Information page in the AnalyticDB for MySQL console.
The VPC CIDR block of the AnalyticDB for MySQL cluster is added to an IP address whitelist of the ApsaraDB RDS for MySQL instance. For more information, see Configure an IP address whitelist.

Sample data

In this example, a table named person is created in the test_adb database of the ApsaraDB RDS for MySQL instance.

CREATE TABLE IF NOT EXISTS test_adb.person(
id int,
name varchar(1023),
age int
);

Data is inserted into the person table.

INSERT INTO test_adb.person values
(1, 'james', 10),
(2, 'bond', 20),
(3, 'jack', 30),
(4, 'lucy', 40);

Procedure

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. On the Data Lakehouse Edition tab, 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.
Select the XIHE engine and an interactive resource group.
Execute the following statement to create an external database:
```
CREATE EXTERNAL DATABASE adb_external_db;
```
Execute the following statement to create an external table.
Note
- The AnalyticDB for MySQL external table must have the same names, number, order, and data types of fields as the ApsaraDB RDS for MySQL table.
- For information about the parameters that are used to create an AnalyticDB for MySQL external table, see CREATE EXTERNAL TABLE.
```
CREATE EXTERNAL TABLE IF NOT EXISTS  adb_external_db.person (
    id int,
    name varchar(1023),
    age int
 )ENGINE = 'MYSQL' TABLE_PROPERTIES = '{
   "url":"jdbc:mysql://mysql-address:3306/test_adb",
   "tablename":"person",
   "username":"mysql-user-name",
   "password":"mysql-user-password",
   "charset":"utf8"
}';
```

Query data.

After the external table is created, you can execute the SELECT statement in the AnalyticDB for MySQL cluster to query data of the person table in the ApsaraDB RDS for MySQL instance.

SELECT * FROM adb_external_db.person;

Sample result:

+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | james |   10 |
|    2 | bond  |   20 |
|    3 | jack  |   30 |
|    4 | lucy  |   40 |
+------+-------+------+
4 rows in set (0.35 sec)

Execute the following statement to write data to the ApsaraDB RDS for MySQL table:
```
INSERT INTO adb_external_db.person SELECT 5, 'lily', 20;
```
Execute the following statement to create a destination database named adb_demo:
```
CREATE DATABASE adb_demo;
```
Execute the following statement to create a destination table named mysql_import_test in the adb_demo database. The destination table is used to store the data that is imported from the ApsaraDB RDS for MySQL instance.
```
CREATE TABLE IF NOT EXISTS  mysql_import_test (
    id int,
    name varchar(1023),
    age int,
   PRIMARY KEY (id)
 )
DISTRIBUTED BY HASH(id);
```
Execute the following statement to import data from the ApsaraDB RDS for MySQL instance to the AnalyticDB for MySQL cluster:
```
INSERT INTO mysql_import_test
SELECT * FROM adb_external_db.person;
```

Execute the following statement to query data in the mysql_import_test table of the AnalyticDB for MySQL cluster:

SELECT * FROM mysql_import_test;

Sample result:

+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | james |   10 |
|    2 | bond  |   20 |
|    4 | lucy  |   40 |
|    5 | lily  |   20 |
|    3 | jack  |   30 |
+------+-------+------+
5 rows in set (0.37 sec)