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.
Use secondary index tables
- 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.
- 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:
- 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.
- 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 |
= |
|
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 |