This topic describes how to query data in and import Tablestore data to AnalyticDB for MySQL Data Lakehouse Edition (V3.0).
Prerequisites
- A Tablestore instance is created. For more information, see Use Tablestore.
- An AnalyticDB for MySQL Data Lakehouse Edition (V3.0) cluster is created. For more information, see Create a Data Lakehouse Edition (V3.0) cluster. Note If a virtual private cloud (VPC) is bound to the created Tablestore instance, make sure that the AnalyticDB for MySQL cluster is created in the same VPC.
Background information
Tablestore is a NoSQL database service developed by Alibaba Cloud. It uses a variety of data models and supports search index queries. You can call Tablestore API operations in AnalyticDB for MySQL to query data, such as point query and range query. In addition, Tablestore can intelligently select a primary table, secondary index table, and search index table. This significantly improves query performance and reduces user costs.
A Tablestore instance is an entity used to manage tables and data in Tablestore. Each instance is equivalent to a database. A Tablestore instance corresponds to an AnalyticDB for MySQL schema or database. If no VPC is bound to the Tablestore instance, you can directly access the Tablestore data from AnalyticDB for MySQL. If a VPC is bound to the Tablestore instance, make sure that the AnalyticDB for MySQL cluster is created in the same VPC.
Description of sample data
person
is created in Tablestore, and four entries are inserted into the table. If you have created source data, skip this step. id (primary key) | name | age |
---|---|---|
1 | james | 10 |
2 | bond | 20 |
3 | jack | 30 |
4 | lucy | 40 |
Procedure
- Go to the SQL editor.
- Log on to the AnalyticDB for MySQL console.
- In the upper-left corner of the page, select the region where the cluster resides.
- In the left-side navigation pane, click Clusters.
- On the Data Lakehouse Edition (V3.0) tab, find the cluster and click the Cluster ID.
- In the left-side navigation pane, choose .
- Create an external database. Create a database named
adb_external_db
.create external database adb_external_db;
- 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 theadb_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. - 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)
- Perform the following steps to import data from Tablestore to AnalyticDB for MySQL:
Data type mappings
Data type supported by Tablestore | Data type supported by AnalyticDB for MySQL |
INTEGER (eight bytes) | BIGINT (eight bytes) |
STRING | VARCHAR |
BINARY | BINARY |
DOUBLE | DOUBLE |
BOOLEAN | BOOLEAN |