>> Did you know that you can now do
data virtualization with Azure SQL Managed Instance?
Join me and Laura on this episode
of Data Exposed SQL Insider Series to
learn all about it and see some really cool demos for
how to get started this week on Data Exposed.
[MUSIC].
>> Hi, I'm Anna Hoffman and welcome to this episode
of Data Exposed SQL Insider Series.
I am super excited to be welcoming back Laura,
who was doing this before.
Laura works in the field with a lot of our customers on
deep engagements and with a lot of new technologies as well.
Laura, firstly, just welcome back to Data Exposed.
>> Thanks Anna for having me back. Nice to be here.
>> Always a pleasure to have you on the show.
Today we're going to be talking about something
I'm just not hearing about
it and this is data virtualization and Azure SQL Managed Instance.
Before we get into what it is,
how it works and hopefully some cool demo maybe near the end,
I wanted to ask what are some of the scenarios where we're seeing
customers want to use
data virtualization with Azure SQL Managed Instance?
>> First thing I'm super excited about this feature.
This feature lets customers query the data
on Blob Storage or ADLs Gen2 Storage,
their CSV or parquet files.
They don't have to actually load the data into Managed Instance.
This comes in handy like if you
get new data and files and you want to
explore it before you even figure out what to
do with it you can see what the format looks like,
you can see what data it contains.
You don't have to ever load it
into Managed Instances if you don't want to.
So if you need an archiving scenario,
maybe it's cooler or cold data,
you can push it out to your Blob Storage or to
ADLs Gen2 storage and just leave it there to query it.
You can also get away from having to do the whole ETL problem.
I don't have to necessarily use ADF or
some other tool to load it into my Managed Instance.
I can just leave it where it sits and query it.
Then I can because of the options and the functionality,
I can create an external table
or I could use a OPENROWSET to query it,
then I can actually use Power BI or any of
my other tools connect to my Managed Instance and
still query that data where it lives and not have
to worry about managing a separate component there.
>> Awesome. Cool. Yeah, it sounds there's a lot of different
actually reasons you might want to use this.
Today if you are going to try to be querying these files,
what can you currently do today?
>> Today I can store the files and
ADLs Gen2 or Azure Blob Storage and I can read the files.
If I have CSV files or parquet files,
I can read them where they live.
This comes in handy too because with Managed Instance we
do have some size limitations and so some customers
find that they can just flip
the data or lead certain data that's not queried super
frequently out on their storage and
then not have to increase the cost of their Managed Instance.
That's another potential benefit for leveraging this feature.
>> Cool. Awesome, yeah,
that makes a lot of sense.
Anything else we dive in and take a look?
>> I want to just touch on there's
two different ways to query the data.
>> Okay.
>> OPENROWSET, which lets you do like click
exploration but I can still create an external table.
If I updated that,
I want to repeatedly query saved shows up in a report,
that's where I would have you want to use
the external table because then we
get better performance out of that.
>> Got it. Makes sense.
>> Okay. Let's get into the demo.
>> Great.
>> To set this up, what I did I created a database,
MySQL insider virtualization demo database.
In the preview, we had to actually
enable the PolyBase feature within
the instance with a GA we don't have to do that anymore.
Also there was a change in some of the formatting.
You'll notice here on my screen I've got
this great external data source and you'll notice
that now it's prefaced with ABS.
This is a public data source.
It's an external public data source actually located in East US.
This means it's Azure Blob Storage.
If I weren't connecting to ADLs Gen2,
my Azure Data Lake Storage,
you would see an ADLs preface.
In the early preview,
you would have seen HTTPS.
I just want to call this out if you
had actually played with this early on,
there were some formatting changes.
You have the preface change and then you'll
also notice that it's in
the format of container at my storage accounts and then the past.
I have a couple options here on how to do this.
I can go here and I can create me external data source,
and then I can use this query here that's going
to go ahead and select using OPENROWSET.
It specifies the specific parquet file and then it
specifies the data source that I had just
created telling you what format.
In this case, parquet versus CSV file.
It'll go ahead and run this query
and you see I get my results back and so on.
I'm directly querying this without having done anything.
This data is not in a table in my Managed Instance.
I have another choice because I
can actually go ahead and I can query
this without specifying a data source.
In that scenario you'll notice that I still have my bulk,
but I give it the full path.
I'm telling it that it's on Azure Blob Storage,
the container at,
and then my storage account where it's located,
and then the full path to the file.
You can see it's specifying the specific per-case files there.
Until now I can go ahead and I can pull this data
back and it comes back pretty quick.
>> Got it. Yeah, it's quick.
This was the scenario you are saying where if
you wanted to do quick exploration,
you could use something like this?
>> Right. This is against a public dataset.
Then we get into what if I have a private dataset?
This is in my own storage that I've
got security around it in and I've got it locked down.
You can create your database scoped credential
and I want to call out that when NGA,
this supports managed identity as
well as the shared access signature.
You create your database scoped credential,
you create your external data source.
This is telling you basically again,
the path it's telling you my container and on
my storage account that I want to access and
then it's using that credential to access it.
Then I can go ahead and I can run a query.
Again, this is OPENROWSET.
This is I want to explore and see what's in here.
This is hitting a specific file so
you can see it's got the final name specified.
My data source is telling it
the actual storage account and
continuing and then this bulk is
providing the path and the filename.
But I can do more than that.
I can also go in and I can say
I want to select from all the files.
Notice that I have an asterisk.parquet.
It's going to pull back the data from all parquet files I
have in that fold and you notice I had six rows the first time,
this time I have 12.
I actually have two files out there with six rows
each and pulled back all of that data.
Another cool thing that you can do
if you run into sometimes when I may have
a lot of data out there and maybe
with parquet it will infer the schema,
with CSV it does not you have to tell it.
But maybe I don't like what it infer.
I can actually go in and I can use this SP describe against
it to see what the data types are
in the system types that it would specify.
If I don't like that,
like sometimes you might see where it does, I don't know,
an nvarchar and it might say 8,000 or something,
and you know that's not going to be
accurate maybe you know it's only going to be 50.
You could commit and you could change that.
Like in this scenario,
I'm still using my OPENROWSET and it's still for
a parquet but I'm actually specifying my data types here.
I can say my passenger count I want as eight,
maybe some trip distance float.
I can specify those and that
can sometimes get me better performance.
Another thing I can do,
even still with the OPENROWSET says I can create a view on it.
I can go ahead and say create view as
a select from my OPENROWSET query.
That I can select from the view that I
created so that I don't
actually have to specify all the rest of the state.
A DBA could set this up for say,
regular business users go ahead and query that.
Then the next fun one I can do is I can do
an external data source where I create my external table.
I create my Data Source and again,
it's still referencing this specific container in
my storage account and I
specified credential for that to access it.
I create my external file format,
so in this case again using
parquet and then I create my external tables.
I tell it what the columns are,
what the data types are,
and I specify the specific location and notice this
will pick up all parquet files in that task folder.
Then I can go ahead and I can query off of my external table.
You can see now again for I wanted to repeat queries and stuff
where I want better performance so maybe
analytics or Power BI type workloads,
this is what I would want to use for that.
>> Got to. Now it makes a lot of sense and I
love how easy it is to get
started with the first scenario
or method that you should have a beginning.
But then, yeah, you're going to
get really serious about this start joining
other tables or doing reporting on top of it then this is the way.
Would you actually be bringing the data in this case,
we are still querying at where?
>> Yeah, it's still residing external on the storage.
If I come into Management Studio here,
and if I go explore my tables,
you'll notice that I have an external table that shows,
and this is actually the table that we created.
The data for this table is not inside of Managed Instance,
so it's not hitting your storage
when they are adding to it any way,
it doesn't increase your backup sizes.
Again, because it's sitting on Blob Storage or ADLs,
that typically is more cost effective.
Now obviously it's not going to perform
exactly the same as if it were in the database.
But, you know,
there's always those tradeoffs, right?
>> One hundred percent. Then one more question,
let's say that more parquet files are added to this folder.
Do I need to go through this process again or is it one to just
pull whatever the latest is in the location that I specified?
>> That's a great question and that's where I go back to.
It depends on how you actually create it.
You'll notice here that in my location,
I did startup parquet.
That means within this location it will pick up all the files.
If I add new files,
they'll become visible as well.
They should fit a data from those will become visible.
>> Okay, cool. That's really awesome. Thanks so much, Laura.
This has been really insightful,
I learned a lot, I definitely want to go play with this.
As folks are getting started do you have any tips
or tricks or other advice for using this?
>> Let me say, pay close attention
to how you're going to use the data.
Decide between OPENROWSET and the external table,
dependent upon how you plan on using it because you really want
to get the best performance possible, and that's why I said,
OPENROWSET is good for the quick exploration of the files
but if it's something you're going to use repeatedly, then yes.
Absolutely go ahead and create that external table for it.
>> Awesome. Cool. Well, Laura,
again, thanks so much for coming on the show.
To our viewers if you like this episode,
go ahead, give it a like, subscribe to our channel,
leave us a comment and let us know what you think about
data virtualization and Azure SQL Managed Instance.
We hope to see you next time on Data Exposed.
[MUSIC]