Tablestore provides the GetRow operation to allow you to read a single row of data and provides operations such as BatchGetRow and GetRange to allow you to read multiple rows of data in a batch.

Note Rows are basic units of tables. Rows consist of primary keys and attributes. A primary key is required for each row. Rows in a table contain primary key columns of the same names and same data types. Attributes are optional for each row. Rows in a table can also contain different attributes. For more information, see Overview.

Use Tablestore SDKs

You can use the following Tablestore SDKs to read data:

Read a single row of data

You can call the GetRow operation to read a single row of data.

CU consumption

The GetRow operation does not consume write CUs. The number of consumed read CUs is rounded up from the calculation result of the following formula: Number of consumed read CUs = (Size of the data in all primary key columns of the row + Size of the data in the attribute columns that are actually read)/4 KB. If the specified row does not exist, one read CU is consumed by the operation.

Operation result description

One of the following results of the read operation may be returned:
  • If the row exists, the primary key columns and attribute columns of the row are returned.
  • If the row does not exist, no row is returned and no error is reported.

Parameters

Parameter Description
tableName The name of the data table.
primaryKey The primary key of the row.
Note The number and types of the primary key columns that you specify must be the same as the actual number and types of primary key columns in the data table.
columnsToGet The columns that you want to return. You can specify the names of primary key columns or attribute columns.

If you do not specify a column name, all data in the row is returned.

Note
  • By default, Tablestore returns the data from all columns of a row when you query the row. You can use the columnsToGet parameter to return specific columns. For example, if col0 and col1 are added to columnsToGet, only the values of the col0 and col1 columns are returned.
  • If you configure the columnsToGet and filter parameters, Tablestore queries the columns that are specified by columnsToGet, and then returns the rows that meet the filter conditions.
maxVersions The maximum number of data versions that can be returned.
Note You must configure at least one of the following parameters: maxVersions and timeRange.
  • If only maxVersions is configured, data of the specified number of versions is returned from the most recent data entry to the earliest data entry.
  • If only timeRange is configured, all data whose versions are in the specified time range or data of the specified version is returned.
  • If maxVersions and timeRange are configured, data of the specified number of versions in the time range is returned from the most recent data entry to the earliest data entry.
timeRange The range of versions or the specific version that you want to read. For more information, see TimeRange.
Note You must configure at least one of the following parameters: maxVersions and timeRange.
  • If only maxVersions is configured, data of the specified number of versions is returned from the most recent data entry to the earliest data entry.
  • If only timeRange is configured, all data whose versions are in the specified time range or data of the specified version is returned.
  • If maxVersions and timeRange are configured, data of the specified number of versions in the time range is returned from the most recent data entry to the earliest data entry.
  • To query data whose versions are in a specified time range, you must configure start and end. start indicates the start timestamp. end indicates the end timestamp. The specified range is a left-closed, right-open interval.
  • To query data of a specific version, you must configure timestamp. timestamp indicates a specified timestamp.

You need to only configure one of timestamp and [start, end).

Valid values of timeRange range from 0 to Long.MAX_VALUE. Unit: milliseconds.

filter The filter that you want to use to filter the query results on the server side. Only rows that meet the filter conditions are returned. For more information, see Configure a filter.
Note If you configure the columnsToGet and filter parameters, Tablestore queries the columns that are specified by columnsToGet, and then returns the rows that meet the filter conditions.

Examples

  • Example 1

    The following code provides an example on how to read data of the latest version from the specified columns of a row:

    private static void getRow(SyncClient client, String pkValue) {
        // Construct the primary key. 
        PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
        primaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME, PrimaryKeyValue.fromString(pkValue));
        PrimaryKey primaryKey = primaryKeyBuilder.build();
    
        // Specify the table name and primary key to read a row of data. 
        SingleRowQueryCriteria criteria = new SingleRowQueryCriteria(TABLE_NAME, primaryKey);
        // Set MaxVersions to 1 to read the latest version of data. 
        criteria.setMaxVersions(1);
        GetRowResponse getRowResponse = client.getRow(new GetRowRequest(criteria));
        Row row = getRowResponse.getRow();
    
        System.out.println("Read complete. Result:");
        System.out.println(row);
    
        // Specify the columns that you want to read. 
        criteria.addColumnsToGet("Col0");
        getRowResponse = client.getRow(new GetRowRequest(criteria));
        row = getRowResponse.getRow();
    
        System.out.println("Read complete. Result:");
        System.out.println(row);
    } 
  • Example 2

    The following code provides an example on how to read a row of data when a filter is used:

    private static void getRow(SyncClient client, String pkValue) {
        // Construct the primary key. 
        PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
        primaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME, PrimaryKeyValue.fromString(pkValue));
        PrimaryKey primaryKey = primaryKeyBuilder.build();
    
        // Specify the table name and primary key to read a row of data. 
        SingleRowQueryCriteria criteria = new SingleRowQueryCriteria(TABLE_NAME, primaryKey);
        // Set MaxVersions to 1 to read the latest version of data. 
        criteria.setMaxVersions(1);
    
        // Configure a filter to return a row in which the value of the Col0 column is 0. 
        SingleColumnValueFilter singleColumnValueFilter = new SingleColumnValueFilter("Col0",
                SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromLong(0));
        // If the Col0 column does not exist, the row is not returned. 
        singleColumnValueFilter.setPassIfMissing(false);
        criteria.setFilter(singleColumnValueFilter);
    
        GetRowResponse getRowResponse = client.getRow(new GetRowRequest(criteria));
        Row row = getRowResponse.getRow();
    
        System.out.println("Read complete. Result:");
        System.out.println(row);
    }
  • Example 3

    The following code provides an example on how to read the data of the Col1 column in a row when a regular expression is used to filter data in the column.

    private static void getRow(SyncClient client, String pkValue) {
     
        SingleRowQueryCriteria criteria = new SingleRowQueryCriteria(tableName);
     
        // Construct the primary key. 
        PrimaryKey primaryKey = PrimaryKeyBuilder.createPrimaryKeyBuilder()
            .addPrimaryKeyColumn(PRIMARY_KEY_NAME, PrimaryKeyValue.fromString(pkValue))
            .build();
        criteria.setPrimaryKey(primaryKey);
     
        // Set MaxVersions to 1 to read the latest version of data. 
        criteria.setMaxVersions(1);
     
        // Configure a filter. A row is returned when cast<int>(regex(Col1)) is greater than 100. 
        RegexRule regexRule = new RegexRule("t1:([0-9]+),", VariantType.Type.VT_INTEGER);
        SingleColumnValueRegexFilter filter =  new SingleColumnValueRegexFilter("Col1",
            regexRule,SingleColumnValueFilter.CompareOperator.GREATER_THAN, ColumnValue.fromLong(100));
        criteria.setFilter(filter);
     
        GetRowResponse getRowResponse = client.getRow(new GetRowRequest(criteria));
        Row row = getRowResponse.getRow();
    
        System.out.println("Read complete. Result:");
        System.out.println(row);
    }

Read multiple rows of data in a batch

You can call the BatchGetRow operation to read multiple rows of data from one or more tables in a batch. The BatchGetRow operation consists of multiple GetRow operations. The process of constructing a suboperation is the same as the process of calling the GetRow operation. BatchGetRow supports filters.

Note that the BatchGetRow operation uses the same parameter configurations for all rows. For example, if ColumnsToGet is set to [colA], only the value of the colA column is read from all rows.

If you call the BatchGetRow operation, each GetRow operation is separately performed and the response to each GetRow operation is separately returned.

When you call the BatchGetRow operation to read multiple rows at a time, some rows may fail to be read. If this happens, Tablestore does not return exceptions, but return BatchGetRowResponse in which error messages of the failed rows are included. Therefore, when you call the BatchGetRow operation, you must check the return values. You can use the isAllSucceed method of BatchGetRowResponse to check whether all rows are read or use the getFailedRows method of BatchGetRowResponse to obtain the information about failed rows.

Examples

The following code provides an example on how to configure the version conditions, columns to read, and filters to read 10 rows of data:

private static void batchGetRow(SyncClient client) {
    MultiRowQueryCriteria multiRowQueryCriteria = new MultiRowQueryCriteria(TABLE_NAME);
    // Specify 10 rows that you want to read. 
    for (int i = 0; i < 10; i++) {
        PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
        primaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME, PrimaryKeyValue.fromString("pk" + i));
        PrimaryKey primaryKey = primaryKeyBuilder.build();
        multiRowQueryCriteria.addRow(primaryKey);
    }
    // Add conditions. 
    multiRowQueryCriteria.setMaxVersions(1);
    multiRowQueryCriteria.addColumnsToGet("Col0");
    multiRowQueryCriteria.addColumnsToGet("Col1");
    SingleColumnValueFilter singleColumnValueFilter = new SingleColumnValueFilter("Col0",
            SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromLong(0));
    singleColumnValueFilter.setPassIfMissing(false);
    multiRowQueryCriteria.setFilter(singleColumnValueFilter);

    BatchGetRowRequest batchGetRowRequest = new BatchGetRowRequest();
    // BatchGetRow allows you to read data from multiple tables. Each multiRowQueryCriteria parameter specifies query conditions for one table. You can add multiple multiRowQueryCriteria parameters to read data from multiple tables. 
    batchGetRowRequest.addMultiRowQueryCriteria(multiRowQueryCriteria);

    BatchGetRowResponse batchGetRowResponse = client.batchGetRow(batchGetRowRequest);

    System.out.println("Whether all operations are successful:" + batchGetRowResponse.isAllSucceed());
    if (!batchGetRowResponse.isAllSucceed()) {
        for (BatchGetRowResponse.RowResult rowResult : batchGetRowResponse.getFailedRows()) {
            System.out.println("Failed rows:" + batchGetRowRequest.getPrimaryKey(rowResult.getTableName(), rowResult.getIndex()));
            System.out.println("Cause of failures:" + rowResult.getError());
        }

        /**
         * You can use the createRequestForRetry method to construct another request to retry the operations on failed rows. Only the retry request is constructed here. 
         * We recommend that you use the custom retry policy in Tablestore SDKs as the retry method. This feature allows you to retry failed rows after batch operations. After you set the retry policy, you do not need to add retry code to call the operation. 
         */
        BatchGetRowRequest retryRequest = batchGetRowRequest.createRequestForRetry(batchGetRowResponse.getFailedRows());
    }
}
            

Read data whose primary key is in a specified range

You can call the GetRange operation to read data whose primary key is in a specified range.

The GetRange operation allows you to read data whose primary key is in a specified range in a forward or backward direction. You can also specify the number of rows to read. If the range is large and the number of scanned rows or the volume of scanned data exceeds the upper limit, the scan stops, and the rows that are read and information about the primary key of the next row are returned. You can initiate a request to start from where the last operation left off and read the remaining rows based on information about the primary key of the next row returned by the previous operation.

Note In Tablestore tables, all rows are sorted by the primary key. The primary key of a table sequentially consists of all primary key columns. Therefore, do not assume that the rows are sorted based on a specific primary key column.

Usage notes

GetRange follows the leftmost matching principle. Tablestore compares values in sequence from the first primary key column to the last primary key column to read data whose primary key is in a specified range. For example, the primary key of a data table consists of the following primary key columns: PK1, PK2, and PK3. When data is read, Tablestore first determines whether the PK1 value of a row is in the range that is specified for the first primary key column. If the PK1 value of a row is in the range, Tablestore stops determining whether the values of other primary key columns of the row are in the ranges that are specified for each primary key column and returns the row. If the PK1 value of a row is not in the range, Tablestore continues to determine whether the values of other primary key columns of the row are in the ranges that are specified for each primary key column in the same manner as PK1.

If one of the following conditions is met, the GetRange operation may stop and return data:
  • The amount of scanned data reaches 4 MB.
  • The number of scanned rows reaches 5,000.
  • The number of returned rows reaches the upper limit.
  • The read throughput is insufficient to read the next row of data because all reserved read throughput is consumed.

CU consumption

The number of read CUs consumed by a GetRange operation is calculated from the start point of the range to the start point of the next row that is unread. The number of read CUs that are consumed for the GetRange operation is rounded up from the calculation result of the following formula: Number of consumed read CUs = (Size of the data in all primary key columns of the rows that meet the query conditions + Size of the data in the attribute columns that are read)/4 KB. For example, if 10 rows that meet the query conditions are read and the sum of the size of the data in all primary key columns of the rows and the size of the data in the attribute columns that are read is 330 bytes, the number of consumed read CUs is rounded up from the calculation result of the following formula: Number of consumed read CUs = (3.3 KB/4 KB). In this case, the GetRange operation consumes one read CU.

Parameters

Parameter Description
tableName The name of the data table.
direction The order in which you want to sort the rows in the response.
  • If you set this parameter to FORWARD, the value of the inclusiveStartPrimaryKey parameter must be smaller than the value of the exclusiveEndPrimaryKey parameter, and the rows in the response are sorted in the ascending order of primary key values.
  • If you set this parameter to BACKWARD, the value of the inclusiveStartPrimaryKey parameter must be greater than the value of the exclusiveEndPrimaryKey parameter, and the rows in the response are sorted in descending order of primary key values.

For example, if you set the direction parameter to FORWARD for a table that contains two primary keys A and B, and the value of A is smaller than the value of B, the rows whose primary key values are greater than or equal to the value of A but smaller than the value of B are returned in ascending order from A to B. If you set the direction parameter to BACKWARD, the rows whose primary key values are smaller than or equal to the value of B and greater than the value of A are returned in descending order from B to A.

inclusiveStartPrimaryKey The start primary key and end primary key of the range that you want to read. The start primary key and end primary key must be valid primary keys or virtual points that contain data of the INF_MIN type and INF_MAX type. The number of columns for each virtual point must be the same as the number of columns of each primary key.

INF_MIN indicates an infinitely small value. All values of other types are greater than the INF_MIN type value. INF_MAX indicates an infinitely great value. All values of other types are smaller than the INF_MAX type value.

  • inclusiveStartPrimaryKey indicates the start primary key. If a row that contains the start primary key exists, the row of data is returned.
  • exclusiveEndPrimaryKey indicates the end primary key. Regardless of whether a row that contains the end primary key exists, the row of data is not returned.

The rows in the data table are sorted in ascending order based on the primary key values. The range that is used to read data is a left-closed, right-open interval. If data is read in the forward direction, the rows whose primary keys are greater than or equal to the start primary key but smaller than the end primary key are returned.

exclusiveEndPrimaryKey
limit The maximum number of rows that can be returned. The value of this parameter must be greater than 0.

An operation stops after the maximum number of rows that can be returned in the forward or backward direction is reached, even if some rows in the specified range are not returned. You can use the value of nextStartPrimaryKey returned in the response to read data in the next request.

columnsToGet The columns that you want to return. You can specify the names of primary key columns or attribute columns.

If you do not specify a column name, all data in the row is returned.

Note
  • By default, Tablestore returns the data from all columns of a row when you query the row. You can use the columnsToGet parameter to return specific columns. For example, if col0 and col1 are added to columnsToGet, only the values of the col0 and col1 columns are returned.
  • If a row is in the specified range that you want to read based on the primary key value but does not contain the specified columns that you want to return, the response excludes the row.
  • If you configure the columnsToGet and filter parameters, Tablestore queries the columns that are specified by columnsToGet, and then returns the rows that meet the filter conditions.
maxVersions The maximum number of data versions that can be returned.
Note You must configure at least one of the following parameters: maxVersions and timeRange.
  • If only maxVersions is configured, data of the specified number of versions is returned from the most recent data entry to the earliest data entry.
  • If only timeRange is configured, all data whose versions are in the specified time range or data of the specified version is returned.
  • If maxVersions and timeRange are configured, data of the specified number of versions in the time range is returned from the most recent data entry to the earliest data entry.
timeRange The range of versions or the specific version that you want to read. For more information, see TimeRange.
Note You must configure at least one of the following parameters: maxVersions and timeRange.
  • If only maxVersions is configured, data of the specified number of versions is returned from the most recent data entry to the earliest data entry.
  • If only timeRange is configured, all data whose versions are in the specified time range or data of the specified version is returned.
  • If maxVersions and timeRange are configured, data of the specified number of versions in the time range is returned from the most recent data entry to the earliest data entry.
  • To query data whose versions are in a specified time range, you must configure start and end. start indicates the start timestamp. end indicates the end timestamp. The specified range is a left-closed, right-open interval that is in the [start, end) format.
  • To query data of a specific version, you must configure timestamp. timestamp indicates a specified timestamp.

You need to only configure one of timestamp and [start, end).

Valid values of timeRange range from 0 to Long.MAX_VALUE. Unit: milliseconds.

filter The filter that you want to use to filter the query results on the server side. Only rows that meet the filter conditions are returned. For more information, see Configure a filter.
Note If you configure the columnsToGet and filter parameters, Tablestore queries the columns that are specified by columnsToGet, and then returns the rows that meet the filter conditions.
nextStartPrimaryKey The start primary key of the next read request. The value of nextStartPrimaryKey can be used to determine whether all data is read.
  • If the value of nextStartPrimaryKey is not empty in the response, the nextStartPrimaryKey value can be used as the value of the start primary key for the next GetRange operation.
  • If the value of nextStartPrimaryKey is empty in the response, all data in the range is returned.

Examples

  • Example 1
    The following code provides an example on how to read data whose primary key is in a specified range in the forward direction. If the nextStartPrimaryKey value is null in the response, all data whose primary key is in the specified range is read.
    private static void getRange(SyncClient client, String startPkValue, String endPkValue) {
        RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria(TABLE_NAME);
    
        // Specify the start primary key. 
        PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
        primaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME, PrimaryKeyValue.fromString(startPkValue));
        rangeRowQueryCriteria.setInclusiveStartPrimaryKey(primaryKeyBuilder.build());
    
        // Specify the end primary key. 
        primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
        primaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME, PrimaryKeyValue.fromString(endPkValue));
        rangeRowQueryCriteria.setExclusiveEndPrimaryKey(primaryKeyBuilder.build());
    
        rangeRowQueryCriteria.setMaxVersions(1);
    
        System.out.println("GetRange result:");
        while (true) {
            GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));
            for (Row row : getRangeResponse.getRows()) {
                System.out.println(row);
            }
    
            // If the NextStartPrimaryKey value is not null, continue the read operation. 
            if (getRangeResponse.getNextStartPrimaryKey() != null) {
                rangeRowQueryCriteria.setInclusiveStartPrimaryKey(getRangeResponse.getNextStartPrimaryKey());
            } else {
                break;
            }
        }
    }         
  • Example 2

    The following code provides an example on how to read data in the forward direction within the range determined by the value of the first primary key column. The start value of the second primary key column is INF_MIN. The end value of the second primary key column is INF_MAX. If the nextStartPrimaryKey value is null in the response, all data in the specified range is read.

    private static void getRange(SyncClient client, String startPkValue, String endPkValue) {
        RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria(TABLE_NAME);
        // Specify the start primary key. In this example, two primary key columns are used. 
        PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
        primaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME1, PrimaryKeyValue.fromString(startPkValue)); // Set the value of the first primary key column to a specific value. 
        primaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME2, PrimaryKeyValue.INF_MIN); // Set the value of the second primary key column to an infinitely small value. 
        rangeRowQueryCriteria.setInclusiveStartPrimaryKey(primaryKeyBuilder.build());
    
        // Specify the end primary key. 
        primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
        primaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME1, PrimaryKeyValue.fromString(endPkValue)); // Set the value of the first primary key column to a specific value. 
        primaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME2, PrimaryKeyValue.INF_MAX); // Set the value of the second primary key column to an infinitely great value. 
        rangeRowQueryCriteria.setExclusiveEndPrimaryKey(primaryKeyBuilder.build());
    
        rangeRowQueryCriteria.setMaxVersions(1);
    
        System.out.println("GetRange result:");
        while (true) {
            GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));
            for (Row row : getRangeResponse.getRows()) {
                System.out.println(row);
            }
    
            // If the nextStartPrimaryKey value is not null, continue to read data. 
            if (getRangeResponse.getNextStartPrimaryKey() != null) {
                rangeRowQueryCriteria.setInclusiveStartPrimaryKey(getRangeResponse.getNextStartPrimaryKey());
            } else {
                break;
            }
        }
    }         
  • Example 3

    The following code provides an example on how to read data whose primary key is within the range specified by ["pk:2020-01-01.log", "pk:2021-01-01.log") from the Col1 column and use regular expression to filter the Col1 column.

    private static void getRange(SyncClient client) {
        RangeRowQueryCriteria criteria = new RangeRowQueryCriteria(TABLE_NAME);
     
        // Specify ["pk:2020-01-01.log", "pk:2021-01-01.log") as the range of the primary key of the data that you want to read. The range is a left-closed, right-open interval. 
        PrimaryKey pk0 = PrimaryKeyBuilder.createPrimaryKeyBuilder()
            .addPrimaryKeyColumn("pk", PrimaryKeyValue.fromString("2020-01-01.log"))
            .build();
        PrimaryKey pk1 = PrimaryKeyBuilder.createPrimaryKeyBuilder()
            .addPrimaryKeyColumn("pk", PrimaryKeyValue.fromString("2021-01-01.log"))
            .build();
        criteria.setInclusiveStartPrimaryKey(pk0);
        criteria.setExclusiveEndPrimaryKey(pk1);
     
        // Set MaxVersions to 1 to read the latest version of data. 
        criteria.setMaxVersions(1);
     
        // Configure a filter. A row is returned when cast<int>(regex(Col1)) is greater than 100. 
        RegexRule regexRule = new RegexRule("t1:([0-9]+),", VariantType.Type.VT_INTEGER);
        SingleColumnValueRegexFilter filter =  new SingleColumnValueRegexFilter("Col1",
            regexRule,SingleColumnValueFilter.CompareOperator.GREATER_THAN,ColumnValue.fromLong(100));
        criteria.setFilter(filter);
    
        while (true) {
            GetRangeResponse resp = client.getRange(new GetRangeRequest(criteria));
            for (Row row : resp.getRows()) {
                // do something
                System.out.println(row);
            }
            if (resp.getNextStartPrimaryKey() != null) {
                criteria.setInclusiveStartPrimaryKey(resp.getNextStartPrimaryKey())
            }
        }
    }