Create a secondary index by using Tablestore SDK for Java - Tablestore

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. You can call the CreateIndex operation to create an index table for an existing data table.

Note

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 a data table.

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: indexName: the name of the index table. primaryKey: the primary key of the index table. The primary key is a combination of all primary key columns and a random number of predefined columns of the data table. If you want to 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. definedColumns: the attribute columns of the index table. The attribute columns are a combination of predefined columns of the data table. indexType: the type of the index table. Valid values: IT_GLOBAL_INDEX and IT_LOCAL_INDEX. If you do not specify the indexType parameter or set the indexType parameter to IT_GLOBAL_INDEX, a global secondary index is created. Tablestore automatically synchronizes data from the indexed columns and primary key columns of the data table to the columns of the index table that you want to create in asynchronous mode. The synchronization latency is within a few milliseconds. If you set the indexType parameter to IT_LOCAL_INDEX, a local secondary index is created. Tablestore automatically synchronizes data from the indexed columns and primary key columns of the data table to the columns of the index table that you want to create in synchronous mode. After data is written to the data table, you can immediately query the data in the index table. indexUpdateMode: the update mode of the index table. Valid values: IUM_ASYNC_INDEX and IUM_SYNC_INDEX. If you do not specify the indexUpdateMode parameter or set the indexUpdateMode parameter to IUM_ASYNC_INDEX, the asynchronous mode is used to update the index. If you use the global secondary index feature, you must set the indexUpdateMode parameter to IUM_ASYNC_INDEX. If you set the indexUpdateMode parameter to IUM_SYNC_INDEX, the synchronous mode is used to update the index. If you use the local secondary index feature, you must set the indexUpdateMode parameter to IUM_SYNC_INDEX.
includeBaseData	Specifies whether to include the existing data of the data table in the index table. The includeBaseData parameter is the last parameter in CreateIndexRequest. If you set the includeBaseData parameter to true, the index table includes the existing data of the data table. If you set the includeBaseData parameter to false, the index table does not include the existing data of the data table.

Examples

Create a global secondary index

The following sample code provides an example on how to create a global secondary index for a data table:

private static void createIndex(SyncClient client) {
    // Specify the name of the index table. 
    IndexMeta indexMeta = new IndexMeta("<INDEX_NAME>"); 
    // Specify the DEFINED_COL_NAME_1 column as the first primary key column of the index table. 
    indexMeta.addPrimaryKeyColumn(DEFINED_COL_NAME_1); 
    // Specify the PRIMARY_KEY_NAME_2 column as the second primary key column of the index table. 
    indexMeta.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2); 
    // Specify the DEFINED_COL_NAME_2 column as the attribute column of the index table. 
    indexMeta.addDefinedColumn(DEFINED_COL_NAME_2); 
    // Specify the data table for which you want to create the index table and specify that the index table includes the existing data of the data table. Apply the settings that you specified for the index table. 
    //CreateIndexRequest request = new CreateIndexRequest("<TABLE_NAME>", indexMeta, true); 
    // Specify the data table for which you want to create the index table and specify that the index table does not include the existing data of the data table. Apply the settings that you specified for the index table. 
    CreateIndexRequest request = new CreateIndexRequest("<TABLE_NAME>", indexMeta, false); 
    /**You can set the IncludeBaseData parameter to true to synchronize the existing data of the data table to the index table after the index table is created. Then, you can query all data from the index table. 
       The amount of time required to synchronize the existing data of the data table to the index table varies based on the amount of data in the data table. 
    */
    //request.setIncludeBaseData(true);
    // Create the index table. 
    client.createIndex(request); 
}

Create a local secondary index

The following sample code provides an example on how to create a local secondary index:

private static void createIndex(SyncClient client) {
    // Specify the name of the index table. 
    IndexMeta indexMeta = new IndexMeta("<INDEX_NAME>"); 
    // Specify the PRIMARY_KEY_NAME_1 column as the first primary key column of the index table. 
    // The first primary key column of the local secondary index must be the same as the first primary key column of the data table. 
    indexMeta.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1); 
    // Specify the DEFINED_COL_NAME_1 column as the second primary key column of the index table. 
    indexMeta.addPrimaryKeyColumn(DEFINED_COL_NAME_1); 
    // Specify the DEFINED_COL_NAME_2 column as the attribute column of the index table. 
    indexMeta.addDefinedColumn(DEFINED_COL_NAME_2); 
    // Set the index type to local secondary index (IT_LOCAL_INDEX). 
    indexMeta.setIndexType(IT_LOCAL_INDEX);
    // Set the index update mode to synchronous update (IUM_SYNC_INDEX). 
    indexMeta.setIndexUpdateMode(IUM_SYNC_INDEX);
    // Specify the data table for which you want to create the index table and specify that the index table includes the existing data of the data table. Apply the settings that you specified for the index table. 
    //CreateIndexRequest request = new CreateIndexRequest("<TABLE_NAME>", indexMeta, true); 
    // Specify the data table for which you want to create the index table and specify that the index table does not include the existing data of the data table. Apply the settings that you specified for the index table. 
    CreateIndexRequest request = new CreateIndexRequest("<TABLE_NAME>", indexMeta, false); 
    /**You can set the IncludeBaseData parameter to true to synchronize the existing data of the data table to the index table after the index table is created. Then, you can query all data from the index table. 
       The amount of time required to synchronize the existing data of the data table to the index table varies based on the amount of data in the data table. 
    */
    //request.setIncludeBaseData(true);
    // Create the index table. 
    client.createIndex(request); 
}

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.