Tablestore:Use the SQL query feature in the Tablestore console

Prerequisites

• If you want to query data as a 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 that is attached to the RAM user to grant all SQL operation permissions to the RAM user. For more information, see Use a RAM policy to grant permissions to a RAM user.

• A data table is created.

Usage notes

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

Step 1: Create a mapping table

1. Log on to the Tablestore console.

2. In the top navigation bar, select a resource group and a region.

3. 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.

4. 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 a mapping table for a table and Create mapping tables for search indexes.

   1. Click the icon.

      Note

      If no mapping table is created, the Create Mapping Table dialog box appears when you click the Query by Executing SQL Statement tab.

      

   2. In the Create Mapping Table dialog box, configure the parameters. The following table describes the parameters.

      Parameter	Description
      Table Type	The type of the table for which you want to create the mapping table. Valid values: Common Table: creates the mapping table for a data table. This is the default value. 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: creates the mapping table for an existing data table. This is the default value. Mapping Table for Search Index: creates the mapping table for an existing search index.
      Advanced Settings	Specifies options for 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: The query results are in eventual consistency mode. This is the default value. 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: improves the query performance by compromising the accuracy of aggregate operations. This is the default value. No: does not improve the query performance by compromising the accuracy of aggregate operations.
      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.

   3. 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 in SQL.

   4. 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.

Click Data Export to export data. You can export and save a maximum of 2,000 data entries to a local CSV file.

FAQ

• How do I select a query method when I use SQL to query data?

• How do I troubleshoot common errors of SQL queries?

• What do I do if the "The sql scanned rows of main table exceeds the quota, main table rows quota is 100000" error message appears when I use SQL to query data?