User Community for HCL Informix
  • Home
  • Blogs
  • Forum
  • About
  • Contact
  • Resources
  • Events

Use MQTT to Publish Data

2/15/2018

1 Comment

 
Picture
                                                                                                            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

    
Picture
Picture
Picture
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.
Picture
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. 

Picture
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.
1 Comment
best resume writer service link
4/5/2020 04:36:25 am

I know that I am not really familiar with everything that has something to do with Information Technology, that's why I am not really exploring it that much. But still, I am looking forward to learn it that's why I am here on your website and I am trying my luck to know it even better. There are terms that are not familiar with me, that's why I want to grab this chance to ask you those questions. I am really eager to know it right now, that's why I will be reading more of this!

Reply



Leave a Reply.

    Archives

    November 2019
    September 2019
    May 2019
    April 2019
    February 2019
    January 2019
    October 2018
    July 2018
    April 2018
    March 2018
    February 2018
    January 2018
    December 2017
    November 2017
    October 2017
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017

    Categories

    All
    Business
    Technical

    RSS Feed

Proudly powered by Weebly
  • Home
  • Blogs
  • Forum
  • About
  • Contact
  • Resources
  • Events