Tablestore lets you read data from a table using single-row, batch, and range queries. To read a single row or a batch of rows, you must specify the complete primary keys. To read a range of rows, you must specify a primary key range or a key prefix. You can also configure queries to return only specific attribute columns, a specified number of data versions, data from a specific time range, or data that matches a filter condition.
Query methods
Tablestore provides the GetRow, BatchGetRow, and GetRange interfaces to read data. You can use the appropriate query method based on your scenario.
When you read data from a table that has an auto-increment primary key column, you must provide the complete primary key, including the value of the auto-increment column. For more information, see Auto-increment primary key column. If you do not have the value of the auto-increment primary key column, you can perform a range read on the first primary key column to retrieve the data.
|
Query method |
Description |
Scenarios |
|
Call the GetRow interface to read a single row. |
Use this method when you know the complete primary key and need to read a small number of rows. |
|
|
Call the BatchGetRow interface to read multiple rows in a single request or read from multiple tables at once. A BatchGetRow operation consists of multiple GetRow sub-operations. The process of constructing a sub-operation is the same as when using the GetRow interface. |
Use this method when you know the complete primary keys and need to read many rows or read data from multiple tables. |
|
|
Call the GetRange interface to read data within a specified range. The GetRange operation allows you to read data whose primary key values are in the specified range in a forward or backward direction. You can also specify the number of rows that you want to read. If the range is large and the number of scanned rows or the volume of scanned data exceeds the upper limit, the scan stops, and the rows that are read and information about the primary key of the next row are returned. You can initiate a request to start from the position in which the last operation left off and read the remaining rows based on the information about the primary key of the next row returned by the previous operation. |
Use this method when you can define a primary key range or a key prefix. Important
If you cannot determine a key prefix, you can scan the entire table by setting the primary key range from the virtual point INF_MIN to INF_MAX. This operation consumes a large amount of compute resources. Use it with caution. |
Prerequisites
-
An OTSClient instance is initialized. For more information, see Initialize an OTSClient instance.
-
A data table is created and data is written to the data table.
Read single row data
You can call the GetRow operation to read a single row of data. After you call the GetRow operation, one of the following results may be returned:
Interface
"""
Description: Gets a single row of data.
`table_name`: The name of the table.
`primary_key`: The primary key, of type dict.
`columns_to_get`: An optional list of column names to retrieve. If omitted, all columns are retrieved.
`column_filter`: An optional parameter to read only rows that meet specific conditions.
`max_version`: An optional parameter that specifies the maximum number of versions to read.
`time_range`: An optional parameter that specifies a version range or a specific version to read. Set at least one of `max_version` or `time_range`.
Returns: The CapacityUnit consumed, the primary key columns, and the attribute columns.
`consumed`: The consumed CapacityUnit, which is an instance of the `tablestore.metadata.CapacityUnit` class.
`return_row`: The row data, including primary key columns and attribute columns. It is a list, such as `[('PK0',value0), ('PK1',value1)]`.
`next_token`: The starting position for the next read in a wide row scenario. It is a binary-encoded value.
Example:
primary_key = [('gid',1), ('uid',101)]
columns_to_get = ['name', 'address', 'age']
consumed, return_row, next_token = client.get_row('myTable', primary_key, columns_to_get)
"""
def get_row(self, table_name, primary_key, columns_to_get=None,
column_filter=None, max_version=1, time_range=None,
start_column=None, end_column=None, token=None,
transaction_id=None):Parameters
|
Parameter |
Description |
|
table_name |
The table name. |
|
primary_key |
The primary key of the row. A primary key includes the primary key column name, type, and value. Important
The number and types of the primary key columns that you set must match those of the table. |
|
columns_to_get |
The set of columns to read. Column names can be primary key columns or attribute columns.
Note
|
|
column_filter |
Use a filter to perform an additional filtering operation on the read results at the server-side. Only data rows that meet the filter conditions are returned. For more information, see Filters. Note
When `columns_to_get` and `column_filter` are used together, the system first retrieves the columns specified in `columns_to_get` and then applies the filter condition to the returned columns. |
|
max_version |
The maximum number of versions to read. Important
Set at least one of `max_version` or `time_range`.
|
|
time_range |
Reads data of a specific version number or within a version number range. For more information, see TimeRange. Important
Set at least one of `max_version` or `time_range`.
Set either `specific_time` or the The timestamp is in milliseconds. The minimum value is 0, and the maximum value is |
|
transaction_id |
The local transaction ID. This parameter is required when you use the local transaction feature to read data. |
Example
The following example shows how to read a single row from a table.
# The first primary key column is 'gid' with an integer value of 1. The second is 'uid' with an integer value of 101.
primary_key = [('gid', 1), ('uid', 101)]
# The attribute columns to return are 'name', 'growth', and 'type'. If columns_to_get is [], all attribute columns are returned.
columns_to_get = ['name', 'growth', 'type']
# Set a filter to add column conditions. The filter condition is that the value of the 'growth' column is not equal to 0.9 AND the value of the 'name' column is equal to 'Hangzhou'.
cond = CompositeColumnCondition(LogicalOperator.AND)
cond.add_sub_condition(SingleColumnCondition("growth", 0.9, ComparatorType.NOT_EQUAL))
cond.add_sub_condition(SingleColumnCondition("name", 'Hangzhou', ComparatorType.EQUAL))
try:
# Call the get_row interface to query data.
# Configure the table name. The last parameter value, 1, indicates that only one version needs to be returned.
consumed, return_row, next_token = client.get_row('<table_name>', primary_key, columns_to_get, cond, 1)
print('Read succeed, consume %s read cu.' % consumed.read)
print('Value of primary key: %s' % return_row.primary_key)
print('Value of attribute: %s' % return_row.attribute_columns)
for att in return_row.attribute_columns:
# Print the key, value, and timestamp of each column.
print('name:%s\tvalue:%s\ttimestamp:%d' % (att[0], att[1], att[2]))
# A client exception occurs, typically due to a parameter error or a network issue.
except OTSClientError as e:
print('get row failed, http_status:%d, error_message:%s' % (e.get_http_status(), e.get_error_message()))
# A server-side exception occurs, typically due to a parameter error or a throttling error.
except OTSServiceError as e:
print('get row failed, http_status:%d, error_code:%s, error_message:%s, request_id:%s' % (e.get_http_status(), e.get_error_code(), e.get_error_message(), e.get_request_id()))
For the complete sample code, see GetRow@GitHub.
Batch read data
You can call the BatchGetRow operation to read multiple rows of data from one or more tables at the same time. The BatchGetRow operation consists of multiple GetRow operations. When you call the BatchGetRow operation, the process of constructing each GetRow operation is the same as the process of constructing the GetRow operation when you call the GetRow operation.
If you call the BatchGetRow operation, each GetRow operation is separately performed. Tablestore separately returns the response to each GetRow operation.
Notes
A batch read operation may fail for some rows. The error messages for the failed rows are included in the returned BatchGetRowResponse, but no exception is thrown. Therefore, when you call the BatchGetRow operation, you must check the return value to determine whether the operation is successful for each row.
The same parameter conditions apply to all rows in a batch read operation. For example, if you set
ColumnsToGet=[colA], only the colA column is read for all rows.A single BatchGetRow operation can read a maximum of 100 rows.
Interface
"""
Description: Batch gets multiple rows of data.
request = BatchGetRowRequest()
request.add(TableInBatchGetRowItem(myTable0, primary_keys, column_to_get=None, column_filter=None))
request.add(TableInBatchGetRowItem(myTable1, primary_keys, column_to_get=None, column_filter=None))
request.add(TableInBatchGetRowItem(myTable2, primary_keys, column_to_get=None, column_filter=None))
request.add(TableInBatchGetRowItem(myTable3, primary_keys, column_to_get=None, column_filter=None))
response = client.batch_get_row(request)
`response` is the returned result, of type tablestore.metadata.BatchGetRowResponse.
"""
def batch_get_row(self, request):
Parameters
For more information, see Parameters for reading a single row.
Example
The following example shows how to read three rows from multiple tables in a single batch operation.
# Specify the columns to return.
columns_to_get = ['name', 'mobile', 'address', 'age']
# Read 3 rows.
rows_to_get = []
for i in range(0, 3):
primary_key = [('gid', i), ('uid', i + 1)]
rows_to_get.append(primary_key)
# Set a filter to add column conditions. The filter condition is that the value of the 'name' column is 'John' AND the value of the 'address' column is 'China'.
cond = CompositeColumnCondition(LogicalOperator.AND)
cond.add_sub_condition(SingleColumnCondition("name", "John", ComparatorType.EQUAL))
cond.add_sub_condition(SingleColumnCondition("address", 'China', ComparatorType.EQUAL))
# Construct a batch read request.
request = BatchGetRowRequest()
# Add the rows to be read from the specified table. The last parameter, 1, indicates that the latest version is read.
request.add(TableInBatchGetRowItem('<table_name1>', rows_to_get, columns_to_get, cond, 1))
# Add the rows to be read from another table.
request.add(TableInBatchGetRowItem('<table_name2>', rows_to_get, columns_to_get, cond, 1))
try:
result = client.batch_get_row(request)
print('Result status: %s' % (result.is_all_succeed()))
table_result_0 = result.get_result_by_table('<table_name1>')
table_result_1 = result.get_result_by_table('<table_name2>')
print('Check first table\'s result:')
for item in table_result_0:
if item.is_ok:
print('Read succeed, PrimaryKey: %s, Attributes: %s' % (item.row.primary_key, item.row.attribute_columns))
else:
print('Read failed, error code: %s, error message: %s' % (item.error_code, item.error_message))
print('Check second table\'s result:')
for item in table_result_1:
if item.is_ok:
print('Read succeed, PrimaryKey: %s, Attributes: %s' % (item.row.primary_key, item.row.attribute_columns))
else:
print('Read failed, error code: %s, error message: %s' % (item.error_code, item.error_message))
# A client exception occurs, typically due to a parameter error or a network issue.
except OTSClientError as e:
print('get row failed, http_status:%d, error_message:%s' % (e.get_http_status(), e.get_error_message()))
# A server-side exception occurs, typically due to a parameter error or a throttling error.
except OTSServiceError as e:
print('get row failed, http_status:%d, error_code:%s, error_message:%s, request_id:%s' % (e.get_http_status(), e.get_error_code(), e.get_error_message(), e.get_request_id()))
For the complete sample code, see BatchGetRow@GitHub.
Range read data
You can call the GetRange operation to read data whose primary key values are in the specified range.
The GetRange operation allows you to read data whose primary key values are in the specified range in a forward or backward direction. You can also specify the number of rows that you want to read. If the range is large and the number of scanned rows or the volume of scanned data exceeds the upper limit, the scan stops, and the rows that are read and information about the primary key of the next row are returned. You can initiate a request to start from the position in which the last operation left off and read the remaining rows based on the information about the primary key of the next row returned by the previous operation.
In Tablestore tables, all rows are sorted by primary key. The primary key of a table sequentially consists of all primary key columns. Therefore, the rows are not sorted based on a specific primary key column.
Notes
The GetRange operation follows the leftmost matching principle. Tablestore compares values in sequence from the first primary key column to the last primary key column to read data whose primary key values are in the specified range. For example, the primary key of a data table consists of the following primary key columns: PK1, PK2, and PK3. When data is read, Tablestore first determines whether the PK1 value of a row is in the range that is specified for the first primary key column. If the PK1 value of a row is in the range, Tablestore stops determining whether the values of other primary key columns of the row are in the ranges that are specified for each primary key column and returns the row. If the PK1 value of a row is not in the range, Tablestore continues to determine whether the values of other primary key columns of the row are in the ranges that are specified for each primary key column in the same manner as PK1.For more information about the principles of range queries, seeGetRangeRange Query Details.
-
The amount of scanned data reaches 4 MB.
-
The number of scanned rows reaches 5,000.
-
The number of returned rows reaches the upper limit.
-
The read throughput is insufficient to read the next row of data because all reserved read throughput is consumed.
When you use GetRange to scan a large volume of data, Tablestore performs only one scan per request. The scan stops if the number of rows exceeds 5,000 or the data size exceeds 4 MB. Data that exceeds these limits is not returned. You must use pagination to retrieve the subsequent data.
Interface
"""
Description: Gets multiple rows of data based on a range condition.
`table_name`: The name of the table.
`direction`: The read direction for the range query. It is a string and can be 'FORWARD' or 'BACKWARD'.
`inclusive_start_primary_key`: The start primary key of the range (inclusive).
`exclusive_end_primary_key`: The end primary key of the range (exclusive).
`columns_to_get`: An optional list of column names to retrieve. If omitted, all columns are retrieved.
`limit`: An optional parameter that specifies the maximum number of rows to read. If omitted, there is no limit.
`column_filter`: An optional parameter to read only rows that meet specific conditions.
`max_version`: An optional parameter that specifies the maximum number of versions to return. Set at least one of `max_version` or `time_range`.
`time_range`: An optional parameter that specifies the version range to return. Set at least one of `max_version` or `time_range`.
`start_column`: An optional parameter for wide row reads. It specifies the starting column for the current read.
`end_column`: An optional parameter for wide row reads. It specifies the ending column for the current read.
`token`: An optional parameter for wide row reads. It specifies the starting column position for the current read. The content is binary-encoded and comes from the result of the previous request.
Returns: A list of results that meet the conditions.
`consumed`: The CapacityUnit consumed by the operation, which is an instance of the `tablestore.metadata.CapacityUnit` class.
`next_start_primary_key`: The primary key of the starting point for the next get_range operation. It is of type dict.
`row_list`: A list of row data returned by the operation, in the format: [Row, ...].
"""
def get_range(self, table_name, direction,
inclusive_start_primary_key,
exclusive_end_primary_key,
columns_to_get=None,
limit=None,
column_filter=None,
max_version=None,
time_range=None,
start_column=None,
end_column=None,
token=None):Parameters
|
Parameter |
Description |
|
table_name |
The table name. |
|
direction |
The read direction.
Assume a table has two primary keys, A and B, where A is less than B. If you perform a forward read on the range |
|
inclusive_start_primary_key |
The start and end primary keys for the range read. The start and end primary keys must be valid primary keys or virtual points composed of INF_MIN and INF_MAX types. The number of columns in a virtual point must be the same as the number of primary key columns. INF_MIN represents negative infinity and is smaller than any other value. INF_MAX represents positive infinity and is larger than any other value.
Rows in a table are sorted by primary key in ascending order. The read range is a left-closed, right-open interval. For a forward read, all rows with primary keys greater than or equal to the start primary key and less than the end primary key are returned. |
|
exclusive_end_primary_key |
|
|
limit |
The maximum number of rows to return. This value must be greater than 0. Tablestore returns the specified maximum number of rows in forward or backward order and then ends the operation, even if there is more data in the interval. In this case, you can use the `next_start_primary_key` from the result to continue reading from the last position in the next request. |
|
columns_to_get |
The set of columns to read. Column names can be primary key columns or attribute columns.
Note
|
|
max_version |
The maximum number of versions to read. Important
Set at least one of `max_version` or `time_range`.
|
|
time_range |
Reads data of a specific version number or within a version number range. For more information, see TimeRange. Important
Set at least one of `max_version` or `time_range`.
Set either `specific_time` or the The timestamp is in milliseconds. The minimum value is 0, and the maximum value is |
|
column_filter |
Use a filter to perform an additional filtering operation on the read results at the server-side. Only data rows that meet the filter conditions are returned. For more information, see Filters. Note
When `columns_to_get` and `column_filter` are used together, the system first retrieves the columns specified in `columns_to_get` and then applies the filter condition to the returned columns. |
|
next_start_primary_key |
Check `next_start_primary_key` in the return result to determine if all data has been read.
|
Example
The following example shows how to perform a forward read on a range. The range is defined by the first primary key column, and the second primary key column spans from the minimum value (INF_MIN) to the maximum value (INF_MAX). The code checks whether `nextStartPrimaryKey` is null to ensure that all data within the range is read.
# Set the start primary key for the range read.
inclusive_start_primary_key = [('gid', 1), ('uid', INF_MIN)]
# Set the end primary key for the range read.
exclusive_end_primary_key = [('gid', 5), ('uid', INF_MAX)]
# Query all columns.
columns_to_get = []
# Return a maximum of 90 rows at a time. If there are 100 results in total and you set limit=90 for the first query, the first query returns a maximum of 90 results and a minimum of 0 results, but next_start_primary_key will not be None.
limit = 90
# Set a filter to add column conditions. The filter condition is that the value of the 'address' column is 'China' AND the value of the 'age' column is less than 50.
cond = CompositeColumnCondition(LogicalOperator.AND)
# If a row does not contain a specified column, configure the pass_if_missing parameter to determine whether the row meets the filter condition.
# If you do not set pass_if_missing or set it to True, a row is considered to meet the filter condition if it does not contain the column.
# If you set pass_if_missing to False, a row is considered not to meet the filter condition if it does not contain the column.
cond.add_sub_condition(SingleColumnCondition("address", 'China', ComparatorType.EQUAL, pass_if_missing = False))
cond.add_sub_condition(SingleColumnCondition("age", 50, ComparatorType.LESS_THAN, pass_if_missing = False))
try:
# Call the get_range interface.
consumed, next_start_primary_key, row_list, next_token = client.get_range(
'<table_name>', Direction.FORWARD,
inclusive_start_primary_key, exclusive_end_primary_key,
columns_to_get,
limit,
column_filter=cond,
max_version=1,
time_range = (1557125059000, 1557129059000) # start_time is greater than or equal to 1557125059000, and end_time is less than 1557129059000.
)
all_rows = []
all_rows.extend(row_list)
# If next_start_primary_key is not empty, continue to read data.
while next_start_primary_key is not None:
inclusive_start_primary_key = next_start_primary_key
consumed, next_start_primary_key, row_list, next_token = client.get_range(
'<table_name>', Direction.FORWARD,
inclusive_start_primary_key, exclusive_end_primary_key,
columns_to_get, limit,
column_filter=cond,
max_version=1
)
all_rows.extend(row_list)
# Print the primary key and attribute columns.
for row in all_rows:
print(row.primary_key, row.attribute_columns)
print('Total rows: ', len(all_rows))
# A client exception occurs, typically due to a parameter error or a network issue.
except OTSClientError as e:
print('get row failed, http_status:%d, error_message:%s' % (e.get_http_status(), e.get_error_message()))
# A server-side exception occurs, typically due to a parameter error or a throttling error.
except OTSServiceError as e:
print('get row failed, http_status:%d, error_code:%s, error_message:%s, request_id:%s' % (e.get_http_status(), e.get_error_code(), e.get_error_message(), e.get_request_id()))
For the complete sample code, see GetRange@GitHub.
FAQ
References
-
To accelerate data queries, you can use secondary indexes or search indexes. For more information, see Secondary index or Search index.
-
To visualize data in a table, you can integrate with tools such as DataV or Grafana. For more information, see Data visualization.
-
To download table data to a local file, you can use DataX or the Tablestore command-line interface (CLI). For more information, see Download to a local file.
-
To compute and analyze data in a table, you can use Tablestore SQL queries. For more information, see SQL query.
NoteYou can also use compute engines such as MaxCompute, Spark, Hive, Hadoop MR, Function Compute, or Flink to compute and analyze table data. For more information, see Compute and analyze data.