Get started with secondary indexes by using Tablestore SDKs
A secondary index extends the primary key query capability of a data table to different columns. When you need to query data by using attribute columns, you can create a secondary index to accelerate data queries. After you specify predefined columns, you can use the predefined columns as index columns or attribute columns of the index table when you create the secondary index. After you create the secondary index, you can use the secondary index to query data.
Prerequisites
A data table is created. The maximum number of versions (max versions) of the data table must be 1, and the time to live (TTL) of the data table must meet one of the following conditions:
-
The TTL of the data table is -1 (data never expires).
-
If the TTL of the data table is not -1, the data table must be in the update-prohibited state (that is, Allow Updates is set to No).
The TTL of a secondary index is the same as the TTL of the data table.
Step 1: (Optional) Manage predefined columns
When you use a secondary index, if no predefined columns are configured or the existing predefined columns do not meet your requirements, you can add or delete predefined columns for the data table.
You can use the Tablestore SDK for Java or Tablestore SDK for Go to manage predefined columns. This section uses the Java SDK as an example.
Add a predefined column
Parameters
|
Parameter |
Type |
Description |
|
tableName (required) |
String |
The name of the data table. |
|
definedColumns (required) |
List |
The predefined column information. Each predefined column contains the following parameters:
|
Sample code
The following sample adds a predefined column named name of the STRING type to the example_table data table.
AddDefinedColumnRequest request = new AddDefinedColumnRequest();
request.setTableName("example_table");
request.addDefinedColumn("name", DefinedColumnType.STRING);
client.addDefinedColumn(request);Delete a predefined column
Parameters
|
Parameter |
Type |
Description |
|
tableName (required) |
String |
The name of the data table. |
|
definedColumns (required) |
List<String> |
The names of the predefined columns to delete. |
Sample code
The following sample deletes the predefined column named name from the example_table data table.
DeleteDefinedColumnRequest request = new DeleteDefinedColumnRequest();
request.setTableName("example_table");
request.addDefinedColumn("name");
client.deleteDefinedColumn(request);Step 2: Create a secondary index
Call the CreateIndex operation to create an index table for an existing data table to accelerate data queries. Secondary indexes are classified into global and local secondary indexes. You can create a global or local secondary index based on your business requirements.
You can also create one or more index tables at the same time as the data table by calling the CreateTable operation. For more information, see Create a data table.
The examples below use Tablestore SDK for Java. The following SDKs are also supported: Go, Python, Node.js, .NET, and PHP.
Create a global secondary index
The following sample creates a global secondary index named example_global_index for the example_table data table. The index table contains existing data.
Set IncludeBaseData to true to include existing data table rows in the index. The time required to backfill existing data varies based on data volume.
IndexMeta indexMeta = new IndexMeta("example_global_index");
indexMeta.addPrimaryKeyColumn("category");
indexMeta.addDefinedColumn("status");
CreateIndexRequest request =
new CreateIndexRequest("example_table", indexMeta, true);
client.createIndex(request);Create a local secondary index
When you create a local secondary index, use the first primary key column of the data table as the first index primary key column, and set the index type and update mode to IT_LOCAL_INDEX and IUM_SYNC_INDEX respectively.
Set IncludeBaseData to true to include existing data table rows in the index. The time required to backfill existing data varies based on data volume.
IndexMeta indexMeta = new IndexMeta("example_local_index");
indexMeta.addPrimaryKeyColumn("user_id");
indexMeta.addPrimaryKeyColumn("category");
indexMeta.addDefinedColumn("status");
indexMeta.setIndexType(IndexType.IT_LOCAL_INDEX);
indexMeta.setIndexUpdateMode(IndexUpdateMode.IUM_SYNC_INDEX);
CreateIndexRequest request =
new CreateIndexRequest("example_table", indexMeta, true);
client.createIndex(request);Step 3: Read data from the index table
You can read a single row or a range of data from an index table. The read method depends on whether the attribute columns to return are in the index table:
-
Attribute columns are in the index table: directly read the index table to obtain the data.
-
Attribute columns are not in the index table: query the data table by yourself to obtain the data.
The examples below use Tablestore SDK for Java. The following SDKs are also supported: Go, Python, Node.js, .NET, and PHP.
Read a single row
Read a single row of data. The following sample reads a single row from the global secondary index example_global_index based on the complete primary key and returns only the status attribute column.
PrimaryKey primaryKey = PrimaryKeyBuilder.createPrimaryKeyBuilder()
.addPrimaryKeyColumn("category", PrimaryKeyValue.fromString("books"))
.addPrimaryKeyColumn("user_id", PrimaryKeyValue.fromString("user-1"))
.addPrimaryKeyColumn("order_id", PrimaryKeyValue.fromLong(101L))
.build();
SingleRowQueryCriteria criteria =
new SingleRowQueryCriteria("example_global_index", primaryKey);
criteria.addColumnsToGet("status");
criteria.setMaxVersions(1);
GetRowResponse response = client.getRow(new GetRowRequest(criteria));
System.out.println(response.getRow());Read a range of rows
Read data within a specified primary key range. Set a start and end primary key on the index table and call getRange in a loop until nextStartPrimaryKey is null.
Use a global secondary index
When the attribute columns to return are in the index table, you can directly read the index table to obtain the data.
private static void scanFromIndex(SyncClient client) {
RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("<INDEX_NAME>");
PrimaryKeyBuilder startPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
startPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MIN);
startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MIN);
startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MIN);
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startPrimaryKeyBuilder.build());
PrimaryKeyBuilder endPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
endPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MAX);
endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MAX);
endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MAX);
rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endPrimaryKeyBuilder.build());
rangeRowQueryCriteria.setMaxVersions(1);
System.out.println("Scan result of the index table:");
while (true) {
GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));
for (Row row : getRangeResponse.getRows()) {
System.out.println(row);
}
if (getRangeResponse.getNextStartPrimaryKey() != null) {
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(getRangeResponse.getNextStartPrimaryKey());
} else {
break;
}
}
}When the attribute columns to return are not in the index table, query the data table by yourself to obtain the data.
private static void scanFromIndex(SyncClient client) {
RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("<INDEX_NAME>");
PrimaryKeyBuilder startPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
startPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MIN);
startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MIN);
startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MIN);
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startPrimaryKeyBuilder.build());
PrimaryKeyBuilder endPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
endPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MAX);
endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MAX);
endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MAX);
rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endPrimaryKeyBuilder.build());
rangeRowQueryCriteria.setMaxVersions(1);
while (true) {
GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));
for (Row row : getRangeResponse.getRows()) {
// Extract the data table primary key from the index row.
PrimaryKey curIndexPrimaryKey = row.getPrimaryKey();
PrimaryKeyColumn pk1 = curIndexPrimaryKey.getPrimaryKeyColumn(PRIMARY_KEY_NAME_1);
PrimaryKeyColumn pk2 = curIndexPrimaryKey.getPrimaryKeyColumn(PRIMARY_KEY_NAME_2);
PrimaryKeyBuilder mainTablePKBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
mainTablePKBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, pk1.getValue());
mainTablePKBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, pk2.getValue());
PrimaryKey mainTablePK = mainTablePKBuilder.build();
// Fetch the required columns from the data table.
SingleRowQueryCriteria criteria = new SingleRowQueryCriteria("<TABLE_NAME>", mainTablePK);
criteria.addColumnsToGet(DEFINED_COL_NAME_3);
criteria.setMaxVersions(1);
GetRowResponse getRowResponse = client.getRow(new GetRowRequest(criteria));
Row mainTableRow = getRowResponse.getRow();
System.out.println(row);
}
if (getRangeResponse.getNextStartPrimaryKey() != null) {
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(getRangeResponse.getNextStartPrimaryKey());
} else {
break;
}
}
}Use a local secondary index
When the attribute columns to return are in the index table, you can directly read the index table to obtain the data.
private static void scanFromIndex(SyncClient client) {
RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("INDEX_NAME");
PrimaryKeyBuilder startPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MIN);
startPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MIN);
startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MIN);
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startPrimaryKeyBuilder.build());
PrimaryKeyBuilder endPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MAX);
endPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MAX);
endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MAX);
rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endPrimaryKeyBuilder.build());
rangeRowQueryCriteria.setMaxVersions(1);
System.out.println("Scan result of the index table:");
while (true) {
GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));
for (Row row : getRangeResponse.getRows()) {
System.out.println(row);
}
if (getRangeResponse.getNextStartPrimaryKey() != null) {
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(getRangeResponse.getNextStartPrimaryKey());
} else {
break;
}
}
}When the attribute columns to return are not in the index table, query the data table by yourself to obtain the data.
private static void scanFromIndex(SyncClient client) {
RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("<INDEX_NAME>");
PrimaryKeyBuilder startPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MIN);
startPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MIN);
startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MIN);
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startPrimaryKeyBuilder.build());
PrimaryKeyBuilder endPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MAX);
endPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MAX);
endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MAX);
rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endPrimaryKeyBuilder.build());
rangeRowQueryCriteria.setMaxVersions(1);
while (true) {
GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));
for (Row row : getRangeResponse.getRows()) {
// Extract the data table primary key from the index row.
PrimaryKey curIndexPrimaryKey = row.getPrimaryKey();
PrimaryKeyColumn pk1 = curIndexPrimaryKey.getPrimaryKeyColumn(PRIMARY_KEY_NAME_1);
PrimaryKeyColumn pk2 = curIndexPrimaryKey.getPrimaryKeyColumn(PRIMARY_KEY_NAME_2);
PrimaryKeyBuilder mainTablePKBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
mainTablePKBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, pk1.getValue());
mainTablePKBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, pk2.getValue());
PrimaryKey mainTablePK = mainTablePKBuilder.build();
// Fetch the required columns from the data table.
SingleRowQueryCriteria criteria = new SingleRowQueryCriteria("TABLE_NAME", mainTablePK);
criteria.addColumnsToGet(DEFINED_COL_NAME3);
criteria.setMaxVersions(1);
GetRowResponse getRowResponse = client.getRow(new GetRowRequest(criteria));
Row mainTableRow = getRowResponse.getRow();
System.out.println(row);
}
if (getRangeResponse.getNextStartPrimaryKey() != null) {
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(getRangeResponse.getNextStartPrimaryKey());
} else {
break;
}
}
}Appendix: Delete an index table
Delete an index table when it is no longer needed.
The examples below use Tablestore SDK for Java. The following SDKs are also supported: Go, Python, Node.js, .NET, and PHP.
The following sample deletes the secondary index example_index from the data table example_table.
DeleteIndexRequest request =
new DeleteIndexRequest("example_table", "example_index");
DeleteIndexResponse response = client.deleteIndex(request);
System.out.println(response.getRequestId());FAQ
References
-
You can also use secondary indexes in the console or by using the command-line tool. For more information, see Use secondary indexes in the console and Use secondary indexes by using the CLI.
-
If you need more flexible and diverse data query acceleration methods, such as multi-condition combination queries, full-text search, prefix queries, and fuzzy queries, you can use search indexes. For more information, see Search index overview.