>> There are several ways of loading data into Azure SQL.
One of them is using an external storage such as Blobstore.
Now, Anna is here today to tell us how to do that,
and show us a little demo on how that works,
today on Data Exposed.
[MUSIC]
>> Hi and welcome to another episode of Data Exposed.
I'm your host Jeroen, and I'm in
the studio today with Anna here. Hi, Anna.
>> Hey Jeroen, thanks for having me.
>> Sure, you are always welcome.
So we're going to talk about something specific,
which is talking about loading data.
So loading data is something that's familiar to
everyone that has ever worked with databases.
Now, when we come to the Cloud,
is there anything that changes here?
Is there anything that I need to know that might be
working differently or new challenges that present themselves?
>> Sure. Yeah, I mean, there's certainly
new ways you might be getting your data.
For example, it might not be stored somewhere On-prem,
it might be stored somewhere in Azure.
In this example, we want to take a look at
if it's stored in Azure Blob Storage,
how can you get that data that might be stored in a.DAT
file or some other format into Azure SQL.
>> Okay, cool. Well, let's just
get started and show us how it's done.
>> So what we're going to use today is an Azure SQL Notebook.
So you see I'm connected to
the SQL Kernel and then attached to my Azure SQL database.
What we're going to do here is try
to import some data using Bulk Insert,
something probably our SQL gurus are very familiar with,
and we're going to try to access it.
Now, when you want to access these different types of storage,
you have to have access to that.
>> Sure.
>> You can do that sometimes using Azure Active Directory,
using some shared key authorization,
and here we're going to use a Shared Access Signature.
>> Right.
>> SAS
>> SAS, yes.
>> Exactly.
>> Yes.
>> So let's go ahead and
dive into you walking through these steps.
Now, the first thing we're going to do is
create a table and schema.
We all know in order to insert data,
we have to have somewhere to insert that data.
>> Of course, otherwise, it goes nowhere.
>> Yeah. So in this case,
we're creating a table called store returns,
and then we're going to create a master key.
We're going to use this master key so that we can
create a database scope credential.
It appears I already have this one,
so that's fine, we'll just use that one.
>> Sure, so long as you remember the credentials, it's okay..
>> So now, it's an interesting part.
We're going to get
a Shared Access Signature from that Blob Storage.
So you can do this using Azure Data Explorer or the Azure portal,
but you have to acquire that signature first, that SAS token.
Now, a few things. Since this token is going to
be blurred out in the video,
so people can't access my data.
There will be a question mark at the beginning of that SAS token,
you need to remove that.
So that can be something to get tripped up on.
>> It's a no.
>> So I'm going to go ahead and do that and
create that Shared Access Signature or SAS token.
The next thing I'm going to do is create an external data source.
Now, if you use some of the more recent versions of SQL Server,
you've seen something similar to
this to access things like PolyBase.
>> Yeah.
>> This is a little bit different,
but the concept is the same.
We are creating an external data source called dataset.
We're specifying its Blob Storage,
we're specifying the location,
and that credential we just created
>> Okay. So we use a credential now it's
basically linked to the external source
and make sure that everything works.
So is there anything
specific I need to know about this command here?
>> Yeah. So just like the question mark in the SAS token,
it's a good question, this one,
the location doesn't have
a trailing slash or hyphen or whatever you want to call it.
So just make sure you remove that because that's
another thing that could potentially trip you up.
>> Because it would just give an error and not work-
>> Yes.
>> Assumingly? Okay.
>> Exactly.
>> Good.
>> So the final step is we're ready to go
ahead and bulk insert a single file.
So I'm going to click this off
and then we can talk about what's happening.
>> Right. So there's a lot of comments in here.
It's a pretty lengthy comment.
So can we just talk about what you're doing here?
>> Yeah, exactly. So there's a few things in here
I wanted to add some comments so that you can
get insight into a few very basic tips
on how you could potentially make this run faster.
So if you're doing much larger data imports or boatloads,
you can get the best performance.
So the first one is I'm setting no count on,
and this can basically reduce your network traffic,
and there's some more info there.
Then just to review what we did,
we're going to bulk insert into
that store returns table that we created in step one.
Then we have to specify what blob and container,
and in this case, what file we want to insert.
So that's what we're doing here.
Then we're going to use the external data source,
the one we created.
>> Right, the one we set up.
>> Yes, exactly.
>> Yes.
>> Specify a few other things.
Now, if possible, loading in batch can help,
especially when you think about Chatty applications,
if you can batch them,
that will definitely increase
your performance by reducing network traffic.
Then finally, a tablock,
which can just help create less that goes
into the log during this insert operation.
>> Cool. So there's things I can
change to make sure the performance
is to best as I can [inaudible]
>> Yeah, and there's a ton more things you
can do to increase the throughput you get here.
There are other tools you can use like
Azure Data Factory or even Azure Databricks to get data in here.
But this is just one example.
So this has completed.
So we can just do a quick select
from that table and see how much we imported in about a minute.
>> Right. It was empty before we started?
>> It was empty. So we see
we brought in almost three million rows.
Yeah, so now you've seen how to import
a file from Blob Storage into Azure SQL database.
>> Right. Well, thanks for showing us that.
There's, of course, several ways of loading data into Azure SQL,
one of which is now using the external data source from Blobstore.
So there's more options,
more choices to make.
But hey, this is just one of them.
So I hope this was useful.
Please like, subscribe, leave a comment,
and I hope to see you next time. Thanks.
[MUSIC]