如何使用SQL查询时序数据_表格存储(Tablestore)-阿里云帮助中心

创建时序表并建立SQL映射关系后，您可以通过控制台、SDK等不同方式使用SQL查询时序数据。

时序表的SQL映射关系

根据每个时间线每个时刻对应一个值或者多个值，时序模型分为“单值模型”和“多值模型”。对于同一个时序表，您可以建立三种SQL映射关系用于数据查询，详细说明请参见下表。

映射关系类型	描述	创建方式	在SQL中的表名
单值模型映射关系	以单值模型查询时序数据。	创建时序表后，系统自动建立SQL映射关系。	与时序表名相同
多值模型映射关系	以多值模型查询时序数据。	创建时序表后，由用户手动建立SQL映射关系。	在时序表名后拼接`::后缀`，即`时序表名::后缀`，其中`后缀`由用户在创建时自定义
时间线元数据映射关系	查询时间线元数据。	创建时序表后，系统自动建立SQL映射关系。	在时序表名称后拼接`::meta`，即`时序表名::meta`

单值模型映射关系

时序表创建后，系统会自动创建单值模型映射关系。在SQL中表名与时序表名称相同，用于以单值模型查询时序数据。

表结构请参见下表。

字段名称	类型	描述
_m_name	VARCHAR	度量名称。
_data_source	VARCHAR	数据源。
_tags	VARCHAR	时间线标签，以数组表示。多个标签的格式为 ["tagKey1=tagValue1","tagKey2=tagValue2"] 。您可以使用tag_value_at函数提取某个标签的值。
_time	BIGINT	数据点的时间戳，单位微秒。
_field_name	VARCHAR	数据列名。
_long_value	BIGINT	整型的数据值。如果该数据列为非整型，则值为NULL。
_double_value	DOUBLE	浮点数类型的数据值。如果该数据列为非浮点数，则值为NULL。
_bool_value	BOOL	布尔类型的数据值。如果该数据列为非布尔值，则值为NULL。
_string_value	VARCHAR	字符串类型的数据值。如果该数据列为非字符串，则值为NULL。
_binary_value	MEDIUMBLOB	二进制类型的数据值。如果该数据列为非二进制，则值为NULL。
_attributes	VARCHAR	时间线属性，格式与标签相同。
_meta_update_time	BIGINT	时间线的元数据更新时间。当用户更新时间线属性时，系统会自动更新时间线元数据更新时间。此外，如果该时间线的数据持续写入，系统也会定时更新该时间，可用于判断一条时间线是否活跃。

多值模型映射关系

当以多值模型查询时序数据时，您需要执行CREATE TABLE语句来创建多值模型映射关系。在SQL中表名为时序表名称后拼接::后缀，其中后缀由用户自定义。一个时序表支持创建多个多值模型映射关系。

创建多值模型映射关系时，您需要在SQL语句中指定多值模型的映射关系名称、包含的数据列的列名和类型等。具体操作，请参见创建多值模型映射关系。

表结构请参见下表。

说明

如果要通过多值模型映射关系读取时间线元数据的属性列（_attributes）或者元数据最近更新时间列（_meta_update_time），您需要将这两列添加到多值模型映射关系中，系统会自动填充这两个元数据列的内容。

字段名称	类型	描述
_m_name	VARCHAR	度量名称。
_data_source	VARCHAR	数据源。
_tags	VARCHAR	时间线标签，以数组表示。多个标签的格式为 ["tagKey1=tagValue1","tagKey2=tagValue2"] 。您可以使用tag_value_at函数提取某个标签的值。
_time	BIGINT	数据点的时间戳，单位微秒。
自定义数据列名	SQL数据类型	自定义的数据列，支持添加多个。如果指定的列名或者类型与表中实际写入的列名或者类型不符，则该列读取结果为null。
_attributes（可选）	MEDIUMTEXT	时间线属性，格式与标签相同。
_meta_update_time（可选）	BIGINT	时间线的元数据更新时间。当用户更新时间线属性时，系统会自动更新时间线元数据更新时间。此外，如果该时间线的数据持续写入，系统也会定时更新该时间，可用于判断一条时间线是否活跃。

时间线元数据映射关系

时序表创建后，系统会自动创建时间线元数据映射关系。在SQL中表名为时序表名称后拼接::meta，用于查询时间线元数据。假如时序表名称为timeseries_table，则时序元数据表的名称为timeseries_table::meta。

表结构请参见下表。

字段名称	类型	描述
_m_name	VARCHAR	度量名称。
_data_source	VARCHAR	数据源。
_tags	VARCHAR	时间线标签。
_attributes	VARCHAR	时间线属性。
_meta_update_time	BIGINT	时间线的元数据更新时间。当用户更新时间线属性时，系统会自动更新时间线元数据更新时间。此外，如果该时间线的数据持续写入，系统也会定时更新该时间，可用于判断一条时间线是否活跃。

SQL语法

创建多值模型映射关系

通过CREATE TABLE语句创建多值模型映射关系。

SQL语法

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

详细参数说明请参见多值模型映射关系的表结构信息。

SQL示例

假设测量的属性同时包含了cpu、memory、disktop三种度量，此处以创建多值类型映射表timeseries_table::muti_model为例介绍。SQL示例如下：

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

查询数据

通过SELECT语句执行时序数据查询。更多信息，请参见查询数据。

表格存储还提供tag_value_at扩展函数用于时间线标签（_tags）中的某个标签（tag）的值以及提取时间线属性（_attributes）中的某个属性值。

假设_tag为["host=abc","region=hangzhou"]，则您可以使用tag_value_at(_tags, "host")提取host标签的值，即"abc"。SQL语句示例如下：

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

SQL示例

查询时间线

创建时序表后，系统会自动创建时间线元数据映射表，您可以使用时间线元数据映射表查询时间线。

此处以时序表名称为timeseries_table，时间线元数据映射表名称为timeseries_table::meta，度量类型为basic_metric为例介绍。

查询时序元数据表中basic_metric度量类型下的时间线。

SELECT * FROM  `timeseries_table::meta` WHERE _m_name = "basic_metric" LIMIT 100;

查询时序元数据表中满足多个标签条件（host=host001, region=hangzhou）的时间线。

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;

查询时序元数据表中满足多个标签条件（host=host001, region=hangzhou）和属性条件（status=online），且在某个时刻之后仍活跃的时间线。

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;

使用单值模型映射表查询时序数据

创建时序表后，系统会自动创建同名的单值模型映射表，您可以使用单值模型映射表查询时序数据。

此处以时序表名称为timeseries_table，单值模型映射表名称为timeseries_table，度量类型为basic_metric为例介绍。

查询时序数据表中basic_metric度量类型的数据。

SELECT * FROM timeseries_table WHERE _m_name = "basic_metric" LIMIT 10;

假设数据源为device001，查询时序数据表中basic_metric度量类型下device001数据源在某个时刻之后的数据。

SELECT * FROM timeseries_table WHERE _m_name = "basic_metric" AND _data_source = "device001" AND _time > (UNIX_TIMESTAMP() - 900) * 1000000 LIMIT 10;

查询时序数据表中满足单个标签条件（host=host001）的时间线的数据。

SELECT * FROM timeseries_table WHERE _m_name = "basic_metric" AND tag_value_at(_tags, "host") = "host001"
 AND _time > (UNIX_TIMESTAMP() - 900) * 1000000 LIMIT 10;

查询时序数据表中满足多个标签条件（host=host001, region=hangzhou）的时间线的数据。

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;

对时序数据表中某条时间线（host=host001）的cpu_usr数据按照时间窗口（600 s聚合一次）进行聚合（avg、max）查询。

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;

使用多值模型映射表查询时序数据

创建时序表后，通过手动创建多值模型映射关系，您可以使用多值模型映射表查询时序数据。关于创建多值模型映射关系的具体操作，请参见创建多值模型映射关系。

此处以时序表名称为timeseries_table，多值模型映射表名称为timeseries_table::muti_model，测量的属性同时包含了cpu、memory、disktop三种度量为例介绍。

查询多值模型映射表中数据源标识为host_01的数据。（假设_data_source中保存了host_id）
```
SELECT * FROM `timeseries_table::muti_model` WHERE _data_source = "host_01" LIMIT 10;
```

查询多值模型映射表中cpu大于20.0的所有度量信息。

SELECT cpu,memory,disktop FROM `timeseries_table::muti_model` WHERE cpu > 20.0 LIMIT 10;

计算多值模型映射表中满足标签条件（region=hangzhou）的主机在日期为2022-01-01内的平均cpu和最大disktop。

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;

使用方式

您可以通过以下方式使用SQL查询时序数据。查询时序数据时，请根据实际需要对应映射表进行操作。

使用控制台。具体操作，请参见通过控制台使用SQL查询。
使用SDK。具体操作，请参见使用SDK。
使用JDBC
- 使用JDBC直连。具体操作，请参见JDBC连接表格存储。
- 通过Hibernate使用。具体操作，请参见通过Hibernate使用。
- 通过MyBatis使用。具体操作，请参见通过MyBatis使用。
使用Go语言驱动。具体操作，请参见使用Go语言驱动。
使用命令行工具。具体操作，请参见使用命令行工具。