Query the total number of rows in a table or return multiple random rows - Tablestore

API operation

You can call the Search or ParallelScan operation and set the query type to MatchAllQuery to perform a match all query.

Parameters

Parameter	Description
query	The type of the query. Set the query parameter to MatchAllQuery.
tableName	The name of the data table.
indexName	The name of the search index.
limit	The maximum number of rows that you want the current query to return. If you want the current query to return multiple random rows of data, set the limit parameter to a positive integer. To query only the number of rows that meet the query conditions without specific data, set the limit parameter to 0.
columnsToGet	Specifies whether to return all columns of each row that meets the query conditions. You can specify the returnAll and columns fields for the columnsToGet 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.
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.

Methods

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

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.

Use the Tablestore CLI

You can use the Tablestore CLI to run the search command to query data by using search indexes. For more information, see Search index.

Run the search command to use the search_index search index to query data and return all indexed columns of each row that meets the query conditions.
```
search -n search_index --return_all_indexed
```

Enter the query conditions as prompted:

{
    "Offset": -1,
    "Limit": 10,
    "Collapse": null,
    "Sort": null,
    "GetTotalCount": true,
    "Token": null,
    "Query": {
        "Name": "MatchAllQuery",
        "Query": {
        }
    }
}

Use Tablestore SDKs

You can perform a match all query by using the following Tablestore SDKs: Tablestore SDK for Java, Tablestore SDK for Go, Tablestore SDK for Python, Tablestore SDK for Node.js, Tablestore SDK for .NET, and Tablestore SDK for PHP. In this example, Tablestore SDK for Java is used.

The following example matches every row in a search index and returns up to 10 rows and the total number of matching rows.

String tableName = "example_table";
String indexName = "example_index";

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(new MatchAllQuery());
searchQuery.setLimit(10);
searchQuery.setTrackTotalCount(SearchQuery.TRACK_TOTAL_COUNT);

SearchRequest request = new SearchRequest(tableName, indexName, searchQuery);
SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
columnsToGet.setReturnAll(true);
request.setColumnsToGet(columnsToGet);

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

Billing

Querying data by using a Search Index consumes read throughput. For more information, see Search Index metering and billing.

FAQ

How do I query the total number of rows in a table?

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.