AnalyticDB for MySQL:Use external tables to import data to Data Lakehouse Edition

Sample data

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

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

Procedure

1. Go to the SQL editor.
   1. Log on to the AnalyticDB for MySQL console.
   2. In the upper-left corner of the page, select the region where the cluster resides.
   3. In the left-side navigation pane, click Clusters.
   4. On the Data Lakehouse Edition (V3.0) tab, find the cluster and click the Cluster ID.
   5. In the left-side navigation pane, choose Job Development > SQL Development.
2. Select the XIHE engine and an interactive resource group.
3. Execute the following statement to create an external database:

   CREATE EXTERNAL DATABASE adb_external_db;

4. Execute the following statement to create an external table.
   Note

   • The AnalyticDB for MySQL external table must have the same names, quantity, 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"
   }';

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

   SELECT * FROM adb_external_db.person;

   The following information is returned:

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

6. Write data to the ApsaraDB RDS for MySQL table.

   INSERT INTO adb_external_db.person SELECT 5, 'lily', 20;

7. Execute the following statement to create a destination database named adb_demo:

   CREATE DATABASE adb_demo;

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

9. 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;

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

    SELECT * FROM mysql_import_test;

    The following information is returned:

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