表格存储快速上手-时序模型

2022-04-27 18:54:49

小提示：快速体验表格存储 Tablestore CLI 工具前，请您先阅读《表格存储快速上手准备》

模型简介

时序模型是针对时间序列数据的特点进行设计的，可适用于物联网设备监控、设备采集数据、机器监控数据等场景。以车联网场景为例，一张车辆状态表的时序模型包括了 measurement(度量类型)，data_source(数据源)，tags(时间线标签)，timestamp(时间戳)，fields(属性列)。数据结构模型图如下所示

表格存储快速上手-时序模型

下面将以一张车辆状态表 car_data 为例，带您体验表格存储 Tablestore 时序模型的基本使用方法。

时序表操作

执行 create 命令创建一张时序表 car_data。

create -m timeseries -t car_data

执行 use --ts 命令选择操作时序表 car_data。

use --ts -t car_data

数据导入。这里提供两种方式导入数据，二选一即可。

- 自定义数据，执行 putts 命令单行写入。示例中写入了1条时序数据。

putts --k '["car_data","car_0000010", ["brand=brand0","id=car_0000010","model=em3"]]' --field '[{"c":"duration","v":121,"isint":true},{"c":"mileage","v":6480,"isint":true},{"c":"power","v":69,"isint":true},{"c":"speed","v":24,"isint":true},{"c":"temperature","v":13,"isint":true}]' --time 1636460000000000

- 下载样例数据，执行 import_timeseries 命令批量导入。样例数据*包含500万条时序数据，可通过 import_timeseries -l 参数自定义导入行数（1000万行内免费使用），示例中导入了5万条时序数据。yourFilePath表示样例数据压缩包解压后的路径。

导入命令

import_timeseries -i yourFilePath -l 50000

输出

Current speed is: 11000 rows/s. Total succeed count 11000, failed count 0.
Current speed is: 13000 rows/s. Total succeed count 24000, failed count 0.
Current speed is: 16400 rows/s. Total succeed count 40400, failed count 0.
Import finished, total count is 50000, failed 0 rows.

执行 getts 命令查询一条时间线的前5个时间点。

getts --k '["car_data","car_0000006", ["brand=brand2","id=car_0000006","model=em2"]]' -l 5

输出

+-------------+-------------+-----------------------------------------------+------------------+----------+---------+-------+-------+-------------+
| measurement | data_source | tags                                          | timestamp        | duration | mileage | power | speed | temperature |
+-------------+-------------+-----------------------------------------------+------------------+----------+---------+-------+-------+-------------+
| car_data    | car_0000006 | ["brand=brand2","id=car_0000006","model=em2"] | 1636560000000000 | 190      | 1770    | 33    | 54    | 29          |
+-------------+-------------+-----------------------------------------------+------------------+----------+---------+-------+-------+-------------+
| car_data    | car_0000006 | ["brand=brand2","id=car_0000006","model=em2"] | 1636560010000000 | 554      | 6670    | 42    | 24    | 12          |
+-------------+-------------+-----------------------------------------------+------------------+----------+---------+-------+-------+-------------+
| car_data    | car_0000006 | ["brand=brand2","id=car_0000006","model=em2"] | 1636560020000000 | 564      | 9750    | 14    | 75    | 22          |
+-------------+-------------+-----------------------------------------------+------------------+----------+---------+-------+-------+-------------+
| car_data    | car_0000006 | ["brand=brand2","id=car_0000006","model=em2"] | 1636560030000000 | 176      | 7950    | 90    | 24    | 22          |
+-------------+-------------+-----------------------------------------------+------------------+----------+---------+-------+-------+-------------+
| car_data    | car_0000006 | ["brand=brand2","id=car_0000006","model=em2"] | 1636560040000000 | 441      | 6280    | 30    | 38    | 31          |
+-------------+-------------+-----------------------------------------------+------------------+----------+---------+-------+-------+-------------+

执行 qtm 命令查询时间线，示例中查询所有时间线返回10条。

qtm -l 10

输出

+-------------+-------------+-----------------------------------------------+------------+------------------+
| measurement | data_source | tags                                          | attributes | update_time      |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000005 | ["brand=brand0","id=car_0000005","model=m0"]  | null       | 1637722788684102 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000009 | ["brand=brand2","id=car_0000009","model=em3"] | null       | 1637722790158982 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000000 | ["brand=brand0","id=car_0000000","model=m3"]  | null       | 1637722787172818 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000008 | ["brand=brand0","id=car_0000008","model=m3"]  | null       | 1637722789832880 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000002 | ["brand=brand1","id=car_0000002","model=nm1"] | null       | 1637722787915852 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000006 | ["brand=brand2","id=car_0000006","model=em2"] | null       | 1637722789006974 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000001 | ["brand=brand2","id=car_0000001","model=em2"] | null       | 1637722787260034 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000004 | ["brand=brand0","id=car_0000004","model=m2"]  | null       | 1637722788529313 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000003 | ["brand=brand1","id=car_0000003","model=nm0"] | null       | 1637722788288273 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000007 | ["brand=brand2","id=car_0000007","model=em2"] | null       | 1637722789315575 |
+-------------+-------------+-----------------------------------------------+------------+------------------+

SQL模式

执行 sql 命令进入 SQL 命令行模式，可通过 sql 语句查询时序表。

sql

时序模型自动生成了sql映射表，可直接执行sql查询。

时间线检索

示例一：查询品牌为 “brand0” 并且型号为 “m3” 的车辆，返回前 10 条。

select 
 * 
from
 `car_data::meta` 
where 
 _m_name = "car_data" 
  and tag_value_at(_tags,"brand") = "brand0" 
  and tag_value_at(_tags,"model") = "m3" 
limit 
 10;

输出

+----------+--------------+----------------------------------------------+-------------+-------------------+
| _m_name  | _data_source | _tags                                        | _attributes | _meta_update_time |
+----------+--------------+----------------------------------------------+-------------+-------------------+
| car_data | car_0000000  | ["brand=brand0","id=car_0000000","model=m3"] | null        | 1637722787172818  |
+----------+--------------+----------------------------------------------+-------------+-------------------+
| car_data | car_0000008  | ["brand=brand0","id=car_0000008","model=m3"] | null        | 1637722789832880  |
+----------+--------------+----------------------------------------------+-------------+-------------------+

示例二：统计品牌为 “brand2” 的车辆总数。

select 
 count(*) 
from 
 `car_data::meta` 
where 
 tag_value_at(_tags,"brand") = "brand2";

输出

+----------+
| count(*) |
+----------+
| 4        |
+----------+

时间点查询

示例三：查询度量名称为“car_data”并且数据源为“car_0000001” 车辆，返回 “power” 度量的前 10 个数据点。

select 
 _time, _field_name, _long_value as value
from 
 `car_data` 
where 
 _m_name = "car_data" 
  and _data_source = "car_0000001"
  and _field_name = "power"
limit 
 10;

输出

+------------------+-------------+-------+
| _time            | _field_name | value |
+------------------+-------------+-------+
| 1636560000000000 | power       | 68    |
+------------------+-------------+-------+
| 1636560010000000 | power       | 41    |
+------------------+-------------+-------+
| 1636560020000000 | power       | 69    |
+------------------+-------------+-------+
| 1636560030000000 | power       | 95    |
+------------------+-------------+-------+
| 1636560040000000 | power       | 27    |
+------------------+-------------+-------+
| 1636560050000000 | power       | 26    |
+------------------+-------------+-------+
| 1636560060000000 | power       | 98    |
+------------------+-------------+-------+
| 1636560070000000 | power       | 82    |
+------------------+-------------+-------+
| 1636560080000000 | power       | 24    |
+------------------+-------------+-------+
| 1636560090000000 | power       | 2     |
+------------------+-------------+-------+

示例四：查询度量名称为“car_data”并且数据源为“car_000002” 的车辆最大行驶速度。

select 
 max(_long_value) as speed
from 
 `car_data` 
where 
 _m_name = "car_data" 
 and _data_source = "car_0000002"
  and _field_name = "speed";

输出

+-------+
| speed |
+-------+
| 100   |
+-------+

示例五：对度量名称为“car_data”并且数据源为“car_0000001”的车辆的室温数据按照时间窗口（60s聚合一次）进行聚合，统计每分钟最低室温。

SELECT
    _time DIV 60000000 * 60 as time_sec,
    min(_long_value) as temperature
FROM
    `car_data`
WHERE
    _data_source = "car_0000001"
    AND _field_name = "temperature"
GROUP BY
    time_sec
ORDER BY
    time_sec ASC
LIMIT
    20;

输出

+------------+-------------+
| time_sec   | temperature |
+------------+-------------+
| 1636560000 | 11          |
+------------+-------------+
| 1636560060 | 10          |
+------------+-------------+
| 1636560120 | 11          |
+------------+-------------+
| 1636560180 | 10          |
+------------+-------------+
| 1636560240 | 11          |
+------------+-------------+
| 1636560300 | 12          |
+------------+-------------+
| 1636560360 | 14          |
+------------+-------------+
| 1636560420 | 10          |
+------------+-------------+
| 1636560480 | 15          |
+------------+-------------+
| 1636560540 | 11          |
+------------+-------------+
| 1636560600 | 11          |
+------------+-------------+
| 1636560660 | 11          |
+------------+-------------+
| 1636560720 | 12          |
+------------+-------------+
| 1636560780 | 14          |
+------------+-------------+
| 1636560840 | 18          |
+------------+-------------+
| 1636560900 | 12          |
+------------+-------------+
| 1636560960 | 11          |
+------------+-------------+
| 1636561020 | 13          |
+------------+-------------+
| 1636561080 | 14          |
+------------+-------------+
| 1636561140 | 10          |
+------------+-------------+

退出 sql 模式

exit;

退出 cli 工具

exit

码农公寓