Import MaxCompute data to AnalyticDB for MySQL

This topic describes how to import MaxCompute data to AnalyticDB for MySQL.

Prerequisites

• Source data is prepared. For more information, see Prepare and Quick Start.

  The following table is created in MaxCompute through the table creation statement. For more information, see Table-level operations. If you have created source data, skip this step.

  CREATE TABLE IF NOT EXISTS odps_nopart_import_test
  (
  uid STRING,
  other STRING
   )
  LIFECYCLE 3;                

  The data within a file named odps_nopart_import_test.txt is imported to a table named odps_nopart_import_test. For more information, see Create tables and import data.

• The preparations for using AnalyticDB for MySQL are completed. For example, a cluster is created and a whitelist is configured. An account and a database are created. For more information, see Quick start of AnalyticDB for MySQL.

Note If AnalyticDB MySQL cluster is in elastic mode, log on to the AnalyticDB MySQL console and view the cluster information. Enable Elastic Network Interface (ENI) in the Network Information section.

1. Connect to an AnalyticDB for MySQL cluster and the target database. For more information, see Connect to a cluster.

   The data in the odps_nopart_import_test table is imported to the test_adb database in AnalyticDB for MySQL in this example.

2. Execute the CREATE TABLE statement to create an external mapping table named odps_nopart_import_test_external_table in the test_adb database. For more information, see CREATE TABLE.

   CREATE TABLE IF NOT EXISTS odps_nopart_import_test_external_table
   (
    uid string,
    other string
   )
    ENGINE='ODPS'
    TABLE_PROPERTIES='{
    "endpoint":"http://service.cn.maxcompute.aliyun-inc.com/api",
    "accessid":"L*******FsE",
    "accesskey":"CcwF********iWjv",
    "project_name":"odps_project1",
    "table_name":"odps_nopart_import_test"
    }'                 

   Parameter	Description
   ENGINE='ODPS'	Specifies the table as an external table whose data engine is MaxCompute.
   TABLE_PROPERTIES	Specifies the connection information used by the AnalyticDB for MySQL database to access the data in MaxCompute.
   endpoint	The endpoint of MaxCompute. Note AnalyticDB for MySQL can access MaxCompute only through the VPC endpoint. For more information, see Configure endpoints.
   accessid	The AccessKey ID used to access the source table in MaxCompute. For more information about how to obtain your accessid and accesskey, see Obtain the AccessKey pair of an account.
   accesskey	The AccessKey secret used to access the source table in MaxCompute.
   project_name	The name of the project in MaxCompute.
   table_name	The name of the source table in MaxCompute.

3. Execute the CREATE TABLE statement to create a destination table named adb_nopart_import_test in the adb_demo database to store data imported from MaxCompute. For more information, see CREATE TABLE.

   CREATE TABLE IF NOT EXISTS adb_nopart_import_test
   (
   uid string,
   other string
   )
   DISTRIBUTE BY HASH(uid);                   

4. Execute the INSERT statement to import data from MaxCompute to AnalyticDB for MySQL.
   • Execute the INSERT INTO statement to import data.

     insert into adb_nopart_import_test
     select * from odps_nopart_import_test_external_table                   

     select * from adb_nopart_import_test
     +---------------+-----------------+
     | uid           | other           |
     +---------------+-----------------+
     | 4             | other4          |
     | 6             | other6          |
     | 5             | other5          |
     | 2             | other2          |
     | 1             | other1          |
     | 3             | other3          |
     | 7             | other7          |
                                 

   • Execute the INSERT OVERWRITE INTO statement to import data asynchronously.

     submit job insert overwrite into adb_nopart_import_test
     select * from odps_nopart_import_test_external_table  
     +---------------------------------------+
     | job_id                                |
     +---------------------------------------+
     | 2020112122202917203100908203303000321 |

     For more information about asynchronous execution, see Submit import or export tasks asynchronously.