Use a secondary index to read data

Updated at:

Tablestore provides single-row and range read methods to read data from index tables. If an index table contains the attribute columns that you want to retrieve, you can read the data directly from the index table. Otherwise, you must query the data from the primary table.

Prerequisites

Before you begin, make sure that you have:

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.

Usage notes

  • Index tables are read-only. Use GetRow or GetRange to read data from an index table.

  • For a local secondary index (LSI), the first primary key column of the index table must match the first primary key column of the data table.

  • Set table_name to the name of the index table, not the data table.

  • Tablestore automatically adds the primary key columns of the data table that are not specified as index columns to the index table. When specifying primary key values, include both the index columns and the added data table primary key columns.

  • If the attribute columns to return are not projected into the index table, query the data table directly to retrieve those columns.

    Read a single row

    Call the GetRow operation to read a single row from an index table. For parameter details, see Read a single row.

    Example

    The following example reads a row from an index table by specifying its primary key:

    $request = [
        'table_name' => '<INDEX_NAME>',       // Name of the index table.
        'primary_key' => [                    // Primary key of the row to read.
                                              // For an LSI, the first column must match the first
                                              // primary key column of the data table.
            ['PK0', 123],
            ['Col1', "abc"],
            ['PK1', 'abc']
        ],
        'max_versions' => 1,                  // Read only the latest version.
        'columns_to_get' => ['Col1']          // Attribute columns to return.
    ];
    $response = $otsClient->getRow($request);

    Read data within a primary key range

    Call the GetRange operation to read rows whose primary key values fall within a specified range. For parameter details, see Read data in a range.

    Each GetRange call returns up to 5,000 rows or 4 MB of data, whichever limit is reached first. If the response includes next_start_primary_key, use that value as the new inclusive_start_primary_key in a subsequent GetRange call to retrieve the remaining rows.

    Example

    The following example reads rows from an index table where the Col1 index column equals "abc", across all values of PK0 and PK1:

    // Define the start primary key. Use INF_MIN for open-ended range columns.
    // For an LSI, the first column must match the first primary key column of the data table.
    $startPK = [
        ['Col1', "abc"],
        ['PK0', null, PrimaryKeyTypeConst::CONST_INF_MIN],  // Start from the minimum value.
        ['PK1', null, PrimaryKeyTypeConst::CONST_INF_MIN]   // Start from the minimum value.
    ];
    
    // Define the end primary key. Use INF_MAX for open-ended range columns.
    $endPK = [
        ['Col1', "abc"],
        ['PK0', null, PrimaryKeyTypeConst::CONST_INF_MAX],  // End at the maximum value.
        ['PK1', null, PrimaryKeyTypeConst::CONST_INF_MAX]   // End at the maximum value.
    ];
    
    $request = [
        'table_name'                  => '<INDEX_NAME>',                // Name of the index table.
        'max_versions'                => 1,                             // Read only the latest version.
        'direction'                   => DirectionConst::CONST_FORWARD, // Read in ascending order.
        'inclusive_start_primary_key' => $startPK,
        'exclusive_end_primary_key'   => $endPK,
        'limit'                       => 10                             // Maximum rows per call.
    ];
    
    $response = $otsClient->getRange($request);
    print "Read capacity units consumed: {$response['consumed']['capacity_unit']['read']}\n";
    
    foreach ($response['rows'] as $rowData) {
        // Process each row.
    }
    
    // If next_start_primary_key is present, more rows are available.
    // Call GetRange again with next_start_primary_key as inclusive_start_primary_key.
    if (!empty($response['next_start_primary_key'])) {
        // Continue reading from $response['next_start_primary_key'].
    }

    FAQ

    What's next

    • For multi-dimensional queries and data analysis—such as queries on non-primary key columns, boolean queries, fuzzy queries, aggregations, and grouping—create a search index. For more information, see Search index.

    • To query and analyze data using SQL statements, use the SQL query feature. For more information, see SQL query.