How to use the SQL query feature in the Tablestore console - Tablestore

Tablestore allows you to use the SQL query feature to efficiently query data. After you create a mapping table in the Tablestore console, you can execute the SELECT statement to efficiently query data in the data table for which the mapping table is created.

Prerequisites

If you want to query data as a Resource Access Management (RAM) user, a RAM user is created and all SQL operation permissions are granted to the RAM user. You can configure "Action": "ots:SQL*" in a custom policy to grant all SQL operation permissions to the RAM user. For more information, see Configure user permissions.
A data table is created.

Usage notes

The SQL query feature is available in the China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Hohhot), China (Ulanqab), China (Shenzhen), China (Chengdu), China (Hong Kong), Germany (Frankfurt), US (Virginia), India (Mumbai), Indonesia (Jakarta), SAU (Riyadh), and Singapore regions.

Step 1: Create a mapping table

Log on to the Tablestore console.
In the top navigation bar, select a region. Example: China (Hangzhou) or China (Shenzhen).
On the Overview page, click the name of the instance that you want to manage or click Manage Instance in the Actions column of the instance.

On the Query by Executing SQL Statement tab, create a mapping table.

Note

You can also directly write an SQL statement to create a mapping table. For more information, see Create mapping tables for tables and Create mapping tables for search indexes.

Click the icon.
Note
If no mapping table has been created, the Create Mapping Table dialog box appears when you click the Query by Executing SQL Statement tab.

In the Create Mapping Table dialog box, configure the parameters that are described in the following table.

Parameter	Description
Table Type	The type of the table for which you want to create the mapping table. Valid values: Common Table (default): creates the mapping table for a data table. Time Series Table: creates the mapping table for a time series table.
Table Name	The name of the table for which you want to create the mapping table.
Mapping Mode	The mode in which the mapping table is created. This parameter is available only if you set the Table Type parameter to Common Table. Valid values: Mapping Table for Table (default): creates the mapping table for an existing data table. Mapping Table for Search Index: creates the mapping table for an existing search index.
Advanced Settings	Specifies the consistency mode and whether to enable inaccurate aggregation for the mapping table. You can turn on Advanced Settings to configure the Consistency Mode and Inaccurate Aggregation parameters. The Advanced Settings parameter is available only if you set the Mapping Mode parameter to Mapping Table for Table.
Consistency Mode	The consistency mode that is supported by the execution engine. This parameter is available only if you turn on Advanced Settings. Valid values: Eventual Consistency (default): The query results are in eventual consistency mode. You can query data a few seconds after the data is written to the table. Strong Consistency: The query results are in strong consistency mode. You can query data immediately after the data is written to the table.
Inaccurate Aggregation	Specifies whether to improve the query performance by compromising the accuracy of aggregate operations. This parameter is available only if you turn on Advanced Settings. Valid values: Yes (default) No
Search Index Name	The name of the search index for which you want to create the mapping table. This parameter is available only if you set the Mapping Mode parameter to Mapping Table for Search Index.
Mapping Table Name	The name of the mapping table that you want to create. If you set the Table Type parameter to Common Table and the Mapping Mode parameter to Mapping Table for Table, the name of the mapping table is the same as the name of the data table and cannot be modified. If you set the Table Type parameter to Common Table and the Mapping Mode parameter to Mapping Table for Search Index, enter a name for the mapping table. If you set the Table Type parameter to Time Series Table, enter a name for the mapping table based on your business requirements. After a mapping table is created for a time series table, the system automatically adds the `Time series table name::` prefix to the mapping table name.

Click Generate SQL Statement.
The system automatically generates the SQL statement to create the mapping table. Sample SQL statement:
```
CREATE TABLE `exampletable` (
    `id` BIGINT(20),
    `colvalue` MEDIUMTEXT,
    `content` MEDIUMTEXT,
    PRIMARY KEY(`id`)
);
```
Important
Make sure that the data types of fields in the mapping table match the data types of fields in the data table. For more information about data type mappings, see Data type mappings.
After you modify the schema of the mapping table based on your business requirements, drag-select the SQL statement and click Execute SQL Statement(F8).
After the execution is successful, the execution result is displayed in the Execution Result section.
Important
- When you create a mapping table, the schema that you specify for the mapping table must include the columns that are required for subsequent data query operations.
- You must drag-select the SQL statement that you want to execute. If you do not drag-select an SQL statement, the system executes the first SQL statement.
- You can drag-select only one SQL statement to execute at a time. If you execute multiple SQL statements at a time, the system reports an error.

Step 2: Query data

After the mapping table is created, you can execute the SELECT statement to query data on the Query by Executing SQL Statement tab. For more information, see Query data.