Tablestore provides multi-row operations such as BatchWriteRow, BatchGetRow, and GetRange.

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

Operations

Multi-row operations include BatchWriteRow, BatchGetRow, and GetRange. The following table describes the operations.

Operation Description
BatchGetRow Reads several rows of data from one or more tables.
BatchWriteRow Inserts rows into, deletes rows from, or updates rows in one or more tables.
GetRange Queries data within a specified range based on primary key values.

Use Tablestore SDKs

You can use the following Tablestore SDKs to perform multi-row operations on data:

BatchWriteRow

You can call this operation to write multiple rows to one or more tables in a request. The BatchWriteRow operation consists of PutRow, UpdateRow, and DeleteRow operations. The process of constructing a suboperation is the same as that of calling the PutRow, UpdateRow, and DeleteRow operations. BatchWriteRow supports conditional updates.

Each suboperation of BatchWriteRow is independently performed, and the result for each suboperation is independently returned.

When multiple rows are written, some rows may fail to be written. If this happens, Tablestore does not return exceptions, but information about the index and error messages of the failed rows is stored in BatchWriteRowResponse. Therefore, when you call the BatchWriteRow operation, you must check the return values. You can use the isAllSucceed method of BatchWriteRowResponse to check whether all operations are successful. If you do not check the return values, some operation failures may be ignored.

If the server detects that invalid parameters exist in some operations, the BatchWriteRow operation may return an exception about parameter errors before performing all the operations in the request.

  • Parameters

    For more information, see Single-row operations.

  • Examples

    The following code provides an example on how to send a BatchWriteRow request, which includes two PutRow operations, one UpdateRow operation, and one DeleteRow operation:

    private static void batchWriteRow(SyncClient client) {
        BatchWriteRowRequest batchWriteRowRequest = new BatchWriteRowRequest();
    
        // Construct rowPutChange1. 
        PrimaryKeyBuilder pk1Builder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
        pk1Builder.addPrimaryKeyColumn(PRIMARY_KEY_NAME, PrimaryKeyValue.fromString("pk1"));
        RowPutChange rowPutChange1 = new RowPutChange(TABLE_NAME, pk1Builder.build());
        // Add columns. 
        for (int i = 0; i < 10; i++) {
            rowPutChange1.addColumn(new Column("Col" + i, ColumnValue.fromLong(i)));
        }
        // Add rowPutChange1 to the batch operation. 
        batchWriteRowRequest.addRowChange(rowPutChange1);
    
        // Construct rowPutChange2. 
        PrimaryKeyBuilder pk2Builder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
        pk2Builder.addPrimaryKeyColumn(PRIMARY_KEY_NAME, PrimaryKeyValue.fromString("pk2"));
        RowPutChange rowPutChange2 = new RowPutChange(TABLE_NAME, pk2Builder.build());
        // Add columns. 
        for (int i = 0; i < 10; i++) {
            rowPutChange2.addColumn(new Column("Col" + i, ColumnValue.fromLong(i)));
        }
        // Add rowPutChange2 to the batch operation. 
        batchWriteRowRequest.addRowChange(rowPutChange2);
    
        // Construct rowUpdateChange. 
        PrimaryKeyBuilder pk3Builder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
        pk3Builder.addPrimaryKeyColumn(PRIMARY_KEY_NAME, PrimaryKeyValue.fromString("pk3"));
        RowUpdateChange rowUpdateChange = new RowUpdateChange(TABLE_NAME, pk3Builder.build());
        // Add columns. 
        for (int i = 0; i < 10; i++) {
            rowUpdateChange.put(new Column("Col" + i, ColumnValue.fromLong(i)));
        }
        // Delete a column. 
        rowUpdateChange.deleteColumns("Col10");
        // Add rowUpdateChange to the batch operation. 
        batchWriteRowRequest.addRowChange(rowUpdateChange);
    
        // Construct rowDeleteChange. 
        PrimaryKeyBuilder pk4Builder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
        pk4Builder.addPrimaryKeyColumn(PRIMARY_KEY_NAME, PrimaryKeyValue.fromString("pk4"));
        RowDeleteChange rowDeleteChange = new RowDeleteChange(TABLE_NAME, pk4Builder.build());
        // Add rowDeleteChange to the batch operation. 
        batchWriteRowRequest.addRowChange(rowDeleteChange);
    
        BatchWriteRowResponse response = client.batchWriteRow(batchWriteRowRequest);
    
        System.out.println("Whether all operations are successful:" + response.isAllSucceed());
        if (!response.isAllSucceed()) {
            for (BatchWriteRowResponse.RowResult rowResult : response.getFailedRows()) {
                System.out.println("Failed rows:" + batchWriteRowRequest.getRowChange(rowResult.getTableName(), rowResult.getIndex()).getPrimaryKey());
                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. 
             */
            BatchWriteRowRequest retryRequest = batchWriteRowRequest.createRequestForRetry(response.getFailedRows());
        }
    }           

BatchGetRow

You can call this operation to read multiple rows from one or more tables in a request. A BatchGetRow operation consists of multiple GetRow operations. The process of constructing a suboperation is the same as that of calling the GetRow operation.

Note that BatchGetRow 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.

Each suboperation of BatchGetRow is independently performed, and the result for each suboperation is independently returned.

When multiple rows are read, some rows may fail to be read. If this happens, Tablestore does not return exceptions, but information about error messages of the failed rows is stored in BatchGetRowResponse. 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 operations are successful or use the getFailedRows method of BatchGetRowResponse to obtain the information about failed rows.

  • Parameters

    For more information, see Single-row operations.

  • 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);
        // Add 10 rows. 
        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. A single multiRowQueryCriteria specifies a query condition for one table. You can add multiple multiRowQueryCriteria conditions 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());
        }
    }
                

GetRange

You can call this operation to read data within a specified range based on primary key values. The range specified by primary key value is a left-closed and right-open interval.

The GetRange operation allows you to read data in a forward or backward direction within a specified range. 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 data exceeds the limit, the scan stops and the read rows and the next primary key information are returned. You can initiate a request to start from where the last operation left off and read the remaining rows based on the next primary key information returned by the previous operation.

Note In Tablestore tables, all rows are sorted by 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.

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 within the 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 compares the PK1 value of a row to that of the start and end primary keys to determine whether the PK1 value of the row is within the range. If the PK1 value of a row is within the range, Tablestore stops comparing the values of other primary key columns and returns the data of the PK1 primary key column. If the PK1 value of a row is not within the range, Tablestore continues to compare the values of other primary key columns in the same way as PK1. For more information about range query principles, see Detailed explanation of GetRange.

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

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 consumed read CUs is calculated based on the formula: Number of consumed read CUs = [(Size of primary key columns in the rows + Size of attribute columns that are read)/4 KB] rounded up. For example, if the specified range to be read contains 10 rows and the total size of primary key columns and the attribute columns that are read is 330 bytes, the number of consumed read CUs is calculated based on the formula: Number of consumed read CUs = (3.3 KB/4 KB) rounded up. In this case, the GetRange operation consumes one read CU.

  • Parameters
    Parameter Description
    tableName The name of the table.
    direction The direction of reading.
    • If this parameter is set to FORWARD, the value of the start primary key must be less than that of the end primary key. The returned rows are sorted in ascending order based on their primary key values.
    • If the value is BACKWARD, the value of the start primary key must be greater than that of the end primary key. The returned rows are sorted in descending order based on their primary key values.

    For example, if you set direction to FORWARD for a table that has two primary keys A and B and the value of A is less than that of B, the rows whose primary key values are equal to or greater than the value of A but less than the value of B are returned in ascending order from A to B. If you set direction to BACKWARD, the rows whose primary key values are equal to or less than 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 to be read. The start and end primary keys must be valid primary keys or virtual points composed of the INF_MIN and INF_MAX type data. The number of columns for each virtual point must be the same as that of each primary key.

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

    • inclusiveStartPrimaryKey indicates the start primary key. If a row that contains the start primary key exists, the row is included in the response.
    • exclusiveEndPrimaryKey indicates the end primary key. The row that corresponds to the end primary key is not contained in the response regardless of whether the row exists.

    The rows in the data table are sorted in ascending order based on the primary key values. The range to read is a left-closed and 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 less 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 returned in the forward or backward direction is reached, even if some rows within 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 names of the columns to be read. You can specify the names of primary key columns and the names of attribute columns.

    If you do not specify this parameter, rows that contain all columns are returned.

    Note
    • By default, Tablestore returns the data from all the columns of the row when you query a row. You can use the columnsToGet parameter to read data from 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 within the specified range to be read based on the primary key value but does not contain the specified columns to return, the response excludes the row.
    • If columnsToGet and filter are used at the same time, the system first obtains the columns specified by columnsToGet and then filters the returned columns.
    maxVersions The maximum number of versions that can be read.
    Note You must set at least one of the following parameters: maxVersions and timeRange.
    • If only maxVersions is specified, data of the specified number of versions is returned starting from the latest version.
    • If only timeRange is specified, all data whose versions are within the specified time range or data of the specified version is returned.
    • If both maxVersions and timeRange are specified, data of the specified number of versions within the time range is returned starting from the latest version.
    timeRange The versions within a specified time range or the specified version to read. For more information, see TimeRange.
    Note You must set at least one of the following parameters: maxVersions and timeRange.
    • If only maxVersions is specified, data of the specified number of versions is returned starting from the latest version.
    • If only timeRange is specified, all data whose versions are within the specified time range or data of the specified version is returned.
    • If both maxVersions and timeRange are specified, data of the specified number of versions within the time range is returned starting from the latest version.
    • To query data within a range, you must set start and end. start indicates the start timestamp. end indicates the end timestamp. The specified range is a left-closed and right-open interval, which includes the start value and excludes the end value.
    • To query data of a specific version, you must set timestamp. timestamp indicates a specified timestamp.

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

    The timestamp value ranges from 0 to Long.MAX_VALUE. Unit: milliseconds.

    filter The filter used 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 columnsToGet and filter are used at the same time, the system first obtains the columns specified by columnsToGet and then filters the returned columns.
    nextStartPrimaryKey The start primary key of the next read. 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 within the range is returned.
  • Example 1
    The following code provides an example on how to read data within a specified range in the forward direction. 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. 
        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 value of the second primary key column in the start primary key is INF_MIN. The value of the second primary key column in the end primary key 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 to. In this example, two primary keys 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;
            }
        }
    }