Tablestore provides the PutRow and UpdateRow operations to allow you to write a single row of data, and the BatchWriteRow operation to allow you to write multiple rows of data in a batch.

Prerequisites

  • The OTSClient instance is initialized. For more information, see Initialization.
  • A data table is created. Data is written to the table.

Insert a single row of data

You can call the PutRow operation to insert a row of data. If the row exists, the PutRow operation deletes all versions of data in all columns from the existing row, and then inserts a new row.

API operations

/**
 * 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 then inserts a new row. The number of capacity units (CUs) that are consumed by the operation is returned. 
 * @api
 * @param [] $request The request parameters. 
 * @return [] The response.  
 * @throws OTSClientException The exception that is returned when a parameter error occurs or the Tablestore server returns a verification error. 
 * @throws OTSServerException The exception that is returned when the Tablestore server returns an error. 
 */
public function putRow(array $request);            

Request parameters

Parameter Description
table_name The name of the data table.
condition The condition that you want to configure to perform the PutRow operation. You can configure a row existence condition or a condition based on column values. For more information, see Configure conditional update.
  • row_existence: the row existence condition.
    Note
    • RowExistenceExpectationConst::CONST_IGNORE indicates that new data is inserted into a row regardless of whether the specified row exists. If the specified row exists, the existing data is overwritten.
    • RowExistenceExpectationConst::CONST_EXPECT_EXIST indicates that new data is inserted only when the specified row exists. The existing data is overwritten.
    • RowExistenceExpectationConst::CONST_EXPECT_NOT_EXIST indicates that new data is inserted only when the specified row does not exist.
  • column_condition: the condition based on column values.
primary_key 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.
  • If a primary key column is an auto-increment primary key column, you need to only set the value of the auto-increment primary key column to a placeholder. For more information, see Configure an auto-increment primary key column.
  • The primary key of a table can contain one to four primary key columns. Primary key columns are sorted in the order in which they are added. For example, PRIMARY KEY (A, B, C) and PRIMARY KEY (A, C, B) have different schemas. Tablestore sorts rows based on the values of all primary key columns.
  • Each primary key column is specified by parameters in the following sequence: the primary key column name, primary key column value (PrimaryKeyValue), and primary key column type (PrimaryKeyType). PrimaryKeyType is optional.
  • The value of PrimaryKeyValue can be an integer, a binary, or a string.
  • You can set PrimaryKeyType to PrimaryKeyTypeConst::CONST_INTEGER that specifies an INTEGER value, PrimaryKeyTypeConst::CONST_STRING that specifies a UTF-8 encoded STRING, PrimaryKeyTypeConst::CONST_BINARY that specifies a BINARY value, or PrimaryKeyTypeConst::CONST_PK_AUTO_INCR that specifies an auto-increment primary key column. If you want to set the primary key column type to BINARY or PK_AUTO_INCR, you must set PrimaryKeyType to PrimaryKeyTypeConst::CONST_BINARY or PrimaryKeyTypeConst::CONST_PK_AUTO_INCR. If you want to use other types of primary key columns, the PrimaryKeyTypeConst::CONST_ is optional.
attribute_columns The attribute columns of the row.
  • Each attribute column is specified by parameters in the following sequence: the attribute column name, attribute column value (ColumnValue), attribute column value type (ColumnType), and timestamp. ColumnType and the timestamp are optional.
  • You can set ColumnType to ColumnTypeConst::CONST_INTEGER that specifies an INTEGER value, ColumnTypeConst::CONST_STRING that specifies a UTF-8 encoded STRING, ColumnTypeConst::CONST_BINARY that specifies a BINARY value, ColumnTypeConst::CONST_BOOLEAN that specifies a BOOLEAN value, or ColumnTypeConst::CONST_DOUBLE that specifies a DOUBLE value. If you want to set the column value type to BINARY, you must set ColumnType to ColumnTypeConst::CONST_BINARY. If you want to use other types of column values, the ColumnTypeConst::CONST_ is optional. You can also set ColumnType to null.
  • The timestamp is the data version number. For more information, see Data versions and TTL.

    You can specify a data version number or use the data version number that is generated by Tablestore. If you do not configure this parameter, the data version number that is generated by Tablestore is used.

    • The version number that is generated by Tablestore is the number of milliseconds that have elapsed since 00:00:00 UTC on January 1, 1970.
    • If you specify the version number, make sure that the version number is a 64-bit timestamp that is accurate to milliseconds and is in the valid version range.
return_content The content that you want to return.

return_type: You can set the value to ReturnTypeConst::CONST_PK to return the primary key of the row. This parameter is used by the auto-increment primary key column feature.

Request format

$result = $client->putRow([
    'table_name' => '<string>', // Specify the name of the data table. 
    'condition' => [
        'row_existence' => <RowExistence>,   
        'column_condition' => <ColumnCondition>
    ],
    'primary_key' => [                              // Specify the primary key. 
        ['<string>', <PrimaryKeyValue>], 
        ['<string>', <PrimaryKeyValue>],
        ['<string>', <PrimaryKeyValue>, <PrimaryKeyType>]
    ],  
    'attribute_columns' => [         // Specify the attribute columns. 
            ['<string>', <ColumnValue>], 
            ['<string>', <ColumnValue>, <ColumnType>],
            ['<string>', <ColumnValue>, <ColumnType>, <integer>]
    ],
    'return_content' => [
        'return_type' => <ReturnType>
    ]
]);         

Response parameters

Parameter Description
consumed The number of CUs that are consumed by the operation.
capacity_unit indicates the number of read and write CUs that are consumed.
  • read: the read throughput
  • write: the write throughput
primary_key The value of the primary key, which is consistent with that in the request.
Note If you set return_type to ReturnTypeConst::CONST_PK, the value of the primary key is returned. This parameter is used by the auto-increment primary key column feature.
attribute_columns The values of attribute columns, which are consistent with those in the request. At present, the value of this parameter is empty.

Response format

[
    'consumed' => [
        'capacity_unit' => [
            'read' => <integer>,
            'write' => <integer>
        ]
    ],
    'primary_key' => [ 
        ['<string>', <PrimaryKeyValue>], 
        ['<string>', <PrimaryKeyValue>],
        ['<string>', <PrimaryKeyValue>, <PrimaryKeyType>]
    ],  
    'attribute_columns' => []
]        

Examples

  • Example 1

    The following code provides an example on how to write a row that contains 10 attribute columns, each of which stores data of only one version. The version numbers (timestamps) are generated by Tablestore.

    $attr = array();
    for($i = 0; $i < 10; $i++) {
        $attr[] = ['Col'. $i, $i]; 
    }
    $request = [
        'table_name' => 'MyTable',
        'condition' => RowExistenceExpectationConst::CONST_IGNORE, // You can set condition to IGNORE, EXPECT_EXIST, or EXPECT_NOT_EXIST. 
        'primary_key' => [ // Specify the primary key. 
            ['PK0', 123],
            ['PK1', 'abc']
        ],
        'attribute_columns' => $attr
    ];
    $response = $otsClient->putRow ($request);            
  • Example 2

    The following 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, you need to specify the version numbers (timestamps).

    $attr = array();
    $timestamp = getMicroTime();
    for($i = 0; $i < 10; $i++) {
        for($j = 0; $j < 3; $j++) {
            $attr[] = ['Col'. $i, $j, null, $timestamp+$j];
        }
    }
    $request = [
        'table_name' => 'MyTable',
        'condition' => RowExistenceExpectationConst::CONST_IGNORE, // You can set condition to IGNORE, EXPECT_EXIST, or EXPECT_NOT_EXIST. 
        'primary_key' => [ // Specify the primary key. 
            ['PK0', 123],
            ['PK1', 'abc']
        ],
        'attribute_columns' => $attr
    ];
    $response = $otsClient->putRow ($request);            
  • Example 3

    The following 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, you need to specify the version numbers (timestamps).

    $attr = array();
    $timestamp = getMicroTime();
    for($i = 0; $i < 10; $i++) {
        for($j = 0; $j < 3; $j++) {
            $attr[] = ['Col'. $i, $j, null, $timestamp+$j];
        }
    }
    $request = [
        'table_name' => 'MyTable',
        'condition' => RowExistenceExpectationConst::CONST_EXPECT_NOT_EXIST, // Configure the condition parameter to write data when the specified row does not exist. 
        'primary_key' => [ // Specify the primary key. 
            ['PK0', 123],
            ['PK1', 'abc']
        ],
        'attribute_columns' => $attr
    ];
    $response = $otsClient->putRow ($request);            
  • Example 4

    The following 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, you need to specify the version numbers (timestamps).

    $attr = array();
    $timestamp = getMicroTime();
    for($i = 0; $i < 10; $i++) {
        for($j = 0; $j < 3; $j++) {
            $attr[] = ['Col'. $i, $j, null, $timestamp+$j];
        }
    }
    $request = [
        'table_name' => 'MyTable',
        'condition' => [
            'row_existence' => RowExistenceExpectationConst::CONST_EXPECT_EXIST, // Configure the condition parameter to write data when the specified row exists. 
            'column_condition' => [                  // If the condition is met, the data is updated. 
                'column_name' => 'Col0',
                'value' => 100,
                'comparator' => ComparatorTypeConst::CONST_GREATER_THAN
            ]
        ,
        'primary_key' => [ // Specify the primary key. 
            ['PK0', 123],
            ['PK1', 'abc']
        ],
        'attribute_columns' => $attr
    ];
    $response = $otsClient->putRow ($request);           

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 or delete attribute columns from a row, delete a specified version of data from an attribute column, or update the existing data in an attribute column. If the row does not exist, a new row is added.
Note If you call the UpdateRow operation only to delete columns from a row and the row does not exist, no row is inserted into the table.

API operations

/**
 * Update a row of data. 
 * @api
 * @param [] $request The request parameters. 
 * @return [] The response. 
 * @throws OTSClientException The exception that is returned when a parameter error occurs or the Tablestore server returns a verification error. 
 * @throws OTSServerException The exception that is returned when the Tablestore server returns an error. 
 */
public function updateRow(array $request);            

Request parameters

Parameter Description
table_name The name of the data table.
condition The condition that you want to specify to perform the UpdateRow operation. You can specify a row existence condition or a condition based on column values. For more information, see Configure conditional update.
primary_key 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.
update_of_attribute_columns The attribute columns you want to update.
  • Each attribute column is specified by parameters in the following sequence: the attribute column name, attribute column value, attribute column value type, and timestamp. The attribute column value type and the timestamp are optional.

    A timestamp is a data version number. You can specify a data version number or use the data version number that is generated by Tablestore. By default, if you do not configure this parameter, the data version number that is generated by Tablestore is used. For more information, see Data versions and TTL.

    • The version number that is generated by Tablestore is the number of milliseconds that have elapsed since 00:00:00 UTC on January 1, 1970.
    • If you specify the version number, make sure that the version number is a 64-bit timestamp that is accurate to milliseconds and is in the valid version range.
  • To delete a specified version of data from an attribute column, you need to only specify the attribute column name and timestamp.

    The timestamp is a 64-bit integer that indicates a specified version of data. Unit: milliseconds.

  • To delete an attribute column, you need to only specify the attribute column name.
    Note A row exists even if all attribute columns in the row are deleted. To delete a row, use the DeleteRow operation.
return_content The content that you want to return.

return_type: You can set the value only to ReturnTypeConst::CONST_PK to return the primary key of the row. This parameter is used by the auto-increment primary key column feature.

Request format

$result = $client->updateRow([
    'table_name' => '<string>', // Specify the name of the data table. 
    'condition' => [
        'row_existence' => <RowExistence>,
        'column_condition' => <ColumnCondition>
    ],
    'primary_key' => [                              // Specify the primary key. 
        ['<string>', <PrimaryKeyValue>], 
        ['<string>', <PrimaryKeyValue>],
        ['<string>', <PrimaryKeyValue>, <PrimaryKeyType>]
    ], 
    'update_of_attribute_columns' => [         // Specify the attribute columns that you want to update. 
        'PUT' => [
            ['<string>', <ColumnValue>], 
            ['<string>', <ColumnValue>, <ColumnType>],
            ['<string>', <ColumnValue>, <ColumnType>, <integer>]
        ],
        'DELETE' => [
            ['<string>', <integer>], 
            ['<string>', <integer>], 
            ['<string>', <integer>], 
            ['<string>', <integer>]
        ],
        'DELETE_ALL' => [
            '<string>',
            '<string>',
            '<string>',
            '<string>'
        ],        
    ],
    'return_content' => [
        'return_type' => <ReturnType>
    ]
]);            

Response parameters

Parameter Description
consumed The number of CUs that are consumed by the operation.
capacity_unit indicates the number of read and write CUs that are consumed.
  • read: the read throughput
  • write: the write throughput
primary_key The value of the primary key, which is consistent with that in the request.
Note If you set return_type to ReturnTypeConst::CONST_PK, the value of the primary key is returned. This parameter is used by the auto-increment primary key column feature.
attribute_columns The values of attribute columns, which are consistent with those in the request. At present, the value of this parameter is empty.

Result format

[
    'consumed' => [
        'capacity_unit' => [
            'read' => <integer>,
            'write' => <integer>
        ]
    ],
    'primary_key' => [ 
        ['<string>', <PrimaryKeyValue>], 
        ['<string>', <PrimaryKeyValue>],
        ['<string>', <PrimaryKeyValue>, <PrimaryKeyType>]
    ],  
    'attribute_columns' => []
]            

Examples

  • Example 1

    The following code provides an example on how to update multiple columns, delete the specified version of a column, and delete the specified column:

    $request = [
        'table_name' => 'MyTable',
        'condition' => RowExistenceExpectationConst::CONST_IGNORE,
        'primary_key' => [ // Specify the primary key. 
            ['PK0', 123],
            ['PK1', 'abc']
        ],
        'update_of_attribute_columns' => [
            'PUT' => [                       // Update multiple columns. 
                ['Col0', 100],
                ['Col1', 'Hello'],
                ['Col2', 'a binary', ColumnTypeConst::CONST_BINARY],
                ['Col3', 100, null, 1526418378526]
            ],
            'DELETE' => [                    // Delete a version of a column. 
                ['Col10', 1526418378526]
            ],
            'DELETE_ALL' => [
                'Col11'                      // Delete a column. 
            ]
        ]
    ];
    $response = $otsClient->updateRow($request);            
  • Example 2

    The following code provides an example on how to specify a condition for the UpdateRow operation:

    $request = [
        'table_name' => 'MyTable',
        'primary_key' => [ // Specify the primary key. 
            ['PK0', 123],
            ['PK1', 'abc']
        ],
        'condition' => [
            'row_existence' => RowExistenceExpectationConst::CONST_EXPECT_EXIST, // Configure the condition parameter to update data when the specified row exists. 
            'column_filter' => [                                                 // Update the row when the value of the Col0 column is greater than 100. 
                'column_name' => 'Col0',
                'value' => 100,
                'comparator' => ComparatorTypeConst::CONST_GREATER_THAN
            ]
        ],    
        'update_of_attribute_columns' => [
            'PUT' => [                       // Update multiple columns. 
                ['Col0', 100],
                ['Col1', 'Hello'],
                ['Col2', 'a binary', ColumnTypeConst::CONST_BINARY],
                ['Col3', 100, null, 1526418378526]
            ],
            'DELETE' => [                    // Delete a version of a column. 
                ['Col10', 1526418378526]
            ],
            'DELETE_ALL' => [
                'Col11'                      // Delete a column. 
            ]
        ]
    ];         

Write multiple rows of data in a batch

You can call the BatchWriteRow operation to write multiple rows to one or more tables in a batch. The BatchWriteRow operation is a set of PutRow, UpdateRow, or DeleteRow operations. When you call the BatchWriteRow operation, the process of constructing the PutRow, UpdateRow, or DeleteRow operations is the same as the process of constructing the PutRow, UpdateRow, or DeleteRow operation when you call the PutRow, UpdateRow, or DeleteRow operation. BatchWriteRow supports conditional update.

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

Usage notes

When you call the BatchWriteRow operation to write multiple rows in a batch, some rows may fail to be written. If this happens, Tablestore does not return exceptions, but returns BatchWriteRowResponse in which the indexes and error messages of the failed rows are included. Therefore, when you call the BatchWriteRow operation, you must check the return values to determine whether the operation on each row is successful. If you do not check the return values, failures of operations on some rows are ignored.

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.

API operations

/**
 * Write, update, or delete the specified rows of data. 
 * When you call the BatchWriteRow operation to write multiple rows in a batch, if some rows fail to be written, the system does not return an exception. However, information about the failed rows is contained in $response. For more information, see the sample response to the BatchWriteRow operation. 
 * @api
 * @param [] $request The request parameters. 
 * @return [] The response. 
 * @throws OTSClientException The exception that is returned when a parameter error occurs or the Tablestore server returns a verification error. 
 * @throws OTSServerException The exception that is returned when the Tablestore server returns an error. 
 */
public function batchWriteRow(array $request);              

Request parameters

BatchWriteRow is a combination of PutRow, UpdateRow, or DeleteRow.
  • Hierarchies are created for tables. Multiple tables can be processed in a batch.

    You can configure the tables parameter to specify information about tables and rows on which you want to perform the write, update, or delete operations.

  • The operation_type parameter is added to distinguish between different operation types.
    You can use OperationTypeConst::CONST_PUT to set the operation type to PUT, use OperationTypeConst::CONST_UPDATE to set the operation type to UPDATE, and use OperationTypeConst::CONST_DELETE to set the operation type to DELETE.
    • If the operation type is PUT, primary_key and attribute_columns are valid.
    • If the operation type is UPDATE, primary_key and update_of_attribute_columns are valid.
    • If the operation type is DELETE, primary_key is valid.

Request format

$result = $client->batchWriteRow([
    'tables' => [                                            // Specify the hierarchies of tables. 
        [
            'table_name' => '<string>',                     // Specify the name of the data table. 
            'operation_type' => <OperationType>,
            'condition' => [
                'row_existence' => <RowExistence>,   
                'column_condition' => <ColumnCondition>
            ],
            'primary_key' => [                              // Specify the primary key. 
                ['<string>', <PrimaryKeyValue>], 
                ['<string>', <PrimaryKeyValue>],
                ['<string>', <PrimaryKeyValue>, <PrimaryKeyType>]
            ], 
            'attribute_columns' => [                        // This parameter is required when the operation type is PUT. 
                    ['<string>', <ColumnValue>], 
                    ['<string>', <ColumnValue>, <ColumnType>],
                    ['<string>', <ColumnValue>, <ColumnType>, <integer>]
            ],
            'update_of_attribute_columns' => [               // This parameter is required when the operation type is UPDATE. 
                'PUT' => [
                    ['<string>', <ColumnValue>], 
                    ['<string>', <ColumnValue>, <ColumnType>],
                    ['<string>', <ColumnValue>, <ColumnType>, <integer>]
                ],
                'DELETE' => [
                    ['<string>', <integer>], 
                    ['<string>', <integer>], 
                    ['<string>', <integer>], 
                    ['<string>', <integer>]
                ],
                'DELETE_ALL' => [
                    '<string>',
                    '<string>',
                    '<string>',
                    '<string>'
                ],
            ],
            'return_content' => [
                'return_type' => <ReturnType>
            ]
        ],
        // Other tables. 
    ]
]);        

Response parameters

Each table indicated by the tables parameter in the response corresponds to each table specified in the request. The following table describes the parameters in the response.
Parameter Description
table_name The name of the data table.
is_ok Indicates whether the operation on the row is successful.
  • A value of true indicates that the operation on the row is successful. In this case, the error parameter is invalid.
  • A value of false indicates that the operation on the row fails.
error The error information in the response when the operation fails.
  • code indicates the error code in the response when the operation on the row fails.
  • message indicates the error message in the response when the operation on the row fails.
consumed The number of CUs that are consumed by the operation.
capacity_unit indicates the number of read and write CUs that are consumed.
  • read: the read throughput
  • write: the write throughput
primary_key The value of the primary key, which is consistent with that in the request.

If a value is specified for return_type, the value of this parameter exists. This parameter is used for the auto-increment primary key column feature.

attribute_columns The values of attribute columns, which are consistent with those in the request. At present, the value of this parameter is empty.

Result format

[
    'tables' => [
        [
            'table_name' => '<string>',
            'rows' => [
                [
                    'is_ok' => true || false,
                    'error' => [
                        'code' => '<string>',
                        'message' => '<string>',
                    ]
                    'consumed' => [
                        'capacity_unit' => [
                            'read' => <integer>,
                            'write' => <integer>
                        ]
                    ],
                    'primary_key' => [ 
                        ['<string>', <PrimaryKeyValue>], 
                        ['<string>', <PrimaryKeyValue>],
                        ['<string>', <PrimaryKeyValue>, <PrimaryKeyType>]
                    ],
                    'attribute_columns' => []
                ],
                // Other rows. 
            ]
        ],
        // Other tables. 
    ]
]           

Examples

The following code provides an example on how to write 30 rows of data to three tables in a batch. Ten rows are written to each table.

// Write data to three tables. Ten rows are written to each table. 
$tables = array();
for($i = 0; $i < 3; $i++) {
    $rows = array();
    for($j = 0; $j < 10; $j++) {
        $rows[] = [
            'operation_type' => OperationTypeConst::CONST_PUT, // Set the operation type to PUT. 
            'condition' => RowExistenceExpectationConst::CONST_IGNORE,
            'primary_key' => [
                ['pk0', $i],
                ['pk1', $j]
            ],
            'attribute_columns' => [
                ['Col0', 4],
                ['Col2', 'Beijing']
            ]
        ];
    }
    $tables[] = [
        'table_name' => 'SampleTable' . $i,
        'rows' => $rows
    ];
}
$request = [
    'tables' => $tables
];
$response = $otsClient->batchWriteRow ($request);
// Process each table that is returned. 
foreach ($response['tables'] as $tableData) {
  print "Handling table {$tableData['table_name']} ...\n";

  // Process the result that is returned by the PutRow operation on the table. 
  $putRows = $tableData['rows'];

  foreach ($putRows as $rowData) {

    if ($rowData['is_ok']) {
      // Data is written. 
      print "Capacity Unit Consumed: {$rowData['consumed']['capacity_unit']['write']}\n";
    } else {

      // Handle errors. 
      print "Error: {$rowData['error']['code']} {$rowData['error']['message']}\n";
    }
  }
}           

The following table describes the examples of detailed sample code.

Example Description
BatchWriteRow1.php Shows you how to perform multiple PUT operations in BatchWriteRow.
BatchWriteRow2.php Shows you how to perform multiple UPDATE operations in BatchWriteRow.
BatchWriteRow3.php Shows you how to perform multiple DELETE operations in BatchWriteRow.
BatchWriteRow4.php Shows you how to perform the UPDATE, PUT, and DELETE operations in BatchWriteRow.
BatchWriteRowWithColumnFilter.php Shows you how to use BatchWriteRow together with conditional update.