EMQ X + IoTDB: store MQTT messages to time series database

IoTDB is the first open source time series database project initiated by Tsinghua University, and it is now a top-level project of Apache. IoTDB can provide users with services such as data collection, storage, and analysis. Due to its lightweight architecture, high performance and high availability, and seamless integration with Hadoop and Spark ecosystems, it meets the needs of massive data storage, high-throughput data writing, and complex data query analysis in the industrial IoT field.

EMQ X is a massively scalable and elastically scalable open source cloud-native distributed IoT messaging middleware, released by EMQ Yingyun Technology, an open source IoT data infrastructure software provider. EMQ X can handle concurrent connections of massive IoT devices efficiently and reliably, and has a built-in powerful rule engine function for high-performance real-time processing of event and message flow data. The rule engine provides a flexible "configured" business integration solution through SQL statements, which simplifies the business development process, improves the usability, and reduces the coupling between the user's business logic and EMQ X.

This article will introduce how to use the MQTT data bridging function of the EMQ X rule engine to receive data sent by the MQTT client and insert it into the time series database IoTDB in real time.

Ready to work

Software and environments used in the examples in this article:

• Operating System: Mac OSX
• IoTDB: Binary Package (Server), Version 0.12.4
• MQTT server: EMQ X Open Source v4.3.11
• MQTT client software: MQTTX v1.6.0

IoTDB installation

First we need to download the binary package of IoTDB Server (stand-alone version) IoTDB official page

After the download is complete, decompress it and enter the decompressed directory:

% ls
LICENSE         README.md       RELEASE_NOTES.md data             ext             licenses         sbin
NOTICE           README_ZH.md     conf             docs             lib             logs             tools

To enable IoTDB's MQTT protocol support, you need to modify IoTDB's configuration file conf/iotdb-engine.properties :

*Subsequent modeling uses a storage group root.sg . To increase write parallelism, virtual_storage_group_num in iotdb-engine.properties needs to be set to the number of machine cores at the same time.

####################
### MQTT Broker Configuration
####################

# whether to enable the mqtt service.
enable_mqtt_service=true

# the mqtt service binding host.
mqtt_host=0.0.0.0

# the mqtt service binding port.
mqtt_port=2883

# the handler pool size for handing the mqtt messages.
mqtt_handler_pool_size=1

# the mqtt message payload formatter.
mqtt_payload_formatter=json

# max length of mqtt message in byte
mqtt_max_message_size=1048576

Among them, enable_mqtt_service is false by default and needs to be changed to true . mqtt_port default value of 061f4adbda2b91 is 1883, in order to avoid conflict with the port number of emqx, it needs to be changed to 2883.

Then use ./sbin/start-server.sh start the IoTDB server:

% ./sbin/start-server.sh
---------------------
Starting IoTDB
---------------------
Maximum memory allocation pool = 2048MB, initial memory allocation pool = 512MB
If you want to change this configuration, please check conf/iotdb-env.sh(Unix or OS X, if you use Windows, check conf/iotdb-env.bat).
2022-01-10 14:15:31,914 [main] INFO o.a.i.d.c.IoTDBDescriptor:121 - Start to read config file file:./sbin/../conf/iotdb-engine.properties
...
2022-01-10 14:14:28,690 [main] INFO o.a.i.d.s.UpgradeSevice:73 - Upgrade service stopped
2022-01-10 14:14:28,690 [main] INFO o.a.i.db.service.IoTDB:153 - Congratulation, IoTDB is set up successfully. Now, enjoy yourself!
2022-01-10 14:14:28,690 [main] INFO o.a.i.db.service.IoTDB:101 - IoTDB has started

We keep this terminal window still, and open a new command line terminal window to start the IoTDB shell tool:

% ./sbin/start-cli.sh
---------------------
Starting IoTDB Cli
---------------------
_____       _________ ______   ______
|_   _|     | _   _ ||_   _ `.|_   _ \
| |   .--.|_/ | | \_| | | `. \ | |_) |
| | / .'`\ \ | |     | | | | | __'.
_| |_| \__. | _| |_   _| |_.' /_| |__) |
|_____|'.__.' |_____| |______.'|_______/ version 0.12.4


IoTDB> login successfully
IoTDB>

At this point, the IoTDB environment is ready. To understand the basic usage of IoTDB, you can refer to the Quick Start page official website.

Install and configure EMQ X

Download and launch EMQ X

We directly use the command line to download the EMQ X open source version for macOS. For more installation packages, please visit EMQ X open source version download page .

% wget https://www.emqx.com/en/downloads/broker/4.3.11/emqx-macos-4.3.11-amd64.zip

Then unzip and start EMQ X:

% unzip -q emqx-macos-4.3.11-amd64.zip
% cd emqx
% ./bin/emqx console

log.to = "console"
Erlang/OTP 23 [erts-11.1.8] [emqx] [64-bit] [smp:8:8] [ds:8:8:8] [async-threads:4] [hipe]
Starting emqx on node emqx@127.0.0.1
Start mqtt:tcp:internal listener on 127.0.0.1:11883 successfully.
Start mqtt:tcp:external listener on 0.0.0.0:1883 successfully.
Start mqtt:ws:external listener on 0.0.0.0:8083 successfully.
Start mqtt:ssl:external listener on 0.0.0.0:8883 successfully.
Start mqtt:wss:external listener on 0.0.0.0:8084 successfully.
Start http:management listener on 8081 successfully.
Start http:dashboard listener on 18083 successfully.
EMQ X Broker 4.3.11 is running now!
Eshell V11.1.8 (abort with ^G)
(emqx@127.0.0.1)1>

Configure rules

EMQ X Dashboard with a browser, and create a rule on the rule engine page:

The SQL statement is:

SELECT
    clientid,
    now_timestamp('millisecond') as now_ts_ms,
    payload.bar as bar
FROM
    "t/#"

Then we add a "Bridge Data to MQTT Broker" action to the rule at the bottom of the page:

This action needs to be associated with a resource. We click "New Resource" in the upper right corner to create a MQTT Bridge resource:

The remote broker address should be filled with the MQTT service address of IoTDB, namely "127.0.0.1:2883". The client ID, user name, and password are all filled in root, because root is the default user name and password of IoTDB.

Other options remain the default values, click the "Test Connection" button to ensure that the configuration is correct, and then click the "New" button in the lower right corner to create a resource.

Now return to the action creation page, the drop-down box of the associated resource is automatically populated with the resource we just created.

Now we continue to fill in more action parameters:

IoTDB doesn't care about message topics, we fill in an arbitrary topic: foo .

IoTDB requires that the message content is in JSON format, and the message content template can be filled in according to the style shown in the figure above. For details, please refer to IoTDB's communication service protocol document .

{
 "device": "root.sg.${clientid}",
 "timestamp": ${now_ts_ms},
 "measurements": [
   "bar"
 ],
 "values": [
   ${bar}
 ]
}

Note that "${clientid}", "${now_ts_ms}" and "${bar}" are all variables extracted from the output of the regular SQL statement, so it must be ensured that these variables correspond to the SELECT clause of the SQL statement .

Now click "OK" to save the action configuration, then click "New" again to complete the rule creation.

Send messages using MQTT Client

Next we use the MQTT client tool - MQTT X to send a message to EMQ X:

MQTT X is a fully open source MQTT 5.0 cross-platform desktop client released by EMQ. Supports rapid creation of multiple simultaneous online MQTT client connections, which is convenient for testing MQTT/TCP, MQTT/TLS, MQTT/WebSocket connections, publishing, subscription functions and other MQTT protocol features.

In the connection parameters of the MQTT client, we only need to fill in one parameter, Client ID: "abc", and the others remain the default values.

After the connection is successful, we send 2 messages with the subject: "t/1", and the message content format is:

{
 "bar": 0.2
}

Then go back to the rule engine page of EMQ X Dashboard, observe the number of hits of the rule, and confirm that the rule is triggered twice:

Finally, we go back to the IoTDB client window of the command line terminal and use the following SQL statement to query the data:

IoTDB> SHOW TIMESERIES root.sg.abc
+---------------+-----+-------------+--------+--------+-----------+----+----------+
|     timeseries|alias|storage group|dataType|encoding|compression|tags|attributes|
+---------------+-----+-------------+--------+--------+-----------+----+----------+
|root.sg.abc.bar| null|     root.sg|   FLOAT| GORILLA|     SNAPPY|null|      null|
+---------------+-----+-------------+--------+--------+-----------+----+----------+
Total line number = 1
It costs 0.006s

IoTDB> SELECT * FROM root.sg.abc
+-----------------------------+---------------+
|                         Time|root.sg.abc.bar|
+-----------------------------+---------------+
|2022-01-10T17:39:41.724+08:00|            0.3|
|2022-01-10T17:40:32.805+08:00|            0.2|
+-----------------------------+---------------+
Total line number = 2
It costs 0.007s
IoTDB>

Data inserted successfully!

Epilogue

So far, we have finished persisting messages to the IoTDB time series database through the EMQ X rule engine function.

In actual production scenarios, we can use EMQ X to handle massive concurrent connections of IoT devices, and flexibly process business functions through the rule engine, then persist the messages sent by the devices to the IoTDB database, and finally use Hadoop/Spark, Flink or Grafana, etc. connect with IoTDB to realize big data analysis, visual display, etc.

The combination of EMQ X + IoTDB is a simple, efficient, scalable, and highly available server-side integration solution. It is a good choice for IoT device management and data processing scenarios.