Query and import data from Tablestore

Sample data

Procedure

1. Go to the SQL Development 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. Find the cluster that you want to manage and click the cluster ID.

   2. In the navigation pane on the left, navigate to Job Development > SQL Development.

2. Create an external database.

   The following is a sample statement for creating the adb_external_db database:

   CREATE EXTERNAL database adb_external_db;

3. Create an external table.

   Note

   The external table in AnalyticDB for MySQL must have the same column names, number of columns, and column order as the table in Tablestore. The column data types must be compatible. For more information about data type mapping, see Data type mapping.

   In the adb_external_db database, create the person table. The sample statement is as follows:

   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 data from and write data to Tablestore, set the value 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 endpoint of the Tablestore instance. To view the VPC endpoint, log on to the Tablestore console and go to the All Instances page.

4. After the external table is created, AnalyticDB for MySQL automatically maps the table data from Tablestore to an AnalyticDB for MySQL table. You can query the data in the person table of Tablestore by using a SELECT statement in AnalyticDB for MySQL.

   SELECT * FROM adb_external_db.person;

   Expected output:

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

5. If you want to import data from Tablestore into AnalyticDB for MySQL, follow these steps.

   1. Create a database in AnalyticDB for MySQL:

      CREATE DATABASE adb_demo; 

   2. Create a table in AnalyticDB for MySQL to store the imported data:

      Note

      The new table must have the same column order and number of columns as the external table created in Step 3. The column data types must be compatible.

      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: You can use the INSERT INTO statement to import data. If a primary key is duplicated, the current write operation is automatically ignored and no update is performed. This operation is equivalent to INSERT IGNORE INTO. For more information, see INSERT INTO. The following is an example:

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

        Query the destination table. Example:

        SELECT * FROM adb_demo.adb_import_test;

        Expected output:

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

      • Method 2: Run the INSERT OVERWRITE statement to import data, overwriting the existing data in the table. The following is an example:

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

      • Method 3: Asynchronously import data by using an INSERT OVERWRITE statement. You can use SUBMIT JOB to submit an asynchronous job, which is then scheduled by the backend. You can add a Hint (/*+ direct_batch_load=true*/) before the write task to accelerate it. For more information, see Asynchronous write. The following is an example:

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

        The statement returns a job ID:

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

        For more information on submitting asynchronous jobs, see Asynchronously submit an import job.

Data type mapping