全部产品
Search
文档中心

表格存储:使用SQL查询时序数据

更新时间:Nov 08, 2023

创建时序表并建立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查询时序数据。查询时序数据时,请根据实际需要对应映射表进行操作。