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

Go to the SQL editor.

Create an external database.

CREATE EXTERNAL DATABASE adb_external_db;

Create an external table. In this example, an external table named test_adb is used.

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_db.test_adb (
    id int,
    name varchar(1023),
    age int,
    dt string
    ) ENGINE='ODPS'
TABLE_PROPERTIES='{
"accessid":"LTAILd4****",
"endpoint":"http://service.cn-hangzhou.maxcompute.aliyun.com/api",
"accesskey":"4A5Q7ZVzcYnWMQPysX****",
"partition_column":"dt",
"project_name":"test_adb",
"table_name":"person"
}';

Query data.

SELECT * FROM adb_external_db.test_adb;

Sample result:

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

Perform the following steps to import data from MaxCompute to AnalyticDB for MySQL:

1. Create a database in the AnalyticDB for MySQL cluster.

CREATE DATABASE adb_demo; 

2. Create a table in the AnalyticDB for MySQL database to store data that is imported from MaxCompute.

Note

The created table must use the same quantity and order of fields as the external table that is created in Step 3. The data types of fields must be compatible between the two tables.

CREATE TABLE IF NOT EXISTS adb_demo.adb_import_test(
    id int,
    name string,
    age int,
    dt string
    PRIMARY KEY(id,dt)
)
DISTRIBUTE BY HASH(id)  
PARTITION BY VALUE('dt'); 

3. Write data to the table.

• Method 1: Execute the INSERT INTO statement to import data. If the primary key has duplicate values, data is not repeatedly inserted and the INSERT INTO statement is equivalent to the INSERT IGNORE INTO statement. For more information, see INSERT INTO.

INSERT INTO adb_demo.adb_import_test
SELECT * FROM adb_external_db.test_adb;

If you want to import data from a specific partition to the adb_demo.adb_import_test table, you can execute the following statement:

INSERT INTO adb_demo.adb_import_test
SELECT * FROM adb_external_db.test_adb 
WHERE dt = '202207'; 

• Method 2: Execute the INSERT OVERWRITE INTO statement to import data. If the primary key has duplicate values, the original value is overwritten by the new value.

INSERT OVERWRITE INTO adb_demo.adb_import_test
SELECT * FROM adb_external_db.test_adb;

• Method 3: Execute the INSERT OVERWRITE INTO statement to asynchronously import data. Typically, the SUBMIT JOB statement is used to submit an asynchronous job. You can add a hint (/* direct_batch_load=true*/) before the data import statement to accelerate the job. For more information, see the "Asynchronous writing" section of the INSERT OVERWRITE SELECT topic.

SUBMIT job 
INSERT OVERWRITE INTO adb_demo.adb_import_test
SELECT * FROM adb_external_db.test_adb;

Sample result:

+---------------------------------------+
| job_id                                |
+---------------------------------------+
| 2020112122202917203100908203303****** |
+---------------------------------------+

• For information about how to asynchronously submit a job, see Asynchronously submit an import job.

Go to the SQL editor.

Create a database. If you have already created a database, skip this step.

CREATE DATABASE adb_demo; 

Create an external table.

CREATE TABLE IF NOT EXISTS test_adb
(
    id int,
    name string,
    age int,
    dt string
)
 ENGINE='ODPS'
 TABLE_PROPERTIES='{
 "endpoint":"http://service.cn-hangzhou.maxcompute.aliyun-inc.com/api",
 "accessid":"LTAILd4****",
 "accesskey":"4A5Q7ZVzcYnWMQPysX****",
 "partition_column":"dt",
 "project_name":"test_adb",
 "table_name":"person"
 }';                 

For information about the external table parameters, see the parameter table in the Use external tables to import data to Data Warehouse Edition topic.

Query data.

SELECT * FROM adb_demo.test_adb;

Sample result:

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

Create a table in the AnalyticDB for MySQL database to store data that is imported from MaxCompute.

CREATE TABLE IF NOT EXISTS adb_import_test
(   id int,
    name string,
    age int,
    dt string,
    PRIMARY KEY(id,dt)
)
DISTRIBUTE BY HASH(id)
PARTITION BY VALUE('dt') LIFECYCLE 30;  

Import data.

(Optional) Check whether the submitted job is an elastic import job.

SELECT job_name, (job_type = 3) AS is_elastic_load FROM INFORMATION_SCHEMA.kepler_meta_async_jobs WHERE job_name = "2023081818010602101701907303151******";

Sample result:

+---------------------------------------+------------------+
| job_name                              | is_elastic_load  |
+---------------------------------------+------------------+
| 2023081517195203101701907203151****** |       1          |
+---------------------------------------+------------------+

If an elastic import job is submitted, 1 is returned for the is_elastic_load parameter. If a regular import job is submitted, 0 is returned.