Save data to ClickHouse

Steup the ClickHouse database, and set username/password to default/public. Taking CentOS as example:

  1. ## install dependencies
  2. sudo yum install -y epel-release
  4. ## download and run the installation script provided by packagecloud.io
  5. curl -s https://packagecloud.io/install/repositories/altinity/clickhouse/script.rpm.sh | sudo bash
  7. ## install ClickHouse server and client
  8. sudo yum install -y clickhouse-server clickhouse-client
  10. ## start the ClickHouse server
  11. clickhouse-server
  13. ## start the ClickHouse client
  14. clickhouse-client

Create the test database:

  1. create database test;

create t_mqtt_msg table:

  1. use test;
  2. create table t_mqtt_msg (msgid Nullable(String), topic Nullable(String), clientid Nullable(String), payload Nullable(String)) engine = Log;

Save data to ClickHouse - 图1

Create the rule:

Go to the EMQX DashboardSave data to ClickHouse - 图2 (opens new window), and type in the follwing SQL:

  1. SELECT * FROM "#"

image

Select an action:

Add an action and select “Data to ClickHouse” from the dropdown list.

image

Provide the arguments of the action:

1). The resource id. We create a new clickhouse resource now:

Click “create” right to the resource Id text box, and then select “clickhouse” and fill in the following paramenters:

image

Click the “Confirm” button.

2). The SQL template. In this example we insert an message to clickhouse:

  1. insert into test.t_mqtt_msg(msgid, clientid, topic, payload) values ('${id}', '${clientid}', '${topic}', '${payload}')

image

Keep all other arguments unchanged and confirm the action creation.

Then click “Create” to confirm the rule creation.

Now the rule has been created, we send an testing message to the broker:

  1. Topic: "t/a"
  2. QoS: 1
  3. Payload: "hello"

And then we can verify if the message is inserted to the clickhouse table:

image