Getting started with Tablestore -- using global secondary index

Summary

The global secondary index has the same storage structure as the main table. Its index column can be the primary key column or predefined column of the main table, and its attribute column is the predefined column of the main table. The data written to the main table is asynchronously synchronized to the global secondary index after a millisecond delay, and can be found.
The same as the main table, such as GetRow, BatchGetRow, and BatchGetRow, query operations can all act on the secondary index. Unlike the main table, the secondary index does not support direct user writing, and only accepts data synchronization from the main table.

Specifying the prefix range of the primary key can realize the range scan (GetRange) of the primary table. The query range must be specified in accordance with the primary key range. If the query range cannot be represented in the form of a primary key prefix, you can use the secondary index to recombine the field order. Compared with the way of GetRange plus filter, the secondary index can greatly reduce the amount of scanned data and improve the query speed.
In this paper, an example is given to illustrate the essence of the second level index accelerated query, using posture and complete code——
In the background of call list query, each call of the user will be recorded in the following main table:

CellNumber and StartTime are the joint primary keys of the table, representing the calling number and the call time respectively.
CalledNumber, Duration and BaseStationNumber are the predefined columns of the table, representing the called number, call Duration and base station number respectively.
Imagine two query scenarios:

Query 1: call list of query number 234567

GetRange directly queries the main table - specifies that the minimum and maximum CellNumber values are 234567, the minimum StartTime value is 0 and the maximum StartTime value is int'u max.

Query 2: query the called bill with number 123456 and return the base station number

If you want to query the main table directly, you must scan the whole table and filter out the rows with the called number of 123456. The performance is poor and the cost is high. At this time, you can create a global secondary index table, as follows——

Global secondary index schema

Place the CalledNumber in the pk column of the secondary index table (the pk column of the primary table will be automatically appended to complete the pk column of the index table to ensure the uniqueness of the row). The base station number BaseStationNumber is required to be returned for query. This column can be set as the attribute column of the index table. Otherwise, the pk obtained from the index table must be used to query the main table. Therefore, the shema of the secondary index is as follows.

Among them, CalledNumber is the user specified index pk column; CellNumber and StartTime are the automatically completed index pk columns.

Query global secondary index

Specify the primary key prefix range CalledNumber from 123456 to 123456, CellNumber from int min to int max, StartTime from int min to int max, use GetRange to query the index table, and specify that the return column contains the base station number.

Let's look at the main code for creating, querying, and deleting a global secondary index.

Establish

There are two ways to create, the effect is equivalent.

Method 1: create a global secondary index table while creating a primary table

//Secondary IndexMeta
IndexMeta indexMeta = new IndexMeta(indexName);
indexMeta.addPrimaryKeyColumn(CALLED_NUMBER);       //Use the predefined column "called u number" of the primary table as the pk column of the secondary index table
//At this time, the remaining two pk columns of the secondary index table will be automatically supplemented: "cell number", "start time"
indexMeta.addDefinedColumn(BASE_STATION_NUMBER);    //Use the predefined column "base station number" of the main table as the attribute column of the secondary index table

//When creating a primary table, create an index table at the same time
TableMeta tableMeta = new TableMeta(tableName);

tableMeta.addPrimaryKeyColumn(CELL_NUMBER, PrimaryKeyType.INTEGER);
tableMeta.addPrimaryKeyColumn(START_TIME, PrimaryKeyType.INTEGER);

tableMeta.addDefinedColumn(new DefinedColumnSchema(CALLED_NUMBER, DefinedColumnType.INTEGER));
tableMeta.addDefinedColumn(new DefinedColumnSchema(BASE_STATION_NUMBER, DefinedColumnType.INTEGER));

TableOptions tableOptions = new TableOptions(-1, 1);
CreateTableRequest createTableRequest = new CreateTableRequest(tableMeta, tableOptions, Arrays.asList(indexMeta));
syncClient.createTable(createTableRequest);

Method 2: create a primary table first, and then add a secondary index table for the existing primary table

Create master table

TableMeta tableMeta = new TableMeta(tableName);

tableMeta.addPrimaryKeyColumn(CELL_NUMBER, PrimaryKeyType.INTEGER);
tableMeta.addPrimaryKeyColumn(START_TIME, PrimaryKeyType.INTEGER);

tableMeta.addDefinedColumn(new DefinedColumnSchema(CALLED_NUMBER, DefinedColumnType.INTEGER));
tableMeta.addDefinedColumn(new DefinedColumnSchema(BASE_STATION_NUMBER, DefinedColumnType.INTEGER));

// Set TTL to -1, never expire; Set maxVersions to 1, as one version is permitted
TableOptions tableOptions = new TableOptions(-1, 1);
CreateTableRequest createTableRequest = new CreateTableRequest(tableMeta, tableOptions);
syncClient.createTable(createTableRequest);

Create secondary index
includeBaseData can specify how indexes are synchronized.
true means that the stock data of the primary table before index creation will also be synchronized to the index table;
false means that only the incremental data of the primary table after index creation will be synchronized.

IndexMeta indexMeta = new IndexMeta(indexName);
indexMeta.addPrimaryKeyColumn(CALLED_NUMBER);       //Use the predefined column "called u number" of the primary table as the pk column of the secondary index table
                                                    //At this time, the remaining two pk columns of the secondary index table will be automatically supplemented: "cell number", "start time"
indexMeta.addDefinedColumn(BASE_STATION_NUMBER);    //Use the predefined column "base station number" of the main table as the attribute column of the secondary index table

//Global secondary index index creation request (if includeBaseData is true, the full data of the primary table will be synchronized first, and then the incremental data will be synchronized; if includeBaseData is false, only the incremental data will be synchronized)
CreateIndexRequest request = new CreateIndexRequest(tableName, indexMeta, true);

//Create a global secondary index
syncClient.createIndex(request);

query

Query the secondary index in the same way as query the primary table. In this example, the user specifies the secondary index pk column as CalledNumber, and the system will automatically supplement the remaining two primary key columns CellNumber and StartTime. When creating a secondary index, the predefined column BaseStationNumber is specified as the attribute column, so you can directly query the index to return the "base station number" information, without reverse querying the main table. However, if you want to return the call Duration column, you need to query the secondary index to return to the primary key column, and then the primary key column to reverse query the primary table.
In this example, you can query the called bill with the number 123456 and return the base station number as follows:

RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria(indexName);

long calledNumber = 123456L;

// Constructing primary keys
PrimaryKeyBuilder startPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
startPrimaryKeyBuilder.addPrimaryKeyColumn(CALLED_NUMBER, PrimaryKeyValue.fromLong(calledNumber));
startPrimaryKeyBuilder.addPrimaryKeyColumn(CELL_NUMBER, PrimaryKeyValue.INF_MIN);
startPrimaryKeyBuilder.addPrimaryKeyColumn(START_TIME, PrimaryKeyValue.INF_MIN);
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startPrimaryKeyBuilder.build());

// Constructing primary keys
PrimaryKeyBuilder endPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
endPrimaryKeyBuilder.addPrimaryKeyColumn(CALLED_NUMBER, PrimaryKeyValue.fromLong(calledNumber));
endPrimaryKeyBuilder.addPrimaryKeyColumn(CELL_NUMBER, PrimaryKeyValue.INF_MAX);
endPrimaryKeyBuilder.addPrimaryKeyColumn(START_TIME, PrimaryKeyValue.INF_MAX);
rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endPrimaryKeyBuilder.build());

rangeRowQueryCriteria.setMaxVersions(1);
rangeRowQueryCriteria.addColumnsToGet(BASE_STATION_NUMBER); //Query the secondary index and return the pk column and the attribute column "base station number"

System.out.println(String.format("number %d All called bills of: ", calledNumber));
while (true) {
    GetRangeResponse getRangeResponse = syncClient.getRange(new GetRangeRequest(rangeRowQueryCriteria));
    for (Row row : getRangeResponse.getRows()) {
        System.out.println(row);
    }

    // If nextStartPrimaryKey is not null, continue reading
    if (getRangeResponse.getNextStartPrimaryKey() != null) {
        rangeRowQueryCriteria.setInclusiveStartPrimaryKey(getRangeResponse.getNextStartPrimaryKey());
    } else {
        break;
    }
}

List secondary indexes

List the secondary indexes of a primary table.

DescribeTableRequest request = new DescribeTableRequest(tableName);
DescribeTableResponse response = syncClient.describeTable(request);
for (IndexMeta indexMeta : response.getIndexMeta()) {
    System.out.println(indexMeta.getIndexName());
}

Query secondary index meta information

You can query the meta information of the secondary index just as you query the main table meta.

DescribeTableResponse response = syncClient.describeTable(new DescribeTableRequest(indexName));
System.out.println(response.getTableMeta());

delete

Delete the secondary index.

DeleteIndexRequest request = new DeleteIndexRequest(tableName, indexName);
syncClient.deleteIndex(request);

Code

The complete code is here: https://github.com/aliyun/tablestore-examples/tree/master/basic/Java/GlobalIndexCRD

summary

The secondary index reorganizes the primary key columns and predefined columns of the primary table in another order. In a specific scenario, you can avoid scanning the primary table in a large range, which greatly improves the query efficiency. Please refer to the official website for more details https://help.aliyun.com/document_detail/91947.html.
If you have any questions or need better online support, welcome to join the pin group: "open communication group of form storage" (Group No.: 23307953). Free online expert service is provided in the group. Welcome to join us.

Posted by agmorgan on Tue, 03 Dec 2019 22:13:23 -0800

Programmer Group