AnalyticDB:Import MaxCompute data using external tables

Enterprise, Basic, and Data Lakehouse editions

By default, an AnalyticDB for MySQL cluster uses the Tunnel Record API to access and import data from MaxCompute. To use the Tunnel Arrow API, you must first enable the Arrow API feature. After it is enabled, the AnalyticDB for MySQL cluster uses the Tunnel Arrow API for data import.

Data warehouse edition

1. Connect to the AnalyticDB for MySQL cluster. For more information, see Connect to an AnalyticDB for MySQL cluster.

2. Create a destination database.

   CREATE database test_adb;

3. Create a MaxCompute external table. This topic uses odps_nopart_import_test_external_table as an example.

   CREATE TABLE IF NOT EXISTS odps_nopart_import_test_external_table
   (
       id int,
       name string,
       age int,
       dt string
   )
    ENGINE='ODPS'
    TABLE_PROPERTIES='{
    "endpoint":"http://service.cn.maxcompute.aliyun-inc.com/api",
    "accessid":"yourAccessKeyID",
    "accesskey":"yourAccessKeySecret",
    "partition_column":"dt",
    "project_name":"odps_project1",
    "table_name":"odps_nopart_import_test"
    }';                 

   Parameter	Description
   ENGINE=’ODPS’	The storage engine of the external table. To read data from and write data to MaxCompute, set this parameter to ODPS.
   endpoint	The endpoint of MaxCompute. Note AnalyticDB for MySQL can access MaxCompute only through a MaxCompute VPC endpoint. To find the VPC endpoint for each region, see VPC Endpoint.
   accessid	The AccessKey ID of an Alibaba Cloud account or a RAM user that has permissions to access MaxCompute. For information about how to obtain an AccessKey ID and an AccessKey Secret, see Obtain an AccessKey pair.
   accesskey	The AccessKey Secret of an Alibaba Cloud account or a RAM user that has permissions to access MaxCompute. For information about how to obtain an AccessKey ID and an AccessKey Secret, see Obtain an AccessKey pair.
   partition_column	The example in this topic is for creating a partitioned table, so you need to configure partition_column. If the MaxCompute table is a non-partitioned table, you must also create a non-partitioned table in AnalyticDB for MySQL. In this case, you do not need to configure partition_column.
   project_name	The name of the workspace in MaxCompute.
   table_name	The name of the source table in MaxCompute.

4. In the test_adb database, create the adb_nopart_import_test table to store data imported from MaxCompute.

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

5. Import the data.

   • Method 1: Run INSERT INTO to import data. If a primary key is duplicated, the new data is automatically ignored and no update is performed. This is equivalent to INSERT IGNORE INTO. For more information, see INSERT INTO. The following is an example:

     INSERT INTO adb_nopart_import_test
     SELECT * FROM odps_nopart_import_test_external_table; 

     Query the data written to the table:

     SELECT * FROM adb_nopart_import_test;

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

     If you need to import data from a specific partition into adb_nopart_import_test, run the following command:

     INSERT INTO adb_nopart_import_test
     SELECT * FROM odps_nopart_import_test_external_table
     WHERE dt = '202207';

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

     INSERT OVERWRITE adb_nopart_import_test
     SELECT * FROM odps_nopart_import_test_external_table;

   • Method 3: Asynchronously import data by using INSERT OVERWRITE. Typically, you can use SUBMIT JOB to submit an asynchronous job. The job is then scheduled by the backend. You can add a hint before the write job to accelerate it. For more information, see Asynchronous writes. The following is an example:

     SUBMIT JOB 
     INSERT OVERWRITE adb_nopart_import_test 
     SELECT * FROM odps_nopart_import_test_external_table;  

     The following result is returned:

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

     For more information about submitting asynchronous jobs, see Submit an asynchronous import job.