Small Standalone Databases for IoT

For many IoT projects you want to historically save your data, however if you’re only saving a small amount of data using a database server such as MySQL can be overkill.

I wanted to come up with a way to save my data and be able to port it between projects and hardware.  My goal was to use Python because it has the best Raspberry Pi support. I looked at some options like:

  • CSV files – simple but filtering and sorting isn’t so easy
  • dBase files – good “old school” approach, however it’s no longer support in Excel and MS Access so viewing the data offline is a little ugly
  • Python Pickle and PickleDB – great for saving game scores but no real sorting
  • SQLite – excellent light weight database

For my light weight Python applications I was really happy with TinyDB. TinyDB offers:

  • a JSON storage interface, so you could easily migrate to MongoDB (or equivalent)
  • pure Python with no dependencies,  (so it runs on all OS’s and versions of Python)
  • supports queries and searches

Getting Started with TinyDB

To install TinyDB :

pip install tinydb

TinyDB is standalone so there are no server components. Your Python code talks directly to one of more JSON files, or you can also create multiple tables in one JSON.

Below is a Python example that create a JSON file with two tables.


# Use TinyDB to create a JSON file with 2 tables
#
from tinydb import TinyDB, Query

db = TinyDB('db2.json')
db.purge_tables() # Clear out the file's old data

table = db.table('Moisture')

table.insert({"date":"2019-05-06","time":"16:00","moisture":200})
table.insert({"date":"2019-05-06","time":"16:10","moisture":210})
table.insert({"date":"2019-05-06","time":"16:20","moisture":220})

table2 = db.table('Light')

table2.insert({"date":"2019-06-06","time":"16:00","light":30})
table2.insert({"date":"2019-06-06","time":"16:10","light":35})
table2.insert({"date":"2019-06-06","time":"16:20","light":36})

db.close()

#
import json

with open('db2.json') as f:
data = json.load(f)

print("JSON file with 2 tables\n")
print(json.dumps(data, indent = 4, sort_keys=True))

The JSON file will have a format like:

json_2tables

A Test Example

For a test example I wanted to simulate a month’s worth of 5 minute data. Then I would try some queries to show the data.

 
# Create a month's worth of 10 minute simulated JSON data
#
# Use a format of:
#    "Pi_1": {
#        "1": {
#            "date": "2019-04-01",
#            "time": "01:00",
#            "moisture": 577,
#            "light": 30
#        }

from tinydb import TinyDB, Query
import random

db = TinyDB('/writing/blog/tinydb/db1.json')
db.purge_tables()
table = db.table('Pi_1')

for iday in range(1,31):
    print("day: ",iday)
    for ihour in range(0,24):
        print("\thour: ", ihour)
        for imin in range(0,56,5):
            thedate = "2019-04-{:02}".format(iday)
            thetime = "{:02}:{:02}".format(ihour, imin)
            table.insert({"date": thedate,"time": thetime ,"moisture": random.randint(200,600),"light":  random.randint(20,100)})


print ("Finished updating JSON file\n\n")
db.close(

Once the JSON data file was created I used a simple Python Tkinter application to query the data by day. My goal was to see how easy it was to query the data and return results. Below is a my TinyDB Tkinter viewing application and code. The bottom slider is used to select the day in the month.

dbtiny_data


# Use a Tkinter 
# Create a dialog that uses TinyDB to query 1 day of data
# from a JSON file
#
from tinydb import TinyDB, Query
import tinydb 
import tkinter as tk

db = TinyDB('db1.json')

# get a selected days data
def sel():
   thedate = "2019-04-{:02}".format(int(var.get()))
   print(thedate)
   table = db.table('Pi_1') 
   data1 = table.search(Query()['date'] == thedate)
   print(data1)
   if data_list.size() > 0:
       data_list.delete(1,data_list.size() -1 )
   for items in data1:
       data_list.insert(data_list.size()+ 1,
                        items['date'] + " " + items['time'] +
                        " -  Moisture : " + str(items['moisture']) +
                        " -  Light : " + str(items['light']) )


root = tk.Tk()
root.title("DBtiny - Daily Data")

var = tk.DoubleVar()
data_list = tk.Listbox(root,height=20, width= 60)
data_list.grid(row=1,column=1)
                     
day_scale = tk.Scale( root,variable = var, from_=1,to=30,orient='horizontal',length=300 ).grid(row=2,column=1)

button1 = tk.Button(root, text="Get Daily Values", command=sel).grid(row=3,column=1)

root.mainloop()

Summary

TinyDB is a good solution for small IoT projects where I’m using Python and I’m not collecting a lot of data.

My next step would be to look at presenting the JSON data in Node-Red.

 

RabbitMQ for IoT

For Internet of Things (IoT) projects that are a lot of different ways that the sensors, devices and client interfaces can be connected together. For many projects using simple MQTT (Message Queue Telemetry Transport) is all that you need. However if you’re trying to merge and build IoT projects that use both MQTT and AMQP (Advanced Message Queue Protocol) or require a REST API then you should take a look at RabbitMQ.

RabbitMQ is an open source middleware solution that natively uses AMQP communications but it has a good selection of plug-ins to support features like: MQTT, MQTT Web Sockets, HTTP REST API and server-to-server communications.

rabbitmq_overview

In this blog we will setting up a RabbitMQ server, and we will look at some of the differences between MQTT and AMQP messaging. Finally an example of an Anduino MQTT message will be presented as both an MQTT and an AMQP item in a Node-Red dashboard.

Getting Started

RabbitMQ can be installed on Window, Linux, MacOS systems and there are also some cloud based offerings. For small systems lower end hardware like a Raspberry Pi can be used.  For complete RabbitMQ installation instructions see: https://www.rabbitmq.com/download.html . To install and run RabbitMQ on a Ubuntu system enter:

sudo apt-get update
sudo apt-get install rabbitmq-server
sudo service rabbitmq-server start

The next step is to add some plug-ins. For my project I loaded the MQTT and Web Administration plug-ins:

sudo rabbitmq-plugins enable rabbitmq_mqtt
sudo rabbitmq-plugins enable rabbitmq_management

The rabbitmqctl command line tool allows you to configure and review the RabbitMQ server. To add a user admin1,  with password admin1, that has config, write and read rights for management and administrator access, enter:

sudo rabbitmqctl add_user admin1 admin1
sudo rabbitmqctl set_permissions -p / admin1 ".*" ".*" ".*"
sudo rabbitmqctl set_user_tags admin1 management administrator

After you’ve defined an administrative user the RabbitMQ web management plug-in can be accessed by: http://ip_address:15672 .

Rabbit_webadmin

The RabbitMQ Web Management tool offers an overview of the present system load, connections, exchanges and queues. 

The RabbitMQ Web Management tool is excellent for small manual changes, if however you are looking a doing a large number of additions or changes then, rabbitmqadmin, the command line management tool can be used. This tool is  installed by:

# Get the cli and make it available to use.
wget http://127.0.0.1:15672/cli/rabbitmqadmin
sudo chmod +x rabbitmqadmin

Comparing MQTT and AMQP

It’s useful to comment about some of the differences between MQTT (Message Queue Telemetry Transport) and AMQP (Advanced Message Queueing Protocol) .

MQTT is a light weight publish-subscribe-based messaging protocol that works well with lower end hardware and limited bandwidth. For Arduino type applications where you only need to pass some sensor data MQTT is an excellent fit.

AMQP has more overhead than MQTT, because it is a more advanced protocol that includes message orientation, queuing, routing, reliability and security. Presently there are no mainstream AMQP Arduino libraries, but numerous programming options for Raspberry Pi, Linux, Windows and MacOS systems exist.  An AMQP IoT example would be to send sensor failures and alarms to dedicated maintenance and alarm queues.

MQTT and AMQP Queues

One of the biggest differences in queues is that MQTT queues are designed to show you the last available message, where as AMQP will store multiple messages in a queue.

A published MQTT message contains a message body, a retain flag and a quality of service (QoS) value.

An AMQP message can be published with added properties such as: time stamp, type of message and expiration information. AMQP messages also support the addition of custom header values. Below is a Python publish example that defines the message type to be “Pi Sensor”, and custom headers are include for status and alarm state.

#!/usr/bin/env python
import pika

node = "192.168.0.121"
user = "pete"
pwd = "pete"

# Connect to a remote AMQP server with a username/password
credentials = pika.PlainCredentials(user, pwd)
connection = pika.BlockingConnection(pika.ConnectionParameters(node,
        5672, '/', credentials))                                    
channel = connection.channel()

# Create a queue if it doesn't already exist
channel.queue_declare(queue='Rasp_1',durable=True)

# Define the properties and publish a message
props = pika.BasicProperties(
    headers= {'status': 'Good Quality',"alarm":"HI"},
    type ="Pi Sensor")
channel.basic_publish(exchange='',
    routing_key='Rasp_1',body='99.5', properties = props)

connection.close()

For this example the Rasp_1 queue can be examined using the Queue->Get Message option in the Web Management Interface. 

q_prop

RabbitMQ Messaging

There are a variety of ways that AMQP messages can be published and subscribed to. The simplest way is to create a queue and then messages can be published and subscribed to from that queue.

rabbitmq_layoutTo help with the distribution and filtering of messages AMQP supports a number of different exchange types. Messages in an exchange use bindings based on a routing key to link them to a queue. 

The main types of exchanges are: direct, fanout, headers and topic. An IoT example of a direct exchange would be if a group of Raspberry Pi sensor values are going into a “Rasp Pi Sensor” exchange. When the Rasp Pi publishes a sensor result to the exchange the message also includes a routing key to link the message to the correct queue.

direct

An IoT example of a fanout exchange would be with critical sensor failures. The sensor failure message is sent to Bill’s and Sam’s work or task queue and the All Maintenance point queue at the same time.

fanout

Connecting MQTT

After the MQTT plug-in is installed RabbitMQ can act like a standalone MQTT broker. MQTT data can also be made available through an AMQP subscription by binding the MQTT exchange to a RabbitMQ queue.

For an MQTT project any ESP8266 supported Arduino hardware can be used. There are a number of MQTT Arduino libraries that are available. For this project I used the PubSubClient that can be installed using the Arduino Library Manager.

As a test project I used at low cost MQ-2 Combustible Gas Sensor ($3) that measures a combination of LPG, Alcohol, Propane, Hydrogen, CO and even methane. Note to fully use this sensor some calibration is required. On the MQ-2 sensor the analog signal is connected to Arduino pin A0 and the analogRead(thePin) function is used to read the sensor value.

MPq2_Setup.png

Below is some example Arduino code required to read the MQ2 gas sensor and publish it to the RabbitMQ MQTT broker with a topic name of : mq2_mqtt.

/*
 Basic ESP8266 MQTT publish client example
*/
#include <ESP8266WiFi.h>
#include <PubSubClient.h>

// Update these with values suitable for your network.
const char* ssid = "your_ssid";
const char* password = "your_password";
const char* mqtt_server = "192.168.0.121"; 
const char* mqtt_user = "admin1";
const char* mqtt_pass= "admin1";

const int mq2pin = A0; //the MQ2 analog input pin

WiFiClient espClient;
PubSubClient client(espClient);

void setup_wifi() {
  // Connecting to a WiFi network
  WiFi.begin(ssid, password);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
}

void reconnect() {
  // Loop until we're reconnected
  Serial.println("In reconnect...");
  while (!client.connected()) {
    Serial.print("Attempting MQTT connection...");
    // Attempt to connect
    if (client.connect("Arduino_Gas", mqtt_user, mqtt_pass)) {
      Serial.println("connected");
    } else {
      Serial.print("failed, rc=");
      Serial.print(client.state());
      Serial.println(" try again in 5 seconds");
      delay(5000);
    }
  }
}

void setup() {
  Serial.begin(9600);
  setup_wifi();
  client.setServer(mqtt_server, 1883);
}

void loop() {
  char msg[8];
  if (!client.connected()) {
    reconnect();
  }

  sprintf(msg,"%i",analogRead(mq2pin));
  client.publish("mq2_mqtt", msg);
  Serial.print("MQ2 gas value:");
  Serial.println(msg);

  delay(5000);
}

Once the MQTT value is published any MQTT client can subscribe to it.  Below is a Python MQTT subscribe example.

# mqtt_sub.py - Python MQTT subscribe example 
#
import paho.mqtt.client as mqtt
 
def on_connect(client, userdata, flags, rc):
    print("Connected to broker")
 
def on_message(client, userdata, message):
    print ("Message received: "  + message.payload)

client = mqtt.Client()
client.username_pw_set("admin1", password='admin1')
client.connect("192.168.0.121", 1883) 

client.on_connect = on_connect       #attach function to callback
client.on_message = on_message       #attach function to callback

client.subscribe("mq2_mqtt") 
client.loop_forever()                 #start the loop

Read MQTT Messages Using AMQP

MQTT clients can subscribe to MQTT messages directly or it’s possible to configure RabbitMQ to have the MQTT data accessible as AMQP. The routing of MQTT messages to AMQP queues is done using the direct exchange method.

mqtt_2_amqp

To configure RabbitMQ to forward MQTT the following steps are done:

  1. Create a new RabbitMQ Queue – For an IoT project this would typically be a 1-to-1 mapping of the MQTT topic to a queue name.
  2. Create a binding between the MQTT Exchange and the Queue – by default all the MQTT topic go into the amq.topic exchange. For MQTT items in this exchange a binding, typically the MQTT topic name, is used as the routing key to the AMQP queue.

These steps can be done in a number of ways : manually, in the RabbitMQ config file, using the rabbitmqadmin command line tool or via a program. Because I was doing this for multiple signals I used rabbitmqadmin tool, and the syntax is:

./rabbitmqadmin declare queue name=mq2_amqp durable=true
./rabbitmqadmin declare binding source=amq.topic destination_type=queue destination=mq2_amqp routing_key=mq2_mqtt

The RabbitMQ Web Admin can be used to verify the exchange to queue binding.

wp_mqtt_2_amqp

 

The CLI tool can also be used to see if there are any values in the queue:

cli_get_queue

Node-Red Dashboard

Node-Red is a visual programming environment that allows users to create applications by dragging and dropping nodes on the screen. Logic flows are then created by connecting the different nodes together.

Node-Red has been preinstalled on Raspbian Jesse since the November 2015. Node-Red can also be installed on Windows, Linux and OSX.  To install and run Node-Red on your specific system see https://nodered.org/docs/getting-started/installation.

To install the AMQP components, select the Manage palette option from the right side of the menu bar. Then search for “AMQP” and install node-red-contrib-amqp. If your installation of Node-Red does not have dashboards installed, search for: node-red-dashboard.

nr_install_amqp

For our Node-Red MQTT and AMQP example we will use a mqtt and a amqp node from the input palette group, and two gauge nodes from the dashboard group.  The complete Node-Red logic for this is done in only 4 nodes!

nr_logic

Nodes are added by dragging and dropped them into the center Flow sheet. Logic is created by making connection wires between inputs and output of a node. After the logic is laid out, double click on each of the nodes to configure their specific properties. You will need to specify the MQTT and AMQP definitions of your RabbitMQ IP address,user rights, MQTT topic and AMQP queue name. You will also need to double click on the gauge nodes to configure the look-and-feel of the web dashboard.

After the logic is complete, hit the Deploy button on the right side of the menu bar to run the logic.  The Node-Red dashboard user interface is accessed by: http://ipaddress:1880/ui. 

For my project I used a number of different MQ sensors and inputs. Below is a picture of Node-Red web dashboard that we created with the same MQTT value being shown natively and as a AMQP queued value.

project

Final Comments

I found that RabbitMQ was easy to install and the Web Administration plug-in along with the rabbitmqadmin command line tool made it very easy to maintain. 

If you’re just looking to show sensor values then a basic MQTT broker might be all you need. However if you’re looking at some future applications like alarm, maintenance or task lists then AMQP exchanges and queue make RabbitMQ an interesting option.

More on RabbitMQ:

RabbitMQ REST API – remote interfacing (Javascript and Python examples)

RabbitMQ connections with Excel

Using QR Codes

The typical UPC-A barcode is visually represented by strips of bars and spaces, that encode a 12-digit number. A QR code (Quick Response Code) is a matrix barcode that can contain up to 4296 characters.

For Pi or Arduino projects QR codes could be used to document what the module is doing, or “if lost please call…”, or web links.

Create Your Own QR Codes

There are a number of different tools available to create your own QR codes. One of the simplest methods is to use Google Charts, and it is called by:

http://chart.apis.google.com?

The important parameters are:
cht=qr – chart type = qr
chs=x – chart size, and
chl= – the data or URL to encode

An example of encoding “Hello World” in a 100×100 QR would be:

http://chart.apis.google.com/chart?chl=Hello+World&chs=100×100&cht=qr

A simple web form that can be used to create QR codes

 <html>  
 <head>  
  <title>Create QR Codes</title>  
 </head>  
 <body>  
 <h1>Create QR Codes</h1>  
 <form action="http://chart.apis.google.com/chart" method="get">  
      Text to embed in QR Code</br>  
      <textarea name="chl" style="height:100px;width:300px;"></textarea>  
      </br>Image Size :</br>   
      <select name="chs">  
           <option value="100x100">100x100</option>  
           <option value="150x150">150x150</option>  
           <option value="200x200">200x200</option>  
           <option value="250x250">250x250</option>  
           <option value="300x300" selected>300x300</option>  
           <option value="350x350">350x350</option>  
           <option value="400x400">400x400</option>  
           <option value="500x500">500x500</option>  
      </select>  
      <input type="hidden" name="cht" value="qr"></br>  
      <p><input type="submit" value="Create QR Code"></p>  
 </form>  
 </body>  
 </html>  

 

html_code

After the image is generated it can be printed, cut to size and then taped to your equipment. If you have a number of Arduino or Pi modules QR codes could be an easy way to determine what is loaded on each module.

Create an Android QR Reader App

MIT’s AppInventer is a great option for Android smart phones and tablets.

For our application we used the following components:

  • 1 Button – to start QR scanning
  • 1 Textbox – to show QR scan results
  • 1 Button – to call a browser if the QR code is a Web link
  • 1 BarcodeScanner – to turn on the camera and process the QR code
  • 1 ActivityStarter – to launch the web browser

screen

The logic starts by defining the camera to be used for the QR scanning, and setting the ActivityStarter.Action to be a browser (android.intent.action.VIEW).

The BarcodeScanner1.AfterScan block puts the decoded QR data into the textbox. If the QR code starts with “http” then the “Open Link” button is enabled.

logic

Once our custom QR reader app is on our device we can start to customize it to our needs. The picture below shows the basic app reading a 100×100 QR code used on an Arduino project. Some future considerations that could be added to this simple app could things like: recording the geographic location of the device or is the data valid.

scan2

QR Codes and the Internet of Things (IoT)

For projects with a lot of sensors or devices QR codes can be helpful to identify and document what each device is used for. If the device is a source of data then a QR code could link to that specific devices data.

The picture below is an example of a solar powered weather device. The ESP8266 based Arduino module uses MQTT to send data back to a Pi node running Node-Red. The QR code on the side of the enclosure has a link to the Node-Red web page.

outside2