Tablestore:Collapse (remove duplicates)

Usage limits

Before you use the collapse feature, note the following constraints:

• Supported column types — The collapse feature applies only to columns of the integer, floating-point number, and Keyword types. Columns of the array type are not supported.

• Pagination — The collapse feature supports pagination only by using offset and limit. Token-based pagination is not supported.

• Aggregation — When you use statistical aggregation together with the collapse feature, the aggregation applies only to the result set before the collapse operation.

• Group count limit — After the collapse operation, the total number of groups returned depends on the maximum value of offset plus limit. A maximum of 100,000 groups can be returned.

• Total count — The total number of rows in the response is the number of matched rows before the collapse operation. You cannot obtain the total number of groups after the collapse operation.

Parameters

The collapse feature is provided by the Search operation and is implemented by using the collapse parameter. The following table describes the parameters used in a collapse query.

Usage

You can use the command line interface or an SDK to collapse results when you query data.

• Use an Alibaba Cloud account or a RAM user with the required permissions for Table Store operations. To grant permissions to a RAM user, see Grant permissions to a RAM user by using a RAM policy.

  If you use an SDK or a command-line tool, create an AccessKey for your Alibaba Cloud account or RAM user if you do not have one.

• You have created a data table.

• A Search Index has been created for the data table.

• If you use an SDK, initialize the Tablestore Client.

• If you use the command-line tool, download and start the tool, then configure the connection to your instance and select the target table. For more information, see Download the command-line tool, Start the tool and configure connection information, and Data table operations.

Run the search command in the command line interface to query data by using a search index, and configure the Collapse parameter in the query conditions to use the collapse feature. For more information, see Search indexes.

• Run the search command to query data in the table by using a search index and return all indexed columns.

search -n search_index --return_all_indexed

• Enter the query conditions as prompted. The following code provides an example:

{
    "Offset": -1,
    "Limit": 10,
    "Collapse": {
        "FieldName": "product_name"
    },
    "Sort": null,
    "GetTotalCount": true,
    "Token": null,
    "Query": {
        "Name": "MatchQuery",
        "Query": {
            "FieldName": "user_id",
            "Text": "00002",
            "MinimumShouldMatch": 1
        }
    }
}

The following table describes the key fields in the example:

After the query returns results, verify that the collapse feature works as expected by checking that the values in the collapse column are unique across the returned rows.

Use an SDK

You can use the Java SDK, Go SDK, Python SDK, Node.js SDK, .NET SDK, or PHP SDK to collapse results when you query data. The following example uses the Java SDK to describe how to use the collapse feature.

The following example queries all rows, collapses the results by the category field, and sorts rows by the price field in descending order. The row with the highest price in each category is returned.

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(new MatchAllQuery());
searchQuery.setLimit(10);
searchQuery.setCollapse(new Collapse("category"));
searchQuery.setSort(new Sort(
        Arrays.asList(new FieldSort("price", SortOrder.DESC))));

SearchRequest.ColumnsToGet columnsToGet =
        new SearchRequest.ColumnsToGet();
columnsToGet.setColumns(Arrays.asList("category", "price"));

SearchRequest request =
        new SearchRequest("example_table", "example_index", searchQuery);
request.setColumnsToGet(columnsToGet);

SearchResponse response = client.search(request);
System.out.println(response.getRows());

Data query

In VCU mode (formerly Reserved mode), querying data by using a search index consumes the compute resources of VCUs. In CU mode (formerly Pay-As-You-Go mode), querying data by using a search index consumes read throughput. For more information, see Search index billing.

Querying data by using a search index consumes read throughput. For more information, see Billable items of search indexes.

Using the collapse feature when you query data does not affect the existing billing rules.

FAQ

• What do I do if no data is found when I call the Search operation for search indexes?

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

References

• Search Index supports various query types for multi-dimensional data queries, including term query, terms query, match all query, match query, phrase match query, range query, prefix query, suffix query, wildcard query, token-based wildcard query, boolean query, geo query, nested query, vector search, and exists query.

• When you query data, you can sort and paginate the result set or perform collapsing (deduplication).

• For data analysis, such as finding the maximum or minimum value, calculating a sum, or counting rows, you can use the statistical aggregation or SQL query features.

• To quickly export data regardless of the result set order, you can use the Parallel Scan feature.