Read data from a secondary index - Tablestore SDK for Java - Tablestore

Use Tablestore SDK for Java to read data through a global or local secondary index.

Prerequisites

Install Tablestore SDK for Java and initialize a client.

Feature description

A secondary index rearranges the primary key columns and predefined columns of a data table to provide an alternative read path. An index table is read-only. It contains the index primary key, the primary key columns that Tablestore automatically appends from the data table, and the attribute columns specified when the index is created. To retrieve an attribute column that is not included in the index table, use the returned data table primary key to query the data table. For more information, see Secondary indexes.

When you read an index table, the names and order of the primary key columns must match the primary key schema of the index table. For example, assume that a data table uses user_id and order_id as its primary key and that category is added as an index column. The following table compares the complete primary key order of global and local secondary indexes.

Index type	Index primary key specified at index creation	Complete primary key order of the index table
Global secondary index	`category`	`category`, `user_id`, and `order_id`. Tablestore appends the unspecified data table primary key columns to the index primary key.
Local secondary index	`user_id` and `category`	`user_id`, `category`, and `order_id`. The first index primary key column must be the same as the first primary key column of the data table.

Important

When a global secondary index is created to include existing data, the index table cannot be read until the existing data is built and synchronized. Wait until the synchronization is complete before you read the index table.

Call getRow to read a row by using the complete primary key of the index table.

public GetRowResponse getRow(GetRowRequest getRowRequest)
        throws TableStoreException, ClientException

Call getRange to read data within a primary key range of the index table.

public GetRangeResponse getRange(GetRangeRequest getRangeRequest)
        throws TableStoreException, ClientException

The following example reads a row from the example_global_index global secondary index by using its complete primary key and returns only the status attribute column.

PrimaryKey primaryKey = PrimaryKeyBuilder.createPrimaryKeyBuilder()
        .addPrimaryKeyColumn("category", PrimaryKeyValue.fromString("books"))
        .addPrimaryKeyColumn("user_id", PrimaryKeyValue.fromString("user-1"))
        .addPrimaryKeyColumn("order_id", PrimaryKeyValue.fromLong(101L))
        .build();

SingleRowQueryCriteria criteria =
        new SingleRowQueryCriteria("example_global_index", primaryKey);
criteria.addColumnsToGet("status");
criteria.setMaxVersions(1);

GetRowResponse response = client.getRow(new GetRowRequest(criteria));
System.out.println(response.getRow());

Parameters

Parameters for reading a single row

GetRowRequest contains the following parameter.

Name	Type	Description
rowQueryCriteria (required)	`SingleRowQueryCriteria`	The conditions for reading a single row.

Single-row read conditions

rowQueryCriteria is of the SingleRowQueryCriteria type and contains the following parameters.

Name	Type	Description
tableName (required)	`String`	The name of the index table.
primaryKey (required)	`PrimaryKey`	The complete primary key of the row in the index table. The primary key must contain the index primary key columns specified at index creation and the data table primary key columns automatically appended by Tablestore. The names and order of the columns must match the index table schema.
columnsToGet (optional)	`Set<String>`	The names of the attribute columns to return. You can specify up to 128 columns. If this parameter is not specified, all attribute columns of the row in the index table are returned. An attribute column that is not included in the index table is not returned and must be retrieved from the data table.
maxVersions (conditionally required)	`int`	The maximum number of versions to return for each attribute column. The value must be greater than 0. A secondary index retains only the latest version. In most cases, set this parameter to `1`. Specify either `maxVersions` or `timeRange`, but not both.
timeRange (conditionally required)	`TimeRange`	The timestamp range of attribute column versions, in milliseconds. The range is left-closed and right-open. Specify either `timeRange` or `maxVersions`, but not both.
filter (optional)	`Filter`	A server-side filter. The row is not returned if it does not meet the filter condition.
startColumn (optional)	`String`	The name of the first attribute column to return in lexicographical order. The specified column is included. This parameter is intended for reading wide rows.
endColumn (optional)	`String`	The name of the last attribute column in lexicographical order. The specified column is excluded. This parameter is intended for reading wide rows.

Parameters for reading a range of rows

GetRangeRequest contains the following parameter.

Name	Type	Description
rangeRowQueryCriteria (required)	`RangeRowQueryCriteria`	The conditions for reading a range of rows.

Range read conditions

rangeRowQueryCriteria is of the RangeRowQueryCriteria type and contains the following parameters.

Name	Type	Description
tableName (required)	`String`	The name of the index table.
inclusiveStartPrimaryKey (required)	`PrimaryKey`	The start primary key of the range. The primary key is included in the result and must contain all primary key columns of the index table. You can use `INF_MIN` and `INF_MAX` to represent the minimum and maximum values of a primary key column.
exclusiveEndPrimaryKey (required)	`PrimaryKey`	The end primary key of the range. The primary key is excluded from the result and must contain all primary key columns of the index table. You can use `INF_MIN` and `INF_MAX` to represent the minimum and maximum values of a primary key column.
direction (optional)	`Direction`	The read direction. Valid values are `FORWARD` and `BACKWARD`. Default value: `FORWARD`. For a forward read, the start primary key must be less than the end primary key. For a backward read, the start primary key must be greater than the end primary key.
limit (optional)	`int`	The maximum number of rows to return in one request. The value must be greater than 0. The default value is `-1`, which specifies that the client does not limit the number of returned rows. The server returns at most 5,000 rows and 4 MB of data in one response.
columnsToGet (optional)	`Set<String>`	The names of the attribute columns to return. You can specify up to 128 columns. If this parameter is not specified, all attribute columns of each row in the index table are returned. If none of the specified attribute columns exist in a row, the row is not returned. Before you query the data table, read columns that exist in the index table or leave this parameter unspecified.
maxVersions (conditionally required)	`int`	The maximum number of versions to return for each attribute column. The value must be greater than 0. A secondary index retains only the latest version. In most cases, set this parameter to `1`. Specify either `maxVersions` or `timeRange`, but not both.
timeRange (conditionally required)	`TimeRange`	The timestamp range of attribute column versions, in milliseconds. The range is left-closed and right-open. Specify either `timeRange` or `maxVersions`, but not both.
filter (optional)	`Filter`	A server-side filter. Only rows that meet the filter condition are returned.
startColumn (optional)	`String`	The name of the first attribute column to return in lexicographical order. The specified column is included. This parameter is intended for reading wide rows.
endColumn (optional)	`String`	The name of the last attribute column in lexicographical order. The specified column is excluded. This parameter is intended for reading wide rows.

Return values

Return values for reading a single row

GetRowResponse contains the following response parameters.

Name	Type	Description
row	`Row`	The returned row. If the row does not exist, `null` is returned.
consumedCapacity	`ConsumedCapacity`	The capacity units consumed by the operation.

Return values for reading a range of rows

GetRangeResponse contains the following response parameters.

Name	Type	Description
rows	`List<Row>`	The rows returned by the request.
nextStartPrimaryKey	`PrimaryKey`	The start primary key for the next request. If this parameter is not `null`, use it as `inclusiveStartPrimaryKey` in the next request. If this parameter is `null`, all rows in the specified range have been read. This parameter may be returned when a response reaches the server limit even if `limit` is not specified.
consumedCapacity	`ConsumedCapacity`	The capacity units consumed by the operation.

Scenarios

Read a range of rows from a global secondary index

The following example reads rows whose category value is books from the example_global_index global secondary index and handles the nextStartPrimaryKey pagination token.

PrimaryKey startPrimaryKey = PrimaryKeyBuilder.createPrimaryKeyBuilder()
        .addPrimaryKeyColumn("category", PrimaryKeyValue.fromString("books"))
        .addPrimaryKeyColumn("user_id", PrimaryKeyValue.INF_MIN)
        .addPrimaryKeyColumn("order_id", PrimaryKeyValue.INF_MIN)
        .build();
PrimaryKey endPrimaryKey = PrimaryKeyBuilder.createPrimaryKeyBuilder()
        .addPrimaryKeyColumn("category", PrimaryKeyValue.fromString("books"))
        .addPrimaryKeyColumn("user_id", PrimaryKeyValue.INF_MAX)
        .addPrimaryKeyColumn("order_id", PrimaryKeyValue.INF_MAX)
        .build();

RangeRowQueryCriteria rangeCriteria =
        new RangeRowQueryCriteria("example_global_index");
rangeCriteria.setInclusiveStartPrimaryKey(startPrimaryKey);
rangeCriteria.setExclusiveEndPrimaryKey(endPrimaryKey);
rangeCriteria.setMaxVersions(1);
rangeCriteria.setLimit(100);

List<Row> rows = new ArrayList<>();
while (true) {
    GetRangeResponse response =
            client.getRange(new GetRangeRequest(rangeCriteria));
    rows.addAll(response.getRows());

    if (response.getNextStartPrimaryKey() == null) {
        break;
    }
    rangeCriteria.setInclusiveStartPrimaryKey(
            response.getNextStartPrimaryKey());
}
rows.forEach(System.out::println);

Query the data table for attribute columns

The following example uses the rows returned in the preceding example. It extracts the data table primary key from each index table primary key and queries the example_table data table for the detail attribute column, which is not included in the index table.

for (Row indexRow : rows) {
    PrimaryKey indexPrimaryKey = indexRow.getPrimaryKey();
    PrimaryKey primaryKey = PrimaryKeyBuilder.createPrimaryKeyBuilder()
            .addPrimaryKeyColumn(
                    "user_id",
                    indexPrimaryKey.getPrimaryKeyColumn("user_id").getValue())
            .addPrimaryKeyColumn(
                    "order_id",
                    indexPrimaryKey.getPrimaryKeyColumn("order_id").getValue())
            .build();

    SingleRowQueryCriteria rowCriteria =
            new SingleRowQueryCriteria("example_table", primaryKey);
    rowCriteria.addColumnsToGet("detail");
    rowCriteria.setMaxVersions(1);

    GetRowResponse response =
            client.getRow(new GetRowRequest(rowCriteria));
    System.out.println(response.getRow());
}