Sava data to MatrixDB

Build the MatrixDB database, set the user name and password to root/public, and create a database named mqtt.

Access MatrixDB through the command line tool psql and create the t_mqtt_msg table:

$ psql -h localhost -U root mqtt

CREATE TABLE t_mqtt_msg (
    id SERIAL primary key,
    msgid character varying(64),
    sender character varying(64),
    topic character varying(255),
    qos integer,
    payload text,
    arrived timestamp without time zone
);

Create a rule:

Open EMQX Dashboard (opens new window) and select the “Rules” tab on the menu to the left.

Fill in the rule SQL:

SELECT * FROM "t/#"

Related action:

Click on the “Add Action” button under “Action Handler”, and then select “Data to MatrixDB” in the pop-up dialog window.

Fill in the parameters required by the action:

The following parameters are required by action “Data to MatrixDB”:

1). Use resource, which is the resource ID. Now the resource drop-down box is empty. You need to create an available MatrixDB resource instance first.

Click the New button on the right side of Use Resource to enter the Create Resource page. The MatrixDB resource requires the following configurable items:

Server, the server address of MatrixDB.

Database, MatrixDB database name.

User, Password, authentication credentials.

Eable Reconnect, whether to enable automatic reconnection.

Pool Size, connection process pool size, which will help obtain the best performance with reasonable configuration.

Enable SSL, whether to enable TLS connection.

After the configuration is complete, click OK to complete the creation.

After the resource is successfully created, we will return to the Add Action page, and Use Resource is also automatically filled in with the resource ID of the Matrix resource just created.

2). Enable Batch Insert, whether to enable batch insert. In high concurrency scenarios, it can significantly improve write performance.

3). Max Batch Insert Count, the largest INSERT SQL entry that can be sent in a single batch request.

4). Max Batch Interval, the maximum waiting interval between two batch requests.

5). Sync or Async Insert, which determines to make a synchronous or asynchronous call.

6). Call Timeout. It refers to the timeout for executing actions in synchronous mode. This option is only valid for synchronous insertion.

7). SQL Template. It contains a placeholder SQL template for inserting or updating data to the database. In this example, we use the following SQL:

INSERT INTO t_mqtt_msg (msgid, topic, qos, payload, arrived)
VALUES (${id}, ${topic}, ${qos}, ${payload}, to_timestamp(${timestamp}::double precision / 1000))

Here we use the placeholder of ${id}, which will be replaced with runtime data when the action is executed.

After the configuration is complete, click OK to complete the addition of the action. Then click the Create button at the bottom of the rule page to complete the rule creation.

After creating MatrixDB resources and rules, we will conduct the test and verification. We directly use the MQTT client tool in Dashboard to publish a message. In this example, we change the message topic to t/1 to hit the rules we set. The Payload and QoS remain unchanged. Then, click publish.

After the message is successfully published, we will be able to see the newly written data in the t_mqtt_msg table: