Transcript SQLSatParisx

Internet of Things
Let’s do some IoT stuff…
with an Arduino board and Azure Stream Analytics
Merci à nos sponsors…
Session Logistics
Speaker : Florian Eiden
• SQL Server MVP
/ Data Architect
• @fleid_bi / http://fleid.fr
Cellenza
156, bd Haussmann
75008 Paris, France
http://www.cellenza.com
http://blog.cellenza.com
English or French?
Recorded, but you are here, so you are the important ones!
Content : 20% slide deck, 80% demo
Some code : C, Python; on Linux and Mac OS… But Azure and Power BI!
Yes you can leave now, no offence taken ;)
Internet of Things
What’s the point?
International Business Times : Connected Cattle
By David Gilbert
Internet of Things
• What’s the point?
• 4 different scenarios (Clemens Vasters – MSDN)
•
•
•
•
Telemetry : my device streams data from sensors
Inquiries : my device asks for stuff for its process to complete
Commands : I order my device to do something
Notifications : I notify my device so that it can do stuff if it wants to
Complete Picture
Event Hub
Stream Analytics
PowerBI.com
Azure SQL DB
Sensors
IP compatible
device
(Windows/Linux)
Arduino.cc (Genuino)
https://www.arduino.cc/en/Tutorial/Blink
Arduino.cc (Genuino)
https://www.arduino.cc/en/Tutorial/Blink
Arduino Yun = Base + Linux + Wifi
& Ethernet
• Base System : Circuit board + pseudo C
• Yun Linux : Linux… so Python in my case :)
(Because Azure SDK in Python!)
Pseudo C
Python
Event Hub : a broker, with
retention, in PaaS
Event Hub
Arduino : C
• Let’s go have a look ;)
Arduino : Python
• Let’s go have a look ;)
• First on the Mac
• Then on the Arduino by ssh
• Then see
So now that we have data coming
in, lets play with it!
(Stream Analytics) Data at Rest
Question
“How many red cars are in the parking lot?”
Answering with a relational database
Walk out to the parking lot
Count vehicles that are: Red, Car
SELECT count(*) FROM ParkingLot
WHERE type = 'Auto'
AND color = 'Red'
14
(Stream Analytics) Data in Motion
Different Question
“How many red cars have passed exit 18A on A-10 in the last hour?”
Answering with a relational database
Pull over, park all vehicles in a lot, keep them there for an hour
Count vehicles in the lot
Not a great solution…
15
Stream Analytics
Different Question
“How many red cars have passed exit 18A on A-10 in the last hour?”
SELECT count(*)
FROM A-10
WHERE Type = ‘Car’ and Color = ‘Red’
GROUP BY TumblingWindow(hour, 1)
16
Temporal aggregation, in T-SQL
TumblingWindow(minute,5)
HoppingWindow(minute, 10 , 5)
SlidingWindow(minute, 3)
17
End-to-End Architecture
Azure Stream Analytics
•
•
•
Temporal Semantics
Guaranteed delivery
Guaranteed up time
Event Inputs
- Event Hubs
- Azure Storage Blobs
Outputs
Transform
-
Temporal joins
Filter
Aggregates
Projections
Windows
etc.
-
Azure SQL DB
Azure Storage Blobs
Event Hubs
Power BI
…
Enrich
- Join with reference
data
Reference Data
- Azure Storage Blobs
- …
Prepare
- Output to storage for
further processing
Azure
Storage
Data Source
Collect
Process
Deliver
18
Power BI (powerbi.com, the
yellow one)
NB : you need the “Pro”
one (10$/month) for real
time stuff
To go further
• Arduino.cc : Starter Kit (Genuino) / Arduino Yun (Linux+Wifi)
• Arduino + Event Hub
Ola Loogman (Kloud) : The IoT with Arduino, Event Hubs and the Azure
Python SDK (blog)
• Event Hub et Stream Analytics
Dipanjan Banik – Stream Analytics Hands on Lab : TollApp (download)
Visual Studio Community Edition (download)
Azure 1 free month offer (activate)
• Power BI : PowerBI.com
http://GUSS.pro/sqlsat