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.
If the attribute columns that you want to return are not included in the index table, you must 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:
public static void GetRowfromIndex()
{
OTSClient otsClient = Config.GetClient();
// Construct the primary key. When you construct the primary key, you must specify the primary key columns of the data table that are not specified as index columns.
PrimaryKey primaryKey = new PrimaryKey
{
{ "col0", new ColumnValue(0) },
{ "pk1", new ColumnValue(0) },
{ "pk2", new ColumnValue("abc") }
};
try
{
// Construct a query request object. The entire row is read if no attribute column is specified.
var request = new GetRowRequest(IndexName, primaryKey);
// Call the GetRow operation to query data.
var response = otsClient.GetRow(request);
Console.WriteLine("Primary Key: " + response.PrimaryKey);
Console.WriteLine("Attribute Column: " + response.Columns[0]);
// If no exception is returned, the operation is successful.
Console.WriteLine("Get row succeeded.");
}
catch (Exception ex)
{
// If an exception is returned, the operation fails. Handle the exception.
Console.WriteLine("Get row failed, exception:{0}", ex.Message);
}
}
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
The following sample code provides an example on how to read data whose primary key values are within a specific range from an index table. In this example, the first primary key column of the index table is col1. The value that is specified for the col1 column for the range is 0.
public static void GetRangeFromIndexTable()
{
Console.WriteLine("Start getRange from index...");
OTSClient otsClient = Config.GetClient();
// Specify the value of the first primary key column col1 and scan the data.
PrimaryKey inclusiveStartPrimaryKey = new PrimaryKey
{
{ "col1", new ColumnValue(0) },
{ "pk1", ColumnValue.INF_MIN },
{ "pk2", ColumnValue.INF_MIN }
};
PrimaryKey exclusiveEndPrimaryKey = new PrimaryKey
{
{ "col1", new ColumnValue(0) },
{ "pk1", ColumnValue.INF_MAX },
{ "pk2", ColumnValue.INF_MAX }
};
GetRangeRequest request = new GetRangeRequest(IndexName, GetRangeDirection.Forward, inclusiveStartPrimaryKey, exclusiveEndPrimaryKey);
GetRangeResponse response = otsClient.GetRange(request);
IList<Row> rows = response.RowDataList;
PrimaryKey nextStartPrimaryKey = response.NextPrimaryKey;
while (nextStartPrimaryKey != null)
{
request = new GetRangeRequest(TableName, GetRangeDirection.Forward, nextStartPrimaryKey, exclusiveEndPrimaryKey);
response = otsClient.GetRange(request);
nextStartPrimaryKey = response.NextPrimaryKey;
foreach (var row in response.RowDataList)
{
rows.Add(row);
}
}
foreach (var row in rows)
{
PrintRow(row);
}
Console.WriteLine("TotalRowsRead: " + rows.Count);
}
private static void PrintRow(Row row)
{
Console.WriteLine("-----------------");
foreach (KeyValuePair<string, ColumnValue> entry in row.GetPrimaryKey())
{
Console.WriteLine(entry.Key + ":" + entry.Value);
}
foreach (Column entry in row.GetColumns())
{
Console.WriteLine(entry.Name + ":" + entry.Value);
}
Console.WriteLine("-----------------");
}
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.