edit-icon download-icon

Data operations

Last Updated: Mar 27, 2018

In Table Store, tables are composed of rows. Each row includes primary keys and attributes. This topic introduces data operations of Table Store.

Table Store rows

A row is a basic feature in the creation of tables in Alibaba Cloud Table Store. Rows are composed of primary keys and attributes. A primary key is required and all rows in the same table must have the same primary key column name and type. Attributes are not required and each row may have different attributes. For more information, see Data model.

Table Store data operations include three types:

  • Single row operations

    • GetRow: Read a single row from the table.

    • PutRow: Insert a row into the table. If the row already exists, the existing row is deleted and the new row is written.

    • UpdateRow: Update a row from the table. You can add or delete attribute columns of an existing row or update the value of an existing attribute column. If the row does not exist, this operation adds a new row.

    • DeleteRow: Delete a row from the table.

  • Batch operations

    • BatchGetRow: Batch read data from multiple rows with one request.

    • BatchWriteRow: Batch insert, update, or delete multiple rows in one request.

  • Range read operations

    • GetRange: Read data from a table within a certain range.

Single row operations

Single row write operations

Table Store has three single row write operations. The following are the descriptions and considerations of these operations.

  • PutRow: Write a new row. If this row already exists, the existing row is deleted and the new row is written.

  • UpdateRow: Update the data of a row. Based on the request content, Table Store adds new columns or modifies/deletes the specified column values for this row. If this row does not exist, a new row is inserted. However, an UpdateRow request with only deletion instructions onto a row that does not exist does not insert a new row.

  • DeleteRow: Delete a row. If the row to be deleted does not exist, nothing happens.

By setting the condition field in the request, you can specify whether a row existence check is performed before executing the write operation. Three condition checking options are available.

  • IGNORE: The row existence check is not performed.

  • EXPECT_EXIST: The row is expected to exist. The operation succeeds only if the row exists. Otherwise, the operation fails.

  • EXPECT_NOT_EXIST: The row is not expected to exist. The operation succeeds only if the row does not exist. Otherwise, the operation fails.

If the condition checking is EXPECT_NOT_EXIST, DeleteRow and UpdateRow fail because it is meaningless to delete or update the non-existing rows. If the condition checking is EXPECT_NOT_EXIST, you can use PutRow to update a non-existing row.

Applications receive the number of consumed capacity units (CU) for successful operations. If the operation fails, such as a parameter check failure, the data size of the row is too large, or the existence check fails, an error code is returned to the application.

The rules for calculating the number of write capacity units (CU) consumed in each operation are defined as follows.

  • PutRow: The sum of the data size of the primary key of the modified row and the data size of the attribute column is divided by 4 KB and rounded up. If the row existence check condition is not IGNORE, a number of read CUs are consumed. This is equivalent to the rounded up value after dividing the data size of the primary key of this row by 4 KB. If an operation does not meet the row existence check condition specified by the application, the operation fails and consumes 1 write CU and 1 read CU. For more information, see API Reference - PutRow.

  • UpdateRow: The sum of the data size of the primary key of the modified row and the data size of the attribute column is divided by 4 KB and rounded up. If UpdateRow contains an attribute column which must be deleted, only the column name is calculated into the data size of this attribute column. If the row existence check condition is not IGNORE, a number of read CUs are consumed. This is equivalent to the rounded up value after dividing the data size of the primary key of this row by 4 KB. If an operation does not meet the row existence check condition specified by the application, the operation fails and consumes 1 write CU and 1 read CU. For more information, see API Reference - UpdateRow.

  • DeleteRow: The data size of the primary key of the deleted row is divided by 4 KB and rounded up. If the row existence check condition is not IGNORE, a number of read CUs are consumed. This is equivalent to the rounded up value after dividing the data size of the primary key of this row by 4 KB. If an operation does not meet the row existence check condition specified by the application, the operation fails and consumes 1 write capacity unit. For more information, see API Reference - DeleteRow.

Also, a certain number of read CUs are consumed by write operations based on specified conditions.

Examples

The following examples illustrate how the number of write CUs are calculated for single row write operations.

Example 1: Use PutRow to write a row.

  1. //PutRow operation
  2. //row_size=len('pk')+len('value1')+len('value2')+8Byte+1300Byte+3000Byte=4322Byte
  3. {
  4. primary_keys:{'pk':1},
  5. attributes:{'value1':String(1300Byte), 'value2':String(3000Byte)}
  6. }
  7. //Original row
  8. //row_size=len('pk')+len('value2')+8Byte+900Byte=916Byte
  9. //row_primarykey_size=len('pk')+8Byte=10Byte
  10. {
  11. primary_keys:{'pk':1},
  12. attributes:{'value2':String(900Byte)}
  13. }

The consumption of the read/write CUs for PutRow is described as follows.

  • When the existence check condition is set to EXPECT_EXIST: The number of write CUs consumed is the rounded up value after dividing 4,322 bytes by 4 KB, and the number of read CUs is the rounded up value after dividing 10 bytes (data size of the primary key of the row) by 4 KB. Therefore, PutRow consumes 2 write CUs and 1 read CU.

  • When the existence check condition is set to IGNORE: The number of write CUs consumed is the rounded up value after dividing 4,322 bytes by 4 KB. No read CU is consumed. Therefore, PutRow consumes 1 write CU and 0 read CU.

  • When the existence check condition is set to EXPECT_NOT_EXIST: The existence check condition of the specified row fails. PutRow consumes 1 write CU and 1 read CU.

Example 2: Use UpdateRow to write a new row.

  1. //UpdateRow operation
  2. //Length of attribute column deleted is calculated for row size
  3. //row_size=len('pk')+len('value1')+len('value2')+8Byte+900Byte=922Byte
  4. {
  5. primary_keys:{'pk':1},
  6. attributes:{'value1':String(900Byte), 'value2':Delete}
  7. }
  8. //The original row does not exist
  9. //row_size=0

The consumption of read/write CUs for UpdateRow is described as follows.

  • When the existence check condition is set to IGNORE: The number of write CUs consumed is the rounded up value after dividing 922 bytes by 4 KB. No read CU is consumed. Therefore, UpdateRow consumes 1 write CU and 0 read CU.

  • When the existence check condition is set to EXPECT_EXIST: The existence check condition of the specified row fails. UpdateRow consumes 1 write CU and 1 read CU.

Example 3: Use UpdateRow to update an existing row.

  1. //UpdateRow operation
  2. //row_size=len('pk')+len('value1')+len('value2')+8Byte+1300Byte+3000Byte=4322Byte
  3. {
  4. primary_keys:{'pk':1},
  5. attributes:{'value1':String(1300Byte), 'value2':String(3000Byte)}
  6. }
  7. //Original row
  8. //row_size=len('pk')+len('value1')+8Byte+900Byte=916Byte
  9. //row_primarykey_size=len('pk')+8Byte=10Byte
  10. {
  11. primary_keys:{'pk':1},
  12. attributes:{'value1':String(900Byte)}
  13. }

The consumption of read/write CUs for UpdateRow is described as follows.

  • When the existence check condition is set to EXPECT_EXIST: The number of write CUs consumed is the rounded up value after dividing 4,322 bytes by 4 KB, and the number of read CUs is the rounded up value after dividing 10 bytes (data size of the primary key of the row) by 4 KB. Therefore, UpdateRow consumes 2 write CUs and 1 read CU.

  • When the existence check condition is set to IGNORE: The number of write CUs consumed is the rounded up value after dividing 4,322 bytes by 4 KB. No read CU is consumed. Therefore, UpdateRow consumes 1 write CU and 0 read CU.

Example 4: Use DeleteRow to delete a non-existent row.

  1. //The original row does not exist
  2. //row_size=0
  3. //DeleteRow operation
  4. //row_size=0
  5. //row_primarykey_size=len('pk')+8Byte=10Byte
  6. {
  7. primary_keys:{'pk':1},
  8. }

The data size before and after modification is 0. Whether the DeleteRow operation succeeds or fails, at least 1 write CU is consumed. Therefore, this DeleteRow consumes 1 write CU.

The consumption of read/write CUs for DeleteRow is described as follows.

  • When the existence check condition is set to IGNORE: The number of write CUs consumed is the rounded up value after dividing 10 bytes (data size of the primary pey of the row) by 4 KB, and the number of read CUs is the rounded up value after dividing 10 bytes by 4 KB. Therefore, DeleteRow consumes 1 write CU and 1 read CU.

  • When the existence check condition is set to EXPECT_EXIST: The number of write CUs consumed is the rounded up value after dividing 10 bytes (data size of the primary key of the row) by 4 KB. No read CU is consumed. Therefore, DeleteRow consumes 1 write CU and 0 read CU.

For more information, see PutRow, UpdateRow, and DeleteRow.

Single row read operations

GetRow is the only single row read operation.

Applications provide the complete primary key and names of all columns to be returned. The column names can be either the primary key or attribute columns. Users are also able to not specify any column names to be returned, in which case all row data is returned.

Table Store calculates the consumed read CUs by adding the data size of the primary key of the read row and the data size of the read attribute column. The data size is divided by 4 KB and rounded up as the number of read CUs consumed in this read operation. If the specified row does not exist, 1 read CU is consumed. Single row read operations do not consume write CUs.

Example

This example illustrates how the number of read CUs consumed by GetRow is calculated.

  1. //GetRow operation
  2. //row_size=len('pk')+len('value1')+len('value2')+8Byte+1200Byte+3100Byte=4322Byte
  3. {
  4. primary_keys:{'pk':1},
  5. attributes:{'value1':String(1200Byte), 'value2':String(3100Byte)}
  6. }
  7. //GetRow operation
  8. //Reading data size=len('pk')+len('value1')+8Byte+1200Byte=1216Byte
  9. {
  10. primary_keys:{'pk':1},
  11. columns_to_get:{'value1'}
  12. }

The number of consumed read CUs is rounded up after dividing 1218 Bytes by 4KB. This GetRow consumes 1 read CU.

For more information, see GetRow.

Multi-Row operations

Table Store provides two multi-row operations: BatchWriteRow and BatchGetRow.

  • BatchWriteRow: Used to insert, modify, or delete multiple rows from one or more tables. BatchWriteRow can be considered as a batch of multiple PutRow, UpdateRow, and DeleteRow operations. The sub-operations in a single BatchWriteRow are executed independently. Table Store separately returns the execution results for each sub-operation to the application. Sometimes, parts of the request are successful, while other parts fail. Even if an error is not returned for the overall request, the application must still check the return results for each sub-operation to determine the actual status. The write CUs consumed by each BatchWriteRow sub-operation are calculated independently.

  • BatchGetRow: Used to read multiple rows from one or more tables. In BatchGetRow, each sub-operation is executed independently. Table Store separately returns the execution results for each sub-operation to the application. Sometimes, parts of the request are successful, while other parts fail. Even if an error is not returned for the overall request, the application must still check the return results for each sub-operation to determine the actual status. The read capacity units consumed by each BatchGetRow sub-operation are calculated independently.

For more information, see BatchWriteRow and BatchGetRow.

Range read operations

GetRange is a range read operation in Table Store. GetRange returns data in a specified range of primary keys to applications.

Rows in Table Store tables are sorted in ascending order of primary keys. GetRange specifies a left-closed-right-open range, and returns data from rows with primary keys in this range. End points of ranges are composed of either effective primary keys or the virtual points: INF_MIN and INF_MAX. The number of columns for the virtual point must be the same as that of the primary key. Here, INF_MIN represents an infinitely small value, so any values of other types are always greater. INF_MAX represents an infinitely large value, so any values of other types are always less.

GetRange must specify the columns to be retrieved by their names. The request column name can contain multiple column names. If a row has a primary key in the read range, but does not contain another column specified for return, the results returned by the request do not contain data from this row. If columns to be retrieved are not specified, the entire row is returned.

GetRange must specify the read direction, which can be either forward or backward. For example, a table has two primary key columns A and B, and A < B. When [A, B) is read in the forward direction, rows with the primary key greater than or equal to A and less than B are returned in the order A to B. When [B,A) is read in the backward direction, rows greater than A and less than or equal to B are returned in the order B to A.

GetRange can specify the maximum number of returned rows. Table Store ends the operation as soon as the maximum number of rows are returned according to the forward or backward direction, even if some rows remain unreturned in the specified ranges.

GetRange may stop execution and return data to the application in the following situations:

  • The total size of row data to be returned reaches 4 MB.

  • The number of rows to be returned is equal to 5000.

  • The number of returned rows is equal to the maximum number of rows specified in requests to be returned.

  • In premature-return situations, responses returned by GetRange contain the primary key for the next row of unread data. Applications can use this value as the starting point for subsequent GetRange operations. If the primary key for the next unread row is null, this indicates all data in the read range has been returned.

Table Store accumulates the total data size of the primary key and attribute column read for all rows from the read range start point to the next row of unread data. The data size is then divided by 4 KB and rounded up to find the number of consumed read CUs. For example, if the read range contains 10 rows and the primary key and actual data size of the attribute column read for each row is 330 Bytes, the number of consumed read CU is 1 (divide the total read data size 3.3 KB by 4 KB and rounded up to 1).

Examples

The following examples illustrate how the number of read CUs are calculated for GetRange.

In these examples, the table contents are as follows. PK1 and PK2 are the table’s primary key columns, and their types are String and Integer, respectively. A and B are the table’s attribute columns.

PK1 PK2 Attr1 Attr2
‘A’ 2 ‘Hall’ ‘Bell’
‘A’ 5 ‘Hello’ Non-exist
‘A’ 6 Non-exist ‘Bloom’
‘B’ 10 ‘Apple’ Non-exist
‘C’ 1 Non-exist Non-exist
‘C’ 9 ‘Alpha’ Non-exist

Example 1: Read Data in a specified range.

  1. //Request
  2. table_name: "table_name"
  3. direction: FORWARD
  4. inclusive_start_primary_key: ("PK1", STRING, "A"), ("PK2", INTEGER, 2)
  5. exclusive_end_primary_key: ("PK1", STRING, "C"), ("PK2", INTEGER, 1)
  6. //Response
  7. consumed_read_capacity_unit: 1
  8. rows: {
  9. {
  10. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 2)
  11. attribute_columns:("Attr1", STRING, "Hall"), ("Attr2", STRING, "Bell")
  12. },
  13. {
  14. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 5)
  15. attribute_columns:("Attr1", STRING, "Hello")
  16. },
  17. {
  18. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 6)
  19. attribute_columns:("Attr2", STRING, "Bloom")
  20. },
  21. {
  22. primary_key_columns:("PK1", STRING, "B"), ("PK2", INTEGER, 10)
  23. attribute_columns:("Attr1", STRING, "Apple")
  24. }
  25. }

Example 2: Use INF_MIN and INF_MAX to read all data in a table.

  1. //Request
  2. table_name: "table_name"
  3. direction: FORWARD
  4. inclusive_start_primary_key: ("PK1", INF_MIN)
  5. exclusive_end_primary_key: ("PK1", INF_MAX)
  6. //Response
  7. consumed_read_capacity_unit: 1
  8. rows: {
  9. {
  10. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 2)
  11. attribute_columns:("Attr1", STRING, "Hall"), ("Attr2", STRING, "Bell")
  12. },
  13. {
  14. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 5)
  15. attribute_columns:("Attr1", STRING, "Hello")
  16. },
  17. {
  18. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 6)
  19. attribute_columns:("Attr2", STRING, "Bloom")
  20. },
  21. {
  22. primary_key_columns:("PK1", STRING, "B"), ("PK2", INTEGER, 10)
  23. attribute_columns:("Attr1", STRING, "Apple")
  24. }
  25. {
  26. primary_key_columns:("PK1", STRING, "C"), ("PK2", INTEGER, 1)
  27. }
  28. {
  29. primary_key_columns:("PK1", STRING, "C"), ("PK2", INTEGER, 9)
  30. attribute_columns:("Attr1", STRING, "Alpha")
  31. }
  32. }

Example 3: Use INF_MIN and INF_MAX in certain Primary Key columns.

  1. //Request
  2. table_name: "table_name"
  3. direction: FORWARD
  4. inclusive_start_primary_key: ("PK1", STRING, "A"), ("PK2", INF_MIN)
  5. exclusive_end_primary_key: ("PK1", STRING, "A"), ("PK2", INF_MAX)
  6. //Response
  7. consumed_read_capacity_unit: 1
  8. rows: {
  9. {
  10. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 2)
  11. attribute_columns:("Attr1", STRING, "Hall"), ("Attr2", STRING, "Bell")
  12. },
  13. {
  14. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 5)
  15. attribute_columns:("Attr1", STRING, "Hello")
  16. },
  17. {
  18. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 6)
  19. attribute_columns:("Attr2", STRING, "Bloom")
  20. }
  21. }

Example 4: Backward reading.

  1. //Request
  2. table_name: "table_name"
  3. direction: BACKWARD
  4. inclusive_start_primary_key: ("PK1", STRING, "C"), ("PK2", INTEGER, 1)
  5. exclusive_end_primary_key: ("PK1", STRING, "A"), ("PK2", INTEGER, 5)
  6. //Response
  7. consumed_read_capacity_unit: 1
  8. rows: {
  9. {
  10. primary_key_columns:("PK1", STRING, "C"), ("PK2", INTEGER, 1)
  11. },
  12. {
  13. primary_key_columns:("PK1", STRING, "B"), ("PK2", INTEGER, 10)
  14. attribute_columns:("Attr1", STRING, "Apple")
  15. },
  16. {
  17. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 6)
  18. attribute_columns:("Attr2", STRING, "Bloom")
  19. }
  20. }

Example 5: Specify a column name not including a PK.

  1. //Request
  2. table_name: "table_name"
  3. direction: FORWARD
  4. inclusive_start_primary_key: ("PK1", STRING, "C"), ("PK2", INF_MIN)
  5. exclusive_end_primary_key: ("PK1", STRING, "C"), ("PK2", INF_MAX)
  6. columns_to_get: "Attr1"
  7. //Response
  8. consumed_read_capacity_unit: 1
  9. rows: {
  10. {
  11. attribute_columns: {"Attr1", STRING, "Alpha"}
  12. }
  13. }

Example 6: Specify a column name including a PK.

  1. //Request
  2. table_name: "table_name"
  3. direction: FORWARD
  4. inclusive_start_primary_key: ("PK1", STRING, "C"), ("PK2", INF_MIN)
  5. exclusive_end_primary_key: ("PK1", STRING, "C"), ("PK2", INF_MAX)
  6. columns_to_get: "Attr1", "PK1"
  7. //Response
  8. consumed_read_capacity_unit: 1
  9. rows: {
  10. {
  11. primary_key_columns:("PK1", STRING, "C")
  12. }
  13. {
  14. primary_key_columns:("PK1", STRING, "C")
  15. attribute_columns:("Attr1", STRING, "Alpha")
  16. }
  17. }

Example 7: Use limit and breakpoints.

  1. //Request 1
  2. table_name: "table_name"
  3. direction: FORWARD
  4. inclusive_start_primary_key: ("PK1", STRING, "A"), ("PK2", INF_MIN)
  5. exclusive_end_primary_key: ("PK1", STRING, "A"), ("PK2", INF_MAX)
  6. limit: 2
  7. //Response 1
  8. consumed_read_capacity_unit: 1
  9. rows: {
  10. {
  11. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 2)
  12. attribute_columns:("Attr1", STRING, "Hall"), ("Attr2", STRING, "Bell")
  13. },
  14. {
  15. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 5)
  16. attribute_columns:("Attr1", STRING, "Hello")
  17. }
  18. }
  19. next_start_primary_key:("PK1", STRING, "A"), ("PK2", INTEGER, 6)
  20. //Request 2
  21. table_name: "table_name"
  22. direction: FORWARD
  23. inclusive_start_primary_key: ("PK1", STRING, "A"), ("PK2", INTEGER, 6)
  24. exclusive_end_primary_key: ("PK1", STRING, "A"), ("PK2", INF_MAX)
  25. limit: 2
  26. //Response 2
  27. consumed_read_capacity_unit: 1
  28. rows: {
  29. {
  30. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 6)
  31. attribute_columns:("Attr2", STRING, "Bloom")
  32. }
  33. }

Example 8: Use GetRange to calculate the consumed read CUs.

GetRange is performed on the following table. PK1 is the table’s primary key column, Attr1 and Attr2 are the attribute columns.

PK1 Attr1 Attr2
1 Non-existent String(1000Byte)
2 8 String(1000Byte)
3 String(1000Byte) Non-existent
4 String(1000Byte) String(1000Byte)
  1. //Request
  2. table_name: "table2_name"
  3. direction: FORWARD
  4. inclusive_start_primary_key: ("PK1", INTEGER, 1)
  5. exclusive_end_primary_key: ("PK1", INTEGER, 4)
  6. columns_to_get: "PK1", "Attr1"
  7. //Response
  8. consumed_read_capacity_unit: 1
  9. rows: {
  10. {
  11. primary_key_columns:("PK1", INTEGER, 1)
  12. },
  13. {
  14. primary_key_columns:("PK1", INTEGER, 2),
  15. attribute_columns:("Attr1", INTEGER, 8)
  16. },
  17. {
  18. primary_key_columns:("PK1", INTEGER, 3),
  19. attribute_columns:("Attr1", STRING, String(1000Byte))
  20. },
  21. }

For this GetRange request:

  • Data size of the first row: len (‘PK1’) + 8 Bytes = 11 Bytes

  • Data size of the second row: len (‘PK1’) + 8 Bytes + len (‘Attr1’) + 8 Bytes = 24 Bytes

  • Data size of the third row: len (‘PK1’) + 8 Bytes + len (‘Attr1’) + 1000 Bytes = 1016 Bytes

The number of consumed read CUs is the rounded up value after dividing 1051 Bytes (11 Bytes + 24 Bytes + 1016 Bytes) by 4 KB. So this GetRange consumes 1 read CU.

For more information, see GetRange.

Best practices

Data operations

Table Store SDK for data operation

Use Table Store Java SDK for table operations.

Use Table Store Python SDK for table operations.

Thank you! We've received your feedback.