Use SQL queries in the Tablestore console

Prerequisites

If you use a RAM user, grant the "Action": "ots:SQL*" permission.
A data table

Step 1: Create a mapping table

Log on to the Tablestore console. Switch the region and resource group at the top of the page as needed.
On the Overview page, click the instance name or click Instances.
Click the Query by Executing SQL Statement tab, and then click the add icon in the left-side tree to open the Create Mapping Table dialog box.

Note
If no mapping table exists, the Create Mapping Table dialog box automatically appears. Alternatively, create a mapping table with a CREATE TABLE statement in the SQL editor. For more information, see DDL operations.

Configure the following parameters in the dialog box.

Parameter	Description
Table Type	Valid values: Common Table (Default): Create a mapping for a data table. Time Series Table: Create a mapping for a time series table.
Table Name	Select the data table or time series table for which you want to create a mapping.
Mapping Mode	This parameter is available only when Table Type is set to Common Table. Valid values: Mapping Table for Table (Default): Create a mapping for an existing data table. Mapping Table for Search Index: Create a mapping for an existing search index.
Advanced Settings	This parameter is available only when Mapping Mode is set to Mapping Table for Table. Turn on this switch to configure the following settings: Consistency Mode: Eventual Consistency (Default. New data becomes queryable within seconds.) or Strong Consistency (New data becomes queryable immediately.). Inaccurate Aggregation: Yes (Default. Trades precision for better performance.) or No.
Search Index Name	This parameter is available only when Mapping Mode is set to Mapping Table for Search Index. Select the search index to bind to the mapping table.
Mapping Table Name	The name of the mapping table. In table mapping mode, the name matches the data table name and cannot be changed. In search index mapping mode or time series table mode, enter a custom name.
Enable Analytical Store	This parameter is available only when Table Type is set to Time Series Table. Turn on this switch to use Analytical Store to store and analyze time series data at lower cost. Important If you created an SQL mapping table with Enable Analytical Store enabled, you must delete the analytical store SQL mapping table before you delete the analytical store. Otherwise, the deletion fails.

Click Next: Generate SQL Statement.

The SQL statement to create the mapping table is automatically generated. Example:
```
CREATE TABLE `test_table` (
    `pk1` VARCHAR(1024),
    `pk2` BIGINT(20),
    `col_a` BIGINT(20),
    `col_b` BIGINT(20),
    `col_c` MEDIUMTEXT,
    `col_d` MEDIUMTEXT,
    PRIMARY KEY(`pk1`, `pk2`)
);
```
Note
- The schema must include all columns that you plan to query.
- Make sure the data types in the mapping match the data types in the data table. For more information about data type mappings, see Data type mapping.
Modify the mapping table schema as needed, select the SQL statement, and then click Execute SQL Statement.

After the statement runs, the result appears in the Execution Result section.

Note
You can select and run only one SQL statement at a time. If no statement is selected, the first statement runs by default.

Step 2: Query data

After you create a mapping table, run SELECT statements on the Query by Executing SQL Statement tab. For more information about SQL syntax, see DQL statements.

Example: Query the first three rows of test_table.

SELECT * FROM test_table LIMIT 3;

Sample output:

+------+-----+-------+-------+--------+--------+
| pk1  | pk2 | col_a | col_b | col_c  | col_d  |
+------+-----+-------+-------+--------+--------+
| row1 |   1 |    10 |   100 | apple  | extra1 |
| row2 |   2 |    20 |   200 | banana | extra2 |
| row3 |   3 |    30 |   300 | apple  | extra3 |
+------+-----+-------+-------+--------+--------+
3 Rows

To export results, click Data Export. Up to 2,000 rows can be exported as a CSV file.

Prerequisites

Step 1: Create a mapping table

Step 2: Query data

References