Send Arduino data via MQTT to plot in Grafana

Logging data is super fun. I wanted to log data I collected on an Arduino MKR WIFI 1010 and I wanted to visualize the data through a service I could check online from wherever. I chose MQTT for local storage and Grafana Cloud for the web visualization. I talked more about the actual data collection in a previous project highlight. This how-to requires an Arduino able to establish a WIFI connection.

I'm doing this specifically to log weather data with a BME280, but this same approach should work for other sensors too. I talk about setting up everything on my website, and I have been storing my code in a github repository. Here I will walk through the steps of how I configured everything to send my data to Grafana Cloud. I was initially inspired by this how-to.

TL;DR Install MQTT and Prometheus on the device you want to send data to, create a free Grafana Cloud account, go to this github repository and configure the code and logging services to send your Arduino data to a local MQTT server and then Grafana Cloud.

Part 1: Arduino MQTT Setup

First we need to send data from the Arduino over MQTT to another device running MQTT to receive data. There is a good Arduino how-to on this for an Uno WIFI Rev 2. Start by including the client and what is necessary to connect to WIFI, for me this looks like:

#include "PubSubClient.h" 
#include <WiFiNINA.h>

Now set up the Arduino to send data at the start of your.ino file by modifying the code below, and define "topics" which will tag your data.

 // MQTT properties
const char* mqtt_server = "192.168.50.100";  // IP address of the MQTT broker
const char* temp_topic = "outdoor/weather/temperature";
const char* humid_topic = "outdoor/weather/humidity";
const char* pressure_topic = "outdoor/weather/pressure";
const char* altitude_topic = "outdoor/weather/altitude";
const char* distance_topic = "outdoor/weather/distance";
const char* mqtt_username = MQTT_user; // MQTT username
const char* mqtt_password = MQTT_pass; // MQTT password
const char* clientID = "arduino"; // MQTT client ID

After the Arduino is connected to WIFI, we connect to the MQTT server specified above:

WiFiClient wifiClient;
// Connect to the computer running MQTT, 
// it is listening on port 1883 
PubSubClient client(mqtt_server, 1883, wifiClient);

Now we define some routines that are used to start WIFI, and connect to the MQTT server.

void connect_MQTT(){
 Serial.print("Connecting to ");
 Serial.println(ssid);
 WiFi.begin(ssid, pass);
 // Wait until the connection has been confirmed before continuing
 while (WiFi.status() != WL_CONNECTED) {
   delay(500);
   Serial.print(".");
 }
 // Debugging - Output the IP Address
 Serial.println("WiFi connected");
 Serial.print("IP address: ");
 Serial.println(WiFi.localIP());
 // Connect to MQTT Broker
 // client.connect returns a boolean value to let us know if the connection was successful.
 if (client.connect(clientID, mqtt_username, mqtt_password)) {
   Serial.println("Connected to MQTT Broker!");
 }
 else {
   Serial.println("Connection to MQTT Broker failed...");
 }
}

In the main loop, connect to the MQTT server and send the collected data. The sent data has to be a string.

connect_MQTT();
 delay(10); // This delay ensures that client.publish doesn't clash with the client.connect call
 // Publish all data to the MQTT Broker
 if (client.publish(temp_topic, String(bmeData[0]).c_str())) {
   delay(10); // This delay ensures that BME data upload is all good
   client.publish(humid_topic, String(bmeData[3]).c_str());
   delay(10); // This delay ensures that BME data upload is all good
   client.publish(altitude_topic, String(bmeData[2]).c_str());
   delay(10); // This delay ensures that BME data upload is all good
   client.publish(pressure_topic, String(bmeData[1]).c_str());
   delay(10);
   client.publish(distance_topic, String(US100_distance).c_str());
   delay(10);
   Serial.println("Weather data sent!");
 }
 // client.publish will return a boolean value depending on whether it succeeded or not.
 // If the message failed to send, we will go to sleep, and try again in 5 minutes
 else {
   Serial.println("Weather data failed to send. Going to sleep and will just keep trying.");
 } 

Disconnect and turn off the WIFI now that the data has been sent over MQTT. This might be unnecessary, I want to save power for my application.

client.disconnect();  // disconnect from the MQTT broker
WiFi.end(); //turn off wifi before sleep 

Part 2: MQTT Server Setup

We need to send the data somewhere. I am using an always-on raspberrypi for a MQTT server with extra storage for the data log, but I imagine this works for other Linux machines and likely Mac and Windows too. Here I am going to use Unix.

First, we need to install MQTT and Prometheus:

• Install MQTT on the device you want to send data to, I used mosquitto

• Install Prometheus on the same device

Make sure MQTT and Prometheus clients are running. I could add MQTT to easily always run on startup, but Prometheus is appears tricky. For now I am manually starting the Prometheus client if the raspberrypi is reset for some reason.

With Prometheus and MQTT running on a device, the Arduino can now send data to the device, success!

We need to tell the device to listen for data with MQTT and forward the data to Prometheus, then Grafana Cloud will plot the Prometheus data.

On your machine that is the MQTT server, we can set up the data to be forwarded to Prometheus. This is where python code and this example comes in.

I chose to do this in python, since I quickly found an MQTT and Prometheus client for it and I could make an sqlite table to locally store the data, but I imagine there are many programming language options out there.

To get the python code working, first install the paho mqtt client and the python Prometheus client. Now we can start to store and send the data in a python script. I want a local copy of my data so I am writing the MQTT data to an SQLite table on a large disk connected to my raspberrypi. This way I will have a local log of my data that is independent of the retention period for my data on Grafana Cloud. You could probably run Grafana locally and not worry about data retention but for now I prefer a permanent copy to disk.

Now I will go through the python code that logs and sends the MQTT data (this is getWeatherData.py in my github for reference but feel free to make your own file). First, include the libraries: (I am using dotenv to store parameters in a.env file specific to my configuration, this is optional)

import paho.mqtt.client as mqtt
from prometheus_client import start_http_server, Summary, Gauge
import time
import datetime
import sqlite3
import os
from dotenv import load_dotenv
from sqlite3 import Error
load_dotenv()

Now setup the Prometheus data to be sent to Grafana Cloud. I have four gauge variables for tracking weather data timeseries.

############ modify this for your prometheus variables #############
# There are different types of variables accepted by prometheus,
# since these are all point measurements in time, Gauge is the right choice
temp = Gauge('temperature', 'Weather Station Temperature')
hum = Gauge('humidity', 'Weather Station Humidity')
alt = Gauge('altitude', 'Weather Station Altitude')
pres = Gauge('pressure', 'Weather Station Pressure')
#####################################################################

Then configure the MQTT parameters to get the data coming from the Arduino.

############ modify this for your mqtt config ##############
MQTT_ADDRESS = os.environ.get("MQTT_ADDRESS") 
MQTT_USER = os.environ.get("MQTT_USER") 
MQTT_PASSWORD = os.environ.get("MQTT_PASSWORD") 
MQTT_TOPIC = 'outdoor/weather/temperature'
MQTT_REGEX = 'home/([^/]+)/([^/]+)'
MQTT_CLIENT_ID = 'raspberrypi'
########################################################  

Skipping around, I am going to talk about the "main" program which looks like:

def main():
   # Start the Prometheus server
   start_http_server(8000)
   # Setup the SQLlite database
   databasePath = setup_database()
   createTable(databasePath)
   # Start mqtt client
   mqtt_client = mqtt.Client(MQTT_CLIENT_ID)
   mqtt_client.username_pw_set(MQTT_USER, MQTT_PASSWORD)
   # Specify what programs to call when mqtt conditions are met
   mqtt_client.on_connect = on_connect
   mqtt_client.on_message = on_message
   # Setup mqtt on a port
   mqtt_client.connect(MQTT_ADDRESS, 1883)
   # Keep running forever
   mqtt_client.loop_forever()
if __name__ == '__main__':
   main()

So when the program starts, main is called then it starts Prometheus, creates the SQLite database, starts listening for MQTT data and then loops forever. I'm going to start with the MQTT code.

First the routine on_connect is run when the Arduino connects to MQTT:

def on_connect(client, userdata, flags, rc):
   """ Run the following when a client connects"""
   # There are various mqtt connect codes, only needed if debugging
   print('Connected with result code ' + str(rc)) 
   # Subscribe mqtt to the following variables
   client.subscribe([('outdoor/weather/temperature',1),('outdoor/weather/humidity',1),
                     ('outdoor/weather/altitude',1),('outdoor/weather/pressure',1)])

This routine just subscribes to the Arduino data that was set earlier. Then when data is received via MQTT, on_message is run:

def on_message(client, userdata, msg):
   """ Run the following command when a MQTT message is received""" 
   process_request(msg)

Which calls a routine to process the message, this is the important part of the code:

 def process_request(msg):
   """A function to read the published data over mqtt."""
   timeVal = datetime.datetime.now()
   # Print the timestep to make sure it is working now 
   print("Current Time:",datetime.datetime.now())
   # Print the message 
   msgStr = str(msg.payload)
   goodMsg = msgStr[2:-1]
   print(msg.topic + ' ' + goodMsg)
   # Make sure we associate prometheus logs with the correct mqtt variable
   # This publishes the mqtt variables to a prometheus gauge  
   # Also insert the data into the SQLite table 
   if msg.topic ==  'outdoor/weather/temperature':
       temp.set(msg.payload)
       sqlMsg = (str(timeVal),str(goodMsg),None,None,None);
       insert_database(sqlMsg)
   elif msg.topic == 'outdoor/weather/humidity':
       hum.set(msg.payload)
       sqlMsg = (str(timeVal),None,str(goodMsg),None,None);
       insert_database(sqlMsg)
   elif msg.topic == 'outdoor/weather/altitude':
       alt.set(msg.payload)
       sqlMsg = (str(timeVal),None,None,None,str(goodMsg));
       insert_database(sqlMsg)
   elif msg.topic == 'outdoor/weather/pressure':
       pres.set(msg.payload)
       sqlMsg = (str(timeVal),None,None,str(goodMsg),None);
       insert_database(sqlMsg)
   elif msg.topic == 'outdoor/weather/distance':
       dist.set(msg.payload)
   else:
       print('Incorrect topic')

I do some string formatting here, but in the end I go through the topics sent by the Arduino over MQTT and correspondingly send the data to the right Prometheus gauge. Each MQTT message has a topic and a payload, the topic is the tag we made on the Arduino, and the payload is the actual value. By checking the topics of each message, I use the.set command to send data to the Prometheus gauges that were defined earlier and I insert the data into the right columns in the sqlite table.

Finally, I am doing the optional step of recording the data to an sqlite table with the following:

def createTable(databasePath):
   """Make the SQLite table if it doesn't exist"""
   sql_create_weatherData_table = 'CREATE TABLE IF NOT EXISTS weatherData (id integer PRIMARY KEY,timedat text NOT NULL, temperature text, humidity text, pressure text, altitude text);'
   conn = setup_database()
   # create table
   if conn is not None:
       c = conn.cursor()
       c.execute(sql_create_weatherData_table)
   else:
       print("Error! cannot create the database connection.")
def setup_database():
   """Set up the database for storing the data sent by mqtt"""
   databasePath =  os.environ.get("SQL_PATH")
   databasePath = str(databasePath)+"/mqtt.sqlite"
   conn = None
   try:
       conn = sqlite3.connect(databasePath)
   except Error as e:
       print(e)
   return conn
def insert_database(sqlMsg):
   """Save the weather data into a database"""
   databasePath =  os.environ.get("SQL_PATH")
   databasePath = str(databasePath)+"/mqtt.sqlite"
   conn = sqlite3.connect(databasePath)
   sql  ='INSERT INTO weatherData (timedat, temperature, humidity, pressure, altitude) values(?,?,?,?,?)'
   cur = conn.cursor()
   cur.execute(sql, sqlMsg) 
   conn.commit()

These routines are called in main() and process_request(msg) to safely create or connect to a local database and then store the mqtt data.

Now we are getting MQTT data and sending it as a Prometheus gauge.

Part 3: Configure Grafana

The hard part is done, now we just need to tell Grafana Cloud data is being sent.

To send Prometheus data to Grafana Cloud, create a free Grafana Cloud account to receive the logged data, and configure it to receive data. To do this, go to the Prometheus option in your Grafana stack and note the configuration options (remote_write, user name, password) for sending data.

In the Prometheus install files (this is in the unzipped install files for me), you need to configure prometheus.yml to send data to Grafana Cloud via remote_write.

Once that is done and prometheus and mqtt are running, run your python code. For me this is typing python3 getWeatherData.py in the Unix terminal in the working directory where the code is. Now you should start to see MQTTdata being printed out in this terminal window, the Arduino is sending the sensor data over MQTT! Hopefully Prometheus is sending data to your Grafana Cloud account too.

Now we setup the Grafana Cloud data to visualize your data in a dashboard you can access from anywhere after logging in. I did this by logging into Grafana and I went to create a dashboard, add an empty panel, and then you should be able to find your variables labelled as gauges in the python code from earlier under Prometheus as a data source and under metrics.

Now you can create a dashboard like the one I have for weather data:

Success!