AnalyticDB:Import MaxCompute data by using external tables

Navigate to the SQL editor.

Create an external database. The following sample statement is used:

CREATE EXTERNAL DATABASE adb_external_db;

Create an external table. This topic uses test_adb as an example.

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":"yourAccessKeyID",
"endpoint":"http://service.cn-hangzhou.maxcompute.aliyun.com/api",
"accesskey":"yourAccessKeySecret",
"partition_column":"dt",
"project_name":"odps_project",
"table_name":"odps_nopart_import_test"
}';

Query the data.

SELECT * FROM adb_external_db.test_adb;

The following result is returned:

+------+-------+------+---------+
| id   | name  | age  |   dt    |
+------+-------+------+---------+
|    1 | james |   10 |  202207 |
|    2 | bond  |   20 |  202207 |
|    3 | jack  |   30 |  202207 |
|    4 | lucy  |   40 |  202207 |
+------+-------+------+---------+

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

1. Create a database in AnalyticDB for MySQL. The following statement is an example:

   CREATE DATABASE adb_demo; 

2. Create a table in AnalyticDB for MySQL to store the data imported from MaxCompute. The following statement is an example:

   Note

   The new table and the external table that you created in Step 3 must have the same field order and number of fields. The data types of the fields must be compatible.

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

3. Write data to the table. The following sample statements are used:

   • Method 1: Execute an `INSERT INTO` statement to import data. If a duplicate primary key exists, the data is automatically ignored and not updated. This operation is equivalent to INSERT IGNORE INTO. For more information, see INSERT INTO. The following statement is an example:

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

     To import data from a specific partition into adb_demo.adb_import_test, you can run the following statement:

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

   • Method 2: Run an `INSERT OVERWRITE INTO` statement to import data. This statement overwrites the existing data in the table. The following statement is an example:

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

   • Method 3: Asynchronously execute an `INSERT OVERWRITE INTO` statement to import data. You can use the SUBMIT JOB command to submit an asynchronous task. The task is scheduled by the backend. You can add a hint (/*+ direct_batch_load=true*/) before the write task to accelerate the task. For more information, see Asynchronous writes. The following statement is an example:

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

     The following result is returned:

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

     For more information, see Submitting import tasks asynchronously.

Navigate to the SQL editor.

Create a database. You can skip this step if a database already exists. The following statement is an example:

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":"yourAccessKeyID",
 "accesskey":"yourAccessKeySecret",
 "partition_column":"dt",
 "project_name":"odps_project",
 "table_name":"odps_nopart_import_test"
 }';                 

For more information about the parameters that you can set for external tables, see Parameter descriptions.

Query the data.

SELECT * FROM adb_demo.test_adb;

The following result is returned:

+------+-------+------+---------+
| id   | name  | age  |   dt    |
+------+-------+------+---------+
|    1 | james |   10 |  202207 |
|    2 | bond  |   20 |  202207 |
|    3 | jack  |   30 |  202207 |
|    4 | lucy  |   40 |  202207 |
+------+-------+------+---------+

Create a table in AnalyticDB for MySQL to store the data imported from MaxCompute. The following statement is an example:

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

Import data.

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

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

The result is as follows:

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

The return value of is_elastic_load is 1 for an elastic import task and 0 for a regular import task.