Query data based on a specific suffix - Tablestore - Alibaba Cloud Documentation Center

You can perform a suffix query to query data in a search index by specifying suffix conditions. For example, you can query packages based on the last four digits of a mobile phone number.

Feature overview

You can use suffix query to query data that ends with specific suffixes. When you use suffix query to query data, you must specify suffix conditions.

You can perform a suffix query to query data only of the FuzzyKeyword type. FuzzyKeyword is a data type that is optimized for features, such as suffix query, prefix query, and wildcard query. The FuzzyKeyword type provides good and stable query performance regardless of the data size. The query performance does not decline when the size of data increases.

Note

If you want to query data of the Keyword type based on suffix conditions, you can reverse data when you write the data and then use prefix query to query data.

API operation

You can call the Search or ParallelScan operation and set the query type to SuffixQuery to perform a suffix query.

Parameters

Parameter	Description
query	The type of the query. Set this parameter to SuffixQuery.
fieldName	The name of the field that you want to match.
suffix	The suffix.
getTotalCount	Specifies whether to return the total number of rows that meet the query conditions. The default value of this parameter is false, which specifies that the total number of rows that meet the query conditions is not returned. If you set this parameter to true, the query performance is compromised.
weight	The weight that you want to assign to the field that you want to query to calculate the BM25-based keyword relevance score. This parameter is used in full-text search scenarios. If you specify a higher weight for the field that you want to query, the BM25-based keyword relevance score for the field is higher. The value of this parameter is a positive floating-point number. This parameter does not affect the number of rows that are returned. However, this parameter affects the BM25-based keyword relevance scores of the query results.
tableName	The name of the data table.
indexName	The name of the search index.
columnsToGet	Specifies whether to return all columns of each row that meets the query conditions. You can configure the returnAll and columns fields for this parameter. The default value of the returnAll field is false, which specifies that not all columns are returned. In this case, you can use the columns field to specify the columns that you want to return. If you do not specify the columns that you want to return, only the primary key columns are returned. If you set the returnAll field to true, all columns are returned.

Methods

You can use the Tablestore console or Tablestore SDKs to perform a suffix query. Before you perform a suffix query, make sure that the following preparations are made:

A RAM user is created and permissions to perform operations on Tablestore are granted to the RAM user. For more information, see Use the AccessKey pair of a RAM user to access Tablestore.
A data table is created. For more information, see Operations on a data table.
A search index is created for the data table. For more information, see Create a search index.

Use the Tablestore console

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 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.
1. By default, the system returns all attribute columns. To return specific attribute columns, turn off All Columns and specify the attribute columns that you want to return. Separate multiple attribute columns with commas (,).
  Note
  By default, the system returns all primary key columns of the data table.
2. Select the And, Or, or Not logical operator based on your business requirements.
  If you select the And logical operator, data that meets the query conditions is returned. If you select the Or operator and specify a single query condition, data that meets the query condition is returned. If you select the Or logical operator and specify multiple query conditions, data that meets one of the query conditions is returned. If you select the Not logical operator, data that does not meet the query conditions is returned.
3. Select a field of the FuzzyKeyword type and click Add.
4. Select SuffixQuery(SuffixQuery) from the Query Type drop-down list and specify the field value.
5. By default, the sorting feature is disabled. If you want to sort the query results based on specific fields, turn on Sort and specify the fields based on which you want to sort the query results and the sorting order.
6. By default, the aggregation feature is disabled. If you want to collect statistics on a specific field, turn on Collect Statistics, specify the field based on which you want to collect statistics, and then configure the information that is required to collect statistics.
Click OK.
Data that meets the query conditions is displayed in the specified order on the Indexes tab.

Use Tablestore SDKs

You can use Tablestore SDK for Java to perform a suffix query.

Important

Tablestore SDK for Java V5.17.0 or later supports suffix query.
To use Tablestore SDKs to perform a suffix query, you must initialize a client. For more information, see Initialize an OTSClient instance.

The following sample code provides an example on how to query the rows in which the value of the Col_FuzzyKeyword column contains the "hangzhou" suffix.

/**
 * Query the rows in which the value of the Col_FuzzyKeyword column contains the "hangzhou" suffix. 
 * @param client
 */
private static void suffixQuery(SyncClient client) {
    SearchQuery searchQuery = new SearchQuery();
    SuffixQuery suffixQuery = new SuffixQuery(); // Set the query type to SuffixQuery. 
    searchQuery.setGetTotalCount(true);
    suffixQuery.setFieldName("Col_FuzzyKeyword");
    suffixQuery.setSuffix("hangzhou");
    searchQuery.setQuery(suffixQuery);
    //searchQuery.setGetTotalCount(true); // Specify that the total number of matched rows is returned. 

    SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);
    // You can configure the columnsToGet parameter to specify the columns to return or specify that all columns are returned. If you do not configure this parameter, only the primary key columns are returned. 
    SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
    //columnsToGet.setReturnAll(true); // Specify that all columns are returned. 
    columnsToGet.setColumns(Arrays.asList("Col_FuzzyKeyword")); // Specify the columns that you want to return. 
    searchRequest.setColumnsToGet(columnsToGet);

    SearchResponse resp = client.search(searchRequest);
    //System.out.println("TotalCount: " + resp.getTotalCount()); // Specify that the total number of matched rows instead of the number of returned rows is displayed. 
    System.out.println("Row: " + resp.getRows());
}

Billing rules

When you use a search index to query data, you are charged for the read throughput that is consumed. For more information, see Billable items of search indexes.

FAQ

References

When you use a search index to query data, you can use the following query methods: term query, terms query, match all query, match query, match phrase query, prefix query, range query, wildcard query, fuzzy query, Boolean query, geo query, nested query, KNN vector query, and exists query. You can select query methods based on your business requirements to query data from multiple dimensions.
You can sort or paginate rows that meet the query conditions by using the sorting and paging features. For more information, see Perform sorting and paging.
You can use the collapse (distinct) feature to collapse the result set based on a specific column. This way, data of the specified type appears only once in the query results. For more information, see Collapse (distinct).
If you want to analyze data in a data table, you can use the aggregation feature of the Search operation or execute SQL statements. For example, you can obtain the minimum and maximum values, sum, and total number of rows. For more information, see Aggregation and SQL query.
If you want to obtain all rows that meet the query conditions without the need to sort the rows, you can call the ParallelScan and ComputeSplits operations to use the parallel scan feature. For more information, see Parallel scan.