(electronic whirring)
- Coming up, we take a look at the next generation of
Azure SQL Data Warehouse,
now generally available,
from how this makes it easier to quickly and efficiently
spin up data warehousing capabilities
for everyday workloads,
allowing you to go from no data warehouse
to over 4,000 cores operating at
petabyte scale in minutes
and leverage the latest generation
of Azure Compute with automatic caching of columns
or segments to analyze your data
and achieve up to 10x speedups of querying.
(digital music)
- We're joined today by John Macintyre
from the Azure Data Engineering Team.
Welcome.
- Hey, it's great to be here.
- So we've had Azure SQL Data Warehouse for a while,
but let's take a step back and explain really
the point of data warehousing and how it differs
from your day-to-day databases.
- Yeah, data warehousing is really about
bringing massive amounts of data from diverse sources
into one definitive, trusted source
for analytics or reporting
that is accessible across an organization.
And there are a couple trends around Data Warehouse
that make it increasingly important today.
The first is that data sources are becoming more diverse.
So we have more and more data coming in
from different places.
The second, is that data is more critical than ever
to every aspect of the business.
So you have more data
and more need to reason over that data.
As a result, we find that people need a definitive source
of truth for trusted, curated data
that an organization can use to gain insights
and make decisions.
The Data Warehouse serves that role.
It gives you a scalable SQL Database
that is optimized for analytics,
enforces structure, data quality, compliance,
and offers security for your most valued data.
But, you know, historically, data warehouses
have required large investments to set up and operate.
And these data warehouses have only been accessible
to those that are willing to make that investment
and able to make that investment.
- And so I know one of the things that we've solved
for here really is to make it possible
for any organization then,
to have a data warehouse.
- Yeah.
I mean, so our goal
when we brought SQL Data Warehouse in the market,
was to make it economically viable and accessible
for everyone.
We're the first cloud Data Warehouse
and cloud provider service that was able to deliver
an enterprise data warehouse
that separated compute and storage.
- This is really great,
'cause you don't have to actually have lots of idle compute
that's just sitting there,
you can flexibly boost capacity on demand.
- Yeah. That's right.
I mean, this type of efficiency is simply not possible
outside of a cloud environment
and we're seeing a lot of companies
that are taking new analytics projects
and new analytics workloads and bringing them into Azure,
because they now have access
to this data warehouse technology.
We're also seeing well-established
on-premises data warehouses offload their data
and their processing into SQL Data Warehouse
to reduce the time it takes to analyze large amounts
of data from days to hours and even minutes.
- So these were all parts of
the existing Azure SQL Data Warehouse service,
but what's the team been focused on, then,
for the next generation of Azure SQL Data Warehouse?
- Yeah, we're really really excited about
the next generation of SQL Data Warehouse.
With this next generation,
we've been focusing on performance and scale.
We want you to be able to enjoy
the elastic scale of the service
while achieving super fast query response times.
Even when those workloads have high concurrency needs.
We also looked at how we can scale compute higher
than what we have today.
If you're coming from one
of those data warehouse appliance environments,
we want to make sure there's enough compute in the system
that can handle the most demanding workloads.
- So where would some of the specific areas be
in terms of focusing on the performance
and improving that and the scale itself,
I mean, were there some bottlenecks maybe
in networking speeds or bus speeds
that you had to correct for?
- Yeah.
I mean, the network can certainly be a bottleneck
and if you're scanning, filtering,
and aggregating hundreds of terabytes of data,
and you're doing this with complex queries,
that can cause a lot of data to be processed and moved.
Especially if that data is repeatedly accessed.
It places a lot of pressure on your network,
which can slow down querying and batch processing.
- Okay, so what have you and the team do, then,
to get past all that?
- We ended up doing two main things.
With Gen 2,
we introduced an SSD-based caching layer to the service.
The idea here is to really take a hybrid approach
and use the combination of remote storage
in a very fast SSD caching layer
that places data with compute
based on user access patterns and frequencies.
Using software, we can also understand
where data needs to be and when that data needs to be there
for the best possible performance.
Second, we took advantage of the amazing compute
and IO throughput provided by Azure's latest hardware.
Azure's latest hardware is now offering NVMe-based SSDs.
And this is able to give petabyte scale,
the high performance of co-location
of compute and storage
all with this unmatched elasticity and economics.
So if you were to compare to the first generation
of SQL Data Warehouse,
you can now scale dramatically higher.
We have five times the amount of compute available
that we did in generation one,
and with generation two,
we updated the resource management system
to handle four times the concurrency.
This is huge.
If you have a very complex,
very demanding SQL analytics workload
that you want to bring to the cloud,
Gen 2 is for you.
- So then, what's a practical example
where we can apply this technology
to a real-world scenario?
- Yeah. Any scenario where you have to reason
over large quantities of data with complex queries
is a really good use for data warehousing.
If you think about it from the perspective
of a very real problem,
that has the potential to impact every one of us,
let's take water consumption.
We can use big data in data warehousing
to inform decisions around planning for less water waste
and drive more efficient water consumption.
So let's take a look at a demo.
I have a data set here of ten years of water supply data
from the United States that also includes demand data.
We have fifteen billion rows of data here.
We're rolling up and conforming the data by county,
and we have both industrial and domestic demand data.
We're also collecting supply data
from reservoir systems as well as stream flow sensors
that are collecting data at fifteen-minute intervals.
As we're doing this analysis,
we're doing year-over-year comparisons.
So let's take a look on the left-hand side,
we're gonna do their generation one data warehouse.
And so, if we look at the current scale level
for that data warehouse, we're at a DW1000.
This is one thousand compute units for our data warehouse.
On the right-hand side,
I have a generation two data warehouse.
That's running at a DW1000 as well.
So what we're gonna do is, we're gonna run this query,
the exact same query on Gen 1
and then we're gonna run this query on Gen 2.
For this particular analysis,
we're gonna really zero in on California data.
We wanna look at what that data shows us during
some of the drought years that we've seen recently
that are happening in the state of California.
So here you can see that already,
on the right-hand side that the query's completed on Gen 2.
And the data will show us that
if we look at year-over-year reservoir data,
we see that declining year over year,
but at the same time both domestic and industrial use
of that water is increasing.
So we have demand increasing steadily
but supply decreasing,
which is consistent with what we've seen
in some of the drought that we've had in California.
- Right. And I think a lot of us are probably expecting
Gen 2 to be faster and Gen 1, just to point out,
that's no sludge.
It's gonna be amazingly fast for a lot of folks.
But we really haven't tapped into Gen 2's potential
'cause I know that you can do more.
- Yeah. We haven't at all.
So we'll get back to Gen 1 in a minute.
We'll see how it's doing,
but let's jump over to another Gen 2 data warehouse.
And so here I have a very similar data warehouse,
a very similar data set.
I have water supply data,
I have water demand data,
although I've loaded 70 years of data
into this data warehouse.
- [Jeremy] Okay.
- [John] I'm also bringing in additional data
into the data set.
So I'm bringing in weather data,
as well as some of the drought condition data
that we have.
So we're gonna run an analysis over this data
again, a hundred and fifty billion rows of data
and we'll run this similar analysis that we did
in the previous data warehouse.
- [Jeremy] So significantly more data
than we ran the last time?
- [John] Yeah. That is a lot of data,
so what I'm gonna do is,
is I'm gonna jump over to the Azure portal
and I'm gonna really turn the dial up
on my compute power here for my data warehouse.
I'm gonna turn it all the way up to thirty thousand
and get the maximum compute that I can out
of this particular data warehouse.
From the same environment,
I could also pause and resume that data warehouse.
So if I wanted to save cost, I could easily do that.
And this is really,
you know, when we talk about on-demand data warehousing,
this is it.
- Volume level 12.
- That's right. Spinal tap.
(laughing)
- All right.
- For those that get that reference.
- Yes. So why don't we switch back
to the actual query itself and run that
and see what it does with this much performance behind it.
- Yeah. For sure.
Let's go back to SQL Server Management Studio.
So here we have that data warehouse.
We have a hundred and fifty billion rows of data
and we're gonna check the DWU setting,
that was at thirty thousand.
So we kick off the query.
We're gonna run that query against that data warehouse,
but while we're doing that,
let's go back.
Let's check out Gen 1.
Let's see if Gen 1 had finished that analysis
on the California data set.
- [Jeremy] Okay.
- [John] So we jump back and we see here that Gen 1,
which was on the left-hand side has completed the analysis.
And that was done in just over a minute,
which is not bad.
- [Jeremy] So far you got fifteen billion rows,
- [John] Fifteen billion rows of data.
We've performed the same query on Gen 1,
but you can clearly see the performance advantages
that we're getting out of Gen 2.
Same scale factor.
Running on same data set.
Running our same analysis.
- [Jeremy] Okay.
- [John] So let's go back and let's look
at our Gen 2 that's in this data warehouse again,
a hundred and fifty billion rows,
and remember, we added additional data sets
to this particular data warehouse.
- [Jeremy] Looks like it finished.
- [John] And the query's done already.
We see precipitation data start to show up.
We see reservoir supply data showing up.
We see year-over-year trends.
And so that analysis is complete already.
- So in spite of all the additional volume
and the complexity,
adding drought data and weather data,
these are speedups due to the auto caching
and how we can really attach and scale compute.
But I know that speed is one aspect in terms of performance,
but what does this actually give us the power to do?
- Yeah. I think you're right.
I mean, I think we're really only starting
to scratch the surface on the insights
that we can gain from some of this data.
And if we think about more of a global problem
around clean water supply globally,
we know that hundreds of millions of people
don't have access to clean water.
Even though 70% of the world is covered with water.
Only 2.5% of that is fresh water,
so that's coming from lakes,
that's coming from glaciers,
rivers, ground water, ice, snow.
And that's some of the data we were bringing in.
We were bringing in some of that precipitation data.
We were bringing in some of that reservoir data.
So let's go back to our California data,
and let's see what we can find from that data.
We can use Power BI to visualize that data.
This allows us to directly explore the data.
So we can look from year to year
and see how the data changes.
We look at maximum temperature ranges.
We look at minimum temperature ranges.
On the left-hand side here, we have our temperature data.
On the right-hand side, we have precipitation data.
So let's drill into,
again, this is where we're really drilling
into our California data here
and if we look at the year 2007,
we know there were drought conditions in 2007.
You can see that from this chart up here.
2007 we have high drought conditions.
- [Jeremy] Yup.
- [John] So let's see what the data tells us from 2007.
I think the interesting thing here
is we can see in Southern California,
this area has very low precipitation.
And for those that know what was happening
in southern California in 2007,
I don't know if you remember,
but there were wild fires everywhere.
I mean, this was a very very bad year
for the state of California.
In fact, a state of emergency was declared.
So we think about what could we do with this data.
And we know California's entering another period of drought
so as we think about those drought conditions coming,
how can we get water supply where we need it.
Either for clean water supply
or for addressing some of these issues.
- And these are things that we're using,
reservoirs, so the fill rates and, kind of,
depletion rates of water.
Things that might happen over many years in some cases.
You've got urban planning, desalination,
various other techniques that you can do to address it.
So really cool examples in terms of
how we might leverage big data
in Azure compute and storage analytics.
And all the things that we're doing here
with the next generation of the Azure SQL Data Warehouse,
but do you need to move your data,
like we see in this case,
the public data sources here.
But the idea to move as an on-prem maybe data service,
might data up into Azure to take advantage of this?
- Yeah. If you're already running applications
in the cloud or building IOT systems
that are more cloud native,
it's just a matter of loading that data
into Azure SQL Data Warehouse.
You know, Azure SQL Data Warehouse natively integrates
with our storage systems and our big data services
to efficiently move and load large volumes of data.
Azure data factories are server-less data integration
and orchestration service that makes it super simple
to set up restartable pipelines and do data transformations.
With Data Factory, you don't have to move your apps
or storage systems to the cloud,
you can easily set up the gateway
and move data from on-premises sources
to your SQL Data Warehouse.
- So really powerful tools,
but where would you recommend people get started today?
- The next generation of Azure SQL Data Warehouse
is available today.
You can try it for yourself and learn more
from the link shown.
- So, really great stuff.
Great seeing this all being brought together
and of course you can keep checking out all
of the updates across the data platform
at Microsoft Mechanics and the latest updates across
the company.
That's all the time we have for this show.
Thanks for watching, we'll see you next time.
(digital music)