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
Use global secondary indexes
The following sample code provides an example on how to read a row of data in a global secondary index based on the primary key of the row:
var TableStore = require('./index.js');
var Long = TableStore.Long;
var client = require('./client');
var params = {
// Specify the name of the index table.
tableName: "<INDEX_NAME>",
// Specify the information about the primary key of row in the index table.
primaryKey: [{ 'col1': Long.fromNumber(2) }, { 'pk1': Long.fromNumber(2) }, { 'pk2': Long.fromNumber(1) }]
};
client.getRow(params, function (err, data) {
if (err) {
console.log('error:', err);
return;
}
console.log('success:', JSON.stringify(data.row, null, 2));
});Use local secondary indexes
The following sample code provides an example on how to read a row of data in a local secondary index based on the primary key of the row:
var TableStore = require('./index.js');
var Long = TableStore.Long;
var client = require('./client');
var params = {
// Specify the name of the index table.
tableName: "<INDEX_NAME>",
// Specify the information about the primary key of row in the index table. The first primary key column of the index table must be the same as the first primary key column of the data table.
primaryKey: [{ 'pk1': Long.fromNumber(1) }, { 'col1': Long.fromNumber(2) }, { 'pk2': Long.fromNumber(2) }]
};
client.getRow(params, function (err, data) {
if (err) {
console.log('error:', err);
return;
}
console.log('success:', JSON.stringify(data.row, null, 2));
});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 col1. The value that is specified for the col1 column for the range is 1.
var TableStore = require('./index.js');
var Long = TableStore.Long;
var client = require('./client');
var params = {
// Specify the name of the index table. In this example, col1 is the first primary key column of the index table. pk1 and pk2 are the primary key columns of the data table. Tablestore automatically adds pk1 and pk2 to the index table as the primary key columns of the index table.
tableName: "<INDEX_NAME>",
// Specify that data is read in the forward direction.
direction: TableStore.Direction.FORWARD,
// Specify the maximum number of versions of data that you want to return.
maxVersions: 10,
// Specify the start and end primary keys. The range of the primary key value is a left-closed, right-opened interval. INF_MIN indicates an infinitely small value. INF_MAX indicates an infinitely great value.
inclusiveStartPrimaryKey: [{ "col1": Long.fromNumber(1) }, { "pk1": TableStore.INF_MIN }, { "pk2": TableStore.INF_MIN }],
exclusiveEndPrimaryKey: [{ "col1": Long.fromNumber(1) }, { "pk1": TableStore.INF_MAX }, { "pk2": TableStore.INF_MAX }],
// Specify the maximum number of rows that can be returned for a call.
limit: 2
};
var resultRows = []
var getRange = function () {
client.getRange(params, function (err, data) {
if (err) {
console.log('error:', err);
return;
}
resultRows = resultRows.concat(data.rows)
// If the data.next_start_primary_key parameter is not empty in the response, continue to read data.
if (data.nextStartPrimaryKey) {
params.inclusiveStartPrimaryKey = [
{ "col1": data.nextStartPrimaryKey[0].value },
{ "pk1": data.nextStartPrimaryKey[1].value },
{ "pk2": data.nextStartPrimaryKey[2].value }
];
getRange()
} else {
console.log(JSON.stringify(resultRows));
}
});
}
getRange()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:
var TableStore = require('./index.js');
var Long = TableStore.Long;
var client = require('./client');
var params = {
// Specify the name of the index table. In this example, pk1 and col1 are specified as the primary key columns of the index table. pk2 is the primary key column of the data table. Tablestore automatically adds pk2 to the index table as the primary key column of the index table.
tableName: "<INDEX_NAMW>",
// Specify that data is read in the forward direction.
direction: TableStore.Direction.FORWARD,
// Specify the maximum number of versions of data that you want to return.
maxVersions: 10,
// Specify the start and end primary keys. The range of the primary key value is a left-closed, right-opened interval. INF_MIN indicates an infinitely small value. INF_MAX indicates an infinitely great value.
inclusiveStartPrimaryKey: [{ "pk1": TableStore.INF_MIN }, { "col1": TableStore.INF_MIN }, { "pk2": TableStore.INF_MIN }],
exclusiveEndPrimaryKey: [{ "pk1": TableStore.INF_MAX }, { "col1": TableStore.INF_MAX }, { "pk2": TableStore.INF_MAX }],
// Specify the maximum number of rows that can be returned for a call.
limit: 2
};
var resultRows = []
var getRange = function () {
client.getRange(params, function (err, data) {
if (err) {
console.log('error:', err);
return;
}
resultRows = resultRows.concat(data.rows)
// If the data.next_start_primary_key parameter is not empty in the response, continue to read data.
if (data.nextStartPrimaryKey) {
params.inclusiveStartPrimaryKey = [
{ "pk1": data.nextStartPrimaryKey[0].value },
{ "col1": data.nextStartPrimaryKey[1].value },
{ "pk2": data.nextStartPrimaryKey[2].value }
];
getRange()
} else {
console.log(JSON.stringify(resultRows));
}
});
}
getRange()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.