How do I use search indexes to query data in the Tablestore console? - Tablestore

This topic describes how to create a search index for a data table and use the search index to perform queries such as queries based on non-primary key columns and Boolean queries in the Tablestore console.

Prerequisites

A data table for which the Max Versions parameter is set to 1 is created. The Time to Live parameter of the data table must meet one of the following conditions. For more information, see Operations on tables.

The time to live (TTL) value of the data table is set to -1, which indicates that data in the data table never expires.
The TTL value of the data table is not set to -1 and the Allow Updates parameters of the data table is set to No.

Usage notes

The data types of fields in a search index must match the data types of fields in the data table for which the search index is created. For more information, see Data type mappings.
If you want to set the TTL value of a data table to a value other than -1, make sure that the UpdateRow operation is forbidden on the data table. The TTL value of a search index that is created for the data table must be less than or equal to the TTL value of the data table. For more information, see TTL of search indexes.

Step 1: Create a search index

You can create a search index to accelerate data queries. Add the fields that you want to query to the search index that you create and configure advanced settings such as the Routing Key, Time to Live, and Pre-sorting parameters, based on your business requirements.

Go to the Indexes tab.
1. Log on to the Tablestore console.
2. In the top navigation bar, select a resource group and a region.
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.
4. On the Tables tab of the Instance Details tab, click the name of the data table for which you want to create a search index or click Indexes in the Actions column of the data table.
On the Indexes tab, click Create Search Index.

In the Create Index dialog box, configure the parameters of the search index.

Retain the default index name that is generated by the system or enter a custom index name based on your business requirements.
Configure the schema of the search index.
Important
The values of the Field Name and Field Type parameters must match those of the data table. For more information about the mappings between the field types in data tables and those in search indexes, see Data type mappings.
- If you set the Schema Generation Type parameter to Manual, specify the names and types for the fields. Specify whether to turn on Array for each field.
- If you set the Schema Generation Type parameter to Auto Generate, the system automatically uses the primary key columns and attribute columns of the data table as index fields. You can select field types and specify whether to turn on Array based on your business requirements.
Note
To optimize performance in some cases, you can use virtual columns. For more information, see Virtual columns.

If you want to configure advanced settings, such as the Routing Key, Time to Live, and Pre-sorting parameters, turn on Advanced Settings. The following table describes the parameters.

Parameter	Description
Routing Key	The custom routing fields. You can select one or more primary key columns as the routing fields. Tablestore calculates the distribution of the index data based on the values of the routing fields. The records with the same values of the routing fields are distributed to the same data partition.
Time to Live	The retention period of data in the search index. The default value is -1, which indicates that the data never expires. The value of this parameter must be greater than or equal to 86400. A value of 86400 indicates one day. You can also set this parameter to -1. Specify a value that is greater than or equal to 86400 if you want the system to automatically clear the historical data in the search index. If the retention period exceeds the TTL value, the data expires and Tablestore automatically deletes expired data.
Pre-sorting	The default order in which the data is returned. Valid values: Default and Custom. A value of Default indicates that the data is sorted based on the primary key. A value of Custom indicates that the data is sorted based on the field that you specify. Configure the Pre-sorting parameter based on your business requirements. Important Search indexes that contain fields of the Nested type do not support index presorting.

Click OK.
After the search index is created, click Index Details in the Actions column of the search index. You can view the information about the search index, such as the metering information and index fields.

Step 2: Query data

When you use a search index to query data, select the query types based on your business requirements. You can configure the columns to be returned and the method of sorting the returned data.

Search indexes support the following query types: term query, range query, prefix query, match query, wildcard query, match phrase query, exists query, terms query, Boolean query, geo query, and nested query. Geo queries are available only for geo fields and nested queries are available only for nested fields.

Go to the Indexes tab.
1. Log on to the Tablestore console.
2. In the top navigation bar, select a resource group and a region.
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.
4. On the Tables tab of the Instance Details tab, click the name of the data table for which you want to create a search index or click Indexes in the Actions column of the data table.
On the Indexes tab, find the search index that you want to use to query data and click Manage Data in the Actions column.

In the Search dialog box, configure query parameters.

By default, the system returns all attribute columns. To return specified attribute columns, turn off All Columns and specify the attribute columns to return. Separate multiple attribute columns with commas (,).
Note
By default, the system returns all primary key columns of the data table.

Select an index field and click Add to add the index field as a query condition. Specify the query type and value for the index field. Repeat this step to add more query conditions as required.

Query type	Description
Term query	This query uses exact matches to retrieve data from the data table. A term query is similar to a query based on string match. If the field is of the TEXT type, Tablestore tokenizes the field values and finds any tokens that exactly match the specified value.
Range query	This query retrieves data within the specified range from the data table. If the field is of the TEXT type, Tablestore tokenizes the field values and finds any tokens that fall within the specified range.
Prefix query	This query retrieves data that contains the specified prefix from the data table. If the field is of the TEXT type, Tablestore tokenizes the field values and finds any tokens that match the specified prefix.
Match query	This query retrieves data in the data table based on approximate matches. If the field is of the TEXT type, Tablestore tokenizes the field values and the query keyword by using the specified analyzer and finds tokens based on approximate matches.
Wildcard query	This query allows you to specify wildcard characters including asterisks () and question marks (?) in the value to match. An asterisk () matches a string of any length, and a question mark (?) matches a single character. The value to match can start with an asterisk (*) or a question mark (?).
Match phrase query	This query is similar to a match query, except that a match phrase query evaluates the positions of tokens. A row meets the query condition only if the order and positions of the tokens in the row match the specified order and positions. If the tokenization method for the column that you want to query is fuzzy tokenization, a match phrase query is faster than a wildcard query to implement a fuzzy query on the column.
Exists query	An exists query is also called a NULL query or a NULL-value query. This query is used in sparse data to determine whether a column of a row exists. For example, you can query the rows in which the address column is not NULL. If you want to check whether a column contains NULL values, use an exists query with the NOT operator.
Terms query	This query is similar to a term query. A terms query supports multiple query keywords. A row of data is returned if at least one of the keywords matches the field value. Terms queries are equivalent to the IN operator in SQL statements.
Boolean query	This query retrieves data in the data table based on a combination of subqueries. Tablestore returns the rows that match the subqueries. Each subquery can be of any type. You can select one of the following operators based on your business requirements: AND, OR, or NOT.
Geo query	Geo queries include geo-distance queries, geo-bounding box queries, and geo-polygon queries. Geo queries are available only for geo fields. Geo-distance query: This query allows you to specify a circular geographical area that is defined by a central point and a radius as a query condition. Tablestore returns the rows in which the value of the specified field falls within the circular geographical area. Geo-bounding box query: This query allows you to specify a rectangular geographical area as a query condition. Tablestore returns the rows in which the value of the specified field falls within the rectangular geographical area. Geo-polygon query: This query allows you to specify a polygon geographical area as a query condition. Tablestore returns the rows in which the value of the specified field falls within the polygon geographical area.
Nested query	This query retrieves the data in the child rows of nested fields. Nested queries are available only for nested fields. To query a nested field, you must specify subqueries and configure the query type and value of each subquery.

By default, the sorting feature is disabled. To enable sorting, turn on Sort, add the index fields based on which the query results are sorted, and then configure the sorting methods.

Click OK.
Data that meets the query conditions is displayed in the specified order on the Indexes tab.

Appendix: Manage search indexes

The following table describes the operations you can perform on search indexes.

Operation	Description
View the detail of a search index	To view the metering data, index fields, and presorting of the search index, perform the following steps: On the Indexes tab, find the search index that you want to manage and click Index Details in the Actions column. In the Index Details dialog box, view the information about the search index.
Modify the schema of a search index	If you want to add, update, or delete index fields for a search index, you can dynamically modify the schema of the search index. For more information, see Dynamically modify schemas.
Modify the TTL value of a search index	If you want the system to automatically clear historical data in a search index or extend the retention period of the data in a search index, you can modify the TTL value of the search index. On the Indexes tab, find the search index that you want to manage and click Index Details in the Actions column. In the Index Details dialog box, click Modify next to the Time to Live. In the Time to Live field, change the TTL value and click Modify. The TTL value must be at least 86400, which is equal to one day, or set to -1. A value of -1 specifies that the data never expires. The TTL value of the search index must be no smaller than that of the data table for which the search index is created.
Delete a search index	If you no longer need a search index to accelerate data queries, you can delete the search index. Important A search index cannot be restored after it is deleted. If you want to use a search index again, create it again. On the Indexes tab, find the search index that you want to delete and click Delete in the Actions column. In the Delete Search Index message, confirm the information of the index that you want to delete and click OK.

FAQ

References

You can also use search indexes by using the Tablestore CLI and Tablestore SDKs. For more information, see Use the Tablestore CLI and Use Tablestore SDKs.
If you want to query new fields or data of new field types without modifying the storage schema and the data in data tables, you can use the virtual column feature. For more information, see Virtual columns.