小提示：快速体验表格存储 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"limit10;
输出
+----------+--------------+----------------------------------------------+-------------+-------------------+ | _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” 的车辆总数。
selectcount(*)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"limit10;
输出
+------------------+-------------+-------+ | _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*60as time_sec, min(_long_value)as temperature FROM `car_data` WHERE _data_source ="car_0000001"AND _field_name ="temperature"GROUPBY time_sec ORDERBY time_sec ASCLIMIT20;
输出
+------------+-------------+ | 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
