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
An OTSClient instance is initialized. For more information, see Initialize an OTSClient instance.
A secondary index is created. For more information, see Create a secondary index.
Usage notes
You can use an index table only to read data.
The first primary key column of a local secondary index must be the same as the first primary key column of the data table.
If the attribute columns that you want to return are not contained in the index table, you need to query data from the data table for which the index table is created to obtain the required attribute columns.
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 call the GetRow operation to read data from an index table, take note of the following items:
You must set the TableName parameter to the name of the index table.
Tablestore automatically adds the primary key columns of the data table that are not specified as index columns to an index table as the primary key columns of the index table. Therefore, when you specify the primary key columns of a row in an index table, you must specify the index columns based on which you create the index table and the primary key columns of the data table.
Examples
The following sample code provides an example on how to read a row of data in an index table based on the primary key of the row:
func GetRowFromIndex(client *tablestore.TableStoreClient, indexName string) {
getRowRequest := new(tablestore.GetRowRequest)
criteria := new(tablestore.SingleRowQueryCriteria);
// Construct the primary key. If you want to 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.
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,)
}
}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 within a specific range. For more information, see Read data whose primary key values are within a specific range.
Parameters
When you call the GetRange operation to read data from an index table, take note of the following items:
You must set the TableName parameter to the name of the index table.
Tablestore automatically adds the primary key columns of the data table that are not specified as index columns to an index table as the primary key columns of the index table. Therefore, when you specify the start primary key and end primary key of the range that you want to query, you must specify the index columns based on which you create the index table and the primary key columns of the data table.
Examples
Use global secondary indexes
The following sample code provides an example on how to read data whose primary key values are within a specific range from a global secondary index. In this example, the first primary key column of the global secondary index is definedcol1. The value that is specified for the definedcol1 column for the range is value1.
func GetRangeFromGlobalIndex(client *tablestore.TableStoreClient, indexName string) {
getRangeRequest := &tablestore.GetRangeRequest{}
rangeRowQueryCriteria := &tablestore.RangeRowQueryCriteria{}
rangeRowQueryCriteria.TableName = indexName
// Construct the start primary key of the range. The range of the primary key value is a left-closed, right-opened interval.
startPK := new(tablestore.PrimaryKey)
// Specify a value for the first primary key column.
startPK.AddPrimaryKeyColumn("definedcol1", "value1")
// Specify a value for the second primary key column. The second primary key column is a primary key column of the data table that is not used as an index column of the global secondary index.
startPK.AddPrimaryKeyColumnWithMinValue("pk1")
// Specify a value for the third primary key column. The third primary key column is a primary key column of the data table that is not used as an index column of the global secondary index.
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")
}Use local secondary indexes
The following sample code provides an example on how to read the data of all rows from a local secondary index:
func GetRangeFromLocalIndex(client *tablestore.TableStoreClient, indexName string) {
getRangeRequest := &tablestore.GetRangeRequest{}
rangeRowQueryCriteria := &tablestore.RangeRowQueryCriteria{}
rangeRowQueryCriteria.TableName = indexName
// Construct the start primary key of the range. The range of the primary key value is a left-closed, right-opened interval.
startPK := new(tablestore.PrimaryKey)
// Specify a value for the first primary key column.
startPK.AddPrimaryKeyColumnWithMinValue("pk1")
// Specify a value for the second primary key column.
startPK.AddPrimaryKeyColumnWithMinValue("definedcol1")
// Specify a value for the third primary key column. The third primary key column is a primary key column of the data table that is not used as an index column of the global secondary index.
startPK.AddPrimaryKeyColumnWithMinValue("pk2")
// Construct the end primary key of the range.
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 the attribute columns of the index table, write code based on the following sample code.
//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")
}FAQ
References
If your business requires multi-dimensional queries and data analysis, you can create a search index and specify the required attributes as the fields of the search index. Then, you can query and analyze data by using the search index. For example, you can use a search index to perform queries based on non-primary key columns, Boolean queries, and fuzzy queries. You can also use a search index to obtain the maximum and minimum values, collect statistics about the number of rows, and group query results. For more information, see Search index.
If you want to execute SQL statements to query and analyze data, you can use the SQL query feature. For more information, see SQL query.