All Products
Search
Document Center

Tablestore:Write data

Last Updated:Nov 10, 2023

Tablestore provides the PutRow operation that allows you to write a single row of data, the UpdateRow operation that allows you to update a single row of data, and the BatchWriteRow operation that allows you to write multiple rows of data at a time.

Note

Rows are the basic units of tables. Rows consist of primary key columns and attribute columns. Primary key columns are required for each row. Rows in a table contain primary key columns of the same names and same data types. Attribute columns are optional for each row. Rows in a table can contain different attribute columns. For more information, see Overview.

Write a single row of data

You can call the PutRow operation to write a row of data. If the row exists, the PutRow operation deletes all versions of data in all columns from the existing row and writes new data. This operation is applicable to scenarios in which you want to write a small amount of data.

Before you write a single row of data, you can configure the following settings based on your business requirements:

  • By default, the system uses the current UNIX timestamp as a data version number. A UNIX timestamp represents the number of milliseconds that have elapsed since January 1, 1970, 00:00:00 UTC. You can also specify a custom data version number. For more information, see Data versions and TTL.

  • Specify row existence and column-based conditions for conditional update. For more information, see Conditional update.

Responses vary based on whether the operation is successful.

  • If the operation is successful, Tablestore returns the number of capacity units (CUs) consumed by the operation.

  • If an error occurs, Tablestore returns an error code. For example, a parameter fails the check, excessive data exists in a row, or a row existence check fails.

    Note

    For more information about error codes, see Error codes.

Update a single row of data

You can call the UpdateRow operation to update the data in a row. You can add attribute columns to a row, remove attribute columns from a row, delete a specific version of data from an attribute column, or update the value of an attribute column. If the row does not exist, a new row is inserted. This operation is applicable to scenarios in which you want to update the existing data, such as removing an attribute column, deleting a specific version of data, or updating the value of an attribute column.

Note

If you call the UpdateRow operation only to remove columns from a row and the row does not exist, no row is inserted into the table.

Before you update a single row of data, you can configure the following settings based on your business requirements:

  • You can add attribute columns to a row, remove attribute columns from a row, delete a specific version of data from an attribute column, or update the value of an attribute column in a request.

  • By default, the system uses the current UNIX timestamp as a data version number. A UNIX timestamp represents the number of milliseconds that have elapsed since January 1, 1970, 00:00:00 UTC. You can also specify a custom data version number. For more information, see Data versions and TTL.

  • Specify row existence and column-based conditions for conditional update. For more information, see Conditional update.

Write multiple rows of data at a time

You can call the BatchWriteRow operation to write multiple rows of data to one or more tables at a time. This operation is applicable to scenarios in which you want to write, delete, or update a large amount of data and scenarios in which you want to write, delete, and update data at the same time.

The BatchWriteRow operation consists of multiple PutRow, UpdateRow, and DeleteRow operations. When you call the BatchWriteRow operation, the process of constructing a suboperation is the same as the process of calling the PutRow, UpdateRow, or DeleteRow operation.

When you call the BatchWriteRow operation, each PutRow, UpdateRow, or DeleteRow operation is separately performed and the response to each operation is separately returned.

Before you write multiple rows of data at a time, you can configure the following settings based on your business requirements:

  • You can write data to, update data in, or delete data from multiple tables at a time in a request.

    • When you call the BatchWriteRow operation to write multiple rows at a time, some rows may fail to be written. If this happens, Tablestore does not return exceptions, but returns BatchWriteRowResponse in which the information about the failed rows is included. 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 rows are written. If you do not check the return values, you may ignore the rows that fail to be written.

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

  • You can separately configure update conditions for each PutRow, UpdateRow, or DeleteRow operation in a request.

  • By default, the system uses the current UNIX timestamp as a data version number. A UNIX timestamp represents the number of milliseconds that have elapsed since January 1, 1970, 00:00:00 UTC. You can also specify a custom data version number. For more information, see Data versions and TTL.

  • Specify row existence and column-based conditions for conditional update. For more information, see Conditional update.

Usage methods

Use the Tablestore console

You can write or update a single row of data in the Tablestore console.

  1. Log on to the Tablestore console.

  2. On the Overview page, find the instance that you want to manage and click Manage Instance in the Actions column.

  3. On the Tables tab of the Instance Details tab, click the name of the table that you want to manage.

  4. On the Query Data tab of the Manage Table page, write or update data based on your business requirements.

    Write a single row of data

    1. Click Insert.

    2. In the Insert dialog box, enter values in the Primary Key Value column.

    3. Click Add Column and configure the Name, Type, Value, and Version parameters.

      If you want to add multiple attribute columns, click Add Column and configure the corresponding parameters.

    4. Click OK.

    Update a single row of data

    1. Select the row that you want to update and click Update.

    2. In the Update dialog box, modify the attribute column information based on your business requirements.

      To add an attribute column, click Add Column and configure the corresponding parameters. To remove an attribute column, select Delete All from the Actions drop-down list. To delete a specific version of data, select Delete from the Actions drop-down list and select the version number of the data that you want to delete. To update the value of an attribute column, select Update from the Actions drop-down list and modify the value.

    3. Click OK.

Use the Tablestore CLI

You can run the following commands to write data on the Tablestore CLI:

  • Run the put command to write a single row of data. For more information, see the Insert a row of data section of the "Operations on data" topic.

    The following sample code provides an example on how to write a row of data to a table. The value of the first primary key column of the row is 86, and the value of the second primary key column is 6771. The row has two attribute columns: the name column of the STRING type and the country column of the STRING type.

    put --pk '["86", 6771]' --attr '[{"c":"name", "v":"redchen"}, {"c":"country", "v":"china"}]'
  • Run the update command to update a row of data. For more information, see the Update a row of data section of the "Operations on data" topic.

    The following sample code provides an example on how to update the data in a row whose value of the first primary key column is 86 and whose value of the second primary key column is 6771. Data is inserted regardless of whether the row exists. If the row exists, the inserted data overwrites the existing data.

    update --pk '["86", 6771]' --attr '[{"c":"name", "v":"redchen"}, {"c":"country", "v":"china"}]' --condition ignore

Use Tablestore SDKs

You can use Tablestore SDK for Java, Tablestore SDK for Go, Tablestore SDK for Python, Tablestore SDK for Node.js, Tablestore SDK for .NET, or Tablestore SDK for PHP to write data. In this example, Tablestore SDK for Java is used.

Write a single row of data

When you write data, you can specify a custom data version number, and specify column-based and row existence conditions.

  • Use the data version number that is automatically generated by the system when you write a row of data

    The following sample code provides an example on how to write a row that contains 10 attribute columns, each of which stores data of only one version. In this example, data version numbers are automatically generated by the system.

    private static void putRow(SyncClient client, String pkValue) {
        // Construct the primary key. 
        PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
        primaryKeyBuilder.addPrimaryKeyColumn("pk", PrimaryKeyValue.fromString(pkValue));
        PrimaryKey primaryKey = primaryKeyBuilder.build();
        // Specify the name of the table. 
        RowPutChange rowPutChange = new RowPutChange("<TABLE_NAME>", primaryKey);
    
        // Add attribute columns. 
        for (int i = 0; i < 10; i++) {
            rowPutChange.addColumn(new Column("Col" + i, ColumnValue.fromLong(i)));
        }
    
        client.putRow(new PutRowRequest(rowPutChange));
    }
                        
  • Specify a custom data version number when you write a row of data

    The following sample code provides an example on how to write a row that contains 10 attribute columns, each of which stores data of three versions. In this example, custom data version numbers are specified.

    private static void putRow(SyncClient client, String pkValue) {
        // Construct the primary key. 
        PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
        primaryKeyBuilder.addPrimaryKeyColumn("pk", PrimaryKeyValue.fromString(pkValue));
        PrimaryKey primaryKey = primaryKeyBuilder.build();
        // Specify the name of the table. 
        RowPutChange rowPutChange = new RowPutChange("<TABLE_NAME>", primaryKey);
    
        // Add attribute columns. 
        long ts = System.currentTimeMillis();
        for (int i = 0; i < 10; i++) {
            for (int j = 0; j < 3; j++) {
                rowPutChange.addColumn(new Column("Col" + i, ColumnValue.fromLong(j), ts + j));
            }
        }
    
        client.putRow(new PutRowRequest(rowPutChange));
    }
                        
  • Specify a row existence condition when you write a row of data

    The following sample code provides an example on how to write a row that contains 10 attribute columns, each of which stores data of three versions, when the specified row does not exist. In this example, custom data version numbers are specified.

    private static void putRow(SyncClient client, String pkValue) {
        // Construct the primary key. 
        PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
        primaryKeyBuilder.addPrimaryKeyColumn("pk", PrimaryKeyValue.fromString(pkValue));
        PrimaryKey primaryKey = primaryKeyBuilder.build();
        // Specify the name of the table. 
        RowPutChange rowPutChange = new RowPutChange("<TABLE_NAME>", primaryKey);
    
        // Specify a row existence condition that expects the specified row to not exist. 
        rowPutChange.setCondition(new Condition(RowExistenceExpectation.EXPECT_NOT_EXIST));
    
        // Add attribute columns. 
        long ts = System.currentTimeMillis();
        for (int i = 0; i < 10; i++) {
            for (int j = 0; j < 3; j++) {
                rowPutChange.addColumn(new Column("Col" + i, ColumnValue.fromLong(j), ts + j));
            }
        }
    
        client.putRow(new PutRowRequest(rowPutChange));
    }                   
  • Specify a column-based condition and a row existence condition when you write a row of data

    The following sample code provides an example on how to write a row that contains 10 attribute columns, each of which stores data of three versions, when the specified row exists and the value of the Col0 column is greater than 100. In this example, custom data version numbers are specified.

    private static void putRow(SyncClient client, String pkValue) {
        // Construct the primary key. 
        PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
        primaryKeyBuilder.addPrimaryKeyColumn("pk", PrimaryKeyValue.fromString(pkValue));
        PrimaryKey primaryKey = primaryKeyBuilder.build();
        // Specify the name of the table. 
        RowPutChange rowPutChange = new RowPutChange("<TABLE_NAME>", primaryKey);
    
        // Specify a row existence condition and a column-based condition that expect the specified row to exist and the value of the Col0 column to be greater than 100. 
        Condition condition = new Condition(RowExistenceExpectation.EXPECT_EXIST);
        condition.setColumnCondition(new SingleColumnValueCondition("Col0",
                SingleColumnValueCondition.CompareOperator.GREATER_THAN, ColumnValue.fromLong(100)));
        rowPutChange.setCondition(condition);
    
        // Add attribute columns. 
        long ts = System.currentTimeMillis();
        for (int i = 0; i < 10; i++) {
            for (int j = 0; j < 3; j++) {
                rowPutChange.addColumn(new Column("Col" + i, ColumnValue.fromLong(j), ts + j));
            }
        }
    
        client.putRow(new PutRowRequest(rowPutChange));
    }
                        

Update a single row of data

When you update a single row of data, you can specify a custom data version number, and specify column-based and row existence conditions.

  • Update a row of data without specifying conditions

    The following sample code provides an example on how to update multiple columns of a row, delete a specific version of data from a column, and remove a column:

    private static void updateRow(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 name of the table. 
        RowUpdateChange rowUpdateChange = new RowUpdateChange("<TABLE_NAME>", primaryKey);
    
        // Update columns. 
        for (int i = 0; i < 10; i++) {
            rowUpdateChange.put(new Column("Col" + i, ColumnValue.fromLong(i)));
        }
    
        // Delete a specific version of data from a column. 
        rowUpdateChange.deleteColumn("Col10", 1465373223000L);
    
        // Remove a column. 
        rowUpdateChange.deleteColumns("Col11");
    
        client.updateRow(new UpdateRowRequest(rowUpdateChange));
    }                   
  • Specify a column-based condition and a row existence condition when you update a row of data

    The following sample code provides an example on how to update a row of data when the specified row exists and the value of the Col0 column is greater than 100:

    private static void updateRow(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 name of the table. 
        RowUpdateChange rowUpdateChange = new RowUpdateChange("<TABLE_NAME>", primaryKey);
    
        // Specify a row existence condition and a column-based condition that expect the specified row to exist and the value of the Col0 column to be greater than 100. 
        Condition condition = new Condition(RowExistenceExpectation.EXPECT_EXIST);
        condition.setColumnCondition(new SingleColumnValueCondition("Col0",
                SingleColumnValueCondition.CompareOperator.GREATER_THAN, ColumnValue.fromLong(100)));
        rowUpdateChange.setCondition(condition);
    
        // Update columns. 
        for (int i = 0; i < 10; i++) {
            rowUpdateChange.put(new Column("Col" + i, ColumnValue.fromLong(i)));
        }
    
        // Delete a specific version of data from a column. 
        rowUpdateChange.deleteColumn("Col10", 1465373223000L);
    
        // Remove a column. 
        rowUpdateChange.deleteColumns("Col11");
    
        client.updateRow(new UpdateRowRequest(rowUpdateChange));
    }         

Write multiple rows of data at a time

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

private static void batchWriteRow(SyncClient client) {
    BatchWriteRowRequest batchWriteRowRequest = new BatchWriteRowRequest();

    // Construct rowPutChange1. 
    PrimaryKeyBuilder pk1Builder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    pk1Builder.addPrimaryKeyColumn("pk", PrimaryKeyValue.fromString("pk1"));
    // Specify the name of the table. 
    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 code of the batch operation. 
    batchWriteRowRequest.addRowChange(rowPutChange1);

    // Construct rowPutChange2. 
    PrimaryKeyBuilder pk2Builder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    pk2Builder.addPrimaryKeyColumn("pk", PrimaryKeyValue.fromString("pk2"));
    // Specify the name of the table. 
    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 code of the batch operation. 
    batchWriteRowRequest.addRowChange(rowPutChange2);

    // Construct rowUpdateChange. 
    PrimaryKeyBuilder pk3Builder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    pk3Builder.addPrimaryKeyColumn("pk", PrimaryKeyValue.fromString("pk3"));
    // Specify the name of the table. 
    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)));
    }
    // Remove a column. 
    rowUpdateChange.deleteColumns("Col10");
    // Add rowUpdateChange to the code of the batch operation. 
    batchWriteRowRequest.addRowChange(rowUpdateChange);

    // Construct rowDeleteChange. 
    PrimaryKeyBuilder pk4Builder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    pk4Builder.addPrimaryKeyColumn("pk", PrimaryKeyValue.fromString("pk4"));
    // Specify the name of the table. 
    RowDeleteChange rowDeleteChange = new RowDeleteChange("<TABLE_NAME>", pk4Builder.build());
    // Add rowDeleteChange to the code of 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 batch operation on failed rows. In this example, only the retry request is constructed. 
         * We recommend that you use the custom retry policy in Tablestore SDKs. You can use this policy to retry the batch operation on failed rows. After you configure the retry policy, you do not need to add retry code to call the operation. 
         */
        BatchWriteRowRequest retryRequest = batchWriteRowRequest.createRequestForRetry(response.getFailedRows());
    }
}            

Billing

You are charged based on the number of CUs consumed by an operation. The consumed CUs may include the metered read and write CUs and the reserved read and write CUs based on the instance type.

Note

For more information about instance types and CUs, see Instance and Read and write throughput.

Operation

CU consumption

PutRow

  • The number of consumed write CUs is rounded up from the calculation result of the following formula: Number of consumed write CUs = (Size of the data in all primary key columns of the row + Size of the data in the inserted attribute columns)/4 KB.

  • The number of consumed read CUs is based on the conditions that you specify. You can specify the condition parameter in a request for the PutRow operation to determine whether to perform a row existence check before the PutRow operation is performed.

    • If the value of the condition parameter is not IGNORE, 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/4 KB.

    • If the specified row existence condition is not met, the operation fails, and one write CU and one read CU are consumed.

UpdateRow

  • The number of consumed write CUs is rounded up from the calculation result of the following formula: Number of consumed write CUs = (Size of the data in all primary key columns of the row + Size of the data in the updated attribute columns)/4 KB.

  • If a request for the UpdateRow operation contains the instructions to remove attribute columns, the length of the name of each attribute column that you want to remove is used as the column size.

  • The number of consumed read CUs is based on the conditions that you specify. You can specify the condition parameter in a request for the UpdateRow operation to determine whether to perform a row existence check before the UpdateRow operation is performed.

    • If the value of the condition parameter is not IGNORE, 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/4 KB.

    • If the specified row existence condition is not met, the operation fails, and one write CU and one read CU are consumed.

BatchWriteRow

The number of consumed read and write CUs is separately calculated based on the PutRow, UpdateRow, and DeleteRow operations contained in a request for the BatchWriteRow operation.

The number of read and write CUs that are consumed by the DeleteRow operation is calculated based on the following rules:

  • The number of consumed write CUs is rounded up from the calculation result of the following formula: Number of consumed write CUs = Size of the data in all primary key columns of the deleted row/4 KB.

  • If the value of the condition parameter is not IGNORE, 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/4 KB.

  • If the specified row existence condition is not met, the operation fails, and one write CU is consumed.