The secondary index feature allows you to query data based on the primary key of a data table and the index columns of the secondary index that is created for the data table. If you need to use the attribute columns of a data table to query data, you can create a secondary index for the data table to accelerate data queries. When you create a secondary index for a data table, you can set the index columns or attribute columns of the secondary index to the predefined columns that you specified when you created the data table. After you create a secondary index, you can use the secondary index to query data.
Secondary indexes are classified into global secondary indexes and local secondary indexes. For more information about the secondary index feature, see Overview.
You can create one or more index tables when you create a data table by calling the CreateTable operation. For more information, see Create data tables.
Prerequisites
An OTSClient instance is initialized. For more information, see Initialize an OTSClient instance.
A data table for which the maxVersions parameter is set to 1 is created. One of the following conditions must be met by the timeToLive parameter of the data table:
The timeToLive parameter of the data table is set to -1, which means that data in the data table never expires.
The timeToLive parameter of the data table is set to a value other than -1, and update operations on the data table are prohibited.
Predefined columns are specified for the data table.
Usage notes
The name of an index table must be different from the name of an existing time series table or data table.
When you create a secondary index, Tablestore automatically adds the primary key columns of the data table that are not specified as index columns to the secondary index as the primary key columns of the secondary index.
When you create 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.
Parameters
Parameter | Description |
mainTableName | The name of the data table. |
indexMeta | The schema information about the index table. The schema information contains the following items:
|
Examples
Create a global secondary index
The following sample code provides an example on how to create a global secondary index that does not include the existing data of the data table for which the global secondary index is created. In the example, the primary key columns of the data table are pk1 and pk2. The primary key column and the attribute column that are specified for the global secondary index are col1 and col2, respectively. The primary key columns of the index table consist of col1, pk1, and pk2. The attribute column of the index table is col2.
var client = require('./client');
var TableStore = require('./index.js');
client.createIndex({
mainTableName: "<TABLE_NAME>", // Specify the name of the data table.
indexMeta: {
name: "<INDEX_NAME>", // Specify the name of the index table.
primaryKey: ["col1"], // Specify a primary key column for the index table.
definedColumn: ["col2"], // Specify an attribute column for the index table.
includeBaseData: false, // Specify that the index table does not include the existing data of the data table for which the index table is created. If you want to include the existing data of the data table in the index table, set the includeBaseData parameter to true.
indexUpdateMode: TableStore.IndexUpdateMode.IUM_ASYNC_INDEX,// By default, the index update mode is asynchronous update (IUM_ASYNC_INDEX).
indexType: TableStore.IndexType.IT_GLOBAL_INDEX,// By default, the index type is global secondary index (IT_GLOBAL_INDEX).
}
}, function (err, data) {
if (err) {
console.log('error:', err);
return;
}
console.log('success:', JSON.stringify(data, null, 2));
});Create a local secondary index
The following sample code provides an example on how to create a local secondary index that does not include the existing data of the data table for which the local secondary index is created. In the example, the primary key columns of the data table are pk1 and pk2. The primary key columns that are specified for the local secondary index are col1 and pk1. The attribute column that is specified for the local secondary index is col2. The primary key columns of the index table consist of col1, pk1, and pk2. The attribute column of the index table is col2.
var client = require('./client');
var TableStore = require('./index.js');
client.createIndex({
mainTableName: "<TABLE_NAME>", // Specify the name of the data table.
indexMeta: {
name: "<INDEX_TABLE>", // Specify the name of the index table.
primaryKey: ["pk1","col1"], // Specify primary key columns for 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.
definedColumn: ["col2"], // Specify an attribute column for the index table.
includeBaseData: false, // Specify that the index table does not include the existing data of the data table for which the index table is created. If you want to include the existing data of the data table in the index table, set the includeBaseData parameter to true.
indexUpdateMode: TableStore.IndexUpdateMode.IUM_SYNC_INDEX// Set the indexUpdateMode parameter to IUM_SYNC_INDEX, which indicates that the index is updated in synchronous mode. If you set the indexType parameter to IT_LOCAL_INDEX, you must set the indexUpdateMode parameter to IUM_SYNC_INDEX.
indexType: TableStore.IndexType.IT_LOCAL_INDEX, // Set the indexType parameter to IT_LOCAL_INDEX, which indicates a local secondary index.
}
}, function (err, data) {
if (err) {
console.log('error:', err);
return;
}
console.log('success:', JSON.stringify(data, null, 2));
});References
After you create a secondary index, you can use the secondary index to read a single row of data or data whose primary key values are within a specific range. For more information, see Use a secondary index to read data.
You can delete a secondary index that you no longer use. For more information, see Delete a secondary index.