Use a secondary index to read data by using Tablestore SDK for Go - Tablestore

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

Before you begin, ensure that you have:

An initialized OTSClient instance. For more information, see Initialize a Tablestore client.
A created secondary index. For more information, see Create a secondary index.

Usage notes

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 data table.
If the attribute columns to return are not included in the index table, you must query the data table to retrieve the data.

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

Parameters

When calling GetRow on an index table, set the following parameters:

Parameter	Description
`TableName`	The name of the index table, not the data table.
Primary key columns	Include both the index columns and the primary key columns of the data table. Tablestore automatically adds the data table's primary key columns that are not specified as index columns to the index table as additional primary key columns.

Example

The following example reads a row from an index table by specifying its full primary key. The primary key consists of the index column definedcol1 followed by the data table's primary key columns pk1 and pk2.

func GetRowFromIndex(client *tablestore.TableStoreClient, indexName string) {
    getRowRequest := new(tablestore.GetRowRequest)
    criteria := new(tablestore.SingleRowQueryCriteria)
    // Construct the primary key.
    // For an LSI, the first primary key column must match the first primary key column of the data table.
    putPk := new(tablestore.PrimaryKey)
    putPk.AddPrimaryKeyColumn("definedcol1", "value1")
    putPk.AddPrimaryKeyColumn("pk1", int64(2))
    putPk.AddPrimaryKeyColumn("pk2", []byte("pk2"))
    criteria.PrimaryKey = putPk

    getRowRequest.SingleRowQueryCriteria = criteria
    getRowRequest.SingleRowQueryCriteria.TableName = indexName
    getRowRequest.SingleRowQueryCriteria.MaxVersion = 1
    getResp, err := client.GetRow(getRowRequest)
    if err != nil {
        fmt.Println("getrow failed with error:", err)
    } else {
        fmt.Println("get row", getResp.Columns[0].ColumnName, "result is", getResp.Columns[0].Value)
    }
}

TableName is set to the index table name. The primary key follows the index table's column order: index column first (definedcol1), then the data table's primary key columns (pk1, pk2). MaxVersion = 1 returns only the latest version of each attribute column.

Read data within a primary key range

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

Parameters

When calling GetRange on an index table, set the following parameters:

Parameter	Description
`TableName`	The name of the index table, not the data table.
Start and end primary key	Specify the full primary key for both ends of the range. Include all index columns and data table primary key columns. The range is a left-closed, right-open interval: `[start, end)`.

GSI vs LSI primary key ordering

The order of primary key columns differs between index types:

Index type	Primary key column order
Global secondary index (GSI)	Index column(s) first, then data table primary key columns
Local secondary index (LSI)	Data table's first primary key column first, then index column(s), then remaining data table primary key columns

For example, if the data table has primary key columns pk1 and pk2, and definedcol1 is the index column:

GSI primary key order: definedcol1 → pk1 → pk2
LSI primary key order: pk1 → definedcol1 → pk2

Examples

Use global secondary indexes

The following example reads rows from a GSI where the index column definedcol1 equals value1. The start and end primary keys use AddPrimaryKeyColumnWithMinValue and AddPrimaryKeyColumnWithMaxValue on pk1 and pk2 to cover all rows matching that index column value.

func GetRangeFromGlobalIndex(client *tablestore.TableStoreClient, indexName string) {
    getRangeRequest := &tablestore.GetRangeRequest{}
    rangeRowQueryCriteria := &tablestore.RangeRowQueryCriteria{}
    rangeRowQueryCriteria.TableName = indexName

    // Construct the start primary key. The range is a left-closed, right-open interval.
    startPK := new(tablestore.PrimaryKey)
    // GSI primary key order: index column first, then data table primary key columns.
    startPK.AddPrimaryKeyColumn("definedcol1", "value1")
    startPK.AddPrimaryKeyColumnWithMinValue("pk1")
    startPK.AddPrimaryKeyColumnWithMinValue("pk2")

    endPK := new(tablestore.PrimaryKey)
    endPK.AddPrimaryKeyColumn("definedcol1", "value1")
    endPK.AddPrimaryKeyColumnWithMaxValue("pk1")
    endPK.AddPrimaryKeyColumnWithMaxValue("pk2")
    rangeRowQueryCriteria.StartPrimaryKey = startPK
    rangeRowQueryCriteria.EndPrimaryKey = endPK
    rangeRowQueryCriteria.Direction = tablestore.FORWARD
    rangeRowQueryCriteria.MaxVersion = 1
    rangeRowQueryCriteria.Limit = 10
    getRangeRequest.RangeRowQueryCriteria = rangeRowQueryCriteria

    getRangeResp, err := client.GetRange(getRangeRequest)

    fmt.Println("get range result is ", getRangeResp)

    for {
        if err != nil {
            fmt.Println("get range failed with error:", err)
        }
        if len(getRangeResp.Rows) > 0 {
            for _, row := range getRangeResp.Rows {
                fmt.Println("range get row with key", row.PrimaryKey.PrimaryKeys[0].Value, row.PrimaryKey.PrimaryKeys[1].Value, row.PrimaryKey.PrimaryKeys[2].Value)
            }
            if getRangeResp.NextStartPrimaryKey == nil {
                break
            } else {
                fmt.Println("next pk is :", getRangeResp.NextStartPrimaryKey.PrimaryKeys[0].Value, getRangeResp.NextStartPrimaryKey.PrimaryKeys[1].Value, getRangeResp.NextStartPrimaryKey.PrimaryKeys[2].Value)
                getRangeRequest.RangeRowQueryCriteria.StartPrimaryKey = getRangeResp.NextStartPrimaryKey
                getRangeResp, err = client.GetRange(getRangeRequest)
            }
        } else {
            break
        }

        fmt.Println("continue to query rows")
    }
    fmt.Println("getrow finished")
}

Key points:

The GSI primary key starts with the index column (definedcol1), followed by the data table's primary key columns (pk1, pk2).
Setting definedcol1 to the same value in both the start and end keys, combined with min/max values for pk1 and pk2, retrieves all rows where definedcol1 = "value1".
If NextStartPrimaryKey is not nil, the result is paginated. The loop continues fetching pages until all matching rows are returned.

Use local secondary indexes

The following example reads all rows from an LSI. Unlike a GSI, the LSI primary key starts with the data table's first primary key column (pk1), followed by the index column (definedcol1), then the remaining data table primary key columns (pk2).

func GetRangeFromLocalIndex(client *tablestore.TableStoreClient, indexName string) {
    getRangeRequest := &tablestore.GetRangeRequest{}
    rangeRowQueryCriteria := &tablestore.RangeRowQueryCriteria{}
    rangeRowQueryCriteria.TableName = indexName

    // Construct the start primary key. The range is a left-closed, right-open interval.
    startPK := new(tablestore.PrimaryKey)
    // LSI primary key order: data table's first PK column, then index column, then remaining PK columns.
    startPK.AddPrimaryKeyColumnWithMinValue("pk1")
    startPK.AddPrimaryKeyColumnWithMinValue("definedcol1")
    startPK.AddPrimaryKeyColumnWithMinValue("pk2")

    // Construct the end primary key.
    endPK := new(tablestore.PrimaryKey)
    endPK.AddPrimaryKeyColumnWithMaxValue("pk1")
    endPK.AddPrimaryKeyColumnWithMaxValue("definedcol1")
    endPK.AddPrimaryKeyColumnWithMaxValue("pk2")
    rangeRowQueryCriteria.StartPrimaryKey = startPK
    rangeRowQueryCriteria.EndPrimaryKey = endPK
    rangeRowQueryCriteria.Direction = tablestore.FORWARD
    rangeRowQueryCriteria.MaxVersion = 1
    rangeRowQueryCriteria.Limit = 10
    getRangeRequest.RangeRowQueryCriteria = rangeRowQueryCriteria

    getRangeResp, err := client.GetRange(getRangeRequest)

    fmt.Println("get range result is ", getRangeResp)

    for {
        if err != nil {
            fmt.Println("get range failed with error:", err)
        }
        if len(getRangeResp.Rows) > 0 {
            for _, row := range getRangeResp.Rows {
                fmt.Println("range get row with key", row.PrimaryKey.PrimaryKeys[0].Value, row.PrimaryKey.PrimaryKeys[1].Value, row.PrimaryKey.PrimaryKeys[2].Value)
                // To return attribute columns stored in the index table, uncomment the following line.
                //fmt.Println("get row", row.Columns[0].ColumnName, "result is ", row.Columns[0].Value)
            }
            if getRangeResp.NextStartPrimaryKey == nil {
                break
            } else {
                fmt.Println("next pk is :", getRangeResp.NextStartPrimaryKey.PrimaryKeys[0].Value, getRangeResp.NextStartPrimaryKey.PrimaryKeys[1].Value, getRangeResp.NextStartPrimaryKey.PrimaryKeys[2].Value)
                getRangeRequest.RangeRowQueryCriteria.StartPrimaryKey = getRangeResp.NextStartPrimaryKey
                getRangeResp, err = client.GetRange(getRangeRequest)
            }
        } else {
            break
        }

        fmt.Println("continue to query rows")
    }
    fmt.Println("getrow finished")
}

Key points:

The LSI primary key starts with pk1 (the data table's first primary key column), then definedcol1 (the index column), then pk2. This order differs from the GSI, where the index column comes first.
Using min/max values for all three columns scans all rows in the LSI.
To retrieve attribute columns stored in the index table, uncomment the row.Columns line in the loop body.

FAQ

For multi-dimensional queries and data analysis — such as queries on non-primary key columns, Boolean queries, fuzzy queries, aggregations, and grouped results — create a search index and query data through it. 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.