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

Prerequisites

  • If you want to use a RAM user to perform operations, a RAM user is created and all SQL operation permissions are granted to the RAM user. You can configure "Action": "ots:SQL*" in the custom policy to grant all SQL operation permissions to the RAM user. For more information, see Grant permissions to a RAM user.
  • A data table is created.

Usage notes

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

Create a mapping table

  1. Log on to the Tablestore console.
  2. In the top navigation bar, select a region. Example: China (Hangzhou) or China (Shenzhen).
  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 that you want to manage.
  4. On the Query by Executing SQL Statement tab, create a mapping table.
    Note You can also compile a SQL statement to create a mapping table. For more information, see Create mapping tables for tables and Create mapping tables for search indexes.
    1. Click the fig_createtablevitural icon.
      Note If no mapping table is created, the Create Mapping Table dialog box is displayed when you click the Query by Executing SQL Statement tab.
      fig_createtablemapping
    2. In the Create Mapping Table dialog box, configure the parameters. The following table describes the parameters.
      Parameter Description
      Table name The name of the data table for which you want to create the mapping table if you select Table mapping for Mapping mode or the name of the data table whose search index for which you want to create the mapping table if you select Multivariate Index mapping for Mapping mode.
      Mapping mode The mode in which the mapping table is created. Default value: Table mapping. Valid values:
      • Table mapping: creates a mapping table for an existing data table.
      • Multivariate Index mapping: creates a mapping table for an existing search index.
      Advanced options Specifies options for the consistency mode and whether to enable inaccurate aggregation for the mapping table. You can turn on Advanced options to configure the Consistency mode and Inaccurate aggregation parameters. The Advanced options parameter is available only if you select Table mapping for Mapping mode.
      Consistency mode The consistency mode that is supported by the execution engine. Default value: Final agreement. Valid values:
      • Final agreement: 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.

      You can configure this parameter only if you turn on Advanced options.

      Inaccurate aggregation Specifies whether query performance can be improved by compromising the accuracy of aggregate operations. Default value: Yes. Valid values:
      • Yes
      • No

      You can configure this parameter only if you turn on Advanced options.

      Search index table The name of the search index for which you want to create the mapping table. You can configure this parameter only if you select Multivariate Index mapping for Mapping mode.
      Map table name The name of the mapping table that you want to create.
      • If you select Table mapping for Mapping mode, the value of this parameter is the same as the value of the Table name parameter and cannot be modified.
      • If you select Multivariate Index mapping for Mapping mode, specify a value for this parameter.
    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 field data types in the mapping table match the field data types in the data table. For information about data type mappings, see Data type mappings.
    4. After you modify the schema of the mapping table based on your business requirements, hold down the left mouse button to select an 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 the mapping table, the schema that you specify for the mapping table must include the columns that are required for subsequent data query operations.
      • Before you execute an SQL statement, select the SQL statement that you want to execute. If you do not select an SQL statement, the system executes the first SQL statement.
      • You cannot execute multiple SQL statements at a time. If you execute multiple SQL statements at a time, the system reports an error.
      fig_sqlquery

Query data

After a 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.