AnalyticDB:Import MaxCompute data by using external tables

Regular import

1. Open the SQL editor. Log on to the AnalyticDB for MySQL console. In the upper-left corner, select a region. In the left-side navigation pane, click Clusters, find and click the cluster ID, then choose Job Development > SQL Development.

2. Create an external database.

   CREATE EXTERNAL DATABASE adb_external_db;

3. Create an external table that maps to the MaxCompute source table.

   Note

   • The external table columns must match the MaxCompute source table exactly: same field names, same number of fields, same field order, and compatible data types. For all TABLE_PROPERTIES parameters, see CREATE EXTERNAL TABLE.

   Parameter	Description
   ENGINE='ODPS'	Specifies MaxCompute as the storage engine.
   endpoint	The VPC endpoint of MaxCompute. Only VPC endpoints are supported. See VPC endpoints for endpoints by region.
   accessid	The AccessKey ID with read permissions on the MaxCompute project.
   accesskey	The AccessKey secret corresponding to the AccessKey ID.
   partition_column	The partition column name. Omit this parameter if the MaxCompute source table is not partitioned.
   project_name	The name of the MaxCompute workspace.
   table_name	The name of the source table in MaxCompute.

   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"
   }';

   Key parameters:

4. Verify the external table by querying the data.

   SELECT * FROM adb_external_db.test_adb;

   Expected output:

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

5. Create a destination database and table in AnalyticDB for MySQL.

   Note

   The destination table and the external table must have the same field order and number of fields, with compatible data types.

   CREATE DATABASE adb_demo;

   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');

6. Import the data. Choose one of the following methods:

   • Method 1 — `INSERT INTO`: Imports data and ignores rows with duplicate primary keys (equivalent to INSERT IGNORE INTO). See INSERT INTO.

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

     To import from a specific partition only:

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

   • Method 2 — `INSERT OVERWRITE INTO`: Overwrites all existing data in the destination table.

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

   • Method 3 — Asynchronous `INSERT OVERWRITE INTO`: Submits the import as a background job. Add the /*+ direct_batch_load=true*/ hint to accelerate the job. See Asynchronous writes.

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

     The command returns a job ID:

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

     Use the job ID to monitor or cancel the job. See Submit an asynchronous import task.

Elastic import

Elastic import runs the import inside a Serverless Spark job, reducing impact on real-time workloads. It requires Milvus V3.1.10.0 or later and a configured job resource group.

1. Open the SQL editor. Log on to the AnalyticDB for MySQL console. In the upper-left corner, select a region. In the left-side navigation pane, click Clusters, find and click the cluster ID, then choose Job Development > SQL Development.

2. Create a database (skip if one already exists).

   CREATE DATABASE adb_demo;

3. Create an external table.

   Note

   • The external table name must match the MaxCompute project name. Otherwise, the external table creation fails.

   • Elastic import requires creating external tables with CREATE TABLE (not CREATE EXTERNAL TABLE).

   • The external table columns must match the MaxCompute source table exactly: same field names, same number of fields, same field order, and same data types.

   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 TABLE_PROPERTIES parameter descriptions, see Parameter descriptions.

4. Verify the external table by querying the data.

   SELECT * FROM adb_demo.test_adb;

5. Create the destination table in AnalyticDB for MySQL.

   Note

   The destination table and the external table must have the same field names, number of fields, field order, and data types.

   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;

6. Import the data using one of the following methods. Both methods use the elastic_load and elastic_load_configs hint parameters to enable and configure the elastic import job. Set elastic_load to true to enable elastic import (default: false). Use elastic_load_configs to specify configuration parameters enclosed in [ ]; separate multiple parameters with |. elastic_load_configs accepts the following parameters:

   • Method 1 — Synchronous elastic import:

     /*+ elastic_load=true, elastic_load_configs=[adb.load.resource.group.name=resource_group]*/
     INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_demo.test_adb;

   • Method 2 — Asynchronous elastic import:

     Important

     Priority queues are not supported for asynchronous elastic import tasks.

     /*+ elastic_load=true, elastic_load_configs=[adb.load.resource.group.name=resource_group]*/
     SUBMIT JOB INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_demo.test_adb;

     The command returns a job ID. Use it to monitor or cancel the job. See Submit an asynchronous import task.

   Parameter	Required	Description
   adb.load.resource.group.name	Yes	The name of the job resource group that runs the elastic import job.
   adb.load.job.max.acu	No	Maximum resources for the elastic import job. Unit: AnalyticDB Compute Units (ACUs). Minimum: 5 ACUs. Default: number of shards plus 1. To query the number of shards: SELECT count(1) FROM information_schema.kepler_meta_shards;
   spark.driver.resourceSpec	No	Resource type of the Spark driver. Default: small. See the Type column in the Spark resource specifications table.
   spark.executor.resourceSpec	No	Resource type of the Spark executor. Default: large. See the Type column in the Spark resource specifications table.
   spark.adb.executorDiskSize	No	Disk capacity of the Spark executor. Valid values: (0, 100]. Unit: GiB. Default: 10 GiB. See Specify driver and executor resources.

7. (Optional) Verify that the job ran as 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******";

   Expected output:

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

   is_elastic_load = 1 indicates an elastic import task. is_elastic_load = 0 indicates a regular import task.