AnalyticDB:Query and import Tablestore data

Step 1: Create an external database

CREATE EXTERNAL DATABASE adb_external_db;

Step 2: Create an external table

The external table must match the Tablestore table in field names, count, and order. Field data types must also be compatible. For data type mappings, see Data type mappings.

The following example creates 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"
}';

Step 3: Query the data

After the external table is created, AnalyticDB for MySQL automatically maps data from the Tablestore table. Run the following SELECT statement to query the data:

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)

Step 1: Create a database

CREATE DATABASE adb_demo;

Step 2: Create a destination table

The destination table must have the same field count and order as the external table. Field 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);

Step 3: Write data

Choose a write method based on how you want to handle duplicate primary keys:

Method 1: INSERT INTO (skip duplicates)

If a row with a duplicate primary key already exists, the row is skipped.

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

Verify the result:

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: INSERT OVERWRITE (replace duplicates)

If a row with a duplicate primary key already exists, the existing row is overwritten.

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

Method 3: SUBMIT JOB (asynchronous import)

In most cases, use SUBMIT JOB to run the import asynchronously. You can add the /*+ direct_batch_load=true*/ hint to accelerate the job.

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

The statement returns a job ID:

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

To monitor the asynchronous job, see Asynchronously submit an import job.