Use a search index to retrieve a Lastpoint index - Tablestore

Why use a search index on a Lastpoint index

A Lastpoint index stores only the most recent data point for each time series. For example, in an Internet of Vehicles (IoV) scenario, a Lastpoint index contains one row per vehicle — the latest reported GPS coordinates, speed, status, and mileage.

The following table shows sample Lastpoint index data for a fleet of vehicles:

_#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

Without a search index, you can only retrieve a row by its primary key (_#h). To answer questions such as "which vehicles are currently idle with more than 300 km of remaining mileage," you would need to scan all rows and filter in the application layer — which is slow and expensive at scale.

A search index built on a Lastpoint index supports inverted indexes and column stores, enabling:

Non-primary key queries — filter by any attribute column, such as vehicle status or remaining mileage
Boolean, fuzzy, and full-text search queries — combine conditions with AND, OR, and NOT operators
Geo queries — find records within a geographic area
k-nearest neighbor (KNN) vector queries — similarity search on vector fields
Aggregations — compute max, min, count, and group-by statistics across the latest data points

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

Before you begin, ensure that:

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

Manage search indexes and query data

Manage search indexes and query data 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.
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.

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.

Keep the default index name or enter a name based on your requirements.
Select the schema generation method.
Important
The Field Name and Field Type values must match the field names and types in the Lastpoint index. For the mappings between field types in Lastpoint indexes and search indexes, see Data types.
- If you set Schema Generation Type to Manual, enter the field name, select the field type, and specify whether to enable Array.
- If you set Schema Generation Type to Auto Generate, the system uses the fields in the Lastpoint index as index fields. Select the field type and specify whether to enable Array based on your requirements.
Note
Virtual columns let you map table columns to one or more virtual columns in a search index without modifying the table schema. Use virtual columns to accelerate queries or apply different tokenization methods. For more information, see Virtual columns.

To configure time to live (TTL), routing key, and pre-sorting settings, turn on Advanced Settings. The following table describes the parameters.

Parameter	Description
Routing key	The routing fields used to control data distribution. Select one or more primary key columns. Records with the same routing field values are placed in the same data partition. In most cases, one routing field is sufficient. If you specify multiple fields, their values are concatenated to form the partition key.
Time to live	The retention period of data in the search index. Unit: seconds. Default value: -1 (data never expires). The TTL must be at least 86,400 seconds (one day) or -1. Tablestore automatically deletes data that exceeds the TTL.
Pre-sorting	The default sort order for returned data. Valid values: Default (sorted by primary key) and Custom (sorted by a field you specify). Important Search indexes that contain Nested fields do not support pre-sorting.

Click OK.

Query data 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. To return specific attribute columns, turn off All Columns for the Columns to Return parameter and enter the attribute columns you want. Separate multiple columns with commas (,).
  
  Note
  All primary key columns of the Lastpoint index are always returned.
2. Select a logical operator: And, Or, or Not.
  
  And returns rows that meet all conditions. Or with a single condition returns rows that meet that condition; with multiple conditions, Or returns rows that meet at least one. Not returns rows that do not meet the specified condition.
3. Select an index field, click Add, and configure the Query Type and Value parameters.
  
  Note
  To specify multiple values for a field, click Add again for the same field and set a different Value.
4. Sorting is disabled by default. To sort results by a specific field, turn on Sort, add the sort fields, and configure the sort order.
5. Statistics collection is disabled by default. To collect statistics on a field, turn on Collect Statistics, add the fields, and configure the statistics settings.
6. Click Yes.
  
  The query results and statistics 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 search index details

To view basic information, metering information, routing key, index fields, and pre-sorting details of a search index:

On the Manage Time Series Table page, click the Basic Information tab.
In the LastPoint Index section, click Index Details in the Search Index column of the Lastpoint index.
In the Index Details panel, review the index information.

Delete a search index

Delete a search index when it is no longer needed.

On the Manage Time Series Table page, click the Basic Information tab.
In the LastPoint Index section, click Delete in the Search Index column of the Lastpoint index.
In the Delete Search Index dialog box, confirm the index information and click OK.

SDK integration

Use the following Tablestore SDKs to work with search indexes on Lastpoint indexes:

Billing

Creating a Lastpoint index is free. Storage and read operations on Lastpoint index data are billed. For more information, see Billable items of the TimeSeries model.
Querying a Lastpoint index through a search index incurs search index fees. For more information, see Billable items of search indexes.

FAQ

Appendix: Sample scenario

In the Internet of Vehicles (IoV) scenario, vehicle sensors report time series data to the cloud. Applications store, query, and analyze this data for use cases such as vehicle status monitoring, fleet positioning, and trajectory display.

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

Note

In this example, _m_name, _data_source, and _tags are time series identifiers that specify the measurement name, data source, and tag information, respectively. _time is the data reporting timestamp. gps, speed, status, total_mileage, and remaining_mileage are time series data fields specifying GPS coordinates, speed, vehicle status, total mileage, and remaining 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 the latest data point for each time series to the Lastpoint index. The resulting Lastpoint index data is shown in the table in the preceding section.

Given the following query requirements, create a search index on the Lastpoint index to use its full query capabilities.

Find vehicles that are currently idle with more than 300 km of remaining mileage.
Find vehicles within a specified geographic area.
Find the maximum speed among vehicles that are currently active.

Create a search index with the following fields: gps, speed, status, and remaining_mileage.
Use the search index to query data. The following table maps each requirement to its implementation.

Query requirement	Implementation
Find vehicles that are currently idle with more than 300 km of remaining mileage	Use a term query on `status` (value: "Idle") combined with a range query on `remaining_mileage` (value > 300).
Find vehicles within a specified geographic area	Use a geo query on the `gps` field to return rows within the target coordinates.
Find the maximum speed among vehicles that are currently active	Use a match query on `status` (value: "Active") with a max aggregation on the `speed` field.