AnalyticDB:Query and import Tablestore data

Sample data

Procedure

1. Go to the SQL editor.

   1. Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. On the Data Lakehouse Edition tab, find the cluster that you want to manage and click the cluster ID.

   2. In the left-side navigation pane, choose Job Development > SQL Development.

2. Create an external database.

   In this example, a database named adb_external_db is created. Sample statement:

   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 information about data type mappings, see the "Data type mappings" section of this topic.

   In this example, a table named person is created in the adb_external_db database. Sample statement:

   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 All 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 All 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 use the same quantity and order of fields as the external table that is 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
      )
      DISTRIBUTED 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 the INSERT IGNORE INTO statement. 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 statement to import data. If the primary key has duplicate values, the original value is overwritten by the new value.

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

      • Method 3: Asynchronously execute the INSERT OVERWRITE INTO statement to import data. In most cases, SUBMIT JOB is used to submit an asynchronous job. You can add a hint (/*+ direct_batch_load=true*/) before the data import statement to accelerate the job. For more information, see the "Asynchronous writing" section of the INSERT OVERWRITE SELECT topic.

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

        The following information is returned:

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

        For information about how to submit asynchronous jobs, see Asynchronously submit an import job.

Data type mappings