Now that we've been granted
the appropriate permissions,
now let's start to actually query some data
and load that data into our workspace.
For that, we're going to go back to the Knowledge Center.
But this time, we're going to go to
the "Use samples immediately".
This will actually help us create
some external tables automatically and
query data that lives in
an external data lake with our Azure Open Datasets.
Once again, we're going to select our existing pool.
We could be using built-in,
but let's leverage our dedicated
SQL pool for this session.
Click on "Use sample".
You can see that once again,
it's automatically generated this script that will
actually create the external table
and the associated objects,
and allow us to query that data in the data lake.
Just to review this script real briefly,
the first object it creates is our external file format.
Then we'll run this first.
What this does is it basically just lets our pool know
what type of data or file format to expect.
In this case, our Azure Open Dataset
is in the parquet format.
The next request here
is going to create and define our external data source,
which once again, defines
the location that that data is going to live.
In this case, it's open-source taxi data
living in our Azure Open Data blob Storage account.
Then finally, this last script
creates our external table.
This is actually the object that
we are going to query and
actually will live inside our SQL pool as well.
That's one of the big advantages of
using an external table
to query data living outside Synapse.
It basically creates
this external table object which persists,
which we can then not only query
but then use it like
a table for other things within the dedicated SQL pool.
We can join it with other data.
We can put some security permissions over it.
There of course is going to be a little bit of
a performance loss because the data isn't living
right inside the managed storage where we can
have all of those great optimizations
and compressions on it,
but we didn't have to load
this data at all in order to start querying it.
We can have this query start running.
Like I said, depending on
the different regions that your workspace and
your SQL pool are in compared to
the blob Storage, and depending
if it's your first time querying,
and a couple other reasons,
the performance may be
slower than what you'd expect in a SQL pool.
But we've saved that time in not actually having
to load this data in the first place to query it.
It's a really great use-case for when you need to keep
some data inside your data lake,
whether it's to be accessed by other tools, or
you want to keep it in its raw file format,
or for accessing data that may live in other data lakes.
We didn't have to include any security permissions here
on this data source because this is an open blob storage.
But you can also authenticate using managed service or
using a storage authentication key so that it can
allow you to access
more private data lakes
that might be in other organizations in
your team and just
give you an option for if you can't actually
move that data into
your dedicated SQL pool in the first place.
You can see we were able to get
our full set of results over
this taxi data without having to
do any data movement into our SQL pool.
If we actually navigate over to our SQL pool,
we'll refresh the page or
just go over to our external tables,
you can see we actually have
this external table object now
persisted here in our database. So it
makes it very easy to collaborate,
easy to keep track of, because now once again,
I can view this table,
I can generate queries off of
it right here from my SQL pool,
and really in a lot of ways, treat it like I
would any other table available to me in my SQL pool.
Now, we've managed to query data in
a data lake living outside of our SQL pool,
but what if you actually wanted to load
that data into your dedicated SQL pool?
Of course, like we said, that gives you
some advantages like being able to have statistics,
columnstore indexes, results
at caching being run over that.
For data that you're accessing very frequently or
joining with really large datasets
and complicated queries,
you can definitely get a lot of value out of
moving it right into the SQL pool.
Really, there's a lot of different ways to do that.
You have different options
based off the tools you prefer to use.
If you're already leveraging Spark,
you can use Spark Cluster and Synapse to do that.
You can leverage our pipeline capabilities to do that.
You can actually even use
this external table to directly load that data in
just by using a CTAS statement.
But whether you prefer a code-based
or a code-free solution,
you've got a lot of different options.
The one I want to show up right
here is using our copy command,
which is really our newest way of loading data in.
At this point, it's been out and about
and fully GA for a bit now,
but it really is one of the most performant ways
if not the most performant way to load
a SQL pool, and also the simplest.
Go to our Knowledge Center.
Don't initiate the guided tour.
Go to our "Browse gallery", and
then from here, "SQL scripts",
and then look for loading data in using the copy command.
We're going to look for the copy command statement,
which is right here, load our New York taxicab dataset.
We'll continue. Same thing,
look at the script, and then put
it over our existing SQL pool.
We'll open this script, and once again,
we'll take some time to go through this script,
see what's going on.
There looks like there's a lot going on,
but this is a relatively straightforward script.
With the copy command,
it's a little different than the external table format.
You don't need to create
that external file format and
that external data source first.
You can just run the copy command,
but you do need to have the table created ahead
of time in order to load the data in.
That's really what most of this script is.
It's just creating the different tables
that we'll be using to actually load this data in.
The big thing that I want to call out that does relate
to performance and optimization is,
as you may know,
every table in a dedicated SQL pool
has both a distribution option and an index option.
This really just relates to how the data is
stored in our managed storage,
whether or not we're building it over indexes in
a columnar format versus a row-based heap format, and
also whether or not we're distributing
it based off a specific hash key
or just a round-robin even distribution.
Based off whether you're looking for
maximum loading performance or maximum query performance,
and based off whether you know the
queries and the distribution keys
you'll be going to be running over,
you have a couple of different options.
The default ones we have here are
a round-robin distribution, which
is great for minimizing data skew,
especially if we don't know
the types of queries we're going to be running yet,
and then clustered columnstore index,
which is just a great default option,
especially as the data, we start to bring in
more and more rows for
ensuring that you have good compression.
We can talk about more of those options you have later.
The only other thing to say
here is that if you're looking
for maximum loading performance,
you can also choose to do a heap table here instead.
That will basically just be
row groups as opposed to the columnar distribution.
That'll give you maximum loading performance.
Then later, you can run
a CTAS statement to switch over to
a clustered columnstore or even in
an ordered columnstore index if you want.
But we'll just keep everything on the defaults here.
You can see we're creating all of
the different tables for
our New York taxi data we'll be loading in.
Then after the tables are created,
we can start to get into our actual copy operations.
I'll start letting this run just so it can start
the loading while I explain this copy statement.
You can see here that it's actually very simple,
which is part of why we love to use it.
It's because it's both simple
but also very performant.
All you need to do is identify
the table you're copying into,
where you're copying from, in this case,
our New York taxi blob,
and then the file type, CSV,
we're using a comma terminator,
an empty field quote,
and then another great thing here is we're
using a label as part of this copy operation.
That will allow us to actually track this data as well.
While this loads, if you go back to
the Knowledge Center, in our gallery for our SQL scripts,
you can find other scripts that are actually
about monitoring the execution of your copy statement.
We want to open this up all the way.
But you can see here that you can
actually track labels and
other options of that copy statement
to actually track those loads as it happens.
Just by running this as part of a stored procedure,
you can actually get a really good view
over what's going on in that job.
We won't run that now because we're loading
a relatively small amount of data.
We'll just flip back over to
our Data hub and look at
the execution of this as it runs.
You can see that we're creating these tables and then
we'll be loading each of
these tables from these files into blob storage in turn.