(upbeat music)
- Hello, and welcome back to Microsoft Mechanics Live!
Coming up next, we're gonna have a leap
and we're gonna have a look at the leap forward
in SQL Server, its next leap,
and we're gonna show you big data capabilities
that are now built into SQL with HDFS and Apache Spark,
as well as our new approach for data virtualization
that allows you to easily integrate data
across multiple data sources
without even needing to move it or perform ETL.
So, very cool stuff.
And to join me, I've got Travis Wright
from the SQL Server team.
Give a big, warm welcome. - Hey.
(audience applauds)
Thanks, Jeremy.
Glad to be back on the show.
- So we've actually done a lot
in terms of advances recently with SQL Server,
all the way from the platform abstraction layer,
the PAL, as I like to call it,
that enables you to run SQL Server on Linux,
the machine learning now built in box in SQL Server.
But with all the data that's out there in big data silos,
adding these capabilities that we're doing now
in terms of the integration and the data virtualization
to SQL Server, in a lot of respects, in a lot of ways,
is way more useful and even bigger milestone
for a lot of people.
Can you tell us more about that?
- Yeah, we know that integrating data
across all these different data silos is hard.
The amount of data out there is just enormous anywhere,
so we really started our journey on data integration
back with SQL Server 2016,
when we introduced the PolyBase feature,
and that allows you to issue a query to SQL Server
and SQL Server will turn around
and then go and query data out of HDFS
in like a Cloudera or Hortonworks cluster, for example.
And now, with SQL Server 2019,
we're making it even easier for you
to integrate all of your data
without having to move it around.
And we have a brand new architecture
that's designed to give you huge improvements
and performance and scalability
through caching and parallel processing,
as well as having an elastically rescalable compute
and storage layer for SQL Server.
- Very cool.
Why don't we explain how this all works architecturally?
- All right, sounds good.
We do this in two ways.
First, we're deeply integrating
the SQL Server parallel processing engine
with big data components
from the open source community like Spark and HDFS
that are now included in the box with SQL Server.
They are a part of SQL Server.
That was the big announcement this week.
And then secondly,
we're going to provide data virtualization connectors
for common data sources like Oracle, Teradata, MongoDB,
SQL Server, and a generic ODBC connector
to make it even easier for you to integrate
all of these data sources through SQL Server
without having to move your data around through ETL.
- Which is really great 'cause big data
is now part of SQL Server.
Now you don't have to move your data
or create separate reporting databases.
You can keep it all in place.
- Right.
So instead of moving your data, transforming it,
and creating duplicate data and all that kind of stuff,
we've really focused our efforts
around data integration on data virtualization now
instead of data movement,
and that allows you to keep your data in place
and yet, still be able to access it
from a single point with SQL Server.
And the nice thing here is that we can really boost
the performance of these queries
by caching that data and partitioning it
across multiple SQL Server instances
that really kind of make that query really run fast.
And you can even combine data from multiple data sources
into this cache, so you can have a joint,
for example, that goes across some data
that's coming into that cache from Oracle
and from another SQL and from HDFS, for example.
- So it's way more secure.
You're not making a persistent copy again of that data.
There's less maintenance, fewer ETL jobs.
You don't have to do ETL in this case.
It's more efficient storage
'cause you're not starting it twice.
The data's actually queryable live
against the virtual cache.
It's not gonna go out of freshness or out of date.
It's gonna stay accurate, right?
- Right, exactly.
A lot of benefits there.
So, let's make this real by doing a demo here.
I'm gonna show you being able to query
across multiple data sources here.
So I'm running here inside of the new Azure Data Studio,
which was formally known as SQL Operations Manager.
It's our cross-platform database tool
that runs on macOS, Windows, and Linux,
and it's open source.
And here, I'm connected to an HPE lab
where we have an HPE cluster of machines
that has the SQL Server 2019 big data cluster running on it.
We're gonna go to the Sales database here
and I'm just gonna right-click on that.
I'm gonna click Create External Table.
This is gonna bring up a new wizard that we have
that makes it easy for you to do this.
I'm just gonna select the Oracle type here.
We'll have more types over here over time.
And then I can click on Next here.
I already have a database master key,
so I'm gonna skip that.
I'm gonna give this a name.
We're gonna pull in
the Oracle Inventory database from Oracle.
I'm gonna grab the server name.
We're gonna go into the XE.
We're gonna create a database credential here.
We're gonna go to SYSTEM and admin123.
Now, what this does
is it actually reaches out to that Oracle server
and it discovers the schema inside of that database
and I can then select the table
that we wanna synchronize over.
So, we just click on this inventory table here, for example.
We can even select the table.
We can see the schema about that table here
and we can do some column mapping if we wanted to
and we can maybe even change the name of the table
that we wanna bring in inside of SQL Server.
Click Next, click Create,
and now, we have an external table
that shows up inside of our table's list here
and we can now go and query that table.
- [Jeremy] And there we go, very cool, very cool.
- Let's move on to creating an external table over HDFS.
- Okay, let's do it. - Let's do that.
I've got a query here that we can run.
And the nice thing about this here is that we have a,
we got a new query from here,
we have a built-in external data source
called SQL storage pool,
and what this does is this allows us
to query the data inside of HDFS using a SQL Server instance
that's actually co-located on every HDFS DataNode.
It's just built-in. You don't have to do anything special.
You just define the read schema
that you want us to apply to the files
that sit in a particular directory or to a particular file.
So now, we can create this table here.
Connect to our SQL here.
Once we have that table created,
we can then just query over that table
just like we would any other type of table
and right now, what we're doing
is we're actually reaching down into HDFS
where we have millions of records in this CSV file
that sit inside of this directory
and we're bringing it back up to SQL Server,
just like you query any other type of table in SQL Server.
- And there it is. - There it is.
- Very cool.
So, I can see how this would hugely valuable,
especially when you're dealing with compliance requirements.
You don't wanna move the data
outside of that Oracle database
or, in this case, you wouldn't wanna move
any of the data actually.
You'd be able to query it here.
It looks local then effectively to SQL.
And the nice thing is you mentioned
that Spark and HDFS, they're really in the box.
What does it look like though
and how does that part of it work?
- In that demo we just saw of querying the HDFS,
it's part of SQL Server.
You can also query that data
and manipulate it using the Spark engine
that's also co-located on that data node.
So now, you have sort of a shared data lake
between SQL and Spark
where you have all that data sitting in HDFS.
So now, what I wanna show you
is another demo where we're going to use
either the Data Studio, to use notebooks,
to interact with the Spark runtime.
- Okay, let's do it.
- So first off, I wanna show you a notebook experience.
It's built into Azure Data Studio here,
and this is where I can come in
and I can write some code in Python or Scala or R
and I can execute that either locally here
on my machine using the Python 3 kernel
underneath the covers
or I can submit jobs into Spark to have it run there.
Now, what I'm showing you here
is I have this file called Artists.txt.
It's a CSV file,
but it's got some pretty crazy data structures here.
It's got a bunch of stuff up here
that defines columns but it's in a weird way.
There's a bunch of whitespaces.
- [Jeremy] So it's nearly unstructured
even though it's a CSV.
- [Travis] Yeah, I mean, this is a mess, right?
- [Jeremy] Yep.
- [Travis] We've got this research stuff
coming out of Microsoft research called PROSE
that allows you to basically make sense of this data
by running some code against it
and it will generate code, which you can then run
against those types of files
and generate a schematized version of it.
So to do this, I'm just gonna run this PROSE library here.
This is included inside
of Azure Data Studio Notebook experience
and inside of the Spark runtime and inside of SQL 2019.
It generates this Python code for me
that when I go down here and I run this,
it will load the data from that file into a data frame
and, what did I do wrong here?
Oh, I missed the pandas at the top.
- [Jeremy] Maybe the top line?
You gotta have the panda.
- [Travis] Gotta have the panda.
- [Jeremy] Otherwise, the panda will be sad.
- [Travis] There we go, include the panda.
There we go. - There it is.
- So, the data is now structured.
We can actually make sense of it
and this makes it easier for SQL Server to read it.
Now, the cool thing is I can take this same Python code
and I can go over and inside of the Spark runtime
on the top of the HDFS,
I can start to apply this to files that are over there.
So here's an HDFS browse experience
where I can actually browse the HDFS
sitting inside of the SQL 2019 big data cluster
and we can see
that I've got that Artists.txt file already there.
Now, let's physically go and create a function
that will be available to us now inside of Spark
and we can run the same thing
over that Artists.txt file inside of the Spark cluster
and you can see that it now schematizes all of this.
And now I can, if I wanted to,
save those back as a CVS file
that's in a nice, beautiful format, for example.
So this is how you can use something like Spark
to do all of your data preparation tasks at scale
across all of your Spark infrastructure.
- Much, much better.
Now that we've prepped the data,
we've got it all into good format, can we analyze it?
- Yeah, definitely.
That's with whole point of this, right?
So, we wanna build and get to where we can analyze it.
And this is where you wanna be able to combine together
kind of the different data sets from Oracle, from HDFS,
and then feed that into your machine learning services
that are built in to SQL Server
where you can run Python or R scripts to train your models.
You can store that model back inside of SQL Server,
to operationalize it,
and then just as part of a stored procedure,
you can actually store your data against that model,
maybe as part of a transaction
or a batch processing thing there.
So, let's take a look at another demo here.
I've got an eCommerce site scenario here
that we're gonna look at
and we're going to use machine learning services
to predict a pattern of behavior from a clickstream
of data from somebody who visit our eCommerce site.
So, let's pivot over and take a look at that now.
So on this one, we got another script here
that we've already created ahead of time.
Let's bring this up now.
So on this one, we're going to, first of all,
kickoff the model training.
So when I kickoff the model training,
this is gonna call this stored procedure here
called train book category visitor.
It's gonna produce some model for me.
You can see the output from the production in that model.
Well, let's go take a look
at what that actually looks like here.
So, what this stored procedure does
is it defines a SQL query that will go
and get some training data out of my database.
And then it will call some R script
that will be doing some featurization of this
using a logistical regression algorithm.
And then what we do is once we run
this sp_execute_external_script right here,
this will execute this R code
against the training data we're sending in
and will produce a model
which we then store inside of the database
by inserting it into the sales_models table here.
Once we've done that, we can then go back out here
and we can do our predictions against this data
by using this stored procedure here.
You'll see that based upon all the clickstream behavior
that we have sitting in HDFS in raw file,
we pull that up, we score it against the model,
and we can actually predict
based upon somebody's clickstream behavior
whether or not we should suggest to them
to go visit the book category, for example.
So this allows us to bring together
data from our inventory system in Oracle
and our HDFS data that has the clickstream data in it
in raw format, in CSV files,
and then feed that into machine learning services
inside of SQL Server, produce that model,
and now, we can score user behavior against it.
- Very cool, so we've seen huge architectural advances here
in terms of this direction for SQL,
being able to really parse over and analyze big data
without having to move it
or do a lot of ETL work against it.
This is really huge.
- Yeah, it's big. (speakers laughing)
Sorry for the pun, but yeah, it's big, this is huge,
and this is really like a different dimension
for SQL Server to scale in to
where you get this caching ability,
you get elastic scalability of your performance,
tiered with compute and storage scalability,
all built on top of this native architecture.
So it's really, if you think about it,
SQL Server is not just a database really anymore.
It's really more of like an integration
and complete analytics platform
that you can use to integrate all these data together.
- Right.
Again, when we think about cross-platform,
it's not only about operating SQL inside of Windows or Linux
or multiple operating system types,
but also against other databases.
So, if somebody wants to learn more
about these capabilities and really try it
and go hands-on for themselves
and connect to Oracle and start using Spark and HDFS,
where do they learn more?
What should they actually be doing right now
to get a hands-on?
- Yeah, exciting news.
We announced the SQL Server 2019 preview this week.
You can go and get that for SQL Server on Windows and Linux.
And if you're interested in the big data cluster part of it,
we encourage you to sign up
for this early adoption program at the link here
where we can then assign you
to work with the program manager on our team
and then we can really get hands-on with you
as you try to evaluate these big data cluster technology
and we can learn from you, kind of how that experience goes.
- Awesome, great to have you on the show again, Travis.
Thank you.
Thanks for coming,
and that's all the time we have for today's show.
- Thanks, everybody. - Goodbye for now.
(audience applauds) (upbeat music)