Use a secondary index to accelerate data queries by using Tablestore SDKs - 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 want 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.

Prerequisites

A data table for which the Max Versions parameter is set to 1 is created. One of the following conditions is met by the TTL parameter of the data table:

The TTL parameter of the data table is set to -1, which means that data in the data table never expires.
If the TTL parameter of the data table is not set to -1, the Allow Updates parameter is set to No.

Note

The TTL of the secondary index is the same as the TTL of the data table.

Step 1: (Optional) Manage predefined columns

If you create a secondary index for a table and the table does not contain predefined columns or the existing predefined columns do not meet your business requirements, you can add or remove predefined columns. You can use Tablestore SDK for Java or Tablestore SDK for Go to manage predefined columns. In this example, Tablestore SDK for Java is used to describe how to use predefined columns.

Add a predefined column

The following sample code provides an example on how to remove the definedColumnName01 and definedColumnName02 predefined columns from a table:

public static void deleteDefinedColumnRequest(SyncClient client){
    DeleteDefinedColumnRequest deleteDefinedColumnRequest = new DeleteDefinedColumnRequest();
    // Specify the name of the table. 
    deleteDefinedColumnRequest.setTableName("<TABLE_NAME>");
    // Remove predefined columns from the table. 
    deleteDefinedColumnRequest.addDefinedColumn("definedColumnName01");
    deleteDefinedColumnRequest.addDefinedColumn("definedColumnName02");
    client.deleteDefinedColumn(deleteDefinedColumnRequest);
}

Delete a predefined column

You can remove predefined columns that you no longer require from a data table.

The following sample code provides an example on how to remove the definedColumnName01 and definedColumnName02 predefined columns from a table:

public static void deleteDefinedColumnRequest(SyncClient client){
    DeleteDefinedColumnRequest deleteDefinedColumnRequest = new DeleteDefinedColumnRequest();
    // Specify the name of the table. 
    deleteDefinedColumnRequest.setTableName("<TABLE_NAME>");
    // Remove predefined columns from the table. 
    deleteDefinedColumnRequest.addDefinedColumn("definedColumnName01");
    deleteDefinedColumnRequest.addDefinedColumn("definedColumnName02");
    client.deleteDefinedColumn(deleteDefinedColumnRequest);
}

Step 2: Create a secondary index

You can 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.

Note

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.

You can use Tablestore SDK for Java, Tablestore SDK for Go, Tablestore SDK for Python, Tablestore SDK for Node.js, Tablestore SDK for .NET, or Tablestore SDK for PHP to create a secondary index. In this example, Tablestore SDK for Java is used to describe how to create a secondary index.

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); 
}

Step 3: Read data from the index table

When you read a single row of data or data whose primary key values are within a specific range in an index table, if the attribute columns that you want to return are contained in the index table, you can read the index table to obtain the required data. Otherwise, you must read the data table to obtain the required data.

You can use Tablestore SDK for Java, Tablestore SDK for Go, Tablestore SDK for Python, Tablestore SDK for Node.js, Tablestore SDK for .NET, or Tablestore SDK for PHP to read data from a secondary index. In this example, Tablestore SDK for Java is used to describe how to read data from a secondary index.

Read a single row of data

The following sample code provides an example on how to read a row of data:

The following sample code provides an example on how to read specific attribute columns of a row from an index table:

private static void getRowFromIndex(SyncClient client) {
    // Construct the primary key. If you want to read data from 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. 
    PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    primaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.fromString("def1"));
    primaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.fromLong(100));
    primaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.fromString("pri1"));
    PrimaryKey primaryKey = primaryKeyBuilder.build();

    // Specify the name of the index table from which you want to read a row of data and the primary key of the row. 
    SingleRowQueryCriteria criteria = new SingleRowQueryCriteria("<INDEX_NAME>", primaryKey);
    // Set the MaxVersions parameter to 1 to read the latest version of data. 
    criteria.setMaxVersions(1);
    GetRowResponse getRowResponse = client.getRow(new GetRowRequest(criteria));
    Row row = getRowResponse.getRow();
    // If the row that you want to read does exist, null is returned. 
    System.out.println("Read complete. Result:");
    System.out.println(row);

    // Specify the attribute columns that you want to read. 
    criteria.addColumnsToGet("Col0");
    getRowResponse = client.getRow(new GetRowRequest(criteria));
    row = getRowResponse.getRow();
    
    System.out.println("Read complete. Result:");
    System.out.println(row);
}

Read data whose primary key values are within a specific range

You can read data whose primary key values are within a specific range.

Use global secondary indexes

If an index table contains the attribute columns that you want to return, you can query the required data from the index table.

private static void scanFromIndex(SyncClient client) {
    // Specify the name of the index table. 
    RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("<INDEX_NAME>"); 

    // Specify the start primary key of the range that you want to query. 
    PrimaryKeyBuilder startPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    // Set the value of the index column to an infinitely small value. 
    startPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MIN); 
    // Set the value of a primary key column of the data table that is not specified as an index column in the index table to an infinitely small value. 
    startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MIN); 
    // Set the value of a primary key column of the data table that is not specified as an index column in the index table to an infinitely small value. 
    startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MIN); 
    rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startPrimaryKeyBuilder.build());

    // Specify the end primary key of the range that you want to query. 
    PrimaryKeyBuilder endPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    // Set the value of the index column to an infinitely great value. 
    endPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MAX); 
    // Set the value of a primary key column of the data table that is not specified as an index column in the index table to an infinitely great value. 
    endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MAX); 
    // Set the value of a primary key column of the data table that is not specified as an index column in the index table to an infinitely great value. 
    endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MAX); 
    rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endPrimaryKeyBuilder.build());

    rangeRowQueryCriteria.setMaxVersions(1);

    System.out.println("Results returned from the index table:");
    while (true) {
        GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));
        for (Row row : getRangeResponse.getRows()) {
            System.out.println(row);
        }

        // If the nextStartPrimaryKey parameter is not null in the response, continue to read data. 
        if (getRangeResponse.getNextStartPrimaryKey() != null) {
            rangeRowQueryCriteria.setInclusiveStartPrimaryKey(getRangeResponse.getNextStartPrimaryKey());
        } else {
            break;
        }
    }
}

If the index table does not contain the attribute columns that you want to return, you need to query the required data from the data table.

private static void scanFromIndex(SyncClient client) {
    // Specify the name of the index table. 
    RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("<INDEX_NAME>"); 

    // Specify the start primary key of the range that you want to query. 
    PrimaryKeyBuilder startPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    // Set the value of the index column to an infinitely small value. 
    startPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MIN); 
    // Set the value of a primary key column of the data table that is not specified as an index column in the index table to an infinitely small value. 
    startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MIN); 
    // Set the value of a primary key column of the data table that is not specified as an index column in the index table to an infinitely small value. 
    startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MIN); 
    rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startPrimaryKeyBuilder.build());

    // Specify the end primary key of the range that you want to query. 
    PrimaryKeyBuilder endPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    // Set the value of the index column to an infinitely great value. 
    endPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MAX); 
    // Set the value of a primary key column of the data table that is not specified as an index column in the index table to an infinitely great value. 
    endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MAX); 
    // Set the value of a primary key column of the data table that is not specified as an index column in the index table to an infinitely great value. 
    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()) {
            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());
            // Specify the primary key of the data table based on the primary key of the index table. 
            PrimaryKey mainTablePK = mainTablePKBuilder.build(); 

            // Query data from the data table. 
            SingleRowQueryCriteria criteria = new SingleRowQueryCriteria("<TABLE_NAME>", mainTablePK);
            // Specify the attribute columns that you want to return. In this example, the DEFINED_COL_NAME_3 column is returned. 
            criteria.addColumnsToGet(DEFINED_COL_NAME_3); 
            // Set the MaxVersions parameter to 1 to read the latest version of data. 
            criteria.setMaxVersions(1);
            GetRowResponse getRowResponse = client.getRow(new GetRowRequest(criteria));
            Row mainTableRow = getRowResponse.getRow();
            System.out.println(row); 
        }

        // If the nextStartPrimaryKey parameter is not null in the response, continue to read data. 
        if (getRangeResponse.getNextStartPrimaryKey() != null) {
            rangeRowQueryCriteria.setInclusiveStartPrimaryKey(getRangeResponse.getNextStartPrimaryKey());
        } else {
            break;
        }
    }
}

Use local secondary indexes

If an index table contains the attribute columns that you want to return, you can query the required data from the index table.

private static void scanFromIndex(SyncClient client) {
    // Specify the name of the index table. 
    RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("INDEX_NAME"); 

    // Specify the start primary key of the range that you want to query. 
    PrimaryKeyBuilder startPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    // Set the value of an index column to an infinitely small value. 
    startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MIN); 
    // Set the value of an index column to an infinitely small value. 
    startPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MIN); 
    // Set the value of the primary key column of the data table that is not specified as an index column in the index table to an infinitely small value. 
    startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MIN); 
    rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startPrimaryKeyBuilder.build());

    // Specify the end primary key of the range that you want to query. 
    PrimaryKeyBuilder endPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    // Set the value of an index column to an infinitely great value. 
    endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MAX);
    // Set the value of an index column to an infinitely great value. 
    endPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MAX); 
    // Set the value of the primary key column of the data table that is not specified as an index column in the index table to an infinitely great value. 
    endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MAX); 
    rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endPrimaryKeyBuilder.build());

    rangeRowQueryCriteria.setMaxVersions(1);

    System.out.println("Results returned from the index table:");
    while (true) {
        GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));
        for (Row row : getRangeResponse.getRows()) {
            System.out.println(row);
        }

        // If the nextStartPrimaryKey parameter is not null in the response, continue to read data. 
        if (getRangeResponse.getNextStartPrimaryKey() != null) {
            rangeRowQueryCriteria.setInclusiveStartPrimaryKey(getRangeResponse.getNextStartPrimaryKey());
        } else {
            break;
        }
    }
}

If the index table does not contain the attribute columns that you want to return, you need to query the required data from the data table.

private static void scanFromIndex(SyncClient client) {
    // Specify the name of the index table. 
    RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("<INDEX_NAME>"); 

    // Specify the start primary key of the range that you want to query. 
    PrimaryKeyBuilder startPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    // Set the value of an index column to an infinitely small value. 
    startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MIN); 
    // Set the value of an index column to an infinitely small value. 
    startPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MIN); 
    // Set the value of the primary key column of the data table that is not specified as an index column in the index table to an infinitely small value. 
    startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MIN); 
    rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startPrimaryKeyBuilder.build());

    // Specify the end primary key of the range that you want to query. 
    PrimaryKeyBuilder endPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    // Set the value of an index column to an infinitely great value. 
    endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MAX); 
    // Set the value of an index column to an infinitely great value. 
    endPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MAX); 
    // Set the value of the primary key column of the data table that is not specified as an index column in the index table to an infinitely great value. 
    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()) {
            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());
            // Specify the primary key of the data table based on the primary key of the index table. 
            PrimaryKey mainTablePK = mainTablePKBuilder.build(); 

            // Query data from the data table. 
            SingleRowQueryCriteria criteria = new SingleRowQueryCriteria("TABLE_NAME", mainTablePK);
            // Specify the attribute columns that you want to return from the data table. In this example, the DEFINED_COL_NAME3 column is returned. 
            criteria.addColumnsToGet(DEFINED_COL_NAME3); 
            // Set the MaxVersions parameter to 1 to read the latest version of data. 
            criteria.setMaxVersions(1);
            GetRowResponse getRowResponse = client.getRow(new GetRowRequest(criteria));
            Row mainTableRow = getRowResponse.getRow();
            System.out.println(row); 
        }

        // If the nextStartPrimaryKey parameter is not null in the response, continue to read data. 
        if (getRangeResponse.getNextStartPrimaryKey() != null) {
            rangeRowQueryCriteria.setInclusiveStartPrimaryKey(getRangeResponse.getNextStartPrimaryKey());
        } else {
            break;
        }
    }
}

Appendix: Delete an index table

This section describes how to delete index tables that you no longer require.

You can use Tablestore SDK for Java, Tablestore SDK for Go, Tablestore SDK for Python, Tablestore SDK for Node.js, Tablestore SDK for .NET, or Tablestore SDK for PHP to delete a secondary index. In this example, Tablestore SDK for Java is used to describe how to delete a secondary index.

The following sample code provides an example on how to delete a secondary index.

private static void deleteIndex(SyncClient client) {
    // Specify the names of the data table and the secondary index. 
    DeleteIndexRequest request = new DeleteIndexRequest("<TABLE_NAME>", "<INDEX_NAME>"); 
    // Delete the secondary index. 
    client.deleteIndex(request); 
}

FAQ

References

You can use secondary indexes in the Tablestore console or the Tablestore CLI. For more information, see Use secondary indexes in the Tablestore console and Secondary index.
If you want to query data in a more efficient and flexible manner, you can use the search index feature. The search index feature provides multiple query methods, including Boolean query, full-text search, prefix query, and fuzzy query. For more information, see Overview.