Tablestore:Use SQL to query time series data

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 point in time 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 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.

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 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.

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 the Create mapping tables in the multi-value model for time series tables section of this topic.

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

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

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 the Mapping tables in the multi-value model section of this topic.

• SQL example

  The following sample code provides an example on how to create a mapping table in the multi-value model named timeseries_table::muti_model for the time series table. The 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 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.

SELECT tag_value_at(_tags, "host") as host FROM timeseries_table LIMIT 1;

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 metric in the mapping table is basic_metric.

• Query time series whose metric name is basic_metric in the mapping table for time series metadata.

  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 mapping table for time series metadata.

  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 that meet multiple tag conditions (host=host001 and region=hangzhou) and a specific property condition (status=online) and that are still active after a specific point in time in the mapping table for time series metadata.

  SELECT * FROM `timeseries_table::meta` WHERE _m_name = "basic_metric" AND tag_value_at(_tags, "host") = "host001" AND tag_value_at(_tags, "region") = "hangzhou"
   AND tag_value_at(_attributes, "status") = "online" AND _meta_update_time > (UNIX_TIMESTAMP() - 900) * 1000000 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 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 whose metric type is basic_metric and data source is device001 after a specific point in time in the time series data table.

  SELECT * FROM timeseries_table WHERE _m_name = "basic_metric" AND _data_source = "device001" AND _time > (UNIX_TIMESTAMP() - 900) * 1000000 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;

• Aggregate (avg and max) the cpu_usr data in a time series that meets a specific tag condition (host=host001) based on a time window of 600 seconds in the time series data table.

  SELECT tag_value_at(_tags, "host") as host,_time DIV 600000000 * 600 as time_sec,avg(_double_value) as cpu_avg,max(_double_value) as cpu_max FROM
   timeseries_table WHERE _m_name = "basic_metric" AND _time > (UNIX_TIMESTAMP() - 21600) * 1000000 AND tag_value_at(_tags, "host") = "host001"
   AND _field_name = "cpu_summary_usr" GROUP BY host,time_sec ORDER BY time_sec LIMIT 100;

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, see the Create mapping tables in the multi-value model for time series tables section of this topic.

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 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;

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.

• Use SQL to query time series data in the Tablestore console. For more information, see Use the SQL query feature in the Tablestore console.

• Use SQL to query time series data by using Tablestore SDKs. For more information, see Use Tablestore SDKs.

• Use SQL to query time series data by using Java Database Connectivity (JDBC).

  • Use JDBC to access Tablestore. For more information, see Use JDBC to access Tablestore.

  • Use Hibernate to use the JDBC driver for Tablestore. For more information, see Use Hibernate to use the JDBC driver for Tablestore.

  • Use MyBatis to use the JDBC driver for Tablestore. For more information, see Use MyBatis to use the JDBC driver for Tablestore.

• Use the Tablestore driver for Go to access Tablestore. For more information, see Use the Tablestore driver for Go to access Tablestore.

• Use SQL to query time series data in the Tablestore CLI. For more information, see SQL query.