Tablestore provides multiple operations for you to read data. You can call the GetRow operation to read a single row of data, the BatchGetRow operation to read multiple rows of data at a time, and the GetRange operation to read data whose primary key values are in the specified range.
Query methods
Tablestore provides the GetRow, BatchGetRow, and GetRange operations to allow you to read data. Before you read data, select the appropriate query method based on the actual query scenario.
If you want to read data from a table that contains an auto-increment primary key column, make sure that you have queried the values of all primary key columns that include the values of the auto-increment primary key column. For more information, see Configure an auto-increment primary key column. If no value is recorded for the auto-increment primary key column, you can call the GetRange operation to specify the range within which data is read based on primary key values from the first primary key column.
Query method | Description | Scenario |
You can call the GetRow operation to read a single row of data. | This method is applicable to scenarios in which all primary key columns of a table can be determined and the number of rows to be read is small. | |
You can call the BatchGetRow operation to read multiple rows of data from one or more tables at a time. The BatchGetRow operation consists of multiple GetRow operations. The process of constructing a suboperation is the same as the process of calling the GetRow operation. | This method is applicable to scenarios in which all primary key columns of a table can be determined and the number of rows to be read is large or data is to be read from multiple tables. | |
Read data whose primary key values are within a specific range | 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 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 where 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. | This method is applicable to scenarios in which the range of all primary key columns of a table or the prefix of primary key columns can be determined. Important If you cannot determine the prefix of primary key columns, you can specify the start primary key column whose data is of the INF_MIN type and the end primary key column whose data is of the INF_MAX type to determine the range of all primary key columns of a table. This operation scans all data in the table but consumes a large amount of computing resources. Proceed with caution. |
Prerequisites
- The OTSClient instance is initialized. For more information, see Initialize an OTSClient instance.
- A data table is created. Data is written to the table.
Read a single row of 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:
If the row exists, the primary key columns and attribute columns of the row are returned.
If the row does not exist, no row is returned and no error is reported.
API operation
/**
* Read a row of data.
* @api
* @param [] $request The request parameters.
* @return [] The response.
* @throws OTSClientException The exception that is thrown if a parameter error occurs or the Tablestore server returns a verification error.
* @throws OTSServerException The exception that is thrown if the Tablestore server returns an error.
*/
public function getRow(array $request); Request parameters
Parameter | Description |
table_name | The name of the table. |
primary_key | The primary key information of the row. The primary key information consists of the primary key column name, primary key type, and primary key value. Important The number and types of primary key columns that you specify must be the same as the actual number and types of primary key columns in the table. |
max_versions | The maximum number of data versions that you can read. Important You must specify at least one of the max_versions and time_range parameters.
|
time_range | The time range of versions or a specific version that you want to read. For more information, see TimeRange. Important You must specify at least one of the max_versions and time_range parameters.
Only one of specific_time and Valid values of the time_range parameter: 0 to |
columns_to_get | The columns that you want to read. You can specify the names of primary key columns or attribute columns.
Note
|
start_column | The column from which the wide-column read operation starts. The response includes the start column. The columns are sorted based on their names in alphabetical order. For example, a table contains three columns: a, b, and c. If the value of the start_column parameter is b, the read operation starts from Column b, and Columns b and c are returned. |
end_column | The column at which the wide-column read operation ends. The response excludes the end column. The columns are sorted based on their names in alphabetical order. For example, a table contains three columns: a, b, and c. If the value of the end_column parameter is b, the read operation ends at Column b, and Column a is returned. |
token | The position from which the next wide-column read operation starts. This parameter is unavailable. |
column_filter | The filter that you want to use to filter the query results on the server side. Only rows that meet the filter conditions are returned. For more information, see Configure filter. Note If you specify both the columns_to_get and column_filter parameters, Tablestore queries the columns that are specified by the columns_to_get parameter, and then returns the rows that meet the filter conditions. |
Request syntax
$result = $client->getRow([
'table_name' => '<string>', // Specify the name of the table.
'primary_key' => [ // Specify the primary key information.
['<string>', <PrimaryKeyValue>],
['<string>', <PrimaryKeyValue>],
['<string>', <PrimaryKeyValue>, <PrimaryKeyType>]
],
'max_versions' => <integer>,
'time_range' => [
'start_time' => <integer>,
'end_time' => <integer>,
'specific_time' => <integer>
],
'start_column' => '<string>',
'end_column' => '<string>',
'token' => '<string>',
'columns_to_get' => [
'<string>',
'<string>',
//...
],
'column_filter' => <ColumnCondition>
]); Response parameters
Parameter | Description |
consumed | The number of capacity units (CUs) that are consumed by the operation. capacity_unit: the number of read/write CUs that are consumed. Parameters:
|
primary_key | The primary key values, which are the same as those specified in the request. Note If the row does not exist, an empty list [] is returned for the primary_key parameter. |
attribute_columns | The attribute columns. Note If the row does not exist, an empty list [] is returned for the attribute_columns parameter.
|
next_token | The position from which the next wide-column read operation starts. This parameter is unavailable. |
Response format
[
'consumed' => [
'capacity_unit' => [
'read' => <integer>,
'write' => <integer>
]
],
'primary_key' => [
['<string>', <PrimaryKeyValue>],
['<string>', <PrimaryKeyValue>],
['<string>', <PrimaryKeyValue>, <PrimaryKeyType>]
],
'attribute_columns' => [
['<string>', <ColumnValue>, <ColumnType>, <integer>]
['<string>', <ColumnValue>, <ColumnType>, <integer>]
['<string>', <ColumnValue>, <ColumnType>, <integer>]
],
'next_token' => '<string>'
] Sample code
Read data of the latest version from the specified columns of a row
The following sample code provides an example on how to read data of the latest version from the specified columns of a row:
$request = [
'table_name' => 'MyTable',
'primary_key' => [ // Specify the primary key information.
['PK0', 123],
['PK1', 'abc']
],
'max_versions' => 1, // Set this parameter to 1 to read data of the latest version.
'columns_to_get' => ['Col0'] // Specify the columns that you want to read.
];
$response = $otsClient->getRow ($request); Read a row of data by using a filter
The following sample code provides an example on how to read a row of data when a filter is used:
$request = [
'table_name' => 'MyTable',
'primary_key' => [ // Specify the primary key information.
['PK0', 123],
['PK1', 'abc']
],
'max_versions' => 1, // Set this parameter to 1 to read data of the latest version.
'column_filter' => [ // Configure a filter to return a row whose value of the Col0 column is 0.
'column_name' => 'Col0',
'value' => 0,
'comparator' => ComparatorTypeConst::CONST_EQUAL,
'pass_if_missing' => false // If the row does not contain the Col0 column, the row is not returned.
]
];
$response = $otsClient->getRow ($request); Read multiple rows of data at a time
You can call the BatchGetRow operation to read multiple rows of data from one or more tables at a 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, and Tablestore separately returns the response to each GetRow operation.
Usage notes
When you call the BatchGetRow operation to read multiple rows at a time, some rows may fail to be read. If this happens, Tablestore does not return exceptions, but returns BatchGetRowResponse in which the information about the failed rows are included. Therefore, when you call the BatchGetRow operation, you must check the return values to determine whether data is successfully read from each row.
The BatchGetRow operation uses the same parameter settings for all rows. For example, if the
ColumnsToGetparameter is set to [colA], only the value of the colA column is read from all rows.You can call the BatchGetRow operation to read a maximum of 100 rows at a time.
API operation
/**
* Read the specified rows of data.
* When you call the BatchGetRow operation to read multiple rows at a time, some rows may fail to be read. If this happens, the system does not return an exception but returns the information about the failed rows in $response. For more information, see the sample response to the BatchGetRow operation.
* @api
* @param [] $request The request parameters.
* @return [] The response.
* @throws OTSClientException The exception that is returned if a parameter error occurs or the Tablestore server returns a verification error.
* @throws OTSServerException The exception that is returned if the Tablestore server returns an error.
*/
public function batchGetRow(array $request);
Request parameters
Compared with the GetRow operation, the BatchGetRow operation has the following changes:
Hierarchies are created for tables. Data from multiple tables can be read at a time.
You can use the tables parameter to specify information about tables and rows on which you want to perform the read operations.
The primary_key parameter is changed to the primary_keys parameter. The primary_keys parameter allows you to specify the primary key information for multiple rows and read data from multiple rows at a time.
Request syntax
$result = $client->batchGetRow([
'tables' => [ // Specify the hierarchies of tables.
[
'table_name' => '<string>', // Specify the name of the table.
'primary_keys' => [ // Specify the primary key information.
[
['<string>', <PrimaryKeyValue>],
['<string>', <PrimaryKeyValue>],
['<string>', <PrimaryKeyValue>, <PrimaryKeyType>]
],
// Specify other parameters.
]
'max_versions' => <integer>,
'time_range' => [
'start_time' => <integer>,
'end_time' => <integer>,
'specific_time' => <integer>
],
'start_column' => '<string>',
'end_column' => '<string>',
'token' => '<string>',
'columns_to_get' => [
'<string>',
'<string>',
//...
],
'column_filter' => <ColumnCondition>
],
// Specify other tables.
]
]); Response parameters
Each table indicated by the tables parameter in the response corresponds to a table specified in the request. The following table describes the parameters in the response.
Parameter | Description |
table_name | The name of the table. |
is_ok | Indicates whether the operation on the row is successful.
|
error | The error information in the response if the operation on the row fails. Parameters:
|
consumed | The number of CUs that are consumed by the operation. capacity_unit: the number of read/write CUs that are consumed. Parameters:
|
primary_key | The primary key values, which are the same as those specified in the request. Note If the row does not exist, an empty list [] is returned for the primary_key parameter. |
attribute_columns | The attribute columns. Note If the row does not exist, an empty list [] is returned for the attribute_columns parameter.
|
next_token | The position from which the next wide-column read operation starts. This parameter is unavailable. |
Response format
[
'tables' => [
[
'table_name' => '<string>',
'rows' => [
[
'is_ok' => true || false,
'error' => [
'code' => '<string>',
'message' => '<string>',
]
'consumed' => [
'capacity_unit' => [
'read' => <integer>,
'write' => <integer>
]
],
'primary_key' => [
['<string>', <PrimaryKeyValue>],
['<string>', <PrimaryKeyValue>],
['<string>', <PrimaryKeyValue>, <PrimaryKeyType>]
],
'attribute_columns' => [
['<string>', <ColumnValue>, <ColumnType>, <integer>]
['<string>', <ColumnValue>, <ColumnType>, <integer>]
['<string>', <ColumnValue>, <ColumnType>, <integer>]
],
'next_token' => '<string>'
],
// Other rows.
]
],
// Other tables.
]
] Sample code
The following sample code provides an example on how to read 30 rows of data from three tables at a time. Ten rows are read from each table.
// Read data from three tables. Ten rows are read from each table.
$tables = array();
for($i = 0; $i < 3; $i++) {
$primary_keys = array();
for($j = 0; $j < 10; $j++) {
$primary_keys[] = [
['pk0', $i],
['pk1', $j]
];
}
$tables[] = [
'table_name' => 'SampleTable' . $i,
'max_versions' => 1,
'primary_keys' => $primary_keys
];
}
$request = [
'tables' => $tables
];
$response = $otsClient->batchGetRow ($request);
// Process the data that is returned from each table.
foreach ($response['tables'] as $tableData) {
print "Handling table {$tableData['table_name']} ...\n";
// Process each row of data in the table.
foreach ($tableData['rows'] as $rowData) {
if ($rowData['is_ok']) {
// Process the data that is read.
$row = json_encode($rowData['primary_key']);
print "Handling row: {$row}\n";
} else {
// Handle errors.
print "Error: {$rowData['error']['code']} {$rowData['error']['message']}\n";
}
}
} The following table describes the examples of detailed sample code.
Example | Description |
Shows you how to call the BatchGetRow operation to read multiple rows from a table. | |
Shows you how to call the BatchGetRow operation to read multiple rows from multiple tables. | |
Shows you how to call the BatchGetRow operation to read the specified columns of multiple rows from a table. | |
Shows you how to call the BatchGetRow operation to process the results that are returned. | |
Shows you how to call the BatchGetRow operation together with a filter. |
Read data whose primary key values are within a specific range
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 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 where 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 the 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.Tablestore
Usage 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.
If one of the following conditions is met, the GetRange operation may stop and return data:
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.
API operation
/**
* Read data whose primary key values are in the range that is specified by the start primary key information and end primary key information.
* The server may truncate this range. If this happens, you must determine whether to call the GetRange operation again based on the value of the next_start_primary_key parameter that is returned in the response.
* You can specify the maximum number of rows that you want to read.
* When you specify the start primary key information and end primary key information, you can use INF_MIN to specify the minimum value and use INF_MAX to specify the maximum value. For more information, see the following sample code.
* @api
* @param [] $request The request parameters.
* @return [] The response.
* @throws OTSClientException The exception that is returned if a parameter error occurs or the Tablestore server returns a verification error.
* @throws OTSServerException The exception that is returned if the Tablestore server returns an error.
*/
public function getRange(array $request); Parameters
Compared with the GetRow operation, the GetRange operation has the following changes:
The primary_key parameter is changed to the inclusive_start_primary_key and exclusive_end_primary_key parameters. The range is a left-closed, right-open interval.
The direction parameter is added to specify the direction for the read operation.
The limit parameter is added to limit the number of returned rows.
Parameter | Description |
table_name | The name of the table. |
inclusive_start_primary_key | The start primary key information and end primary key information of the range that you want to read. The start primary key column and end primary key column must be valid primary key columns or virtual columns whose data is of the INF_MIN type and INF_MAX type. The number of columns in the range specified by virtual columns must be the same as the number of primary key columns of the specified table. INF_MIN indicates an infinitely small value. All values of other types are greater than a value of the INF_MIN type. INF_MAX indicates an infinitely great value. All values of other types are smaller than a value of the INF_MAX type.
The rows in the table are sorted in ascending order based on primary key values. The range that is used to read data is a left-closed, right-open interval. If data is read in the forward direction, the rows whose primary key values are greater than or equal to the start primary key value but smaller than the end primary key value are returned. |
exclusive_end_primary_key | |
direction | The order in which you want to sort the rows in the response.
For example, a table has two primary key values A and B, and Value A is smaller than Value B. If you set the direction parameter to DirectionConst::CONST_FORWARD and specify a |
limit | The maximum number of rows that can be returned. The value of this parameter must be greater than 0. Tablestore stops an operation after the maximum number of rows that can be returned in the forward or backward direction is reached, even if some rows in the specified range are not returned. You can use the value of the next_start_primary_key parameter returned in the response to read data in the next request. |
max_versions | The maximum number of data versions that you can read. Important You must specify at least one of the max_versions and time_range parameters.
|
time_range | The time range of versions or a specific version that you want to read. For more information, see TimeRange. Important You must specify at least one of the max_versions and time_range parameters.
Only one of specific_time and Valid values of the time_range parameter: 0 to |
columns_to_get | The columns that you want to read. You can specify the names of primary key columns or attribute columns.
Note
|
start_column | The column from which the wide-column read operation starts. The response includes the start column. The columns are sorted based on their names in alphabetical order. For example, a table contains three columns: a, b, and c. If the value of the start_column parameter is b, the read operation starts from Column b, and Columns b and c are returned. |
end_column | The column at which the wide-column read operation ends. The response excludes the end column. The columns are sorted based on their names in alphabetical order. For example, a table contains three columns: a, b, and c. If the value of the end_column parameter is b, the read operation ends at Column b, and Column a is returned. |
token | The position from which the next wide-column read operation starts. This parameter is unavailable. |
column_filter | The filter that you want to use to filter the query results on the server side. Only rows that meet the filter conditions are returned. For more information, see Configure filter. Note If you specify both the columns_to_get and column_filter parameters, Tablestore queries the columns that are specified by the columns_to_get parameter, and then returns the rows that meet the filter conditions. |
Request syntax
$result = $client->getRange([
'table_name' => '<string>', // Specify the name of the table.
'inclusive_start_primary_key' => [ // Specify the start primary key information.
['<string>', <PrimaryKeyValue>],
['<string>', <PrimaryKeyValue>],
['<string>', <PrimaryKeyValue>, <PrimaryKeyType>]
],
'exclusive_end_primary_key' => [ // Specify the end primary key information.
['<string>', <PrimaryKeyValue>],
['<string>', <PrimaryKeyValue>],
['<string>', <PrimaryKeyValue>, <PrimaryKeyType>]
],
'direction' => <Direction>, // Specify the order in which you want to sort the rows in the response.
'limit' => <Direction>,
'max_versions' => <integer>,
'time_range' => [
'start_time' => <integer>,
'end_time' => <integer>,
'specific_time' => <integer>
],
'start_column' => '<string>',
'end_column' => '<string>',
'token' => '<string>',
'columns_to_get' => [
'<string>',
'<string>',
//...
],
'column_filter' => <ColumnCondition>
]); Response parameters
Parameter | Description |
consumed | The number of CUs that are consumed by the operation. capacity_unit: the number of read/write CUs that are consumed. Parameters:
|
primary_key | The primary key values, which are the same as those specified in the request. |
attribute_columns | The attribute columns.
|
next_start_primary_key | The start primary key information of the next read request. The value of the next_start_primary_key parameter can be used to determine whether all data is read.
|
Response format
[
'consumed' => [
'capacity_unit' => [
'read' => <integer>,
'write' => <integer>
]
],
'next_start_primary_key' => [
['<string>', <PrimaryKeyValue>],
['<string>', <PrimaryKeyValue>],
['<string>', <PrimaryKeyValue>, <PrimaryKeyType>]
],
'rows' => [
[
'primary_key' => [
['<string>', <PrimaryKeyValue>],
['<string>', <PrimaryKeyValue>],
['<string>', <PrimaryKeyValue>, <PrimaryKeyType>]
],
'attribute_columns' => [
['<string>', <ColumnValue>, <ColumnType>, <integer>]
['<string>', <ColumnValue>, <ColumnType>, <integer>]
['<string>', <ColumnValue>, <ColumnType>, <integer>]
]
],
// Other rows.
]
]
Sample code
The following sample code provides an example on how to read data whose primary key values are in the specified range:
// Query the rows whose value of the PK0 column is in the range of [1, 4).
// You must provide complete primary key columns to specify the range. If the query range does not involve the values of a column, set the values of the column to an infinitely great value (INF_MAX) or an infinitely small value (INF_MIN).
$startPK = [
['PK0', 1],
['PK1', null, PrimaryKeyTypeConst::CONST_INF_MIN] // INF_MIN indicates an infinitely small value.
];
// You must provide complete primary key columns to specify the range. If the query range does not involve the values of a column, set the values of the column to an infinitely great value (INF_MAX) or an infinitely small value (INF_MIN).
$endPK = [
['PK0', 4],
['PK1', null, PrimaryKeyTypeConst::CONST_INF_MAX] // INF_MAX indicates an infinitely great value.
];
$request = [
'table_name' => 'SampleTable',
'max_versions' => 1, // Set this parameter to 1 to read the latest version.
'direction' => DirectionConst::CONST_FORWARD, // Query data in the forward direction.
'inclusive_start_primary_key' => $startPK, // Specify the start primary key information.
'exclusive_end_primary_key' => $endPK, // Specify the end primary key information.
'limit' => 10 // Set this parameter to 10 to specify that up to 10 rows of data can be returned.
];
$response = $otsClient->getRange ($request);
print "Read CU Consumed: {$response['consumed']['capacity_unit']['read']}\n";
foreach ($response['rows'] as $rowData) {
// Process each row of data.
} The following table describes the examples of detailed sample code.
Example | Description |
Shows you how to call the GetRange operation. | |
Shows you how to call the GetRange operation to obtain the specified columns. | |
Shows you how to call the GetRange operation to obtain the specified number of rows. | |
Shows you how you call the GetRange operation together with a filter. |