MQTT to InfluxDB¶
For storing data from our sensors we like to use
InfluxDB - time-series database.
As a bridge between MQTT and InfluxDB we created a
Service connects to InfluxDB and MQTT broker and by users config subscribe MQTT topics and store data from messages.
Step 1: Install the MQTT to InfluxDB service¶
sudo pip3 install --upgrade mqtt2influxdb
Step 2: Create the
sudo mkdir /etc/hardwario
Step 3: Open the configuration file¶
For text editing, we use nano editor. You can save changes by pressing key combination
Ctrl + O and exit editor by pressing
Ctrl + X.
sudo nano /etc/hardwario/mqtt2influxdb.yml
Step 4: Paste this snippet to the configuration file¶
1mqtt: 2 host: 127.0.0.1 3 port: 1883 4 5influxdb: 6 host: 127.0.0.1 7 port: 8086 8 database: node 9 10points: 11 - measurement: temperature 12 topic: node/+/thermometer/+/temperature 13 fields: 14 15 value: $.payload 16 17 tags: 18 id: $.topic 19 channel: $.topic 20 21 - measurement: relative-humidity 22 topic: node/+/hygrometer/0:4/relative-humidity 23 fields: 24 25 value: $.payload 26 27 tags: 28 id: $.topic 29 30 - measurement: illuminance 31 topic: node/+/lux-meter/0:0/illuminance 32 fields: 33 34 value: $.payload 35 36 tags: 37 id: $.topic 38 39 - measurement: pressure 40 topic: node/+/barometer/0:0/pressure 41 fields: 42 43 value: $.payload 44 45 tags: 46 id: $.topic 47 48 - measurement: co2 49 topic: node/+/co2-meter/-/concentration 50 fields: 51 52 value: $.payload 53 54 tags: 55 id: $.topic 56 57 - measurement: voltage 58 topic: node/+/battery/+/voltage 59 fields: 60 61 value: $.payload 62 63 tags: 64 id: $.topic 65 66 - measurement: button 67 topic: node/+/push-button/+/event-count 68 fields: 69 70 value: $.payload 71 72 tags: 73 id: $.topic 74 channel: $.topic
In the section tags you can use identifiers, e.g.:
tags: room: bedroom
Step 5: Configuration file test¶
mqtt2influxdb -c /etc/hardwario/mqtt2influxdb.yml --test
Step 6: Start the MQTT to InfluxDB service¶
pm2 start `which python3` --name "mqtt2influxdb" -- `which mqtt2influxdb` -c /etc/hardwario/mqtt2influxdb.yml
Step 7: Save the PM2 state (so it will start after reboot)¶
If you want to see temperature records from the database in CSV format, use this command:
influx -database node -execute "select * from temperature;" -format csv
Then you must restart the service when you change the configuration file
pm2 restart mqtt2influxdb
Configuration file structure and possibilities¶
In Step 4 we paste the configuration file, here will be described possibilities in the configuration. In configuration you can use JSONPath. For example in measurement you can identify tag id from MQTT topic by syntax $.topic as you can see in Step 4.
MQTT part of the configuration file is where you define a connection to MQTT broker.
mqtt2influxdb supports secured connection! This section is required.
1mqtt: 2 host: MQTT Broker adress (required) 3 port: MQTT Broker port (required) 4 username: Username to secured MQTT broker (optional) 5 password: Users password to secured MQTT broker (optional) 6 cafile: CA to secured MQTT broker (optional) 7 certfile: Certificate to secured MQTT broker (optional) 8 keyfile: Certificate Key file to secured MQTT broker (optional)
You can define web hooks so data can be posted to your endpoint. This section is optional.
1http: 2 destination: Endpoint url (required) 3 action: Request type (required) 4 username: Username for secured request (optional) 5 password: Password for secured request (optional)
An important part of the config is of course the definition of InfluxDB connection. This section is required.
1influxdb: 2 host: InfluxDB adress (required) 3 port: InfluxDB port (required) 4 database: Database name (required) 5 username: Username to InfluxDB (optional) 6 password: Users password to InfluxDB (optional) 7 ssl: SSL connection (optional)
Decode base64 messages. This section is optional.
1base64decode: 2 source: base64 coded message (required) 3 target: encoded message (required)
Points section is where you define messages you want to store in database. This section is required.
1points: 2 measurement: Measurement name in database (required) 3 topic: Define MQTT topic where messages are posting to (required) 4 httpcontent: Define payload in http request if filled in HTTP chapter (optional) 5fields: 6 value: Value field in InfluxDB (required) 7 type: Variable type (required)
For every measurement you can define specific database name. This field is optional.
database: Specific database to store measurement (optional)