本文介绍如何通过Lindorm-cli连接并使用云数据库Lindorm时序引擎。

下载客户端

常用命令

  • help:查看帮助。
  • connect:连接服务器。
  • precision:时间显示格式,支持rfc3339、h、m、s、 ms、u或ns。
  • exit/quit/ctrl+d:退出Lindorm实例。

获取连接串

  1. 登录Lindorm控制台
  2. 实例列表单击目标实例ID,进入实例详情页面。
  3. 选择数据库连接,单击时序引擎页签。
  4. 时序引擎页面查看私网地址或公网地址。
    说明 默认情况下, 公网地址未开启,如果需要使用 公网地址,请单击 开通外网地址申请 公网地址。
    TSQL CLient连接-1

使用示例

  1. 使用以下命令连接Lindorm实例。
    ./lindorm-cli -url http://ld-xxxx-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242
    说明 请将 http://ld-xxxx-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242替换为您的Lindorm实例的连接地址和端口。
  2. 创建数据表。
    创建一个名为sensor的表。其中两个Tag分别为device_id和region,Tag的数据类型为VARCHAR。两个Field命名为temperature和humidity,数据类型为DOUBLE。time表示数据的时间,时间精度单位为ms。创建语句如下:
    CREATE TABLE sensor (
        device_id VARCHAR TAG,
        region VARCHAR TAG,
        time BIGINT,
        temperature DOUBLE,
        humidity DOUBLE);
  3. 查看所有数据表,查询语句如下:
    show tables;
    columnName typeName
    ---------- --------
    default    sensor
    
    1 rows in set (28 ms)
  4. 描述数据表,语句如下:
    describe table sensor;
    columnName  typeName
    ----------  --------
    device_id   VARCHAR
    region      VARCHAR
    time        BIGINT
    temperature DOUBLE
    humidity    BIGINT
    
    5 rows in set (22 ms)
  5. 往数据表中写入数据。
    您可以单条依次写入数据也可以批量写入数据,语句如下:
    • 单条依次写入:
      insert into sensor (device_id, region, time, temperature, humidity) values('F07A1260','north-cn',1619076780000,12.1,45);
      insert into sensor (device_id, region, time, temperature, humidity) values('F07A1260','north-cn',1619076790000,13.2,47);
      insert into sensor (device_id, region, time, temperature, humidity) values('F07A1260','north-cn',1619076800000,10.6,46);
      insert into sensor (device_id, region, time, temperature, humidity) values('F07A1261','south-cn',1619076780000,18.1,44);
      insert into sensor (device_id, region, time, temperature, humidity) values('F07A1261','south-cn',1619076790000,19.7,44);
    • 批量写入:
      insert into sensor (device_id, region, time, temperature, humidity) 
      values ('F07A1260','north-cn',1619076780000,12.1,45),
             ('F07A1260','north-cn',1619076790000,13.2,47),
             ('F07A1260','north-cn',1619076800000,10.6,46),
             ('F07A1261','south-cn',1619076780000,18.1,44),
             ('F07A1261','south-cn',1619076790000,19.7,44);
  6. 查询数据。
    • 条件查询

      查询设备F07A1260在时间范围16190767800001619076800000之间的数据:

      select device_id,region,time,temperature,humidity from sensor where device_id = 'F07A1260' and time >= 1619076780000 and time <= 1619076800000;
      device_id region   time                      temperature humidity
      --------- ------   ----                      ----------- --------
      F07A1260  north-cn 2021-04-22T15:33:00+08:00 12.1        45
      F07A1260  north-cn 2021-04-22T15:33:10+08:00 13.2        47
      F07A1260  north-cn 2021-04-22T15:33:20+08:00 10.6        46
      
      3 rows in set (41 ms)
    • 降采样

      查询设备F07A1260在时间范围16190767800001619076800000之间的温度,按照20秒降采样后取最大值:

      select device_id,region,time,max(temperature) as max_temperature from sensor where device_id = 'F07A1260' and time >= 1619076780000 and time <= 1619076800000 sample by 20s;
      device_id region   time                      max_temperature
      --------- ------   ----                      ---------------
      F07A1260  north-cn 2021-04-22T15:33:00+08:00 13.2
      F07A1260  north-cn 2021-04-22T15:33:20+08:00 10.6
      
      2 rows in set (33 ms)
    • 聚合操作

      计算所有区域的最大温度:

      select region,max(temperature) as max_temperature from sensor where time >= 1619076780000 and time <= 1619076800000 group by region;
      region   max_temperature
      ------   ---------------
      north-cn 13.2
      south-cn 19.7
      
      2 rows in set (76 ms)
    • 查询最新值

      查询设备F07A1260和设备F07A1261的最新温度:

      select device_id,time,latest(temperature) as latest_temperature from sensor where device_id = 'F07A1260' or device_id = 'F07A1261';
      device_id time                      latest_temperature
      --------- ----                      ------------------
      F07A1260  2021-04-22T15:33:20+08:00 10.6
      F07A1261  2021-04-22T15:33:10+08:00 19.7
      
      2 rows in set (28 ms)