Design a time series model for an Internet of Vehicles scenario-Tablestore(Tablestore)-阿里云帮助中心

In an Internet of Vehicles (IoV) scenario, time series data for vehicles primarily includes trajectory information, such as vehicle speed and GPS coordinates. This topic describes how to design the schemas for time series tables and mapping tables in an IoV scenario.

Time series table schema

Assume that the time series table for storing vehicle metadata is named vehicles_timeseriesdata. The following table describes the table schema.

Field name	Type	Description
_m_name	String	The measure name. It indicates the name of the physical quantity or metric that the time series data measures.
_data_source	String	The data source. It indicates the identity of the data source that generates the time series. This field can be empty.
_tags	String	The tags for the time series. You can customize multiple key-value pairs of the string type.
_time	Integer	The time when the data is reported.
vin_id	String	The vehicle identification number (VIN), which uniquely identifies a vehicle.
mfrs	String	The manufacturer.
model	String	The vehicle model.
speed	Double	The current speed.
gps	String	The GPS coordinates of the vehicle. The format is `latitude,longitude`. The latitude comes first, followed by the longitude. The latitude must be in the range of [-90, +90], and the longitude must be in the range of [-180, +180]. For example, `30.23,52.78`.
mileage	Double	The current mileage.
emission	Double	The emission value.

Mapping table schema

A time series model can be a single-value model or a multi-value model. This depends on whether each point in time in a time series corresponds to a single value or multiple values. For a single time series table, you can create three types of SQL mappings to query data.

Single-value model mapping table schema

After you create a time series table, the system automatically creates a mapping table in the single-value model in SQL for the time series table. The name of the mapping table in SQL is the same as the name of the time series table. You can use the mapping table in the single-value model to query time series data in the time series table.

The single-value model mapping table is named vehicles_timeseriesdata. The following table describes the table schema.

Column name	Type	Description
_m_name	VARCHAR	The metric name.
_data_source	VARCHAR	The data source.
_tags	VARCHAR	The tags of the time series. The value is an array that contains multiple tags in the ["tagKey1=tagValue1","tagKey2=tagValue2"] format. You can use the tag_value_at function to extract the value of a tag.
_time	BIGINT	The timestamp of the data point. Unit: microsecond.
_field_name	VARCHAR	The name of the data column.
_long_value	BIGINT	The data value of the integer type. If the data type of the data column is not integer, the value is NULL.
_double_value	DOUBLE	The data value of the floating-point type. If the data type of the data column is not floating-point, the value is NULL.
_bool_value	BOOL	The data value of the Boolean type. If the data type of the data column is not Boolean, the value is NULL.
_string_value	VARCHAR	The data value of the string type. If the data type of the data column is not string, the value is NULL.
_binary_value	MEDIUMBLOB	The data value of the binary type. If the data type of the data column is not binary, the value is NULL.
_attributes	VARCHAR	The properties of the time series. The format of properties is the same as the format of tags.
_meta_update_time	BIGINT	The point in time when the metadata of the time series is updated. When you update the properties of a time series, the system automatically updates the metadata update time of the time series. If you continue to write data to the time series, the system updates the metadata update time of the time series at regular intervals. You can use the metadata update time to determine whether the time series is active.

Multi-value model mapping table schema

If you want to query time series data by using a mapping table in the multi-value model, execute the CREATE TABLE statement to create a mapping table in the multi-value model. The name of the mapping table in SQL is in the Name of the time series table::Suffix format. Specify Suffix when you create a mapping table in SQL for the time series table. You can create multiple mapping tables in the multi-value model in SQL for a time series table.

For example, if the suffix is multi_model, the multi-value model mapping table is named vehicles_timeseriesdata::multi_model. The following table describes the table schema.

Field name	Type	Description
_m_name	VARCHAR	The measure name.
_data_source	VARCHAR	The data source.
_tags	VARCHAR	The tags for the time series, represented as an array. The format for multiple tags is ["tagKey1=tagValue1","tagKey2=tagValue2"]. Use the tag_value_at function to fetch the value of a specific tag.
_time	BIGINT	The timestamp of the data point, in microseconds.
vin_id	VARCHAR	The vehicle identification number (VIN), which uniquely identifies a vehicle.
mfrs	VARCHAR	The manufacturer.
model	VARCHAR	The vehicle model.
speed	Double	The current speed.
gps	VARCHAR	The GPS coordinates of the vehicle. The format is `latitude,longitude`. The latitude comes first, followed by the longitude. The latitude must be in the range of [-90, +90], and the longitude must be in the range of [-180, +180]. For example, `30.23,52.78`.
mileage	Double	The current mileage.
emission	Double	The emission value.

Time series metadata mapping table schema

After you create a time series table, the system automatically creates a mapping table for time series metadata. The name of the mapping table in SQL is in the Name of the time series table::meta format. You can use the mapping table to query time series metadata. For example, if the name of the time series table is timeseries_table, the name of the mapping table for time series metadata is timeseries_table::meta.

The time series metadata mapping table is named vehicles_timeseriesdata::meta. The following table describes the table schema.

Column name	Type	Description
_m_name	VARCHAR	The metric name.
_data_source	VARCHAR	The data source.
_tags	VARCHAR	The tags of the time series.
_attributes	VARCHAR	The properties of the time series.
_meta_update_time	BIGINT	The point in time when the metadata of the time series is updated. When you update the properties of a time series, the system automatically updates the metadata update time of the time series. If you continue to write data to the time series, the system updates the metadata update time of the time series at regular intervals. You can use the metadata update time to determine whether the time series is active.