Tablestore can store large amounts of structured data and supports various types of index schemas for accelerated query and analytics in different scenarios. When you use the SQL query feature, you can perform index-based queries by using explicit access to a secondary index table. Tablestore provides the following methods to query data by using a search index: automatic selection of a search index and explicit access to a search index.

Note For more information about secondary indexes and search indexes, see Overview of secondary indexes and Overview of search indexes.

Use secondary index tables

Notice Secondary indexes cannot be automatically selected for data queries.
Tablestore supports only explicit access to secondary index tables. If you want to access a secondary index table by using explicit access to the secondary index table, perform the following steps:
  1. Execute the CREATE TABLE statement to create a mapping table for the secondary index table that you want to access. For more information, see Create mapping tables for tables.
  2. Execute the SELECT statement to query data. For more information, see Query data.

Use search indexes

When you use SQL to perform complex queries such as queries based on non-primary key columns and Boolean queries, we recommend that you create a search index for the data table whose data you want to query. After the search index is created, you can use one of the following methods to query data by using the search index in SQL:

  • Explicit access to a search index

    If you want to use the specified search index to query data, you can perform the following operations to use this method:

    1. Execute the CREATE TABLE statement to create a mapping table for the search index. For more information, see Create mapping tables for search indexes.
    2. Execute the SELECT statement to query data. For more information, see Query data.
  • Automatic selection of a search index
    Notice If you specify that the query results must be in strong consistency mode or query performance cannot be improved by compromising the accuracy of aggregate operations when you create a mapping table for the table whose data you want to query, Tablestore does not automatically select a search index for data query.

    When the search index that you want to access is not explicitly specified, if all filtering columns in the WHERE clause and return columns in the SELECT statement are contained in a search index, Tablestore automatically selects the search index for data query. For example, in the Select A,B,C from exampletable where A=XXX and D = YY; statement, if the A, B, C, and D columns are contained in a search index of the exampletable table, Tablestore automatically selects the search index for data query.

If SQL statements that combine GROUP BY clauses and aggregate functions match the aggregation capability of the Search API operation of a search index, Tablestore also identifies operators and pushes them down to the search index. For more information about operator pushdown, see Computing pushdown.

Mapping between SQL expressions and Search queries in search indexes

SQL expression Example Search query
without predicate N/A Match all query
=
  • a = 1
  • b = "hello world"
 Term query
> a > 1 Range query
>= a >= 2
< a < 5
<= a <= 10
is null a is null Exists query
is not null a is not null
and a = 1 and b = "hello world" Boolean query
or a > 1 or b = 2
not not a = 1
!= a !=1
like a like "%s%" Wildcard query
in a in (1,2,3) Terms query
text_match text_match("a", "tablestore cool") Match query
text_match_phrase text_match_phrase("a", "tablestore cool") Match phrase query