>> On this episode,
you'll learn how IoT and Azure SQL database go together.
Join us on Data Exposed.
[MUSIC]
>> Hi. I'm Anna Hoffman,
and welcome to this episode of Data Exposed.
Today, I'm joined by a program manager on
the SQL DB team, Davide Mauri.
Thanks for joining us today. How're you doing?
>> Great. Thank you, Anna. Nice to be back.
>> Awesome. We're always happy to have you here at Data Exposed.
Today, we're going to talk about IoT.
What you're trying to tell me is that
Azure SQL and IoT go together?
>> Right. Exactly that.
I know it sounds strange because mainly,
last year or so,
all the discussion were around IoT in NoSQL database.
They are great for IoT, I'm not saying the opposite.
But I just want to highlight that Azure SQL is also great for IoT.
Why? I will discuss that in just couple of minutes,
but let's start with this architecture.
As you can see, this is
a classic IoT architecture where you
have things as it shows in the picture,
that are pushing data into Azure,
in this case by IoT Hub,
but you could also use Event Hub as well.
Then you probably want to do some transformation
using Function or Azure Databricks,
or stream processing also to process data in real-time.
Then at some point, you want to land
that data that has been processing real-time
somewhere so that you can create an historical view of the data,
you can integrate with other tools,
you can do analytics on it.
Azure SQL is really
an amazing database for that because it has really
a lot of features that would help IoT to get amazing performance.
I can prove you if you don't believe yet.
Some of the feature I want to basically highlight that are really,
really helpful for IoT are of course the columnstore.
I think we should discuss off
columnstore a little bit more in a dedicated video.
Basically, if people are already
used with the idea of columnstore is,
we are storing data on
a column by column basis instead of row by row,
and that helps a lot in doing near real-time analytics.
But we also have a rowstore that allows,
for example, to store data in an ordered fashion.
It's really perfect, for example,
for doing something like time series analytics.
Then we can read scale-out, mean that,
while you are ingesting data at scale inside database,
we can create a copy,
well, not really a copy,
but a replication, better,
of the database in another node,
and then allow other application to access
that secondary node that will not interfere with the primary one.
You can still ingest data at scale,
while you are doing maybe
really complex analytical query that takes minutes or
even tens of minutes to be
executed without affecting each other performance.
I would say honestly that's amazing.
If you really need super high scalability,
we can even have what we call memory optimized table that are
memory table that doesn't use locking to preserve consistency.
They can scale to amazingly high number.
If you really need to ingest a lot of
concurrent data, you can do that too.
It's really encompasses all the possible need
that an IoT solution may have,
and of course we also support JSON.
If your IoT device is just sending JSON data to SQL,
we accept happily that too because we can
manipulate JSON quite easily in Azure SQL.
All these feature allows you to create
some architecture known as Lambda or Kappa architecture,
depending on which, I would say,
flavor of the architecture you want to implement.
But you can rest assured that no
matter which architecture you choose,
Lambda or Kappa, you can easily apply it on Azure SQL.
>> Awesome. When we talk about scale of IoT,
sometimes we're ingesting a lot of events.
Is that something Azure SQL can handle?
>> Yeah, sure. For example,
one of the thing I created over the last year is an example
that everyone can run on their own Azure subscription,
that in just a few step is able to
ingest one billion of rows per day.
Those are a huge amount of data as you can imagine.
Using a tiny SKU of Hyperscale in this case,
because we want to use Hyperscale because we want to
scale up a lot in terms of size.
But we are just using 16 cores.
Now just to give you an idea,
my current laptop that I'm using to
record this video is using eight cores.
With just the 16 cores in the Cloud we can
ingest one billion of rows per day,
and just using 50 percent of the CPU resources,
which means that we have enough space to handle spikes
of workload that maybe
are not expected but still we need to handle.
I would say IoT fits pretty well in Azure SQL.
>> Wow. That sounds pretty amazing.
Can you show us something that proves that?
>> Yes, absolutely. I'm happy you ask that to me.
Because I prefer, basically exactly what
the user and the people
watching this video can also do themselves.
They can go to
GitHub repository that will be linked in the description,
or watch the video that is also visible here and that will
be again available in the description as a link;
and basically create their own Kappa or Lambda
architecture on Azure using the technology they want to use.
In our case we are going to use Azure SQL.
We will be able to ingest data at 600,000 message per minute,
which roughly is one billion of message per day,
which is quite a lot.
Let me just show that to you in action for real.
This is a dashboard I enable on
my portal that monitors the status of my IoT solution.
On the left here you can see I have five simulators,
because the entire solution people can use from GitHub,
also includes simulators to
generate realistic IoT data in a JSON format.
I have five of them generating 10 megabytes per second of data,
which is something like 10,000 rows per second,
which by minute basis is 600,000 rows per second.
As you can see here,
Azure SQL was able to ingest
all the data that were processed upfront by Stream Analytics,
because the idea of this architecture is that you are
ingesting data in to Event Hub or IoT Hub.
You do some processing in real-time using Stream Analytics,
or Azure Function or Databricks.
In this case we are using Stream Analytics,
and then you land it on Azure SQL.
Of course, you want to be sure that
Azure SQL is fast enough to allow you
to ingest the data without any performance issues.
As you can see is doing that really steadily.
There is basically as much as data we are sending in,
as much as data is sending out from Stream Analytics,
which means it is the landing nicely in Azure SQL.
As you can see here the CPU is something around,
let's say 10-50 percent.
Then there is something more useful is the log here which
is expected here but it doesn't go over 30 percent.
Except at some point just before this demo,
I tried to do something just to show what could happen if there is
a peak in number of message to be processed.
As you can see, at some point,
the number of output events reached 1.2 million per minute.
So twice the time we would normally expect on average.
Still Azure SQL was able to nicely deal with it,
staying within or below 70 percent of overall usage,
and still it recover basically
all the message that were delayed
for something that I did just on purpose,
and reconnect to have almost real-time ingestion of the data.
My current database is almost eight terabytes of data.
Let's now move to
much more complex query where
we know it will require a lot of CPU,
that would potentially steal that CPU for another primary.
But we are on the secondary,
so we can run any complex query,
even one complex like that where we are aggregating data,
actually also calculating values extracting it from JSON,
which is actually using a lot of CPU here.
No matter how much this query is going to take,
it will take probably 30-40 second
here because we are aggregating a lot of data.
There will be no impact on the primary.
The primary is still inserting data at scale at
600,000 rows per minute without any problem.
Because the fact that we have scaled out,
means that we are using one node to ingest data at scale,
and another one to do the processing.
This took only 13 second this time,
and is aggregating data over
the last hour so is also using the hot data.
As we are ingesting it we are also
reading this exactly the same data,
but there is no conflict between them.
This is great for IoT,
because this means that you can ingest data
and do complex real-time analytics without having to
bother about conflicting usage or locking problem,
and that will enable
amazing possibilities for each Kappa in real-time analytics,
which are the bread and butter of IoT.
>> Awesome, that's pretty powerful stuff.
The idea that we can keep our read and write workloads separately,
and also the idea that we can do things like ingest
600,000 rows per second is craziness.
Thank you for showing us this today.
We'll put some references so people can try this out on their own.
We'll see you next time on Data Exposed. Thank you.
>> Thank you so much.
[MUSIC]