IoT Bytes

Bits and Bytes of IoT

Store MQTT Data from Sensors into SQL Database

Pradeep Singh | 20th Jan 2017

mqtt-to-db

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 ‘+’

singlelevelmqtt

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 ‘#’

multilevelmqtt

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 –

mqtt_sqlitedb

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 pahomqtt
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 Octocat. 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 –

mqtt_to_db_python

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-

bottleneck

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

Advertisements

6 thoughts on “Store MQTT Data from Sensors into SQL Database

  1. Hey Pradeep ,
    Could you please provide the link to the library’s used in you python code?
    Also is there a way to push the data into phpmyadmin?

    Regards,
    Amrita

    Like

  2. On callback “on_connect” just 3 arguments was given…

    def on_connect(mosq, obj, rc)

    The correct is 4:
    def on_connect(mosq, obj, flags, rc):

    Regards,

    Carlos Roberto

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: