Store MQTT Data from Sensors into SQL Database

Pradeep Singh | 20th Jan 2017

MQTT is a great protocol for Sensors to publish data to their Subscribers. However, when it comes to analytics or reporting, you need historical data. Most MQTT brokers don’t provide any built-in mechanism to save MQTT data into Database. This may be the missing piece in your IoT Project. So, let’s explore how can you save MQTT data from Sensors into a SQL Database.

You can leverage the concept of MQTT Wildcard Topics to write a simple connector that can push MQTT Data into SQL Database. All you need to do is, write a MQTT client with Wildcard Subscription and based on the Message Topic insert the values into a SQL Database. Let’s quickly explore basic MQTT Wildcards –

1. MQTT Wild Cards:

MQTT supports a hierarchical topic namespace. This allows application designers to organize topics to simplify their management. Levels in the hierarchy are delimited by the ‘/’ character, such as “Home/LivingRoom/DHT22/Humidity“.

For subscriptions to these hierarchical Topics, two wildcard characters are supported:

1. Single Level Wild Card +
2. Multi Level Wild Cards #

1.1 Single Level Wild Card (+):

A ‘+’ character represents a single level of the hierarchy and is used between delimiters. For example “home/+/DHT22/Humidity“. Following example shows the Topics filtered with ‘+’

1.2 Multi Level Wildcard (#):

A ‘#’ character represents a complete sub-tree of the hierarchy and thus must be the last character in a subscription topic string, such as “Home/BedRoom/#“. Following example shows the Topics filtered with ‘#’

2. Using MQTT Wildcards to create a DB Agent for MQTT Data:

As explained above Wildcard Topics will allow you to subscribe to multiple MQTT Topics at the same time. When you receive data from any of these Subscribed Topics, you may look into to MQTT Topic field, and based on that you may call an appropriate function to save the information into Database Table.

The following diagram will explain it better –

3. Implementation using Python:

I have implemented this logic to save MQTT Data into DB Tables using Python. If you want, you may download and execute the python scripts to understand it better.

3.1 Prerequisites:

1. Python 2.7
2. SQLite – included with Python, no need to install
3. Python pip – for installing packages (link)
4. Paho MQTT Client – Install it with pip command “pip install paho–mqtt”
5. MQTT Broker – If you don’t have your own, use “iot.eclipse.org“

3.2 Code File:

You can download the Python code samples from GitHub Repository . Download all the files and save them in a folder. Following are four python files that you need to download –

1. initialize_DB_Tables.py  – Execute this file to create “IoT.db” sqlite database file with Tables to store data.
2. mqtt_Publish_Dummy_Data.py – Execute this file to start publishing dummy MQTT Data.
3. mqtt_Listen_Sensor_Data.py – Execute this file to receive MQTT Data transmitted by “mqtt_Publish_Dummy_Data.py” and save that into “IoT.db” file using “store_Sensor_Data_to_DB.py” file.
4. store_Sensor_Data_to_DB.py – No need to execute this file. It will be invoked by “mqtt_Listen_Sensor_Data.py” to save the received MQTT data into “IoT.db” database.

The following diagram shows the relationship between python scripts and data flow direction –

3.3 Quick Demo:

Following is a quick demo with these python scripts –

---

Additional Information (Optional)

---

4. Bottleneck? :

In the case of very high MQTT message volume; using a single MQTT Client subscribed to multiple topics (using Wildcard Topic), may become a bottleneck. In such a situation you may outrun Processing capabilities or Network Bandwidth of the machine hosting this MQTT Client.

4.1 Processing Bottleneck:

If your Wildcard Topic is supposed to deliver very high volume of messages, you may use AMQP Queues to hold the data temporarily. You may consider using any open source AMQP implementations such as RabbitMQ.

Following diagram explains this approach-

4.2 Network Bandwidth Bottleneck:

In case you hit Network Bandwidth bottleneck, you may consider adding additional NIC card to your machine and configure Link Aggregation between the Host with MQTT Client and Network Switch. If you Host or Network switch doesn’t support this, you may have to rework on your Wildcard Topic and use multiple MQTT Clients for different sets of Topics.

5. Duplicated MQTT Messages:

On a network connection with problems related to packet drops and jitter, MQTT Broker and Subscribers may receive the same message multiple times (duplicate messages). To tackle this problem you should use MQTT Quality of Service 2. QoS 2 will guarantees that each message is delivered exactly once. For this approach to work optimally, all the Publishers and Subscribers must use QoS value 2.

 

 

I hope you must have got an idea about saving data from your IoT sensors into the Database. If not, feel free to ask the questions 🙂

---

References:

MQTT.Org

Paho MQTT Client

Mosquitto Test Server

IoT.Eclipse.Org Test Server

MQTT Topics & Best Practices

HiveMQ – The missing piece between MQTT and a SQL database in a M2M landscape