# Integrate with SQL Server
In this article, we will simulate the temperature and humidity data, and report these data to EMQX Cloud via the MQTT protocol, and then we will use EMQX Cloud Data Integrations to dump the data to SQL Server.
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)
# SQL Server Configuration
Install SQL Server with Docker
# Password requires a combination of upper and lower case and special characters docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Mqtt_public' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest # Connect to your SQL Server instance sqlcmd -S localhost -U sa -P Mqtt_public -d master
1
2
3
4
5Create a database
CREATE DATABASE emqx; go;
1
2USE emqx; go;
1
2Create table
Use the following SQL command to create
temp_hum
table, and this table will be used for storing the temperature and humidity data reported by devices.CREATE TABLE temp_hum(client_id VARCHAR(64) NULL, temp NVARCHAR(100) NULL, hum NVARCHAR(100) NULL, up_timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP); go;
1
2
3
4
5
6Configure odbc driver
Provide your SQL Server version through the EMQX Cloud tickets (opens new window) service, and the ODBC driver will be installed for your deployment in the background.
# Data Integrations Configuration
Go to Deployment Details and click on Data Integrations
on the left menu bar.
Create SQL Server Resource
Click on
SQL Server
under the Data Persistence.Fill in the information of the sqlserver database 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 SQL Server resource.Create Rule
Choose the SQL Server 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 time when the message was reportedup_timestamp
, client ID, payload viatemp_hum/emqx
topic. Also, we can read temperature and humidity from this topic.SELECT timestamp as up_timestamp, clientid as client_id, payload.temp as temp, payload.hum as hum FROM "temp_hum/emqx"
1
2
3
4You can use
SQL Test
to see the resultAdd Action
Click on the Next action in the bottom to enter action view. Select the resource created in the first step, select
Data Persistence - Data to SQL Server
as Action Type, and enter the following data to insert into the SQL template.insert into temp_hum(client_id, temp, hum) values ('${client_id}', '${temp}', '${hum}')
1Click on
Confirm
to create action.View Resource Detail
Click on the resource to see the detail.
Check Rules Monitoring
Click the monitor icon of rule to see the metrics
# Test
Use MQTTX (opens new window) to simulate reporting temperature and humidity data
You need to replace broker.emqx.io with the deployment connection address you have created and add the client-side authentication information in the EMQX Dashboard.
topic:
temp_hum/emqx
payload:
{ "temp": "24.1", "hum": "34.5" }
1
2
3
4
View data dump results
select * from temp_hum order by up_timestamp;
1