area128.com

Blog

  • BI on a shoestring – an ode to materialised views

    Some background info

    As I may or may not explain on some other post, some other day, I run my home lab on hardware that’s well past the “obsolete” stage and marching full speed towards becoming “vintage”.

    Said lab, hosts all sorts of little services, private and public, including this here, marvellous website as well as a docker instance of BrickSync, a great little piece of software that keeps our BrickOwl and BrickLink stores in sync.

    On a routine search for my lost disk space, I (re)discovered that BrickSync spits out a snapshot of the store inventory in XML format every time an update was triggered. Either by an order or by me. It also saves a copy of every order in XML format.

    All in all, I discovered over 20 GB worth of XML files that contain a nearly full history of my store. A short python time later and I had a POC that could walk through all the files and dump all the data. Now what?

    The failed attempt

    A little voice in the back of my head had been shouting Elastic, which I hadn’t used before. A chat with a friend confirmed the idea. A ton of little files, ingested and turned into useful, searchable information. Exactly the task Elastic was made for.

    So, off I went to set up a stack on my poor little home lab. That’s when I was reminded that Elastic is meant to run on proper server hardware. I kept adding memory, trying to get it to do something useful, and I eventually got to that point with 4 GB of ram.

    Yes, it run and yes, it looked like it was doing something but not much and not fast. Compared to everything else on that server, its footprint looked like Bigfoot was stepped on by a dinosaur, and the spatter was causing OOM kills among the rest of the services.

    Back to the drawing board

    Then I thought my data is already very structured. Do I really get much benefit from using Elastic? How about pushing the data in some database, MariaDB being the prime candidate, and then treating it like the time series it is, maybe using Prometheus and Grafana.

    While researching the subject, the Postgres name kept popping up and I decided to give it a shot. I have a couple of instances running but never developed for it. A quick setup and a quick update to the python scripts and data was loading in the background while I continued researching the Prometheus – Grafana setup.

    As it turns out, Prometheus is itself a database. A Time Series Database to be precise and as it turns out, Postgres also has a time series extension, TimescaleDB. The things you find out if you RTFM first…

    By this point, I already had nearly 50 million records imported and I had already discovered that Grafana can actually pick up data directly from Postgres. I decided to skip the completely unfamiliar to me, Prometheus step, and leave that lesson for another day.

    My thought process in roughly this order:

    • This is SQL. I know this!
    • The Grafana query builder is a bit of a pain. I’ll create views in Postgres.
    • These views are kinda slow. Since I don’t have real time data to worry about, I could cache the view results into a table…
    • Hey look! Materialised views!

    Where were those when I was young? That’s similar to the SQL Server Indexed Views, right? Is that where the idea of cached views came from? This brain could do with some defragmenting and re-indexing…

    What did we achieve?

    What a fantastic thing those materialised views! Aggregate 50 million rows in 2, 3, 4 different ways, base your queries on those and get sub-second responses!

    Refreshing these views takes a few minutes on this beast of a system, but new data is only available a few times a day so daily updates are more than enough. Those will probably be scheduled to run during the night.

    The current setup consists for one Postgres and one Grafana instance, each allowed 512MB of ram. I can get the Grafana container killed if I open a bunch of pages back to back but I have not yet seen the Postgres container die.

    What’s next?

    The TimescaleDB extension might still come in handy. Incremental updates to the materialised views, time buckets and alleged space savings with columnar storage compression are definitely worth checking out.

    For the front end, something lighter than Grafana might be worth exploring. At the moment it feels like I’m using a sledge hammer to present data straight out of views. Maybe something based on Apache ECharts would be enough. A project for when we need to recover server resources.

    Why?

    So what does it do? Well it does exactly what it was meant to do. It provides very clear and valuable insights into the inner workings of an AFOL’s mind. It also highlights other mysteries of the lego world.

    How, for example, when black, white and grey parts only make up a quarter of the store stock, they account for over half of the sales. Or how when over half of the sales are of black, white and grey parts, all the things that people build and share are so colourful…

    Part Count by Color
    Part Count by Color over the last 5 years. X-axis could do with some more formatting
    Parts sales by color

    It does more and it can do even more. My next challenge is to determine what other data can be extracted and visualised, and more importantly, find out in what way is it of any value.

    In any case, finally getting to play with Postgres in a non-superficial way is a win in itself. It seems to have a very slightly larger idle footprint than MariaDB and it has been a very well behaved tenant on this limited server so far.

    Materialised views were a very nice, unexpected little surprise. Apparently Postgres has a full meme worth of such features waiting to be explored. Can’t wait!

  • Raspberry Pi as HDMI-CEC to IR Bridge

    Note: This article was first published in June 2016, in a previous incarnation of this blog and has been rescued using the Way Back Machine.

    The Problem:

    I recently got an IP-TV subscription and the set-top box delivered by my provider does not support HDMI-CEC. The remote control included in the package has limited “smart” functionality – as in, it can be configured to somewhat control your TV – but that’s far from ideal, or functional if your set-up consists of anything more than a TV. In my case the set-up is not that much more complex. It consists of a TV, an AVR receiver and an OpenELEC media player. With the help of a Pulse Eight USB – CEC Adapter, I’ve been able to happily control them all from my TV remote over HDMI-CEC.

    The new addition to the group was causing me a bunch of headaches. Not only did I need the set-top box remote to change channels, but I also needed the AVR remote to select the correct input. Using a second HDMI input on the TV could solve that problem but the ARC channel from the TV to the receiver is not pass-through. My Samsung TV seems to pre-process audio to either PCM or DTS NEO 2:5.

    The Solution:

    Well, the most obvious one would be a Logitech Harmony but where would the fun be in that? Nothing to break open, nothing to tinker with and I would have to keep the set-top box in a visible place to maintain line of sight. An Arduino came to mind (and I may still get back to this idea – more on that further down), but at the time is sounded like a lot of work.

    Then I remembered that the Raspberry Pi HDMI port supports CEC and that while I was setting up the OpenELEC player, I had to set the correct address for the AVR to switch to the right input. That’s when the idea started becoming simple. Connect the Pi to a free HDMI port and make it pretend to be the set-top box. Listen to the CEC commands coming from the TV and translate them to IR commands using LIRC.

    All ingredients were available. The good people of Pulse Eight have made libCEC available, including sample clients and there are a ton of tutorials out there on how to use LIRC to make an IR Remote out of a Raspberry Pi so I got to work.

    The Hardware:

    The transmitter circuit is straight forward. A couple of IR LEDs, a current limiting resistor and an NPN transistor that can be switched at 3.3V. The 2N2222 seems to be the transistor of choice in most circuits I came across but the BC338 that was in my box of loose components works nicely too. Any resistor below 500 Ohms, should be fine. It could even be skipped if the power supply is strong enough to prevent a voltage drop on the 5V line. Of course the range of the transmitter and the lifetime of the LEDs depend on that resistor so pick one that suits your needs. The capacitor is added as extra protection against that voltage drop and again, it can be omitted.

    A piece of scrap strip-board and a quick soldering job and I had a prototype. I didn’t have any IR LEDs around but luckily enough my TV came with an “IR Extender” module which seems to contain two IR LEDs in a nicely polished package with a long wire and a 3.5mm jack.

    If it’s not obvious from the picture, the resistor and capacitor were added after the initial “board design”. On the bench, the circuit worked without them but when I tried powering the Raspberry Pi from the USB port of the set-top box, the current wasn’t enough and the Pi kept rebooting every time I pressed a key on the remote.

    The Software:

    LIRC:

    I started with a standard Raspbian Jessie installation, at least for the purposes of this story. I actually net-boot Raspbian from NFS using u-boot but the result is the same and that’s perhaps a story for another entry. The LIRC installation and configuration is extensively covered online so I will just highlight the key steps. Using a DIY transmitter means that the lirc-rpi driver was needed. This driver creates a virtual device using two of the GPIO pins on the Raspberry Pi. To enable that, I needed to load the lirc-rpi overlay at boot time by adding the following line in config.txt.

    dtoverlay=lirc-rpi

    This defines the hardware for the kernel and allows the lirc-rpi driver to be loaded and the /dev/lirc0 device to be created. The LIRC installation is standard and there are plenty of places where “remote” definitions can be found for the lirc.conf file. I found one for my set-top box on a Gist from proycon. With the original remote and an IR receiver, such a configuration file can also be created with the irrecord utility which is part of the lirc package.

    CEC Client:

    At this point I had working hardware and LIRC configured. I was able to issue commands to the set-top box from the command line using the irsend utility and all I needed was to tap into the incoming HDMI-CEC messages and handle those. libCEC and the utilities that come with it are great. The cec-client made it simple to manually play around with CEC messages until things started making sense. With the help of cec-o-matic, I was then able to create a list of commands that I needed to send and consume in order to bring the TV, AVR and set-top box in sync.

    Sample clients are also provided in different languages. Since I would be coding on the Raspberry Pi over SSH sessions, I opted for python. Now, python is by far not my strongest skill and I kind of got lost trying to get the python libraries working by using the libCEC packages. Something about Swig is mentioned but I didn’t know how exactly to get an API definition using that. I took the next easiest route, I cloned the libCEC repository, followed the instructions and built the library from source. The python client worked straight away.

    There are a couple of things that needed doing to the client to get the functionality I wanted out of it. First of all, I wanted it to handle key pressed events so I went ahead and mapped the key pressed events in KeyPressCallback to irsend commands using a whole list of these:

    if key == 0:
os.system("irsend SEND_ONCE VIP1853 OK")
elif key == 1:
os.system("irsend SEND_ONCE VIP1853 UP")

    For some reason that I didn’t really look into, that callback function handles both key-pressed and key-released events with only the duration to distinguish between them. On top of that, some keys only raise a single event that doesn’t start at duration 0. This may be something done by my TV, libCEC or the protocol itself. In any case, with these conditions at the top of the callback, I was able to filter the events I wanted to handle:

    if duration == 0 or ((key == 150 or key == 145) and duration == 500) or (key == 69 and duration >= 1000):

    The next thing to do was to make the Raspberry Pi announce that it’s connected to the HDMI port that is used by the set-top box. This tricks the TV and the AVR to switch to the “correct” input when I select the Raspberry Pi from the TV’s CEC menu. For that, I needed to override the auto-detected physical address of the client at start-up in the SetConfiguration function:

    self.cecconfig.iPhysicalAddress = 0x2200

    0x2200 corresponds to the DVD input of my AVR (2.2.0.0), where the set-top box is connected and that was it! The client did 99% of what I wanted so I went ahead and created a daemon start-up script for it so that it starts and runs in the background at boot time. The start-up script needs to be copied to /etc/init.d/ and scheduled by running:

    sudo update-rc.d cecClient.sh defaults

    The Result:

    The remaining 1% consisted of three problems. First, the python client wouldn’t stay running because the main loop was expecting input from the console for its standard functionality. Since I’m running the client as a service, that wasn’t needed any more and commenting out that raw_input line solved the issue.

    METADATA-START

    The second was that by default, libCEC tries to power up my AVR when I switch to the Raspberry Pi. For some reason, it’s unable to get the correct power status from the AVR and issues a POWER ON command which more often than not actually puts my AVR on stand-by. My TV also seems to have trouble handling the power status of the AVR correctly so I’m suspecting Pioneer’s CEC implementation may be the issue. I was able to clear the automatically-filled device list of wakeDevices but I guess I’m doing it at the wrong place because it still happens once after the client starts. Since I’m not rebooting the Pi, this is good enough for me.

    The last problem was power control of the set-top box. Since communication is only possible in one direction, I can’t check if the set-top box is powered on or gone to stand-by mode. I can’t even check the status of the HDMI port for that because when on stand-by, the set-top box goes into screensaver mode and the HDMI port remains active. To work around this, I mapped the IR POWER button to the unused CEC Tools menu option and now power can be controlled by a couple of button presses.

    With all that in place, I’m now again able to hide away all other remote controls and control everything from a single one. Without line-of-sight needed, the set-top box could also join the rest of the cable mess in the drawer under the TV and stay out of sight.

    I have uploaded the LIRC configuration files as well as the modified python client and daemon script on Github for anyone interested in trying this out.

    A warning for the adventurous: the GPIO port on the Raspberry Pi is as sensitive to over-voltage as everyone claims. Even a charged capacitor is enough to damage the BCM2708 SOC. I found out the hard way by plugging in the board on the Pi the wrong way around.

    Further Ideas:

    Inspired by the Pulse Eight USB – CEC Adapter, I am considering an Adruino based version of this, using the same pass-through trick so that an extra HDMI port isn’t occupied. With a custom Arduino implementation, such a device could be used to emulate and control multiple non-CEC capable devices, be instantly on and consume around 50mA instead of the Pi’s 500(ish). Libraries are already available for both CEC and IR, there are examples of the circuit needed to interface with CEC and a prototype interface can be done using half of a standard HDMI cable. The discouraging part is having to do programming and debugging in front of the TV. It may be an idea for a Kickstarter project or for Pulse Eight to introduce as a new product.