After you create a time series table in Tablestore and a mapping table for the time series table in SQL, you can execute SQL statements in the Tablestore console or by using a Tablestore SDK to query time series data in the time series table.

Mapping tables for a time series table in SQL

The TimeSeries model is classified into the single-value model and the multi-value model based on whether one or more values are generated at each time point in a time series. The following table describes the types of mapping tables that you can create for a time series table in SQL to query data.

Mapping table type Description Creation method Name of the mapping table in SQL
Mapping tables in the single-value model Queries time series data by using the mapping table in the single-value model. After you create a time series table, the system automatically creates a mapping table in SQL for the time series table. Same as the name of the time series table.
Mapping tables in the multi-value model Queries time series data by using the mapping table in the multi-value model. After you create a time series table, you manually create a mapping table in SQL. The name of the mapping table is in the Name of the time series table::Suffix format. Specify Suffix when you create a mapping table in SQL.
Mapping tables for time series metadata Queries time series metadata. After you create a time series table, the system automatically creates a mapping table in SQL. The name of the mapping table is in the Name of the time series table::meta format.

Mapping tables in the single-value model

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 following table describes the schema of the mapping table in SQL.

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 and multiple tags are 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: microseconds.
_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.

Mapping tables in the multi-value model

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 mapping table in SQL uses a name that concatenates the ::Suffix string to the name of the time series table. Specify Suffix when you create a mapping table in SQL. You can create multiple mapping tables in the multi-value model in SQL for a time series table.

When you create a mapping table in the multi-value model for a time series table, specify the name of the mapping table, and the names and types of the data columns in the mapping table. For more information, see Create mapping tables in the multi-value model for time series tables.

The following table describes the schema of the mapping table in SQL.

Note If you want to read the properties column (_attributes) of the time series metadata or the metadata update time column (_meta_update_time) by using a mapping table in the multi-value model, add the two columns to the mapping table. The system automatically fills the content in the two metadata columns.
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 and multiple tags are 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: microseconds.
The name of the custom data column. SQL data types You can add multiple custom data columns to the mapping table in SQL.

If the name or type of the specified column in the mapping table in SQL does not match the name or type of the column in the time series table, the values of the column in the mapping table are null.

_attributes (optional) MEDIUMTEXT The properties of the time series. The format of properties is the same as the format of tags.
_meta_update_time (optional) BIGINT The point in time when the metadata of the time series is updated.

Mapping tables for time series metadata

After you create a time series table, the system automatically creates a mapping table for time series metadata. The mapping table uses a name that concatenates the ::meta string to the name of the time series table. 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 time series metadata table is timeseries_table::meta.

The following table describes the schema of the mapping table in SQL.

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.

SQL syntax

Create mapping tables in the multi-value model for time series tables

You can execute the CREATE TABLE statement to create a mapping table in the multi-value model for a time series table.

  • SQL syntax
    CREATE TABLE `timeseries_table::user_mapping_name` (
  `_m_name` VARCHAR(1024), 
  `_data_source` VARCHAR(1024), 
  `_tags` VARCHAR(1024), 
  `_time` BIGINT(20),
  `user_column_name1 ` data_type, 
  ......
  `user_column_namen ` data_type,
  PRIMARY KEY(`_m_name`,`_data_source`,`_tags`,`_time`)
);

    For more information about the parameters in the SQL syntax, see the table schema in Mapping tables in the multi-value model.

  • SQL example

    The following sample code shows how to create a mapping table in the multi-value model named timeseries_table::muti_model for the time series table. The types of metrics in the mapping table are cpu, memory, and disktop. SQL sample code: 

    CREATE TABLE `timeseries_table::muti_model` (
  `_m_name` VARCHAR(1024), 
  `_data_source` VARCHAR(1024), 
  `_tags` VARCHAR(1024),
  `_time` BIGINT(20),
  `cpu` DOUBLE(10),
  `memory` DOUBLE(10),
  `disktop` DOUBLE(10),
  PRIMARY KEY(`_m_name`,`_data_source`,`_tags`,`_time`)
);

Query data

You can execute the SELECT statement to query time series data. For more information, see Query data.

Tablestore provides the tag_value_at extension function to allow you to extract the value of a tag in the tags (_tags) of a time series. You can also use the function to extract the value of a property in the properties (_attributes) of a time series.

If the value of _tags is ["host=abc","region=hangzhou"], you can use tag_value_at(_tags, "host") to extract the value abc of the host tag. The following SQL statement shows an example:
SELECT tag_value_at(_tags, "host") as host FROM timeseries_table LIMIT 1;

SQL examples

Query time series

After you create a time series table, the system automatically creates a mapping table for time series metadata. You can use the mapping table to query time series.

In this example, a time series table named timeseries_table and a mapping table for time series metadata named timeseries_table::meta are used. The type of the metric in the mapping table is basic_metric.

  • Query time series whose metric name is basic_metric in the time series metadata table. 
    SELECT * FROM  `timeseries_table::meta` WHERE _m_name = "basic_metric" LIMIT 100;
  • Query time series that meet multiple tag conditions (host=host001 and region=hangzhou) in the time series metadata table. 
    SELECT * FROM `timeseries_table::meta` WHERE _m_name = "basic_metric" AND tag_value_at(_tags, "host") = "host001"
 AND tag_value_at(_tags, "region") = "hangzhou" LIMIT 100;

Query time series data by using the mapping table in the single-value model

After you create a time series table, the system automatically creates a mapping table in the single-value model for the time series table. You can use the mapping table to query time series data.

In this example, a time series table named timeseries_table and a mapping table in the single-value model named timeseries_table are used. The type of the metric in the mapping table is basic_metric.

  • Query the data whose metric type is basic_metric in the time series data table. 
    SELECT * FROM timeseries_table WHERE _m_name = "basic_metric" LIMIT 10;
  • Query the data in the time series that meets a single tag condition (host=host001) in the time series data table. 
    SELECT * FROM timeseries_table WHERE _m_name = "basic_metric" AND tag_value_at(_tags, "host") = "host001"
 AND _time > (UNIX_TIMESTAMP() - 900) * 1000000 LIMIT 10;
  • Query the data in the time series that meet multiple tag conditions (host=host001 and region=hangzhou) in the time series data table. 
    SELECT * FROM timeseries_table WHERE _m_name = "basic_metric" AND tag_value_at(_tags, "host") = "host001" AND tag_value_at(_tags, "region") = "hangzhou"
 AND _time > (UNIX_TIMESTAMP() - 900) * 1000000 LIMIT 10;

Query time series data by using a mapping table in the multi-value model

After you create a time series table, you can create a mapping table in the multi-value model for the time series table. You can use the mapping table to query time series data. For more information about how to create a mapping table in the multi-value model for a time series table, see Create mapping tables in the multi-value model for time series tables.

In this example, a time series table named timeseries_table and a mapping table in the multi-value model named timeseries_table::muti_model are used. The types of metrics in the mapping table are cpu, memory, and disktop.

  • Query the data whose data source is host_01 by using the mapping table in the multi-value model. In this example, host_id is stored in _data_source.
    SELECT * FROM `timeseries_table::muti_model` WHERE _data_source = "host_01" LIMIT 10;
  • Query information about the metrics in the time series whose cpu value is greater than 20.0 by using the mapping table in the multi-value model. 
    SELECT cpu,memory,disktop FROM `timeseries_table::muti_model` WHERE cpu > 20.0 LIMIT 10;
  • Calculate the average cpu values and maximum disktop values of the hosts that meet a specific tag condition (region=hangzhou) on January 1, 2022 by using the mapping table in the multi-value model. 
    SELECT avg(cpu) as avg_cpu,max(disktop) as max_disktop FROM `timeseries_table::muti_mode` WHERE tag_value_at(_tags,"region") = "hangzhou"
 AND _time > 1640966400000000 AND _time < 1641052799000000 GROUP BY _data_source;

Methods

You can use SQL to query time series data by using one of the following methods. When you query time series data, you can perform operations on the mapping tables based on your business requirements.