Thingsboard is a great IoT logging platform, however some management of data can be impossible to do from the GUI and can get annoying. The following database hacks provide a workaround to some of the common issues.
Connecting To The Database
- SSH to your ThingsBoard server
- If you don’t remember the password:
cd /etc/thingsboard/confgrep SPRING_DATASOURCE thingsboard.yml
- Connect with
psql -U <username> -d <database> -h 127.0.0.1 -W- The default database name is ‘thingsboard’
- Leave the
postgres=#prompt with\q
Database Tables
Postgres does not support ‘show tables’. Use ‘\dt‘ to view tables:
thingsboard=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------------------+-------+----------
public | admin_settings | table | postgres
public | alarm | 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 | entity_view | table | postgres
public | event | table | postgres
public | relation | table | postgres
public | rule_chain | table | postgres
public | rule_node | table | postgres
public | tb_user | table | postgres
public | tenant | table | postgres
public | ts_kv | table | postgres
public | ts_kv_latest | table | postgres
public | user_credentials | table | postgres
public | widget_type | table | postgres
public | widgets_bundle | table | postgres
(22 rows)Describe a table with ‘\d‘, e.g.:
thingsboard=# \d device
Table "public.device"
Column | Type | Collation | Nullable | Default
-----------------+------------------------+-----------+----------+---------
id | character varying(31) | | not null |
additional_info | character varying | | |
customer_id | character varying(31) | | |
type | character varying(255) | | |
name | character varying(255) | | |
search_text | character varying(255) | | |
tenant_id | character varying(31) | | |
Indexes:
"device_pkey" PRIMARY KEY, btree (id)- device – Contains the device to ID mappings. This is a different ID than what can be found from the ThingsBoard device control panel in the GUI
- ts_kv – Telemetry data. entity_id can be the device ID, ‘key’ is the field name.
Removing unwanted telemetry fields
ThingsBoard never forgets. If you have sent data to a device ID, it will remember this field for ever, and always show these fields as being available for graphing & reporting on the dashboards. Latest Telemetry from the GUI will show these fields have not been received for a long time.
The following session determines a device ID for a known device (SoilSensor1), then deletes the test fields key1 to key4, along with ‘values’ which was created in error during an integration configuration. For the GUI, the ts_kv_latest table also needs cleaning.
thingsboard=# select id,name from device where name='SoilSensor1';
id | name
---------------------------------+-------------
1ea5d8e1f450e60959cbf5261aa9fac | SoilSensor1
(1 row)
thingsboard=# select distinct key from ts_kv where entity_id='1ea5d8e1f450e60959cbf5261aa9fac';
key
----------------
soilPC
soilMoisture
devCharge
chargeStatus
power
devPower
key1
key4
key2
battery
busVoltage
current
values
key3
pressure
temperature
humidity
onBatteryPower
(18 rows)
thingsboard=# delete from ts_kv where entity_id='1ea5d8e1f450e60959cbf5261aa9fac' and key like 'key%';
DELETE 4
thingsboard=# delete from ts_kv where entity_id='1ea5d8e1f450e60959cbf5261aa9fac' and key='values';
DELETE 20
thingsboard=# select distinct key from ts_kv where entity_id='1ea5d8e1f450e60959cbf5261aa9fac';
key
----------------
power
devPower
battery
busVoltage
current
pressure
temperature
humidity
onBatteryPower
soilPC
soilMoisture
devCharge
chargeStatus
(13 rows)
thingsboard=# delete from ts_kv_latest where entity_id='1ea5d8e1f450e60959cbf5261aa9fac' and key like 'key%';
DELETE 4
thingsboard=# delete from ts_kv_latest where entity_id='1ea5d8e1f450e60959cbf5261aa9fac' and key='values';
DELETE 1Note, if you are viewing the Devices tab in ThingsBoard, then some details cache. Navigate to another section then return to verify the unwanted data has gone.
Renaming a field
If you send data to ThingsBoard with a mistake in the field name, when you correct it, ThingsBoard can not tie the two sets of data together. While this is perfectly reasonable, it can be quite annoying. The following example had a rogue apostrophe when sending humidity data. As a result we ended up with two datasets, humidity and humidity’. The data was joined together and verified via a graph on the dashboard:
thingsboard=# select id,name from device where name='SoilSensor2';
id | name
---------------------------------+-------------
1eab940ff2287c0a4259322b72a0dfe | SoilSensor2
(1 row)
thingsboard=# select distinct key from ts_kv where entity_id='1eab940ff2287c0a4259322b72a0dfe';
key
----------------
power
devPower
battery
current
busVoltage
pressure
temperature
humidity
onBatteryPower
humidity'
soilMoisture
soilPC
devCharge
chargeStatus
(14 rows)
thingsboard=# update ts_kv set key='humidity' where key='humidity''' and entity_id='1eab940ff2287c0a4259322b72a0dfe';
UPDATE 54827
thingsboard=# delete from ts_kv_latest where key='humidity''' and entity_id='1eab940ff2287c0a4259322b72a0dfe';
DELETE 1Note the single quote needs to be doubled to escape it, and because we already have a field called humidity in the latest telemetry, we just delete rather than rename.
When changing a graph, if you edit it, click on the field, you can just rename in plain text rather than delete the old and set up the new. This is useful if you have added attributes such as colour or a custom label, e.g. ‘Humidity (%)’.