All Products
Search
Document Center

Tablestore:Read data

Last Updated:Nov 03, 2023

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 in a batch, and the GetRange operation to read data whose primary key is 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.

Important

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

Read a single row of data

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.

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. 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 in the specified 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 is 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.

Read data whose primary key values are in the specified range by using an iterator

You can call the GetRangeIterator operation to read data whose primary key values are in the specified range by using an iterator.

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, and an iterator is required to read data.

Prerequisites

  • The OTSClient instance is initialized. For more information, see Initialization.
  • 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.

  • 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

/// <summary>
/// Read a single row of data based on the specified primary key information. 
/// </summary>
/// <param name="request">Data query request</param>
/// <returns>Response of GetRow</returns>
public GetRowResponse GetRow(GetRowRequest request);

/// <summary>
/// The asynchronous mode of GetRow. 
/// </summary>
public Task<GetRowResponse> GetRowAsync(GetRowRequest request);          

Parameters

Parameter

Description

tableName

The name of the table.

primaryKey

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.

columnsToGet

The columns that you want to read. You can specify the names of primary key columns or attribute columns.

  • If you do not specify a column, all data in the row is returned.

  • If you specify columns but the row does not contain the specified columns, the return value is null. If the row contains some of the specified columns, the data in some of the specified columns of the row is returned.

Note
  • By default, Tablestore returns data from all columns of a row when you query the row. You can use the columnsToGet parameter to return data from specific columns. If col0 and col1 are added to the columnsToGet parameter, only the values of the col0 and col1 columns are returned.

  • If you specify both the columnsToGet and filter parameters, Tablestore queries the columns that are specified by the columnsToGet parameter, and then returns the rows that meet the filter conditions.

maxVersions

The maximum number of data versions that you can read.

Important

You must specify at least one of the maxVersions and timeRange parameters.

  • If you specify only the maxVersions parameter, data of the specified number of versions is returned from the most recent data entry to the earliest data entry.

  • If you specify only the timeRange parameter, all data whose versions are in the specified time range or data of the specified version is returned.

  • If you specify both the maxVersions and timeRange parameters, data of the specified number of versions in the specified time range is returned from the most recent data entry to the earliest data entry.

timeRange

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 maxVersions and timeRange parameters.

  • If you specify only the maxVersions parameter, data of the specified number of versions is returned from the most recent data entry to the earliest data entry.

  • If you specify only the timeRange parameter, all data whose versions are in the specified time range or data of the specified version is returned.

  • If you specify both the maxVersions and timeRange parameters, data of the specified number of versions in the specified time range is returned from the most recent data entry to the earliest data entry.

  • To query data whose versions are in a specific time range, you must specify the start_time and end_time parameters. The start_time parameter specifies the start timestamp. The end_time parameter specifies the end timestamp. The specified range is a left-closed, right-open interval that is in the [start_time, end_time) format.

  • To query data of a specific version, you must specify the specific_time parameter. The specific_time parameter specifies a specific timestamp.

Only one of specific_time and [start_time, end_time) is required.

Valid values of the timeRange parameter: 0 to Int64.MaxValue. Unit: millisecond.

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 columnsToGet and filter parameters, Tablestore queries the columns that are specified by the columnsToGet parameter, and then returns the rows that meet the filter conditions.

Sample code

Read a row of data

The following sample code provides an example on how to read a row of data:

    // Specify the primary key information of the row. The primary key information must be the same as the primary key information that is specified in TableMeta when the table is created. 
    PrimaryKey primaryKey = new PrimaryKey();
    primaryKey.Add("pk0", new ColumnValue(0));
    primaryKey.Add("pk1", new ColumnValue("abc"));

    try
    {
        // Construct a query request object. The entire row is read if no column is specified. 
        var request = new GetRowRequest(TableName, primaryKey);

        // Call the GetRow operation to query data. 
        var response = otsClient.GetRow(request);

        // Return the data of the row. In this example, the sample code that is used to return the data of the row is omitted. To view the detailed sample code, visit the GitHub link that is provided for this sample code. 

        // If the operation is successful, no exception is returned. 
        Console.WriteLine("Get row succeeded.");
    }
    catch (Exception ex)
    {
        // If the operation fails, an exception is returned. Handle the exception. 
        Console.WriteLine("Update table failed, exception:{0}", ex.Message);
    }
            

To view the detailed sample code, visit GetRow@GitHub.

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.

In this example, the values of the col0 and col1 columns that meet the following filter conditions are returned: the value of the col0 column is 5 or the value of the col1 column is not ff.

    // Specify the primary key information of the row. The primary key information must be the same as the primary key information that is specified in TableMeta when the table is created. 
    PrimaryKey primaryKey = new PrimaryKey();
    primaryKey.Add("pk0", new ColumnValue(0));
    primaryKey.Add("pk1", new ColumnValue("abc"));

    var rowQueryCriteria = new SingleRowQueryCriteria("SampleTable");
    rowQueryCriteria.RowPrimaryKey = primaryKey;

    // Condition 1: The value of the col0 column is 5. 
    var filter1 = new RelationalCondition("col0",
                RelationalCondition.CompareOperator.EQUAL,
                new ColumnValue(5));

    // Condition 2: The value of the col1 column is not ff. 
    var filter2 = new RelationalCondition("col1", RelationalCondition.CompareOperator.NOT_EQUAL, new ColumnValue("ff"));

    // Construct a combination of Condition 1 and Condition 2. The conditions are evaluated by using the OR operator. 
    var filter = new CompositeCondition(CompositeCondition.LogicOperator.OR);
    filter.AddCondition(filter1);
    filter.AddCondition(filter2);

    rowQueryCriteria.Filter = filter;

    // Specify col0 and col1 as the columns that you want to read. Tablestore queries the values of the col0 and col1 columns, and then returns the rows that meet the filter conditions. 
    rowQueryCriteria.AddColumnsToGet("col0");
    rowQueryCriteria.AddColumnsToGet("col1");

    // Construct a GetRowRequest object. 
    var request = new GetRowRequest(rowQueryCriteria);

    try
    {
        // Perform the query. 
        var response = otsClient.GetRow(request);

        // Return data or perform the related logical operation. In this example, the code that is used to return data or perform the related logical operation is omitted. 

        // If the operation is successful, no exception is returned. 
        Console.WriteLine("Get row with filter succeeded.");
    }
    catch (Exception ex)
    {
        // If the operation fails, an exception is returned. Handle the exception. 
        Console.WriteLine("Get row with filter failed, exception:{0}", ex.Message);
    }          

To view the detailed sample code, visit GetRowWithFilter@GitHub.

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 in a batch. 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. BatchGetRow supports filters.

If you call the BatchGetRow operation, each GetRow operation is separately performed and the response to each GetRow operation is separately returned.Tablestore

Usage notes

  • When you call the BatchGetRow operation to read multiple rows in a batch, some rows may fail to be read. If this happens, Tablestore does not return exceptions, but returns BatchGetRowResponse in which the indexes and error messages of the failed rows are included. Therefore, when you call the BatchGetRow operation, you must check the return values to determine whether the status of each row is successful.

  • You can call BatchGetRow to read a maximum of 100 rows at a time.

API operation

/// <summary>
/// <para>Read multiple rows of data from one or more tables at a time. </para>
/// <para>The BatchGetRow operation is a set of GetRow operations. Each operation is performed, returns results, and computes the consumed capacity units (CUs) in an independent way. </para>
/// Compared with a large number of GetRow operations, the BatchGetRow operation can reduce the response time and increase the data read rate. 
/// </summary>
/// <param name="request">Request instance</param>
/// <returns>Response instance</returns>
public BatchGetRowResponse BatchGetRow(BatchGetRowRequest request);

/// <summary>
/// The asynchronous mode of BatchGetRow. 
/// </summary>
public Task<BatchGetRowResponse> BatchGetRowAsync(BatchGetRowRequest request);            

Sample code

The following sample code provides an example on how to read 10 rows of data at a time:

// Construct a request object to read multiple rows of data at a time. Specify the primary key information for 10 rows of data. 
List<PrimaryKey> primaryKeys = new List<PrimaryKey>();
for (int i = 0; i < 10; i++)
{
    PrimaryKey primaryKey = new PrimaryKey();
    primaryKey.Add("pk0", new ColumnValue(i));
    primaryKey.Add("pk1", new ColumnValue("abc"));
    primaryKeys.Add(primaryKey);
}

try
{
    BatchGetRowRequest request = new BatchGetRowRequest();
    request.Add(TableName, primaryKeys);

    // Call the BatchGetRow operation to read 10 rows of data. 
    var response = otsClient.BatchGetRow(request);
    var tableRows = response.RowDataGroupByTable;
    var rows = tableRows[TableName];

    // Return the data of the rows. In this example, the sample code that is used to return the data of the rows is omitted. To view the detailed sample code, visit the GitHub link that is provided for this sample code. 

    // When you call the BatchGetRow operation to read multiple rows at a time, some rows may fail to be read. You must check the return values to determine whether data is successfully read from each row. To view the detailed sample code, visit the GitHub link that is provided for this sample code. 
}
catch (Exception ex)
{
    // If the operation fails, an exception is returned. Handle the exception. 
    Console.WriteLine("Batch get row failed, exception:{0}", ex.Message);
}            

To view the detailed sample code, visit BatchGetRow@GitHub.

Read data whose primary key values are in the specified range

You can call the GetRange operation to read data whose primary key is in the specified range.

The GetRange operation allows you to read data whose primary key is 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.

Note

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 is 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.

Each GetRange call scans data once. If the size of data that you want to scan by calling the GetRange operation is large, the call stops scanning when the number of scanned rows reaches 5,000 or the size of scanned data reaches 4 MB. Tablestore does not return the remaining data that meets the query conditions and you can use the paging method to obtain the remaining data that meets the query conditions.

API operation

/// <summary>
/// Query data whose primary key values are in the specified range. 
/// </summary>
/// <param name="request">Request instance</param>
/// <returns>Response instance</returns>
public GetRangeResponse GetRange(GetRangeRequest request);

/// <summary>
/// The asynchronous mode of GetRange. 
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public Task<GetRangeResponse> GetRangeAsync(GetRangeRequest request);              

Parameters

Parameter

Description

tableName

The name of the table.

direction

The order in which you want to sort the rows in the response.

  • If you set this parameter to FORWARD, the start primary key value must be smaller than the end primary key value, and the rows in the response are sorted in ascending order of primary key values.

  • If you set this parameter to BACKWARD, the start primary key value must be greater than the end primary key value, and the rows in the response are sorted in descending order of primary key values.

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 FORWARD and specify a [A, B) range for the table, Tablestore returns the rows whose primary key values are greater than or equal to Value A but smaller than Value B in ascending order from Value A to Value B. If you set the direction parameter to BACKWARD and specify a [B, A) range for the table, Tablestore returns the rows whose primary key values are smaller than or equal to Value B and greater than Value A in descending order from Value B to Value A.

inclusiveStartPrimaryKey

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 inclusiveStartPrimaryKey parameter specifies the start primary key column and value. If a row contains the start primary key column, the data of this row is returned.

  • The exclusiveEndPrimaryKey parameter specifies the end primary key column and value. If a row contains the end primary key column, the data of this row is not returned.

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.

exclusiveEndPrimaryKey

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 nextStartPrimaryKey parameter returned in the response to read data in the next request.

columnsToGet

The columns that you want to read. You can specify the names of primary key columns or attribute columns.

  • If you do not specify a column, all data in the row is returned.

  • If you specify columns but the row does not contain the specified columns, the return value is null. If the row contains some of the specified columns, the data in some of the specified columns of the row is returned.

Note
  • By default, Tablestore returns data from all columns of a row when you query the row. You can use the columnsToGet parameter to return data from specific columns. If col0 and col1 are added to the columnsToGet parameter, only the values of the col0 and col1 columns are returned.

  • If a row is in the specified range that you want to read based on primary key values but does not contain the specified columns that you want to return, the response excludes the row.

  • If you specify both the columnsToGet and filter parameters, Tablestore queries the columns that are specified by the columnsToGet parameter, and then returns the rows that meet the filter conditions.

maxVersions

The maximum number of data versions that you can read.

Important

You must specify at least one of the maxVersions and timeRange parameters.

  • If you specify only the maxVersions parameter, data of the specified number of versions is returned from the most recent data entry to the earliest data entry.

  • If you specify only the timeRange parameter, all data whose versions are in the specified time range or data of the specified version is returned.

  • If you specify both the maxVersions and timeRange parameters, data of the specified number of versions in the specified time range is returned from the most recent data entry to the earliest data entry.

timeRange

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 maxVersions and timeRange parameters.

  • If you specify only the maxVersions parameter, data of the specified number of versions is returned from the most recent data entry to the earliest data entry.

  • If you specify only the timeRange parameter, all data whose versions are in the specified time range or data of the specified version is returned.

  • If you specify both the maxVersions and timeRange parameters, data of the specified number of versions in the specified time range is returned from the most recent data entry to the earliest data entry.

  • To query data whose versions are in a specific time range, you must specify the start_time and end_time parameters. The start_time parameter specifies the start timestamp. The end_time parameter specifies the end timestamp. The specified range is a left-closed, right-open interval that is in the [start_time, end_time) format.

  • To query data of a specific version, you must specify the specific_time parameter. The specific_time parameter specifies a specific timestamp.

Only one of specific_time and [start_time, end_time) is required.

Valid values of the timeRange parameter: 0 to Int64.MaxValue. Unit: millisecond.

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 columnsToGet and filter parameters, Tablestore queries the columns that are specified by the columnsToGet parameter, and then returns the rows that meet the filter conditions.

nextStartPrimaryKey

The start primary key information of the next read request. The value of the nextStartPrimaryKey parameter can be used to determine whether all data is read.

  • If the value of the nextStartPrimaryKey parameter is not empty in the response, the value can be used as the start primary key information for the next GetRange operation.

  • If the value of the nextStartPrimaryKey parameter is empty in the response, all data within the range is returned.

Sample code

The following sample code provides an example on how to read data whose primary key values are in the specified range:

// Read all rows whose primary key values are in the range of (0, INF_MIN) to (100, INF_MAX). 
var inclusiveStartPrimaryKey = new PrimaryKey();
inclusiveStartPrimaryKey.Add("pk0", new ColumnValue(0));
inclusiveStartPrimaryKey.Add("pk1", ColumnValue.INF_MIN);

var exclusiveEndPrimaryKey = new PrimaryKey();
exclusiveEndPrimaryKey.Add("pk0", new ColumnValue(100));
exclusiveEndPrimaryKey.Add("pk1", ColumnValue.INF_MAX);

try
{
    // Construct a request object to read data whose primary key values are in the specified range. 
    var request = new GetRangeRequest(TableName, GetRangeDirection.Forward,
                    inclusiveStartPrimaryKey, exclusiveEndPrimaryKey);

    var response = otsClient.GetRange(request);

    // Continue the read operation if only part of data is returned. 
    var rows = response.RowDataList;
    var nextStartPrimaryKey = response.NextPrimaryKey;
    while (nextStartPrimaryKey != null)
    {
        request = new GetRangeRequest(TableName, GetRangeDirection.Forward,
                        nextStartPrimaryKey, exclusiveEndPrimaryKey);
        response = otsClient.GetRange(request);
        nextStartPrimaryKey = response.NextPrimaryKey;
        foreach (RowDataFromGetRange row in response.RowDataList)
        {
            rows.Add(row);
        }
    }

    // Return the data of the rows. In this example, the sample code that is used to return the data of the rows is omitted. To view the detailed sample code, visit the GitHub link that is provided for this sample code. 

    // If the operation is successful, no exception is returned. 
    Console.WriteLine("Get range succeeded");
}
catch (Exception ex)
{
    // If the operation fails, an exception is returned. Handle the exception. 
    Console.WriteLine("Get range failed, exception:{0}", ex.Message);
}            

To view the detailed sample code, visit GetRange@GitHub.

Read data whose primary key values are in the specified range by using an iterator

You can call the GetRangeIterator operation to read data whose primary key values are in the specified range by using an iterator.

API operation

/// <summary>
/// Obtain data from multiple rows whose primary key values are within the specified range. Return the iterator used to process each row of data. 
/// </summary>
/// <param name="request"><see cref="GetIteratorRequest"/></param>
/// <returns>Return the <see cref="RowDataFromGetRange"/> iterator. </returns>
public IEnumerable<RowDataFromGetRange> GetRangeIterator(GetIteratorRequest request);           

Sample code

The following sample code provides an example on how to read all rows whose primary key values are in the range of (0, "a") to (1000, "xyz"):

// Read all rows whose primary key values are in the range of (0, "a") to (1000, "xyz"). 
PrimaryKey inclusiveStartPrimaryKey = new PrimaryKey();
inclusiveStartPrimaryKey.Add("pk0", new ColumnValue(0));
inclusiveStartPrimaryKey.Add("pk1", new ColumnValue("a"));

PrimaryKey exclusiveEndPrimaryKey = new PrimaryKey();
exclusiveEndPrimaryKey.Add("pk0", new ColumnValue(1000));
exclusiveEndPrimaryKey.Add("pk1", new ColumnValue("xyz"));

// Construct a CapacityUnit object to record the number of CUs consumed by iteration. 
var cu = new CapacityUnit(0, 0);

try
{
    // Construct a GetIteratorRequest object. Filter conditions are supported. 
    var request = new GetIteratorRequest(TableName, GetRangeDirection.Forward, inclusiveStartPrimaryKey,
                                                exclusiveEndPrimaryKey, cu);

    var iterator = otsClient.GetRangeIterator(request);
    // Use the iterator that reads data in a traversal way. 
    foreach (var row in iterator)
    {
        // Execute the processing logic. 
    }

    Console.WriteLine("Iterate row succeeded");
} 
catch (Exception ex)
{
    Console.WriteLine("Iterate row failed, exception:{0}", ex.Message);
}            

To view the detailed sample code, visit GetRangeIterator@GitHub.