Tablestore can store large volumes 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.
Use secondary index tables
Secondary indexes cannot be automatically selected for data queries. You must manually write SQL statements to create a mapping table for a secondary index table.
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 operations:
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 a mapping table for a table.
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 you create a search index, you can use one of the following methods to query data by using the search index in SQL:
Automatic selection of a search index
If you specify that the query results must be in strong consistency mode or the query performance cannot be improved by compromising the accuracy of aggregate operations when you create a mapping table for the data 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, Tablestore automatically selects a search index for data query if all filtering columns in the WHERE clause and return columns in the SELECT statement are contained in the search index. For example, in the Select A,B,C from exampletable where A=XXX and D = YY; statement, Tablestore automatically selects a search index of the exampletable table for data query if the A, B, C, and D columns are contained in the search index.
Explicit access to a search index
If you want to use a specific search index to query data, you can perform the following operations to use the explicit access to a search index method:
Execute the CREATE TABLE statement to create a mapping table for the search index. For more information, see Create mapping tables for search indexes.
Execute the SELECT statement to query data. For more information, see Query data.
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 the operators down to the search index. For more information about operator pushdown, see Computing pushdown.
Appendix: mappings between features of search indexes and SQL expressions
Search indexes provide the same features as SQL expressions. The following table describes the mappings between features of search indexes and SQL expressions.
SQL expression | Example | Feature of search indexes |
without predicate | N/A | |
= |
| |
> | a > 1 | |
>= | a >= 2 | |
< | a < 5 | |
<= | a <= 10 | |
is null | a is null | |
is not null | a is not null | |
and | a = 1 and b = "hello world" | |
or | a > 1 or b = 2 | |
not | not a = 1 | |
!= | a !=1 | |
like | a like "%s%" | |
in | a in (1,2,3) | |
text_match | text_match(a, "tablestore cool") | |
text_match_phrase | text_match_phrase(a, "tablestore cool") | |
array_extract | array_extract(col_long) | |
nested_query | nested_query(`tags.tagName` = 'tag1' AND `tags.score` = 0.2) | |
order by | nested_query col_long | |
limit | limit 10 | |
min() | min(col_long) | |
max() | max(col_long) | |
sum() | sum(col_long) | |
avg() | avg(col_long) | |
count() | count(col_long) | |
count(distinct) | count(distinct col_long) | |
any_value() | any_value(col_long) | |
group by | group by col_long |
References
When you use a search index to accelerate SQL queries, you can use features based on the search index, such as full-text search, Array type in search indexes, NESTED supported in search indexes, and virtual columns of search indexes.