# Integrate with TimescaleDB
In this article, we will simulate temperature and humidity data and report these data to EMQX Cloud via the MQTT protocol and then use the EMQX Cloud Data Integrations to dump the data into TimescaleDB.
Before you start, you will need to complete the following:
- Deployments have already been created on EMQX Cloud (EMQX Cluster).
- For Professional Plan users: Please complete Peering Connection Creation first, all IPs mentioned below refer to the internal network IP of the resource.(Professional Plan with a NAT gateway can also use public IP to connect to resources)
# TimescaleDB Configuration
Install TimescaleDB
docker run -d --name timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=password timescale/timescaledb:1.7.4-pg12
1Create database
docker exec -it timescaledb psql -U postgres CREATE database emqx; \c emqx
1
2
3Create table
Use the following SQL statement to create
temp_hum
table. This table will be used to save the temperature and humidity data reported by devices.CREATE TABLE temp_hum ( up_timestamp TIMESTAMPTZ NOT NULL, client_id TEXT NOT NULL, temp DOUBLE PRECISION NULL, hum DOUBLE PRECISION NULL ); SELECT create_hypertable('temp_hum', 'up_timestamp');
1
2
3
4
5
6
7
8Insert test data and view it
INSERT INTO temp_hum(up_timestamp, client_id, temp, hum) VALUES (to_timestamp(1603963414), 'temp_hum-001', 19.1, 55); SELECT * FROM temp_hum;
1
2
3
# Data Integrations Configuration
Go to Deployment Details and click on Data Integrations
on the left menu bar.
Create TimescaleDB Resource
Click on
TimescaleDB
under the Data Persistence.Fill in the timescaledb database information you have just created and click
Test
. If there is an error, you should check if the database configuration is correct. Then click onNew
to create TimescaleDB resource.Create Rule
Choose the TimescaleDB resource under Configured Resources, click on
New Rule
and enter the following rule to match the SQL statement. In the following rule we read the timeup_timestamp
when the message is reported, the client ID, the message body (Payload) from thetemp_hum/emqx
topic and the temperature and humidity from the message body respectively.SELECT timestamp div 1000 AS up_timestamp, clientid AS client_id, payload.temp AS temp, payload.hum AS hum FROM "temp_hum/emqx"
1
2
3
4Create Action
Click on the
Next
button in the bottom to enter action view. Select the resource created in the first step and enter the following data to insert into the SQL template.INSERT INTO temp_hum(up_timestamp, client_id, temp, hum) VALUES (to_timestamp(${up_timestamp}), ${client_id}, ${temp}, ${hum})
1Click on
Confirm
to create action.View Resource Detail
Click on the resource to see the detail.
Check Rule Monitoring
Click the monitor icon of rule to see the metrics
# Test
Use MQTT X (opens new window) to simulate temperature and humidity data reporting
You need to replace broker.emqx.io with the created deployment connection address, and add client authentication information to the EMQX Dashboard.
View data dump results
select * from temp_hum order by up_timestamp desc limit 10;
1