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.
Secondary indexes are classified into global secondary indexes and local secondary indexes. For more information about the secondary index feature, see Overview.
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
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 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
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;
}
}
}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.