Use a search index to retrieve a Lastpoint index - Tablestore

Search indexes accelerate the retrieval of Lastpoint indexes and provide multi-dimensional query and statistical analysis capabilities. This topic describes how to use a search index to retrieve a Lastpoint index.

Background information

Search indexes support multi-dimensional data queries and statistical analysis in big data scenarios based on inverted indexes and column stores. If you want to perform a complex query, such as query based on non-primary key columns, Boolean query, fuzzy query, full-text search, or k-nearest neighbor (KNN) vector query on data in a Lastpoint index, or if you want to analyze data in a Lastpoint index, such as obtaining the maximum and minimum values, collect statistics about the number of rows, or group data, you can specify these attributes as fields in a search index that is created for the Lastpoint index and then use the search index to query and analyze data in the Lastpoint index.

Note

This topic provides a sample scenario in which a search index is used to retrieve a Lastpoint index. For more information, see Appendix: Sample scenario. If you want to learn more about the features and capabilities of search indexes, see Search index.

Prerequisites

A Lastpoint index is created for a time series table. For more information, see Create a Lastpoint index.

Procedure

You can create, delete, and view search indexes, and use search indexes to retrieve Lastpoint indexes in the LastPoint Index section of the Manage Time Series Table page.

Create a search index

Go to the Instance Management page.
1. Log on to the Tablestore console.
2. In the top navigation bar, select a resource group and a region.
3. On the Overview page, click the instance name or click Manage Instance in the Actions column of the instance.
Go to the Manage Time Series Table page.
1. On the Instance Management page, click the Time Series Tables tab.
2. On the Time Series Tables tab, click the time series table name or click Manage Data in the Actions column of the time series table.

Create a search index for the Lastpoint index.

In the LastPoint Index section of the Basic Information tab, click Create Search Index in the Search Index column of the Lastpoint index.

In the Create Search Index dialog box, configure the parameters for the search index.

Retain the default index name that is generated by the system, or enter an index name based on your business requirements.
Select the method that you want to use to generate a schema for the search index.
Important
The values of the Field Name and Field Type parameters must match the field names and field types in the Lastpoint index. For the mappings between field types in Lastpoint indexes and search indexes, see Data types.
- If you set the Schema Generation Type parameter to Manual, manually enter the field name, select the field type, and specify whether to enable Array.
- If you set the Schema Generation Type parameter to Auto Generate, the system automatically uses the fields in the Lastpoint index as index fields. You can select the field type and specify whether to enable Array based on your requirements.
Note
Virtual columns allow you to map columns in a table to one or more virtual columns in a search index without modifying the table schema. Virtual columns can be used to accelerate queries or specify different tokenization methods. For more information about the features and usage of virtual columns, see Virtual columns.

If you want to specify the time to live (TTL), routing key, and presorting settings for the search index, turn on Advanced Settings and configure the parameters. The following table describes the parameters.

Parameter	Description
Routing Key	The custom routing fields. You can select one or more primary key columns as the routing fields. In most cases, you need to specify only one routing field. If you specify multiple routing fields, the system concatenates the values of the routing fields into one value as the partition key. Tablestore calculates the distribution of the index data based on the values of the routing fields. The records with the same values of the routing fields are distributed to the same data partition.
Time to Live	The retention period of data in the search index. Unit: seconds. The default value is -1, which specifies that the data never expires. The TTL must be at least 86,400 seconds (one day) or -1. A value of -1 specifies that the data never expires. If you want the system to automatically clear the historical data in the search index, specify a value that is greater than or equal to 86400. If the retention period exceeds the TTL, Tablestore automatically deletes expired data.
Pre-sorting	The default order in which the data is returned. Valid values: Default and Custom. A value of Default specifies that the data is sorted based on the primary key. A value of Custom specifies that the data is sorted based on the field that you specify. Configure the Pre-sorting parameter based on your business requirements. Important Search indexes that contain Nested fields do not support index presorting.

Click OK.

Retrieve data by using a search index

In the LastPoint Index section of the Basic Information tab, click Manage Data in the Search Index column of the Lastpoint index.
In the Search dialog box, query the data.
1. By default, the system returns all columns. If you want the system to return specific attribute columns, turn off All Columns for the Columns to Return parameter and enter the attribute columns that you want to return. Separate multiple attribute columns with commas (,).
  Note
  By default, the system returns all primary key columns of the Lastpoint index.
2. Select the And, Or, or Not logical operator based on your business requirements.
  If you select And as the logical operator, the system returns data that meets the specified conditions. If you select Or as the logical operator and configure a single condition, the system returns data that meets the specified condition. If you select Or as the logical operator and configure multiple conditions, the system returns data that meets at least one of the conditions. If you select Not as the logical operator, the system returns data that does not meet the specified conditions.
3. Select an index field, click Add, and then configure the Query Type and Value parameters for the index field.
  Note
  If you want to specify multiple values of an index field, click Add each time you add the index field and configure the Value parameter to specify a different value.
4. By default, the system disables the sorting feature. If you want to sort the returned results based on a specific field, turn on the Sort switch, and then add the fields based on which you want to sort data and configure the sorting method based on your requirements.
5. By default, the system disables the statistics feature. If you want to collect statistics on a specific field, turn on the Collect Statistics switch, and then add the fields on which you want to collect statistics and configure the statistics information based on your business requirements.
6. Click Yes.
  The data that meets the query conditions and the statistics results are displayed in the LastPoint Index section of the Basic Information tab.

Manage search indexes

The following table describes the operations you can perform on search indexes.

Operation

Description

View the details of a search index

To view the basic information, metering information, routing key, index fields, and presorting details of a search index, perform the following steps:

On the Manage Time Series Table page, click the Basic Information tab.
In the LastPoint Index section of the Basic Information tab, click Index Details in the Search Index column of the Lastpoint index.
In the Index Details message, you can view the specific information.

Delete a search index

You can delete a search index if you no longer need it.

On the Manage Time Series Table page, click the Basic Information tab.
In the LastPoint Index section of the Basic Information tab, click Delete in the Search Index column of the Lastpoint index.
In the Delete Search Index message, confirm that the information of the index that you want to delete is correct, and click OK.

Development integration

You can use Tablestore SDKs for the following programming languages to use search indexes that are created for Lastpoint indexes:

Billing

You are not charged for creating a Lastpoint index. You are charged for the storage usage of data in a Lastpoint index and when you read data from a Lastpoint index. For more information, see Billable items of the TimeSeries model.
If you retrieve data in a Lastpoint index by using a search index, search index-related fees will be incurred according to the billing rules of the search index feature. For more information, see Billable items of search indexes.

FAQ

Appendix: Sample scenario

In the Internet of Vehicles (IoV) scenario, sensors report the time series data of vehicles to the cloud. Users can store, query, and analyze the time series data to meet business requirements, such as vehicle status report, vehicle positioning, traffic management, and screen mirroring of vehicle trajectory.

The following table shows the sample data in a time series table.

Note

In this example, the _m_name, _data_source, and _tags field are time series identifiers, which specify the measurement name, data source, and tag information of the time series, respectively. The _time field specifies the time when data is reported. The gps, speed, status, total_mileage, and remaining_mileage fields are time series data in the time series, which specify the vehicle GPS coordinates, vehicle speed, vehicle status, total vehicle mileage, and remaining vehicle mileage, respectively.

_m_name	_data_source	_tags	_time	gps	speed	status	total_mileage	remaining_mileage
Platform A	sensor1	["region=hangzhou","car_model=sedan","number_plate=ZheA D7512*","color=white"]	1730422800000000	30.245853,120.178564	0	Idle	20000	450
Platform A	sensor1	["region=hangzhou","car_model=sedan","number_plate=ZheA D7512*","color=white"]	1730423400000000	30.245853,120.178564	0	Idle	20000	450
Platform A	sensor2	["region=hangzhou","car_model=suv","number_plate=ZheC 72B2*","color=black"]	1730779200000000	30.245278,120.150269	50	Active	15000	300
Platform A	sensor2	["region=hangzhou","car_model=suv","number_plate=ZheC 72B2*","color=black"]	1730779800000000	30.245853,120.213654	80	Active	15050	250
Platform B	sensor3	["region=hangzhou","car_model=sedan","number_plate=ZheB 121*9","color=blue"]	1730862000000000	30.246013,120.124470	60	Active	18200	300
Platform B	sensor3	["region=hangzhou","car_model=sedan","number_plate=ZheB 121*9","color=blue"]	1730862600000000	30.246022,120.124460	0	Idle	18230	270

Tablestore automatically synchronizes data of the latest point in time in time series in the time series table to the Lastpoint index. The following table shows the sample data in the Lastpoint index.

_#h	_m_name	_data_source	_tags	_time	gps	speed	status	total_mileage	remaining_mileage
4c#PlatformA#07	Platform A	sensor1	["region=hangzhou","car_model=sedan","number_plate=ZheA D7512*","color=white"]	1730423400000000	30.245853,120.178564	0	Idle	20000	450
25#PlatformA#ae	Platform A	sensor2	["region=hangzhou","car_model=suv","number_plate=ZheC 72B2*","color=black"]	1730779800000000	30.245853,120.213654	80	Active	15050	250
b2#PlatformB#4b	Platform B	sensor3	["region=hangzhou","car_model=sedan","number_plate=ZheB 121*9","color=blue"]	1730862600000000	30.246022,120.124460	0	Idle	18230	270

Assuming you have the following query requirements, you can create a search index for the Lastpoint index and fully utilize the search index capabilities to query data.

Query vehicles that are currently idle and have a remaining mileage of more than 300 kilometers.
Query vehicles within the specified area.
Query the maximum speed of vehicles that are currently active.

Create a search index.
The search index fields include gps, speed, status, and remaining_mileage, which specify vehicle GPS coordinates, vehicle speed, vehicle status, and remaining vehicle mileage, respectively.
Use search index capabilities to query data. The following table describes the implementation methods.

Query requirement	Implementation method
Query vehicles that are currently idle and have a remaining mileage of more than 300 kilometers	Use a combination of term query and range query by using the search index to return the rows in which the value of the `status` field exactly matches "Idle" and the value of the `remaining_mileage` field is less than or equal to 300.
Query vehicles within the specified area	Use geo query by using the search index to return the rows in which the value of the `gps` field falls within the specified range
Query the maximum speed of vehicles that are currently active	Use match query and aggregation by using the search index to query the rows in which the value of the `status` field exactly matches "Active" and return the row in which the value of the `speed` field is the maximum among the rows.