Thingsboard 通过数据库查看遥测数据

一、相关配置规则链中，收到的遥测数据要保存目前我使用的是 postgresql二、参考链接TB的数据表结构说明https://blog.csdn.net/ieflex/article/details/104009420SQL语句学习https://blog.csdn.net/qq_37465638/article/details/81867389postgresql的安装使用说明https://w

可可芯意

2862人浏览 · 2022-01-17 16:40:23

可可芯意 · 2022-01-17 16:40:23 发布

一、相关配置

规则链中，收到的遥测数据要保存
目前我使用的是 postgresql

二、参考链接

TB的数据表结构说明
https://blog.csdn.net/ieflex/article/details/104009420
SQL语句学习
https://blog.csdn.net/qq_37465638/article/details/81867389
postgresql的安装使用说明
https://www.jianshu.com/p/68dc464b92a1
postgreSQL psql工具使用详解
https://www.cnblogs.com/nanshanjushi/p/11324607.html

三、具体步骤

登录数据库

ubuntu@VM-0-4-ubuntu:~$ sudo su - postgres
postgres@VM-0-4-ubuntu:~$

进入SQL控制台

postgres@VM-0-4-ubuntu:~$ psql
psql (12.7 (Ubuntu 12.7-1.pgdg18.04+1))
Type "help" for help.

postgres=#

链接数据库

postgres=# \connect thingsboard
You are now connected to database "thingsboard" as user "postgres".
thingsboard=#

查看所有数据库

thingsboard=# \d
                              List of relations
 Schema |                Name                 |       Type        |  Owner
--------+-------------------------------------+-------------------+----------
 public | admin_settings                      | table             | postgres
 public | alarm                               | table             | postgres
 public | api_usage_state                     | table             | postgres
 public | asset                               | table             | postgres
 public | attribute_kv                        | table             | postgres
 public | audit_log                           | table             | postgres
 public | component_descriptor                | table             | postgres
 public | customer                            | table             | postgres
 public | dashboard                           | table             | postgres
 public | device                              | table             | postgres
 public | device_credentials                  | table             | postgres
 public | device_profile                      | table             | postgres
 public | entity_view                         | table             | postgres
 public | event                               | table             | postgres
 public | oauth2_client_registration          | table             | postgres
 public | oauth2_client_registration_info     | table             | postgres
 public | oauth2_client_registration_template | table             | postgres
 public | relation                            | table             | postgres
 public | rule_chain                          | table             | postgres
 public | rule_node                           | table             | postgres
 public | rule_node_state                     | table             | postgres
 public | tb_schema_settings                  | table             | postgres
 public | tb_user                             | table             | postgres
 public | tenant                              | table             | postgres
 public | tenant_profile                      | table             | postgres
 public | ts_kv                               | partitioned table | postgres
 public | ts_kv_2017_01                       | table             | postgres
 public | ts_kv_2021_05                       | table             | postgres
 public | ts_kv_2021_06                       | table             | postgres
 public | ts_kv_2021_07                       | table             | postgres
 public | ts_kv_2021_08                       | table             | postgres
 public | ts_kv_2021_10                       | table             | postgres
 public | ts_kv_2021_11                       | table             | postgres
 public | ts_kv_2021_12                       | table             | postgres
 public | ts_kv_2022_01                       | table             | postgres
 public | ts_kv_dictionary                    | table             | postgres
 public | ts_kv_dictionary_key_id_seq         | sequence          | postgres
 public | ts_kv_indefinite                    | table             | postgres
 public | ts_kv_latest                        | table             | postgres
 public | user_credentials                    | table             | postgres
 public | widget_type                         | table             | postgres
 public | widgets_bundle                      | table             | postgres
(42 rows)

thingsboard=#

遥测数据在 ts_kv_2022_01这类型的表，查看一下表结构


thingsboard=# \d ts_kv_2022_01
                       Table "public.ts_kv_2022_01"
  Column   |            Type             | Collation | Nullable | Default
-----------+-----------------------------+-----------+----------+---------
 entity_id | uuid                        |           | not null |
 key       | integer                     |           | not null |
 ts        | bigint                      |           | not null |
 bool_v    | boolean                     |           |          |
 str_v     | character varying(10000000) |           |          |
 long_v    | bigint                      |           |          |
 dbl_v     | double precision            |           |          |
 json_v    | json                        |           |          |
Partition of: ts_kv FOR VALUES FROM ('1640995200000') TO ('1643673600000')
Indexes:
    "ts_kv_2022_01_pkey" PRIMARY KEY, btree (entity_id, key, ts)

这里的entity_id就是设备ID，所以通过id来SELECT

thingsboard=# SELECT * FROM ts_kv_2022_01 WHERE entity_id='ac3fec30-740d-11ec-bd2e-27176d3ae09b';
              entity_id               | key |      ts       | bool_v | str_v | long_v |  dbl_v  | json_v
--------------------------------------+-----+---------------+--------+-------+--------+---------+--------
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642123667898 |        |       |        | 0.00105 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642123668858 |        |       |        | 0.00105 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071818471 |        |       |        |    0.02 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071942863 |        |       |        | 0.00103 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071838778 |        |       |        | 0.00123 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071945338 |        |       |        | 0.00103 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071945993 |        |       |        | 0.00103 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071946297 |        |       |        | 0.00103 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642125097338 |        |       |        | 0.00105 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642125098298 |        |       |        | 0.00105 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071946952 |        |       |        | 0.00103 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071947257 |        |       |        | 0.00103 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071947898 |        |       |        | 0.00103 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071948049 |        |       |        | 0.00103 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071956538 |        |       |        |   0.001 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071957178 |        |       |        |   0.001 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071966476 |        |       |        | 0.00103 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071967418 |        |       |        | 0.00103 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071972618 |        |       |        |   0.001 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071982794 |        |       |        | 0.00105 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642071984058 |        |       |        | 0.00105 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642400016072 |        |       |        |  0.0469 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642400018952 |        |       |        |  0.0456 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642399944424 |        |       |        |  0.0442 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642399844575 |        |       |        |  0.0375 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642399947272 |        |       |        |  0.0456 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642399950152 |        |       |        |  0.0469 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642399878175 |        |       |        |  0.0415 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642399953144 |        |       |        |  0.0469 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642400021832 |        |       |        |  0.0469 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642399995912 |        |       |        |  0.0429 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642399998792 |        |       |        |  0.0442 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642399890664 |        |       |        |  0.0389 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642399893512 |        |       |        |  0.0402 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642400001672 |        |       |        |  0.0576 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642399955912 |        |       |        |  0.0469 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642399958792 |        |       |        |  0.0456 |
 ac3fec30-740d-11ec-bd2e-27176d3ae09b |  60 | 1642399961672 |        |       |        |  0.0469 |

其中，ts是时间戳（10位秒+3位毫秒），可以搜一下固定范围内的数据

SELECT * FROM ts_kv_2022_01 WHERE entity_id='ac3fec30-740d-11ec-bd2e-27176d3ae09b' AND ts>1642406418000 AND ts<1642408462000;

退出 SELECT后的显示，用 :之后 q即可

腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

计算机网络微课堂笔记

腾讯云开发者社区

Rabbitmq在java中的使用

腾讯云开发者社区

java try catch 之后定位不到具体报错行_JAVA入门（三）上

点击蓝字｜关注我们一、异常与异常处理异常简介代码中：阻止当前方法或作用域继续实现的，称之为异常java中的所有异常类都继承Throwable类，Exception 的父类是 Throwable编码环境用户操作输入出现问题由java虚拟机自动抛出和自动捕获需要手动添加抛出和捕获语句文件找不到ThrowableErrorException虚拟机错误 VirtualMachineError...