[MUSIC].
>> Hi and welcome back.
My name is JRJ, and I'm here to tell you about one of
the most eagerly anticipated features in SQL Server 2019,
and that's data virtualization.
What is data virtualization,
and why is it so eagerly anticipated?
Well, simply put,
data virtualization enables you to
bring all of your data together at
query time rather than having to build complex ETL pipelines in
order to be able to unify the data in a single query.
So what I'm going to do is rather than go
through the details of data virtualization at a conceptual level,
I'm just going to show you the differences between
a local query and a virtualized query,
both partially and fully virtualized.
So to do that,
what we're going to do is we're going to switch over
now to Azure Data Studio,
and you can see here I have a workbook open,
and let's go in and start evaluating that.
So here you can see I have a very simple query.
I have two local tables in my database,
and if I run that query,
you could imagine the result comes back nice and quickly.
I have about a second,
and I get my dataset back in the notebook.
However, what if all of that data wasn't in the SQL Server?
What if that data was actually available in remote SQL Servers,
and we wanted to access that data all at the same time?
Well, you can use data virtualization to solve that problem.
But in order to do it,
we need to set up some metadata.
So the first thing we need to do is to create a master key,
and a master key is a key inside
the database that we use to protect
all of the other metadata inside it.
You can see from the metadata here which algorithm we're using,
when it's created, and interesting things like that.
Now I need to enable the PolyBase feature in order to be able to
access remote sources and remote databases,
and create a database credential to
be able to authenticate against those remote sources,
and you can see here that I've created a few in the past,
as a couple of Oracle,
and a couple of SQL ones in there as well.
But today, we're going to go against a SQL Data Source,
and you can see here that in order to do that,
I need to create an external data source.
Here, I specify my location, in this case,
a SQL Server address somewhere in Azure,
and I pass in that credential
to enable that authentication to take place.
So let's go ahead and create that,
and you can see again, there's the metadata inside the database.
Now, as a general rule,
I like to keep the external tables which define
those external datasource objects
separate from my internal tables,
and I do that using a schema.
So let's go ahead and create an external schema,
and now we can come down here and create our first external table.
The first external table we're going to create is
web click streams which is the first table,
and in this case it's more like a fact table,
and we're going to store that.
So in that external database,
we have exactly the same database.
We're just using it again to illustrate this scenario.
Now, we can get onto the process of virtualizing a clickstream,
the web clickstreams table.
Here you can see I have the same table web clickstreams,
but now I'm using the EXT schema.
So I'm accessing the external table,
but to all intents and purposes,
the rest of the query is exactly the same.
If I run that query now,
let's say it takes a little bit longer because
we're going to go and get that data remotely,
and you could say it's about 3.5 seconds.
But we can see that we've got
that data here and it's exactly the same.
So everything underneath the hood
is completely transparent to me as a user.
Now what if I actually go ahead and
virtualize the second external table in this query?
You remember that the first one was web clipstreams,
that the second one is the item table.
So let's go ahead and do that,
and now I have both tables virtualized.
So now, what happens when I run this last query?
This last query is going to run exactly the same query,
but both external tables are virtualized,
and you can see that actually the query is almost as
fast as the first version, the local query.
Now why is that? Why do we get this difference in performance?
Well, the reason is that if you look at
the SQL Servers intelligent enough using
its cost-based optimizer to understand that
both the tables are external and they come from the same source,
and that it can see that it can push this join and
the aggregation down against that remote source.
So we're leveraging the compute of
that remote source to resolve this query in real time.
But that gives you a quick overview of the capabilities that you
get out of using data virtualization technology
and how you can actually transparently present that data
back to an end-user without having to
make physical copies of that data,
without having to move it or build
a complex ETL pipeline in
order to be able to query data in real time.
Thanks very much for joining,
and I look forward to catching up with you again soon.
[MUSIC]