Tablestore:Suffix query

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 fuzzy 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.

API operation

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

Parameters

Examples

Before you perform a suffix query, make sure that the following preparations are made:

• An Alibaba Cloud account or a RAM user that has Tablestore operation permissions is created. For information about how to grant Tablestore operation permissions to a RAM user, see Use a RAM policy to grant permissions to a RAM user.

  If you want to use Tablestore SDKs to perform a suffix query, an AccessKey pair is created for the Alibaba Cloud account or RAM user. For more information, see Create an AccessKey pair.

  Warning

  If the AccessKey pair of your Alibaba Cloud account is leaked, your resources are exposed to potential risks. We recommend that you use the AccessKey pair of a RAM user to perform operations. This prevents the AccessKey pair of your Alibaba Cloud account from being leaked.

• 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.

• If you want to use Tablestore SDKs to perform a suffix query, an OTSClient instance is initialized. 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 PrefixQuery. 
    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

• What do I do if no data can be found by calling the Search operation?

• How do I increase the value of the limit parameter to 1000 when I call the Search operation of the search index feature to query data?

• Why does a token become invalid when pagination is used in a search index-based query?

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.