Data operations

Last Updated: Aug 03, 2017

In Table Store, tables are composed of rows. Each row includes Primary Key and Attributes. This chapter will introduce the data operations of Table Store.

Row of Table Store

Row is the basic unit that composes the tables of Table Store. They are composed of a Primary Key and Attributes. A Primary Key is required and all rows in the same table must have the same Primary Key column names and types. Attributes are not mandatory and each row may have different Attributes. For more information, refer to Table Store data model.

There are three types of Table Store data operations:

  • 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 will be deleted before the new row is written.

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

    • DeleteRow: Delete a row.

  • Batch operations

    • BatchGetRow: Batch read the data of multiple rows in one request.

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

  • Range read

    • GetRange: Read the data of the table within a certain range.

Single row operations

Single row write operations

Table Store has three single row write operations: PutRow, UpdateRow and DeleteRow. The following are the descriptions and considerations of these operations:

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

  • UpdateRow: Update a row’s data. Based on the request content, Table Store will add new columns or modify/delete the specified column values for this row. If this row does not exist, a new row will be inserted. However, an UpdateRow request onto an inexistent row with only deletion instructions will not insert a new row.

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

By setting the condition field in the request, the application can specify whether a row existence check is performed before executing the write operation. There are three condition checking options:

  • IGNORE: The row existence checking 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.

The operation DeleteRow or UpdateRow will fail if the condition checking is EXPECT_NOT_EXIST, because it is meaningless to delete or update the non-existing rows. You can use PutRow operation to update a non-existing row if the condition checking is EXPECT_NOT_EXIST.

Applications will receive the number of the consumed capacity units for successful operations. If the operation fails, such as the parameter check fails, the row’s data size is too large, or the existence check fails, an error code will be 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 Primary Key of the modified row and the data size of Attribute column is divided by 4 KB and rounded up. If the row existence check condition is not IGNORE, a number of read CUs will be consumed which is equivalent to the value rounded up 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 details, refer to API Reference - PutRow.

  • UpdateRow: The sum of the data size of Primary Key of the modified row and the data size of attribute column is divided by 4 KB and rounded up. If UpdateRow contains an Attribute column which shall 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 will be consumed which is equivalent to the value rounded up 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 details, refer to 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 will be consumed which is equivalent to the value rounded up 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 details, refer to API Reference - DeleteRow.

A certain number of read CUs will be also consumed by write operations based on the specified condition.

Examples

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

Example 1: Use the PutRow operation 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 the PutRow operation is described as follows:

  • When the existence check condition is set to EXPECT_EXIST: The number of write CUs consumed is the value rounded up after dividing 4,322 bytes by 4 KB, and the number of read CUs is the value rounded up after dividing 10 bytes (data size of the Primary Key of the row) by 4 KB. Therefore, the PutRow operation 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 value rounded up after dividing 4,322 bytes by 4 KB. No read CU is consumed. Therefore, the PutRow operation 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. The PutRow operation consumes 1 write CU and 1 read CU.

**Example 2: Use the UpdateRow operation to write a new row.

  1. //UpdateRow operation
  2. //Length of attribute column deleted will be 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 the UpdateRow operation is described as follows:

  • When the existence check condition is set to IGNORE: The number of write CUs consumed is the value rounded up after dividing 922 bytes by 4 KB. No read CU is consumed. Therefore, the UpdateRow operation 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. The UpdateRow operation consumes 1 write CU and 1 read CU.

Example 3: Use the UpdateRow operation 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 the UpdateRow operation is described as follows:

  • When the existence check condition is set to EXPECT_EXIST: The number of write CUs consumed is the value rounded up after dividing 4,322 bytes by 4 KB, and the number of read CUs is the value rounded up after dividing 10 bytes (data size of the Primary Key of the row) by 4 KB. Therefore, the UpdateRow operation 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 value rounded up after dividing 4,322 bytes by 4 KB. No read CU is consumed. Therefore, the UpdateRow operation consumes 1 write CU and 0 read CU.

Example 4: Use the DeleteRow operation 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 both before and after modification is 0. Whether the DeleteRow operation succeeds or fails, 1 write CU at least is consumed. Therefore, this DeleteRow operation consumes 1 write CU.

The consumption of read/write CUs for the DeleteRow operation is described as follows:

  • When the existence check condition is set to IGNORE: The number of write CUs consumed is the value rounded up after dividing 10 bytes (data size of the Primary Key of the row) by 4 KB, and the number of read CUs is the value rounded up after dividing 10 bytes by 4 KB. Therefore, the DeleteRow operation 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 value rounded up after dividing 10 bytes (data size of the Primary Key of the row) by 4 KB. No read CU is consumed. Therefore, the DeleteRow operation consumes 1 write CU and 0 read CU.

For more information, refer to the PutRow, UpdateRow and DeleteRow chapters in the API Reference.

Single row read operations

There is only one single row read operation: GetRow.

Applications provide the complete Primary Key and the names of all columns to be returned. The column names can be either Primary Key or Attribute columns. Users may not specify any column names to return, in which case all data of the row data will be 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 4KB 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 capacity units consumed by a GetRow operation 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 capacity units is rounded up after dividing 1218 Bytes by 4KB. This GetRow operation consumes 1 read CU.

For more information, refer to the GetRow chapter in the API Reference.

Multi-Row operations

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

  • BatchWriteRow operations are used to insert, modify or delete multiple rows from one or more tables. BatchWriteRow operations 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 will return the execution results for each sub-operation to the application separately. It may be the case that parts of the request succeed while other parts fail. Even if an error is not returned for the overall request, the application still must check the return results for each sub-operation to determine the correct status. The write CUs consumed by each BatchWriteRow sub-operation are calculated independently.

  • BatchGetRow is used to read multiple rows from one or more tables. In BatchGetRow, each sub-operation is executed independently. Table Store will return the execution results for each sub-operation to the application separately. It may be the case that parts of the request succeed while other parts fail. Even if an error is not returned for the overall request, the application still must check the return results for each sub-operation to determine the correct statuses. The read capacity units consumed by each BatchGetRow sub-operation are calculated independently.

For more information, refer to the BatchWriteRow and BatchGetRow chapters in the API Reference.

Range read operations

Table Store provides the range read operation GetRange. This operation returns data in the specified range of Primary Key to applications.

The rows in Table Store tables are sorted in the ascending order of Primary Keys. Each GetRange operation 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 for the Primary Key. Here, INF_MIN represents an infinitely small value, so any values of other types are strictly greater than it. INF_MAX represents an infinitely large value, so any values of other types are strictly smaller than it.

GetRange operations must specify the columns to get 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 other column specified for return, the results returned by the request will not contain data from this row. If columns to get are not specified, the entire rows will be returned.

GetRange operations must specify the read direction which can be either forward or backward. If a table has two Primary Key columns A and B, and A < B. When [A, B) is read in the forward direction, rows with Primary Key greater than or equal to A and less than B will be 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 retuned in the order B to A.

GetRange operations can specify the maximal number of returned rows. Table Store will end the operation as soon as the maximal number of rows is returned according to the forward or backward direction, even if there are remaining rows in the specified ranges.

In the following situations, GetRange operations may stop execution and return data to the application:

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

  • The number of rows to return is equal to 5000.

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

  • In such premature-return cases, responses returned by the GetRange request will contain the Primary Key for the next row of unread data. Applications can use this value as the starting point for a subsequent GetRange operation. 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 Primary Key and the 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 the consumed read CUs. For example, if the read range contains 10 rows and the Primary Key and the 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 is calculated for the GetRange operations.

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 ‘Hell’ ‘Bell’
‘A’ 5 ‘Hello’ Non-exist
‘A’ 6 Non-exist ‘Blood’
‘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. cosumed_read_capacity_unit: 1
  8. rows: {
  9. {
  10. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 2)
  11. attribute_columns:("Attr1", STRING, "Hell"), ("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, "Blood")
  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. cosumed_read_capacity_unit: 1
  8. rows: {
  9. {
  10. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 2)
  11. attribute_columns:("Attr1", STRING, "Hell"), ("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, "Blood")
  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. cosumed_read_capacity_unit: 1
  8. rows: {
  9. {
  10. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 2)
  11. attribute_columns:("Attr1", STRING, "Hell"), ("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, "Blood")
  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. cosumed_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, "Blood")
  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. cosumed_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. cosumed_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. cosumed_read_capacity_unit: 1
  9. rows: {
  10. {
  11. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 2)
  12. attribute_columns:("Attr1", STRING, "Hell"), ("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. cosumed_read_capacity_unit: 1
  28. rows: {
  29. {
  30. primary_key_columns:("PK1", STRING, "A"), ("PK2", INTEGER, 6)
  31. attribute_columns:("Attr2", STRING, "Blood")
  32. }
  33. }

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

GetRange is performed on the following table. PK1 is the table’s Primary Key column and 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. cosumed_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 the consumed read CUs is the value rounded up after dividing 1051 Bytes (11 Bytes + 24 Bytes + 1016 Bytes) by 4 KB. So this GetRange operation consumes 1 read CU.

For more information, refer to the GetRange chapter in the API Reference.

Best practices

Data operations

Table Store SDK for data operation

TableStore Java SDK for data operation

TableStore Python SDK for data operation

Thank you! We've received your feedback.