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

Description of sample data

Procedure

1. Go to the SQL editor.
   1. Log on to the AnalyticDB for MySQL console.
   2. In the upper-left corner of the page, select the region where the cluster resides.
   3. In the left-side navigation pane, click Clusters.
   4. On the Data Lakehouse Edition (V3.0) tab, find the cluster and click the Cluster ID.
   5. In the left-side navigation pane, choose Job Editor > SQL Development.
2. Create an external database.
   Create a database named adb_external_db.

   create external database adb_external_db;

3. Create an external table.
   Note The AnalyticDB for MySQL external table must have the same names, quantity, and order of fields as the Tablestore table. The data types of fields must be compatible between the two tables. For more information about data type mappings, see the "Data type mappings" section of this topic.

   Create a table named person in the adb_external_db database.

   CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_db.person (
       id int,
       name string,
       age int
   ) ENGINE = 'OTS'  
   TABLE_PROPERTIES = '{
   "mapped_name":"person",
   "location":"https://w0****la.cn-hangzhou.vpc.tablestore.aliyuncs.com"
   }'

   Parameter	Description
   ENGINE='OTS'	The storage engine of the external table. To read and write Tablestore data, set the storage engine to OTS.
   mapped_name	The name of the table in the Tablestore instance. To view the table name, log on to the Tablestore console and go to the Instances page.
   location	The VPC URL of the Tablestore instance. To view the VPC URL, log on to the Tablestore console and go to the Instances page.

4. After an external table is created, AnalyticDB for MySQL automatically maps data from the Tablestore table to the AnalyticDB for MySQL table. Execute the following SELECT statement in AnalyticDB for MySQL to query data of the person table in the Tablestore instance:

   select * from adb_external_db.person;

   The following information is returned:

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

5. Perform the following steps to import data from Tablestore to AnalyticDB for MySQL:
   1. Create a database in AnalyticDB for MySQL.

      CREATE DATABASE adb_demo; 

   2. Create a table in AnalyticDB for MySQL to store data imported from Tablestore.
      Note The created table must have the same quantity and order of fields as the external table 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
      )
      DISTRIBUTE BY HASH(id);                   

   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 INSERT IGNORE INTO. For more information, see INSERT INTO.

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

        Execute the SELECT statement to query the data written to the table.

        select * from adb_demo.adb_import_test;

        The following information is returned:

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

      • 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.person;

      • Method 3: Asynchronously execute the INSERT OVERWRITE INTO statement to import data. Typically, SUBMIT JOB is used to submit an asynchronous task. You can add a hint (/* direct_batch_load=true*/) before the data import statement to accelerate the task. For more information, see Asynchronous writing.

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

        The following information is returned:

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

        For more information about how to submit asynchronous tasks, see Asynchronously submit an import or export task.

Data type mappings