Data operations

Last Updated: Sep 30, 2017

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

Table Store rows

A row is the basic unit composing the tables of Table Store. Rows 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 name and type. Attributes are not mandatory and each row may have different Attributes. For more information, see 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 and the new row is written.

    • UpdateRow: Update a row from the table. Applications 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 in one request.

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

  • Range read

    • 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 will be deleted and 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 with only deletion instructions onto a row that does not exist 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 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 will 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 will receive the number of 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 between 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 will be 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 between 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 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. 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 will be 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 will be 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 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 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 Key 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 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 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 will separately return the execution results for each sub-operation to the application. Sometimes, parts of the request will succeed, 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 correct 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 will separately return the execution results for each sub-operation to the application. Sometimes, parts of the request will succeed, 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 correct status. The read capacity units consumed by each BatchGetRow sub-operation are calculated independently.

For more information, see BatchWriteRow and BatchGetRow.

Range read operations

Table Store provides GetRange, a range read operation. GetRange returns data in a specified range of Primary Keys to applications.

Rows in Table Store tables are sorted in an 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 strictly greater than it. INF_MAX represents an infinitely large value, so any values of other types are strictly smaller than it.

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 will not contain data from this row. If columns to be retrieved are not specified, the entire row will be 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 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 returned in the order B to A.

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

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

  • 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 such premature-return cases, responses returned by GetRange will 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 the 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 is 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 ‘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 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. 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 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.