天天看點
傳回

表格存儲快速上手-時序模型

小提示：快速體驗表格存儲 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
sql 存儲 監控 NoSQL 物聯網 表格存儲資料模型 nosql存儲 sql存儲過程函數 ssd存儲 ssd高性能存儲
上一篇: SHELL中使用sed替換文本
下一篇: 參數量僅為原來1%，北郵等利用超分算法提出高性能視訊傳輸方法

繼續閱讀