Thingsboard 时序数据和属性数据

Thingboard id之谜

thingboard使用cassandra-java提供的jar包生成带“-”的uuid；

存储到pg的id是去掉"-"(转换类UUIDConverter)，并按照一定规则排序的31位uuid(重点31位MLG)

返回给前端页面使用的，提供是带“-”的uuid（getData方法会转换）

 

时序数据

启用pg和cassandra混合模式后，cassandra只存储和时序数据相关的三张表

ts_kv_cf、ts_kv_latest_cf、ts_kv_partition_cf

thingsboard时序数据说明文档 https://thingsboard.io/docs/user-guide/telemetry/#websocket-api

 

主要接口

保存时序数据接口

POST  /api/plugins/telemetry/{entityType}/{entityId}/timeseries/{scope}
// 还有一个接口路径上多一个{ttl}的接口
Head  X-Authorization :token
Body 样例 {"key1":"value1", "key2":true, "key3": 3.0, "key4": 4}
含时间遥测数据，毫秒值 
{"ts":1561448813161, "values":{"key1":"value1", "key2":"value2"}}
调用样例：
curl -X POST --header 'Content-Type: application/json' --header 'Accept: */*' --header 'X-Authorization: Bearer –token--' -d '{"key1":"value1", "key2":true, "key3": 3.0, "key4": 4}' 'http://127.0.0.1:8080/api/plugins/telemetry/DEVICE/75a82e30-697f-11e9-8f29-453c4a68cf5e/timeseries/TEST'

名称	类型	含义	是否必填
entityType	String	实体类型，例如DEVICE	是
entityId	String	实体id	是
scope	String	无(随意填一个)	是
body	Application/json	Post消息体，遥测数据	是

 

查询时序数据接口

/api/plugins/telemetry/{entityType}/{entityId}/values/timeseries
Head  X-Authorization :token
//调用样例
curl -X GET --header 'Accept: application/json' --header 'X-Authorization: Bearer --token' 'http://127.0.0.1:8080/api/plugins/telemetry/DEVICE/75a82e30-697f-11e9-8f29-453c4a68cf5e/values/timeseries?limit=100&agg=NONE&keys=key1%2Ckey2&startTs=1451649500512&endTs=1556435934100'

名称	类型	含义	是否必填
entityType	String	实体类型，例如DEVICE	是
entityId	String	实体id	是
keys	request param	遥测属性键名	是
limit	request param	返回条数	是
startTs	request param	开始时间毫秒值	是
endTs	request param	结束时间毫秒值	是

注意 startTs要小于查询时序数据的时间

CassandraBaseTimeseriesDao --- > cassandra查询主类 如下：获取所以最新时序数据

private PreparedStatement getFindLatestStmt() {
    if (findLatestStmt == null) {
        findLatestStmt = prepare(SELECT_PREFIX +
                ModelConstants.KEY_COLUMN + "," +
                ModelConstants.TS_COLUMN + "," +
                ModelConstants.STRING_VALUE_COLUMN + "," +
                ModelConstants.BOOLEAN_VALUE_COLUMN + "," +
                ModelConstants.LONG_VALUE_COLUMN + "," +
                ModelConstants.DOUBLE_VALUE_COLUMN + " " +
                "FROM " + ModelConstants.TS_KV_LATEST_CF + " " +
                "WHERE " + ModelConstants.ENTITY_TYPE_COLUMN + EQUALS_PARAM +
                "AND " + ModelConstants.ENTITY_ID_COLUMN + EQUALS_PARAM +
                "AND " + ModelConstants.KEY_COLUMN + EQUALS_PARAM);
    }
    return findLatestStmt;
}

查询最新时序数据

注意返回的结果都是String类型

http://localhost:8080/api/plugins/telemetry/DEVICE/ca18f500-2e1b-11eb-9c2a-e7022e5c36ea/values/timeseries

{
    "mudbus_tag1": [
        {
            "ts": 1606719582282,
            "value": "100.11000061035156"
        }
    ],
    "mudbus_tag2": [
        {
            "ts": 1606719582282,
            "value": "58.02734375"
        }
    ]
}

 

需要了解cassandra，看这部分博客 cassandra知识点列表

cassandra sql查询样例

cassandra 工具dev-center

use thingsboard;
--- 查询最新遥测数据
select * from ts_kv_latest_cf where entity_type = 'DEVICE' and entity_id = 6f4c5940-672b-11e9-87c3-1b15e9582776;

--查询key分区
select * from ts_kv_partitions_cf where entity_type = 'DEVICE' and entity_id = 2523e9d0-60f7-11e9-b7b8-edae55f030fc and key = '定子温度';

use thingsboard;
--强制查询
---select * from ts_kv_cf where entity_type = 'DEVICE' and entity_id  = 2523e9d0-60f7-11e9-b7b8-edae55f030fc ALLOW FILTERING;
---select * from ts_kv_cf where entity_type = 'DEVICE' and entity_id  = 2523e9d0-60f7-11e9-b7b8-edae55f030fc and key = '定子温度' ALLOW FILTERING;
select * from ts_kv_cf where entity_type = 'DEVICE' and entity_id  = 2523e9d0-60f7-11e9-b7b8-edae55f030fc 
   and key = '定子温度' and partition = 1554076800000;
   
   use thingsboard;
   select * from ts_kv_latest_cf where entity_type = 'DEVICE' and entity_id = 75a82e30-697f-11e9-8f29-453c4a68cf5e;
   select * from ts_kv_partitions_cf where entity_type = 'DEVICE' and entity_id = 75a82e30-697f-11e9-8f29-453c4a68cf5e ALLOW FILTERING;
         
   select * from ts_kv_cf where entity_type = 'DEVICE' and entity_id  = 75a82e30-697f-11e9-8f29-453c4a68cf5e ALLOW FILTERING;
   select * from ts_kv_latest_cf where entity_type = 'DEVICE' and entity_id = 75a82e30-697f-11e9-8f29-453c4a68cf5e;

属性数据

thingsboard将属性数据分为三类：服务端属性(SERVER_SCOPE)、客户端属性(CLIENT_SCOPE)

SHARED_SCOPE(共享属性)，属性存放在表attribute_kv

 relation 关联表也是thingsboard很重要的一张表

20200206 Add

顾名思义，服务端属性，是thingsboard服务平台上可以添加的属性，可以添加、修改和删除。

共享属性和服务端属性操作类似；这两个相当于服务端维护人员给设备和资产打标签;

客户端属性是通过mqtt协议或者其他协议从设备上上传的属性（一般是变化比较小的标志例如设备型号等），此属性不可修改和删除，只能通过协议上报。

猜测: 共享属性的设置，从设计上应该是为了向外部共享一些信息或者配置；例如gateway的配置就是通过共享属性进行保存，然后下发到gateway网关。

 

时序数据和thingsboard传输:MQTT /COAP/HTTP

### HTTP
# 单条数据
curl -v -X POST -d "{\"temperature\": 25}"  http://10.201.82.69:8080/api/v1/IszSkvcSoFJBJnCr70oB/telemetry --header "Content-Type:application/json"
//多条数据
curl -v -X POST -d  "{\"index\": 34,\"latitude\":35.66,\"longitude\":104.88}" http://10.201.82.69:8080/api/v1/yiizf25Xx938rJQUsQeT/telemetry --header "Content-Type:application/json"

时序数据和websocket

https://thingsboard.io/docs/user-guide/telemetry/#websocket-api

ws(s)://host:port/api/ws/plugins/telemetry?token=$JWT_TOKEN

### 页面数据显示
{"tsSubCmds":[{"entityType":"DEVICE","entityId":"2523e9d0-60f7-11e9-b7b8-edae55f030fc","scope":"LATEST_TELEMETRY","cmdId":5}],"historyCmds":[],"attrSubCmds":[{"entityType":"DEVICE","entityId":"2523e9d0-60f7-11e9-b7b8-edae55f030fc","scope":"CLIENT_SCOPE","cmdId":4}]}


{"subscriptionId":4,"errorCode":0,"errorMsg":null,"data":{},"latestValues":{}}


{"subscriptionId":5,"errorCode":0,"errorMsg":null,"data":{"出口压力":[[1555495102815,"19.4283"]],"定子温度":[[1555495102815,"53.856"]],"流量":[[1555495102815,"63.3829"]]},"latestValues":{"出口压力":1555495102815,"流量":1555495102815,"定子温度":1555495102815}}