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

You can use the mapping table that is created for a table or the mapping table that is created for a search index to query data in SQL. Select a query method based on the scenario.

Background information

Tablestore can store large amounts of structured data and supports various types of index schemas for accelerated query and analytics in different scenarios. Tablestore provides the following index schemas: primary keys of data tables based on which you can perform the single-row read (GetRow) and range read (GetRange) operations, secondary index tables that contain custom primary key columns, and search indexes that support inverted indexes and spatio-temporal indexes.

When you use the SQL query feature, you can query data based on 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. For more information, see Index selection policy.

Sample scenario

In this scenario, the data table exampletable contains the id primary key column and the name and context attribute columns. The id primary key column is of the Integer type, and the name and context attribute columns are of the String type. A search index named exampletable_searchindex is created for the data table. The search index contains the id and context columns. The id column is of the Integer type and the context column is of the Text type.

This topic uses this scenario as an example to describe how to use different index schemas to query data in SQL.

Use mapping tables of tables
Use mapping tables of search indexes

Use mapping tables of tables

Select one of the methods in this section to use mapping tables of tables based on the scenario. When you use a mapping table that is created for a table, you can specify whether the query results are in strong consistency mode and whether query performance can be improved by compromising the accuracy of aggregate operations.

Note

For information about how to create a mapping table for a table, see Create a mapping table for a table.

Method 1: Specify that the query results are in eventual consistency mode and that query performance can be improved by compromising the accuracy of aggregate operations

Create a mapping table named exampletable for the exampletable data table and retain the default settings for other parameters.
```
CREATE TABLE `exampletable` (`id` BIGINT, `name` MEDIUMTEXT, `context` MEDIUMTEXT, PRIMARY KEY(id)) ENGINE='Tablestore';
```
Query data.
When you execute the following SQL statement to query data, Tablestore automatically selects the data table for data query because the id, name, and context columns are not all contained in the exampletable_searchindex search index.
```
SELECT * FROM exampletable LIMIT 10;
```
When you execute the following SQL statement to query data, Tablestore automatically selects the exampletable_searchindex search index for data query because the search index contains the id and context columns.
```
SELECT id,context FROM exampletable LIMIT 10;
```

Method 2: Specify that the query results are in strong consistency mode

Create a mapping table named exampletable for the exampletable data table and set data_consistency to strong.

CREATE TABLE `exampletable` (`id` BIGINT, `name` MEDIUMTEXT, `context` MEDIUMTEXT, PRIMARY KEY(id)) ENGINE='Tablestore', ENGINE_ATTRIBUTE='{"data_consistency": "strong"}';

Query data. For more information, see Query data.
If search indexes are used to query data, the query results are in eventual consistency mode, which does not ensure strong consistency. Therefore, Tablestore does not use search indexes for data query.

Method 3: Specify that the query results are in eventual consistency mode and that query performance cannot be improved by compromising the accuracy of aggregate operations

Create a mapping table named exampletable for the exampletable data table and set data_consistency to eventual and allow_inaccurate_aggregation to false.

CREATE TABLE `exampletable` (`id` BIGINT, `name` MEDIUMTEXT, `context` MEDIUMTEXT, PRIMARY KEY(id)) ENGINE='Tablestore', ENGINE_ATTRIBUTE='{"data_consistency": "eventual", "allow_inaccurate_aggregation": false}';

Query data. For more information, see Query data.
The aggregate feature of search indexes does not provide completely accurate results. Therefore, Tablestore does not use search indexes for data query.

Use mapping tables of search indexes

If you want to use a specific search index to query data, you can create a mapping table for the search index.

Note

For information about how to create a mapping table for a search index, see Create mapping tables for search indexes.

Create a mapping table named exampletable_searchindex_test for the search index.

CREATE TABLE `exampletable_searchindex_test` (`id` BIGINT, `context` MEDIUMTEXT) ENGINE='searchindex' ENGINE_ATTRIBUTE='{"index_name": "exampletable_searchindex", "table_name": "exampletable"}'

Query data.

SELECT id,context FROM exampletable_searchindex_test WHERE text_match(context, "tablestore cool") LIMIT 10;