使用命令行工具 - 表格存储

使用命令行工具（Tablestore CLI）创建时序表后，您可以通过CLI命令写入时序数据并对时间线进行检索，以及查询时序数据。您也可以通过SQL语句检索时间线以及查询时序数据。

前提条件

已创建时序模型实例。具体操作，请参见创建时序模型实例。
已下载命令行工具。具体操作，请参见下载。
已启动并配置实例。具体操作，请参见启动并配置。
已获取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
```