Integrating EMQX with ClickHouse

During the creation of the resource, you will see a popup, and clicking 'New' will leads you to the rule creation page.

EMQX provides a powerful [rule engine](https://docs.emqx.com/en/cloud/latest/rule_engine/rules.html) that can transform, and enrich the raw MQTT message before sending it to third-party systems.

Here's the rule used in this tutorial:

```
SELECT
   clientid AS client_id,
   (timestamp div 1000) AS timestamp,
   topic AS topic,
   payload.temp AS temp,
   payload.hum AS hum
FROM
"temp_hum/emqx"
```

It will read the messages from the `temp_hum/emqx` topic and enrich the JSON object by adding client\_id, topic, and timestamp info.

So, the raw JSON you send to the topic:

```
{"temp": 28.5, "hum": 0.68}
```

You can use the SQL test to test and see the results.

Now click on the "NEXT" button. This step is to tell EMQX Cloud how to insert refined data into your ClickHouse database.

### Add a response action[​](#add-a-response-action "Direct link to Add a response action")

If you have only one resource, you don't need to modify the 'Resource' and 'Action Type'.
You only need to set the SQL template. Here's the example used for this tutorial:

```
INSERT INTO temp_hum (client_id, timestamp, topic, temp, hum) VALUES ('${client_id}', ${timestamp}, '${topic}', ${temp}, ${hum})
```

This is a template for inserting data into Clickhouse, you can see the variables are used here.

### View rules details[​](#view-rules-details "Direct link to View rules details")

Click "Confirm" and "View Details". Now, everything should be well set. You can see the data integration works from rule details page.

All the MQTT messages sent to the `temp_hum/emqx` topic will be persisted into your ClickHouse Cloud database.

## Saving Data into ClickHouse[​](#saving-data-into-clickhouse "Direct link to Saving Data into ClickHouse")

We will simulate temperature and humidity data and report these data to EMQX Cloud via the MQTT X and then use the EMQX Cloud Data Integrations to save the data into ClickHouse Cloud.

### Publish MQTT messages to EMQX Cloud[​](#publish-mqtt-messages-to-emqx-cloud "Direct link to Publish MQTT messages to EMQX Cloud")

You can use any MQTT client or SDK to publish the message. In this tutorial, we will use [MQTT X](https://mqttx.app/), a user friendly MQTT client application provided by EMQ.

Click "New Connection" on MQTTX and fill the connection form:

- Name: Connection name. Use whatever name you want.
- Host: the MQTT broker connection address. You can get it from the EMQX Cloud overview page.
- Port: MQTT broker connection port. You can get it from the EMQX Cloud overview page.
- Username/Password: Use the credential created above, which should be `emqx` and `xxxxxx` in this tutorial.

Click the "Connect" button on top right and the connection should be established.

Now you can send messages to the MQTT broker using this tool.
Inputs:

1. Set payload format to "JSON".
2. Set to topic: `temp_hum/emqx` (the topic we just set in the rule)
3. JSON body:

```
{"temp": 23.1, "hum": 0.68}
```

Click the send button on the right. You can change the temperature value and send more data to MQTT broker.

The data sent to EMQX Cloud should be processed by the rule engine and inserted into ClickHouse Cloud automatically.

### View rules monitoring[​](#view-rules-monitoring "Direct link to View rules monitoring")

Check the rule monitoring and add one to the number of success.

### Check the data persisted[​](#check-the-data-persisted "Direct link to Check the data persisted")

Now it's time to take a look at the data on the ClickHouse Cloud. Ideally, the data you send using MQTTX will go to the EMQX Cloud and persist to the ClickHouse Cloud's database with the help of native data integration.

You can connect to the SQL console on ClickHouse Cloud panel or use any client tool to fetch data from your ClickHouse. In this tutorial, we used the SQL console.
By executing the SQL:

```
SELECT * FROM emqx.temp_hum;
```

### Summary[​](#summary "Direct link to Summary")

You didn't write any piece of code, and now have the MQTT data move from EMQX cloud to ClickHouse Cloud. With EMQX Cloud and ClickHouse Cloud, you don't need to manage the infra and just focus on writing you IoT applications with data storied securely in ClickHouse Cloud.

- [Connecting EMQX](#connecting-emqx)
  - [Assumptions](#assumptions)- [Get your ClickHouse Cloud service](#get-your-clickhouse-cloudservice)- [Create an MQTT service on EMQX Cloud](#create-an-mqtt-service-on-emqx-cloud)
      - [Get an account](#get-an-account)- [Create an MQTT cluster](#create-an-mqtt-cluster)- [Add client credential](#add-client-credential)- [Enable NAT gateway](#enable-nat-gateway)- [Integration EMQX Cloud with ClickHouse Cloud](#integration-emqx-cloud-with-clickhouse-cloud)
        - [Create ClickHouse resource](#create-clickhouse-resource)- [Create a new rule](#create-a-new-rule)- [Add a response action](#add-a-response-action)- [View rules details](#view-rules-details)- [Saving Data into ClickHouse](#saving-data-into-clickhouse)
          - [Publish MQTT messages to EMQX Cloud](#publish-mqtt-messages-to-emqx-cloud)- [View rules monitoring](#view-rules-monitoring)- [Check the data persisted](#check-the-data-persisted)- [Summary](#summary)

Was this page helpful?