Pipe Dreams: Fun with Azure and IoT

On December 11-12, 2014, I participated in a hackathon in Miami with some other evangelists across Microsoft. The goal was to build something cool for Azure and/or Windows 10.  I teamed up with three Davids: David Crook, David Giard, and Dave Voyles.

Our idea

We wanted to build something that had real-life application.  David Crook and I had been working together looking at big data topics and projects for a potential Microsoft Virtual Academy show.  David looped me in with some folks at Microsoft who work with oil & gas companies, and we started to investigate that domain. 

In the oil & gas industry, tampering with pipelines is a big concern.  With the rising cost of these natural resources, people sometimes attempt to siphon off oil/gas from a pipe.  At best, this is stealing, and at worst, it can cause pressure changes that lead to explosions.  For our project, we decided to make an application that monitors pipelines and could tell us when there was a big change in flow rate.  Of course, we weren’t working with real pipelines at a hackathon, so we use a light sensor to simulate flow rate, and when you covered the light sensor to make it dark, that was an unsafe drop in flow.  It should work in theory, right?  :) 

We also wanted to play with both Azure and Windows if possible, so the idea of doing a project in which some IoT sensors generated data, which was pushed into Azure and processed, and then displayed in a Windows front-end application, was compelling.  (Although we ended up with a website frontend.) 

Our problem statement/description is articulated very nicely by David Giard at http://www.viddler.com/v/2b62ce4e


Our architecture and code

Here is an early architecture diagram, scribbled on the paper from the hotel.  :) 

Early Architecture Diagram for Pipe Dreams

The final architecture was a little simpler than that.   

Pipe Dreams Architecture

Our team got an end-to-end scenario working at our DX internal hackathon.  We are taking data from an Arduino with weather shield and Raspberry Pi gateway setup and passing it into an Event Hub, then using Stream Analytics to filter it into a SQL Azure database.  We are sending 5 streams to represent 5 different sensors: 1 sensor is from the Raspberry Pi and Arduino, and the other 4 are sending random sample data from a console app.  We can cover the light sensor on the Arduino to change values and simulate a drop in pipe flow pressure.   

Here is an example of the data set that the Arduino would generate every second:

{"millis":22212,"seqno":21,"winddir":-1,"windspeedmph":-1.0,"windgustmph":0.0,"windgustdir":0, "windspdmph_avg2m":0.7,"winddir_avg2m":0,"windgustmph_10m":4.8,"windgustdir_10m":-1,"hmdt":48.5, "temp":73.4,"tempH":24.0,"rainin":0.73,"dailyrainin":0.73,"prss":101717.75,"batt":4.34,"lght":0.34}

The final step is to visualize this data in SQL Azure.  Since the Windows 10 APIs weren’t quite ready yet, we ended up displaying our data in a website frontend instead.  We created an Azure website at http://pipedreams.azurewebsites.net/.  Below is a screenshot.  There is a nice animation each time the data updates. 

Pipe Dreams front end screenshot

David Crook came up with our app idea and wrote the sample data generator.  Dave Voyles developed our beautiful front end.  David Giard worked on the web service, mobile service, and SQL.  I did the IoT work with the Arduino and Raspberry Pi, and (with David Crook) wrote the logic to push the data through Azure from Event Hub to Stream Analytics to SQL Azure.  

The IoT work specifically was a great learning opportunity for me, since I’m a noob in the IoT space.  We used an Arduino board with a weather shield and a Raspberry Pi to send weather-type data (temperature, amount of light, wind, etc.) to an Event Hub in Azure.  A big help in learning was the ConnectTheDots project.  There are more resources on ConnectTheDots at the end of this post. 

You can access our code at https://dpeted.visualstudio.com/DefaultCollection/Field%20Hacks/_git/PipeDreams

Gotchas and lessons learned

The biggest issue was that we had a huge data pipeline, and we occasionally wouldn’t see data arriving in the SQL Azure database at the end, so we would need to figure out what step wasn’t working.  Here are some strategies I used for troubleshooting:

· For troubleshooting the Arduino and Raspberry Pi, there are some good “Troubleshooting Tips” at the bottom of https://github.com/MSOpenTech/connectthedots/wiki/Walkthrough-of-the-code.  Specifically, I used “tail /home/RaspberryPiGateway/logs” to check the diagnostics logs on the Raspberry Pi. 

· Dashboard view of Event Hub.  On the old Azure portal, click on “Service Bus”, then click on your specific service bus namespace.  Click “Event Hubs” and then click on your specific event hub.  This is the dashboard view; you can also get back to it from the other tabs by clicking “Dashboard” at the top.  You should see activity moving across the chart.  

· Operation Logs on the Azure portal.  From the dashboard view of your event hub (instructions on how to navigate there in previous bullet point), there is a link to the operation logs on the right sidebar.  I believe the direct URL is https://manage.windowsazure.com#Workspaces/ManagementServices/AdminLogView

Event Hub Operation Logs

· Sign into the SQL database and check the number of rows a couple of times…make sure it is increasing. 

During the hackathon, I jotted down a few other notes to help others who might want to play with this code. 

· The Arduino board, Raspberry Pi, and my laptop must all be on the same network to communicate with each other. 

· To enable Azure Stream Analytics, you have to go to https://account.windowsazure.com/PreviewFeatures and click “Try it now” next to Stream Analytics.

· The Stream Analytics queries needed to match up the column names of the input/output properly.  For example, my query was: SELECT millis as ID, '3' as SensorID, lght as WindSpeed, temp as Temperature, hmdt as Humidity, 40.036 as Longitude, -97.022 as Latitude FROM DeviceInput.  In the “Inputs” section of the Stream Analytics, the Name/Input Alias must be DeviceInput to match this query.  On the “Output” side, make sure you are specifying the correct database/server/table name (of course) but you also need to match the column names in the query above.  So, in our output SQL Azure database, the column names were ID, SensorID, WindSpeed, Temperature, etc.  In our incoming data from the Event hub, the column names (or first part of the JSON) were millis, lght, temp, etc. 


My team won first place!  We also entered our code in the hACTOthon (a Microsoft-internal hackathon run by ACTO, Americas Chief Technology Office) and were runners-up. 

Hackathons are such a great way to learn by doing. I learned a lot from others (Pete Brown and Stacey Mulcahy have lots of IoT experience and answered some questions, and Jared Bienz had a great 2am insight – need to be on same network to connect to boards, which I had forgotten at 2am), and I was able to help others too (I figured out some SQL issues, some connection string issues, some Mobile Services issues, and helped people with git).

Jennifer Marsman, Dave Voyles, David Crook, and David Giard at the Miami Hackathon

Finally, I have some helpful resources below. 

Pipe Dreams source code (apologies that it isn’t documented the best…our focus was creating working code in a short time, since it was a hackathon): https://dpeted.visualstudio.com/DefaultCollection/Field%20Hacks/_git/PipeDreams 

Connect the Dots

Project URL: http://ConnectTheDots.io

Documentation/Quick Start: https://github.com/MSOpenTech/connectthedots/wiki/Step-By-Step

Blog Post: http://msopentech.com/?p=883411

Twitter: https://twitter.com/OpenAtMicrosoft/status/542697101474295808

Facebook: https://www.facebook.com/msopentech/posts/737071926383104

Oil/Gas Pipeline Videos

Trans Alaska Oil Pipeline Tour: https://www.youtube.com/watch?v=_OrAmGOFOEk

Natural Gas Pipelines Operations: https://www.youtube.com/watch?v=aTTJeTaYDyc

Hot Tapping Gas Pipelines: https://www.youtube.com/watch?v=nFEsa0fxTMU

Robotic Device for detecting Leaks in Pipes of Gas, Oil or Water: https://www.youtube.com/watch?v=X1N2fJV20ww

My Teammates’ Perspectives on the hackathon

David Giard: http://davidgiard.com/2014/12/22/PipeDreamsAndHack10.aspx

David Crook: http://indiedevspot.azurewebsites.net/2015/03/14/mocking-iot-telemetry-data-with-azure/#more-5191