使用命令列工具 - Tablestore

使用命令列工具（Tablestore CLI）建立時序表後，您可以通過CLI命令寫入時序資料並對時間軸進行檢索，以及查詢時序資料。您也可以通過SQL語句檢索時間軸以及查詢時序資料。

前提條件

已建立時序模型執行個體。具體操作，請參見建立時序模型執行個體。
已下載命令列工具。具體操作，請參見下載Tablestore CLI。
已啟動並配置執行個體。具體操作，請參見啟動並配置。
已擷取AccessKey。具體操作，請參見擷取AccessKey。

範例情境

本文以車連網情境中的一張車輛狀態表car_data為例，帶您體驗時序模型的基本使用方法。一張車輛狀態表的時序模型包括了measurement（度量類型）、data source（資料來源）、tags（時間軸標籤）、timestamp（時間戳記）和fields（屬性列），資料結構模型如下圖所示。

時序表操作

執行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表示範例資料壓縮包解壓後的路徑，例如D:\\timeseries_demo_data_5000000。
```
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.
```

執行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 |
+-------------+-------------+-----------------------------------------------+------------+------------------+

執行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          |
+-------------+-------------+-----------------------------------------------+------------------+----------+---------+-------+-------+-------------+

使用SQL查詢時序資料

建立時序表後，系統會自動為時序表建立兩個SQL映射關係，包括時序資料表和時序中繼資料表。

時序資料表：與時序表名稱相同，用於查詢時序資料。car_data的時序資料表名稱為car_data。
時序中繼資料表：在時序表名稱後拼接::meta，用於查詢時間軸中繼資料。car_data的時序中繼資料表名稱為car_data::meta。

執行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_0000175”車輛，返回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 10;

返回結果樣本如下：

+------------+-------------+
| time_sec   | temperature |
+------------+-------------+
| 1636560000 | 11          |
+------------+-------------+
| 1636560060 | 10          |
+------------+-------------+
| 1636560120 | 11          |
+------------+-------------+
| 1636560180 | 10          |
+------------+-------------+
| 1636560240 | 11          |
+------------+-------------+
| 1636560300 | 12          |
+------------+-------------+
| 1636560360 | 14          |
+------------+-------------+
| 1636560420 | 10          |
+------------+-------------+
| 1636560480 | 15          |
+------------+-------------+
| 1636560540 | 11          |
+------------+-------------+

退出SQL模式。
```
exit;
```
退出命令列工具。
```
exit
```