MQTT Broker Tableau Integration for IoT Data Analysis

MQTT Broker Tableau Integration for IoT Data Analysis

MQTT Broker is a perfect middleware and fastest broker to be used for collecting data from your IoT edge devices and store and analyse it the way you needed.  Tableau is a cool BI tool that will help you analyse the data in all dimensions.  MQTT Broker Tableau integration will help you store all your data in the format you needed into the Tableau for a perfect data analysis. This blog helps you visualize data of MQTT Broker with Tableau by connecting it to MySQL or MongoDB.

MQTT Broker Tableau Integration using MySQL

 Changes to be done in MQTT Broker: 

  1. Open the Conf folder(Bevywise/MQTTRoute/conf)
  2. Change the DB Server to MySQL
  3. Give the MySQL Password in “MySQL Password =”
  4. Save the File
  5. Check that the MySQL is running on 3306 Port.
  6. Start the MQTT Broker using sh runbroker.sh inside the bin/ folder.
  7. Next you need to connect the client and verify the  creation of “bevywise” DB in MySQL.

To Verify the DB is Created or not in MySQL:

  1. Login to MySQL by the command “mysql -u<username> -p<password>”
  2. Next type “show databases;” command to view the created DB.
  3. In Output, you can see “bevywise” db is created.

Connect Tableau via MySQL ODBC Connector :

Tableau will use MySQL ODBC connector driver to talk to the MySQL. You might already have the required driver installed on your computer. If the driver is not available in your computer, Tableau displays a message in the connection dialog box with a link to the Driver Download page where you can find driver links and installation instructions. Click the “Data Source” drop down box and Select “MySQL”. Next choose OS & Bit Version and Install the driver.

Make the connection and set up the Tableau:

1.Start Tableau and under Connect, select MySQL. Then do the following:

  • Enter the name of the server that hosts the database.(Give “localhost” if MySQL is running in the same machine)
  • Enter the user name and password.
  • Select Sign In.

2. On the data source page, do the following:

  • From the Database drop-down list, select a database or use the text box to search for a database by name.
  • Under Table, select a table or use the text box to search for a table by name.
  • Drag the table to the canvas, and then select the sheet tab to start your analysis.

MQTT Broker Tableau Integration via MongoDB

Configuring MQTT Broker:

  1. Please download and install MongoDB Plugin for Bevywise MQTT Broker.
  2. Follo the steps in Readme.md file to configure and setup the MongoDB Plugin.
  3. Next Restart the MQTT broker and Connect the device.

To Verify the DB is Created or not in MongoDB:

  1. Login to Mongo DB by the command “mongo –port 27017”
  2. Next type “show dbs” command to view the Created DB.
  3. In Output, you can see “bevywise” db is created

Connecting Tableau via MySQL ODBC Connector :

Tableau use MongoDB Bi connector driver to talk to the MongoDB. You might already have the required driver installed on your computer. If the driver is not installed on your computer, Tableau displays a message in the connection dialog box with a link to the Driver Download page where you can find driver links and installation instructions. Click the “Data Source” drop down box and Select “MongoDB Bi Connector”. Next choose OS & Bit Version and Download the driver.

Creating Authentication for Mongo DB:

Login to the MongoDB shell by the command “mongo –port 27017

Next use the “bevywise” database by the command ” use bevywise
Type the below query to create a username and password for the DB

db.createUser(

{

  user: “<Username>”,

  pwd: “<Password>”,

roles: [ { role: “userAdminAnyDatabase”, db: “bevywise” } ]

  }

)

Re-start and login to the MongoDB by the command

mongo –port 27017 -u “myUserAdmin” -p “abc123” –authenticationDatabase “bevywise”

For more reference refer the Enable Auth link

Installation and Setup of MongoDB Bi connector in windows:

  1. To install the Mongo DB Bi connector, Double click the downloaded .msi Connector file and Install it.
  2. Next you need to check the installation of connector inside the MongoDB folder which is inside the program files.
  3. Follow the link to setup the MongoDB BI Connector.
  4. After Installation, don’t exit from the terminal.

Make the connection and set up the Tableau:

1.Start Tableau and under Connect, select MongoDB BI Connector. For a complete list of data connections, select More under To a Server. Then do the following:

  • Enter the name of the server as “localhost”.
  • Enter your user name with associated database name, and password.
  • Select Sign In.
  • If Tableau can’t make the connection, verify that your credentials are correct. If you still can’t connect, your computer is having trouble locating the server. Contact your network administrator or database administrator.

2. On the data source page, do the following:

  • (Optional) Select the default data source name at the top of the page, and then enter a unique data source name for use in Tableau. For example, use a data source naming convention that helps other users of the data source figure out which data source to connect to.
  • Select the sheet tab to start your analysis.

Download the MQTT Broker now to get started with the MQTT Broker Tableau integration.

download now
Feel free to contact support for any questions or help.

Visualize IOT Data with Elasticsearch

Visualize IOT Data with Elasticsearch

All data collected from the edge devices should be analysed to drive proper decisions. A good tool to visualize IOT Data will help users drive better decisions. Each business want to visualize iot data with their own or comfortable tools as for their convenience.

A complete IOT implementation needs a powerful data processing, user friendly visualization and a quick decision driving notifications. To enable such a complete implementation, MQTT Broker can now talk to any Big data engine to store the data for further processing.  

MQTT Broker & ElasticSearch

We added Elastic Search as a default extension into the MQTT Broker. You can just install the Elastic Search and configure the details in the conf/data_store.conf and the MQTT Broker will start writing all the received data into the Elastic Search engine. You can use The default index as  mqtt and you can change it in the data_store.conf file. And also you can visualize the data using Kibana powerful visualization tool from the Elastic.co. Most tools today can read data from Elastic to show it in a nice user friendly way.

Handling JSON

MQTT Broker will split the JSON to one level and store it in document store as individual columns, so that it will be easy to query and make decisions.

For example, a data from a HVAC Sensor production_room_sensor_4 in the JSON format will look like.

{“Humidity”:”50%” , “Temperature”:”27°C”, “Time”:”Tuesday, 19 Dec 2017 14:22:33″ }

The same will be stored in a more flat structure for easy construction of visualization Queries. If you are using a more nested JSON for data transfer

sender:production_room_sensor_4, “Humidity”:”50%” , “Temperature”:”27°C”, “Time”:”Tuesday, 19 Dec 2017 14:22:33″

Visualize IOT Data with any Platform

MQTT Broker also send the received data to any of your python program for you to process and store into your own data store

# The parameter data will be in dict format and the keys are ‘sender’,’topic’, ‘message’, ‘unixtime’, ‘timestamp’

def handle_Received_Payload(data):
# Write your code here. Use your connection object to
# Send data to your data store

We will be adding more integrations to the MQTT Broker by default. So that it can used as a plug and play with minimal implementation from the development team.

Download and the Try the latest FREE MQTT Broker now.

download now

Contact support for any assistance.