Tablestore allows you to read a single row of data or data whose primary key values are within a specific range from an index table. If the index table contain the attribute columns that you want to return, you can read the index table to obtain the data. Otherwise, you need to query the data from the data table for which the index table is created.
Prerequisites
A client is initialized. For more information, see Initialize a Tablestore client.
A secondary index is created. For more information, see Create a secondary index.
Precautions
Index tables can only be used to read data.
The first primary key column of a local secondary index table must be the same as the first primary key column of the primary table.
If the attribute columns to be retrieved are not in the index table, you must look up the data in the primary table.
Read a single row of data
You can call the GetRow operation to read a single row of data. For more information, see Read a single row of data.
Parameters
When you use the GetRow operation to read data from an index table, note the following:
Set table_name to the name of the index table.
Tablestore automatically adds the primary key columns from the data table to the primary key of the index table if they are not already defined as index columns. Therefore, when you set the primary key for a row, you must specify both the index columns and these automatically added primary key columns.
Example
The following example shows how to read a row of data with a specified primary key from an index table.
# Construct the primary key. The first primary key column is definedcol1 with a value of 1. The second primary key column is pk1 with a value of 101. The third primary key column is the added data table primary key pk2 with a value of 11.
# If you read data from a local secondary index, the first primary key column of the index table must be the same as the first primary key column of the data table.
primary_key = [('definedcol1', 1), ('pk1', 101), ('pk2', 11)]
# The attribute columns to return are definedcol2 and definedcol3. If columns_to_get is set to [], all attribute columns of the index table are returned.
columns_to_get = ['definedcol2', 'definedcol3']
# Set a filter to add column conditions. The filter condition is that the value of the definedcol2 column is not equal to 1 and the value of the definedcol3 column is equal to 'test'.
cond = CompositeColumnCondition(LogicalOperator.AND)
cond.add_sub_condition(SingleColumnCondition("definedcol2", 1, ComparatorType.NOT_EQUAL))
cond.add_sub_condition(SingleColumnCondition("definedcol3", 'test', ComparatorType.EQUAL))
try:
# Call the get_row operation to query data.
# Configure the index table name. The last parameter, 1, indicates that only one version of the value is returned.
consumed, return_row, next_token = client.get_row('<INDEX_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 version of each column.
print('name:%s\tvalue:%s' % (att[0], att[1]))
# A client exception occurs, usually due to invalid parameters or a network error.
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, usually due to invalid parameters 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()))
Read a range of data
You can call the GetRange operation to read a range of data. For more information, see Read a range of data.
Parameters
When you use the GetRange operation to read data from an index table, note the following:
Set table_name to the name of the index table.
Tablestore automatically adds the primary key columns from the data table to the primary key of the index table if they are not already defined as index columns. Therefore, when you set the start and end primary keys, you must specify both the index columns and these automatically added primary key columns.
Example
The following example shows how to read data within a specified primary key range.
# Set the start primary key for the range query. If you read data from a local secondary index, the first primary key column of the index table must be the same as the first primary key column of the data table.
inclusive_start_primary_key = [('definedcol1', 1), ('pk1', INF_MIN), ('pk2', INF_MIN)]
# Set the end primary key for the range query.
exclusive_end_primary_key = [('definedcol1', 5), ('pk1', INF_MAX), ('pk2', INF_MIN)]
# Query all columns in the index table.
columns_to_get = []
# Return a maximum of 90 rows at a time. If there are 100 results in total and you set limit to 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 is not None.
limit = 90
# Set a filter to add column conditions. The filter condition is that the value of the definedcol2 column is less than 50 and the value of the definedcol3 column is equal to 'China'.
cond = CompositeColumnCondition(LogicalOperator.AND)
# If a row does not contain the specified column, you must 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 that does not contain the column meets the filter condition.
# If you set pass_if_missing to False, a row that does not contain the column does not meet the filter condition.
cond.add_sub_condition(SingleColumnCondition("definedcol3", 'China', ComparatorType.EQUAL, pass_if_missing=False))
cond.add_sub_condition(SingleColumnCondition("definedcol2", 50, ComparatorType.LESS_THAN, pass_if_missing=False))
try:
# Call the get_range operation.
# Set the index table name.
consumed, next_start_primary_key, row_list, next_token = client.get_range(
'<INDEX_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(
'<INDEX_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, usually due to invalid parameters or a network error.
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, usually due to invalid parameters 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())
FAQ
References
If you have multi-dimensional query requirements, such as queries on non-primary key columns, composite column queries, and fuzzy queries, or data analytics requirements, such as calculating maximum values, counting rows, and grouping data, you can add the required attributes as fields to a search index. Then, you can use the search index to query and analyze the data. For more information, see Search index.
To query and analyze data using SQL, you can use the SQL query feature. For more information, see SQL query.