Use MQTT to Publish Data

                                                                                                            Updated: 8/30/18

One of the key features that makes Informix technology the ideal choice as an IoT database is its built-in support for MQTT protocol. Allowing users to publish data directly via MQTT. This article will show the users how to do it and we will be using the sample code that is available in the GitHub repository - https://github.com/informix/samples-MQTT-python. 

MQTT 

MQTT is a simple, and lightweight publish-subscribe messaging protocol. This document will not get into the details of MQTT, and some prior knowledge of MQTT is assumed. For information about MQTT, please refer to http://mqtt.org 
 
Wire Listener 

Using the wire listener, you can publish data via MQTT to insert data into a table. This can be done with traditional relational tables, collections, TimeSeries tables and through the TimeSeries virtual table interface.   
 
There is a new listener type named mqtt.  Listed below is a sample wire listener properties file to enable mqtt.

Database

Insert into a Relational Table 

The following Python sample code is in the GitHub repository as insMqttRel.py. It shows how to insert data into a relational table. To create the database and the table schema for the following examples, you can run the iot_db.sql from the GitHub repository. The data will be inserted into database below:

Database

This code sets up a callback function on_publish, connects to the wire listener running on the localhost at port 27883, and then inserts 10 rows using an mqtt publish. The first parameter to the publish call passes in the database name:table name.  The second parameter is the data to be inserted, and the third parameter is the quality of service.  The msgstr that gets inserted is a JSON formatted string of data that matches the table column definition.

Insert into a Collection 

To insert into a collection the change to the above code is minor.  We could use the same msgstr or in this example we made a minor change to put all data into its own field, “d”.  The publish is made to a collection within the database.  If the collection does not exist it will be created as is the common behavior with NoSQL databases.

Database

The complete sample code is available in the GitHub repository as insMqttCollection.py 
 

Insert into a TimeSeries Virtual Table Interface 

Inserting into a TimeSeries Virtual Table Interface is similar to inserting into a relational table.  In this example, one of the columns is a BSON column.  This would be similar to inserting into a relational table with a BSON column.  
 
The schema for the vti table (iot_data_v) in this case is

Database

The code for the msgstr and the publish to the iot database and iot_data_v table is:

Database

The complete sample code is available in the GitHub repository as insMqttTS_vti.py 

Insert into a TimeSeries 

Inserting directly into a TimeSeries table has the benefits of using the TimeSeries Loader (TSL).  We will talk more about the TSL in a future blob. There are a few things to be aware of when inserting directly into a TimeSeries table.  The definition of the TimeSeries table is listed below (can be created by running iot_db.sql).  

Database

 You can see below that the msgstr has an id field, a ts field and all other fields will be put into the bson field json_data.

Something to make note of here is how the ts (timestamp) is formatted.  In this example, we use the Unix system timestamp, then convert it to milliseconds, and then insert it as a json using {“$date”: %s}.  This will give you the equivalent of datetime year to fraction(3). 
 
If you want to use a full fraction(5), you would use the currentTimeStr instead of currentTimeInt as shown below.  Note the format of the string with a T between the day and the hour formatting.  

Database

Also notice the difference between inserting directly into the TimeSeries table compared to inserting into the Virtual Table Interface.  With the VTI, we specified the “desc” field.  When inserting through the VTI this gets discarded after the 1st insert, as this part of the data is only stored once.  When inserting directly into the TimeSeries table, if we were to add the “desc” field with some data, it would be stored in the bson column, along with the col1, col2 fields.  So, to properly get data into the TimeSeries, it is best to seed the TimeSeries, or do an initial insert with all data, including the “desc” column. The code sample seed.sql in the GitHub repository shows an example to seed the TimeSeries. Then use this method to insert successive data. In the GitHub repository you can also find multiple Python samples (insMqttTS_#.py) showing the different ways of inserting into a TimeSeries table. 

Darin Tracy
Software Engineer at HCL
Connect with me on LinkedIn

Informix is a trademark of IBM Corporation in at least one jurisdiction and is used under license.