The MongoDB Statbox - Graphs of stats in 5cm by 6cm

Using just a Pi Zero, a Unicorn Hat, a slice of Python and a plug-in of Wifi, find out how you can turn you database stats into shining beacons of information in the return of the TinkerTank.

Back in the days when Compose was called something else, we also had the TinkerTank where we brought the world of making and the world of databases together to make some cool gadgetry. The last project, MongoDB in Lights lit up RGB LEDs on an Arduino shield. But there was an issue with that project... it needed a PC or Mac to hand to act as a proxy, gathering and preparing statistics. Those statistics were gathered over a serial connection and displayed in lights. A year and a half on and we thought we must be able to do better than that. So let's start with the hardware of the MongoDB Statbox....

The video is sped up so you don't have to wait so long to see the lights changing. If you can't play the video, it looks like this:

Well, you can't really see the brains like that so let's pull the top off...

If you can already tell what's needed there skip to the Software section further on. Otherwise read on:

Hardware and OS setup

We decided to build this using the $5 Raspberry Pi Zero, partly because it's so cheap and small and partly because if anyone else wants to build their own, they can use any other recent Raspberry Pi model (A+, B+, Pi 2) as well. The RGB LEDs use the Pimoroni Unicorn Hat available in the US through AdaFruit which has a Python library to support it. The Pi Zero keeps its cost down by not having any header pins on the board, so until someone starts shipping ready-headered Pi Zeros, you'll need to solder in a 40x2 pin header onto the board. The other bits needed are a USB to OTG cable, a WiFi dongle, a microSD card of 8GB or more and a power supply (a good one with 2amps out - those LEDs eat power).

We aren't going to run through the process of setting up the Pi Zero. You'll need to get the latest copy of Raspian Linux, Jessie, from the Raspberry Pi site. For setting up, you'll also want a keyboard and display initially - you'll need to plug them in and follow their guide to get it onto the SD card ready to boot. To attach the keyboard and display to the Pi Zero you'll need a USB-OTG hub as it lacks the USB ports of it's bigger siblings. Now, with microSD card installed, you can boot up the device, log in and use raspi-config to expand the filesystem, set a hostname, turn on SSH access and turn off booting into the X desktop. You'll also want to attach it to your WiFi network. There's various guides but the TL;DR version for a typical network is, as root, edit /etc/wpa_supplicant/wpa_supplicant.conf and append:

 network={
        ssid="YourWiFiSSID"
        psk="YourPassword"
        key_mgmt=WPA-PSK
}

With the obvious substitutions made, of course. If the WiFi dongle is plugged in too, reboot to test it.

Once the Wifi is working, disassemble everything. Plug the Unicorn Hat into the Pi Zero, plug the WiFi adapter into the USB-OTG cable and plug that into the USB port. Now plug the power into the Pi Zero and after a minute or so you should be able to, on a machine on the same WiFi network, run ssh pi@yourhostname.local and get connected. If you are running Windows, you won't be able to use mDNS, the technology behind .local addresses, by default. The simplest way to activate it is to install iTunes for Windows – just install it, you don't have to use it – and that will add mDNS support for you.

We now have a rather lovely little device. A full Linux system which you can remotely log into over WiFi with SSH. You may already be brimming with ideas on how to use that little configuration, but for us... we've got a database to query and lights to illuminate so let's move on to...

Software setup

We're now going to create a database connection, read a statistic from it and turn it into a rolling graph. And we'll be doing this in Python, mainly because that's what the API for the Unicorn hat supports. Talking about Unicorn Hat support, you'll find the full instructions for installing that on its Github page - the short version of which is run:

curl -sS get.pimoroni.com/unicornhat | bash  

Which will set up the libraries and the demos. After you've finished playing with the demos (Unicorn paint is quite fun), we can get back to our task, the next step of which is to connect to MongoDB. For this we'll need Pymongo so either pip install pymongo (for Python 2.7) or pip3 install pymongo (for Python 3.x). We can start building out code now. There's a finished version of this code, statbox.py, up in the Compose examples repository but we'll step through and explain it here as we build it up. The first step is to import us some libraries:

import pymongo  
import unicornhat as unicorn  
import sys,ssl,time  

Now we want to connect to MongoDB...

MongoDB connections

The statistic we've chosen to track is the number of current connections. We could have gone for something simpler taken from a collection query or aggregation and nothing stops you from doing just that when you decide to implement this. Our choice makes life a little more interesting here as we need to have an admin privileged account to see the server statistics. For this reason we're going to connect to one of Compose's MongoDB+ deployments. The older elastic deployments of MongoDB at Compose don't have the ability to give MongoDB users admin privileges. But we do have to make an SSL connection to the MongoDB+ system and remember to create an admin user with admin privileges. Then we can write some code like this:

connecturl = "mongodb://user:password@host1.example.com:port,host2.example.com:port2/admin?ssl=true"  
client = pymongo.MongoClient(connecturl,ssl_ca_certs="./cacert.pem",ssl_cert_reqs=ssl.CERT_OPTIONAL)  

The connecturl string comes from the Compose console's overview, under Connection Strings for the admin database of the deployment we're using. You can tell that because it ends "/admin?ssl=true". We use that URL to connect in the next line, but there's a couple of extra parameters we need to specify. We need the public TLS certificate for the server saved to a file named cacert.pem first. Compose users will find that on the overview page for their MongoDB+ deployment too. Once we have that file we can tell the PyMongo library to use that with ssl_ca_certs="./cacert.pem" and to ensure it just verifies the self-signed certificate, we add ssl_cert_reqs=ssl.CERT_OPTIONAL (which is the only reason we imported the ssl library). We're light on error checking here as this is just an example, so let's assume that all works.

We'll set up some colors:

 red=[255,0,0]
 green=[0,255,0]
 blue=[0,0,255]
 black=[0,0,0]
 white=[255,255,255]

And then initialize two arrays for the values and colours of our data. We'll also set our current maximum value for that data:

maxval=8  
values=[ 0, 0, 0, 0, 0, 0, 0, 0 ]  
colors=[ black, black, black, black, black, black, black, black]  

Time to initialize those LEDs and the first thing to do here is set the brightness to something minimal. These can be some very bright LEDs and unless you want people who see what you have made staggering away emoting "The goggles, they do nothing!", you need to set the brightness low. Also, you may install this device in a box at some point and discover things are not as correctly oriented as you'd hoped. Uncomment the rotation line and rotate things in software rather than disassembling them:

unicorn.brightness(0.10)  
#unicorn.rotation(90)

Now we can dive into a loop to start updating the display:

while True:  
  conncounts = client["admin"].command({"serverStatus":1})["connections"]
  newval=conncounts["current"]

We use a serverStatus command on the server. It's packed full of server information, but what we're after is the connections section. That has the current, maximum and lifetime counts for connections. We just extract the current value as newval. The next question for us is what colour is that column going to be. If we retrieve the last value and compare newval with it we could say if it's unchanged from the last value, make it blue, if it's higher, make it red for rising, if it's lower, make it green for um... going down. Yes, we could:

  oldval=values[7]
  if oldval==newval:
    newcol=blue
  elif oldval<newval:
    newcol=red
  elif oldval>newval:
    newcol=green

Then, we can see if we still have enough space for the newest value by comparing it with the maximum value. That maximum starts off at 8, representing the 8 single pixels. If the new value is greater than the maximum value, the latter is doubled until that's not the case. If that wasn't needed then in case things have been over-scaled, the whole set of current values are checked to see if they are all below half the maximum value. If they are, it takes the maximum value down. This little process ensures that as much of the display is showing information as possible. As the color coding makes rises and falls visible it will show trends even when the 8x8 LED resolution isn't up to it:

  if newval>maxval:
    while newval>maxval:
      maxval=maxval*2
      newcol=white
  elif maxval>8:
    midval=maxval/2
    scaledown=True
    for i in range(0,8):
      if values[i]>midval:
        scaledown=False
    if scaledown:
      maxval=midval
      newcol=white

With the new values worked out, we can push them into our list of displayable values and discard the oldest values:

  values.pop(0)
  colors.pop(0)
  values.append(newval)
  colors.append(newcol)

#  print(values)

For demonstration and validation purposes, we can print the values here. Uncomment as needed.

Now comes the main show, lighting the pixels. First we calculate a fudge factor for our bars – this is the maximum value divided by 8. Then we scan through the columns, taking the value, dividing it by the fudge and then drawing that many pixels in the column's colour and filling in the rest with black:

  fudge=maxval/8

  for x in range(0,8):
    val=values[x]
    col=colors[x]
    adjval=int(val/fudge)
    for y in range(0,adjval):
      unicorn.set_pixel(x,y,col[0],col[1],col[2])
    if adjval<8:
      for y in range(adjval,8):
        unicorn.set_pixel(x,y,0,0,0)

And with everything set up and drawn, there's only one last thing to do before going to sleep for five seconds.... show the world your work:

  unicorn.show()
  time.sleep(5)

And everything keeps happily looping, the graph keeps moving, marking where it rescaled in white, showing trending up and down even with high numbers.

Up and running

To run our statbox.py we need to give it root privileges. Because of reasons. Well, most I/O is locked down on the Pi is why. It can be unlocked but for most purposes it is easy enough to just run sudo python3 statbox.py and see how things light up.

The final stage would be to make the program run as soon as you powered up the Pi. There's a couple of things you need to do for this to work. First run raspi-config and select "4 Wait for Network at Boot", then select the "Slow" option. The side effect of this is that the /etc/rc.local file will run after, instead of alongside, the boot process. We can now edit the /etc/rc.local file and before the exit 0 at the end insert:

python3 /home/pi/statbox/statbox.py &  

The rc.local file is run as root so we don't need to sudo, but we do want to make one change as it won't be running in our application's directory. This means we need to use an absolute path to our security certificate. Change the line where we connect to MongoDB to:

client = pymongo.MongoClient(connecturl,ssl_ca_certs="/home/pi/statbox/cacert.pem",ssl_cert_reqs=ssl.CERT_OPTIONAL)  

Reboot and you should have everything running automatically.

What Next?

There's lots of possibilities for this device. There's more than enough power to run a web server so you could make it on-the-fly configurable. Or you could make it look more finished with a custom built box. For us, this is a proof-of-concept that you can make powerful desktop devices that are able to acquire database data on their own. The next step? Keep following here for the next Compose TinkerTank – we might surprise you.