>> Learn how you can use
the new data virtualization technologies and SQL Server
2022 to access data anywhere including in your Data Lakes.
This week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman and welcome
to this episode of Data Exposed.
We're back with the SQL Server 2022 series
and once again joined by Bob Ward.
We got lots of exciting stuff that we've
covered so far and lots more to come.
So Bob, where are we on this wheel of power for 2022.
>> I know it's a lot in 2022.
We actually we're in the fourth category of the
five called Data Virtualization Object Storage.
>> Data Virtualization Object Storage.
Does that have something to do with like Data Lake?
>> I'm glad you asked that,
whether its a data lake or not.
The concept of data virtualization was from PolyBase.
We started this idea of SQL Server being a data hub.
Leave the data where it lives and query it from SQL Server.
We've expanded on that and including
some features that a lot of people consider to be Data Lakes.
Let's talk about what that looks like.
That's been the challenge, access data.
We realize all your data's not just sitting on
SQL Server somewhere and Data Lake have become very popular.
We've snapped into support that in the following ways.
Number one, we're still going to support
those great functionalities and T-SQL like OPENROWSET,
External Tables but you're going to
love this new one called CETAS.
I'll explain what that is a little bit later.
But we've changed the model some a little bit.
We're now using Rest APIs in the engine
now instead of PolyBase services to support Azure Blob Storage,
Azure Data Lake Storage,
and S3 Compatible Storage.
Well, what that lights up is the following the ability now to
support Parquet and Delta natively,
which a lot of people use in Data Lakes.
You think about this combination now,
not only supporting Azure Storage concepts look at
all the different providers out here for S3.
It's not just Amazon S3,
it's all the different people that support
the S3 compatible storage protocol.
Then along the way we said, well, why don't we
just support backup restore.
It's not a matter just accessing these files need systems,
you can do a native backup restore as well from
SQL Server into and out of S3 compatible storage.
I know it's extending the model and us realizing you want to
connect SQL Server to
different areas in your business from storage perspective,
in new exciting ways,
you may have seen some of these partners start talk a lot
about this with SQL Server 2022.
Now when I've talked about Parquet and Delta,
I've had SQL customers ask
me like what does the excitement here like,
why would I care about these files?
First of all, let's talk about S3.
Created by Amazon, but it's an open protocol based on REST.
It's very popular and that's why you
saw those providers out there,
is typically fairly inexpensive,
it's very durable and a lot of
unlimited storage capabilities for you.
Naturally, we want to go support that with SQL Server.
Parquet is interesting,
it's an open source file format,
very popular today and here's why.
The schema is built into the file to
binary file and it's calmer based,
so it's very efficient for compression and retrieval data.
Like a column store index type scenario.
Then on comes Databricks they built
an open source storage architecture called Data Lake,
supporting a concept called Delta tables.
Delta tables are really Parquet files,
but they've added transaction and log support to the file format.
We naturally said if these are going to be popular in Data Lakes,
we should support them with PolyBase.
If you look a little bit broader now and of
the PolyBase story for SQL Server, you look at on the left here,
these are the PolyBase services
based ODBC driver support you have,
now you have a new REST API support.
Again, the story's amazing you can join across this.
You could build a SQL view in
SQL Server that joins SQL tables with
Oracle tables from Oracle systems
with Parquet table sitting in S3 stored somewhere.
The SQL user wouldn't know it
because you've put an abstraction layer on top of it.
That is the beauty of data virtualization.
>> I love it. Its exiting
>> I want to show it to you.
>> Oh yeah.
>> You want to see a Data Lake?
>> Yeah.
>> I've got some simple examples,
but I think you'll actually like in,
let's take a look.
First of all, I love this.
Just by enabling PolyBase and SQL Server,
I can go query a public dataset of
Parquet files with no credentials and no Azure subscription,
no cost, just to see the example.
What I did here is use the OPENROWSET T-SQL syntax to access
an Azure Blob Storage New York City Taxi data
that's been stored out there over the last several decades.
Now you notice the syntax here here's
Azure Blob Storage, here's a container,
and then here's a folder
that's been stored of different Parquet file.
I'm not picking a file and picking a folder,
and I'm telling SQL Server it's Parquet based because SQL
knows that, it's format Parquet.
When I select, it's like an ad hoc dataset to query.
I run it, I'll get back data that looks like a SQL Server table.
I put a view on top of this,
nobody even knows it's Parquet
stored in Azure Blob Storage, it's pretty cool.
Now the other thing that's really exciting about this,
we've put intrinsics into the syntax for function support.
Let's say I'd like to know the names of the files,
of the Parquet files, I like to get
more information about the structure,
I can use this built-in function syntax called file path
on top of the folder to run
the same OPENROWSET and if I query and come back,
I will see a set of Parquet files,
the actual path of them.
I know what the Parquet format actually looks like.
>> Awesome.
>> Now one other thing I want to show you, and that is,
this is an example that I will show you end the SQL workshop.
We've talked about SQL workshop
before this out there now and GitHub repo,
this is a notebook.
I know you love notebooks Anna,
it's a built-in notebook that you can look to the browser and see
examples of how to use Parquet with an S3 compatible provider.
I've got several examples here to show you to get started,
but I won't show you CETAS,
Create External Table As Select.
What I'm doing is I'm exporting Parquet data into
an S3 provider based on a SQL query. You see that right here.
Here's a create external table,
I'm going to show in here
the select statement from the SQL table itself,
the location and think of that as being
the target for the Parquet files go.
I can easily just export to Parquet.
Now, since it's an external table
where the data is stored in Parquet,
not in SQL I can do a join between
that new external table and an existing SQL Server table.
No one even knows that the part of
the join is really Parquet data behind the scenes.
>> It's awesome, you're just writing SQL.
>> I'm just running SQL and
the data is all staying workshop along,
whether it's in SQL Server,
whether it's on DS3 system with Parquet.
This whole technology is based on
supported with Azure Blob Storage,
Data Lake Storage, or again,
any S3 compatible provider.
>> Awesome, I think, people are excited about this.
I'm excited about this
>> I think so, because people
want to use that common T-SQL language they know and love,
you've got security on top of this some in SQL perspective,
you can keep your SQL assets and your investments,
but still access things like Data Lakes.
>> Amazing.
>> I showed you new REST API Data Sources,
of course, using these three different types of technologies.
Also the ability to use Parquet and Delta natively in the system.
I didn't show you delta, but that's supported as well.
Then finally, the ability to do backup restore,
which is actually could be quite popular option.
Here's why, because S3 providers out there are now becoming
popular object's storage locations for all files in organizations.
We're just supporting SQL native backup, restore into them.
>> Awesome cool. Bob, thanks so much.
I learned a lot during this episode.
I think our viewers probably did too,
probably pretty excited about these new data virtualization.
>> Absolutely.
>> Scenarios. A call out we're
talking a lot about people go try stuff out.
You mentioned the SQL workshops,
so just a reminder to folks in the description,
we've got links to all things
to learn more, whether it's the deck,
the workshop, to learn module,
more videos, all stuff.
Be sure to check the links in
the description if you'd like this episode, go ahead,
give it a like leave us a comment and let us know
what you think of these new capabilities and
stay tuned for the next episode of the SQL Server 2022 series.
We hope to see you next time on Data Exposed.
[MUSIC]