-: Hey, what's up Gurus.
In this lesson, we are going to talk about
building external tables.
What do I mean by that?
Well, we're going to take a look at first,
what is an external table?
Then we're going to talk about the syntax that you need
in order to build external tables.
So let's get started by talking about
what an external table actually is.
Well, an external table is just a table that comes
from files that are stored outside of your database.
Pretty simple, external table.
It is not used for frequently access tables.
So you would not use this lesson of
the stuff we're talking about for frequently access tables,
but why do we care?
Well, when we need to access data
without copying an entire data set,
an external table is an excellent way to do it.
Basically, it gives you a way to access data
hosted outside of your database, quickly.
Having said that, now that we have some basics around
what an external table is,
let's take a look at some sample syntax
so that we can start to understand what we actually do.
So there's a couple of ways that we can do this.
We can create and alter external tables in Azure Storage
and we can also use external tables with Synapse.
For our purposes, I am going to jump us in
and we'll talk about Azure Storage as our example.
So what you're looking at here is
a sample script for Azure Storage.
So let's start by kind of breaking down
what's going on with the syntax.
So the very first line here, we have 3 options.
We have .create, .alter, or .create-or-alter.
So .create is going to fail if your table already exists.
So you want to use this if you don't have existing tables.
You would use .alter if your table already exists
and we just want to change what's there.
Obviously, if there's not a table, that's going to fail
or we could use the .create-or-altar if we're not sure
and this is not going to fail either way.
It's just going to move forward and create and
or replace your table.
And so then after that
we're simply going to use the external table
and we are going to include the schema that we want to use.
And I'll show you an example of what that looks like
in a minute, but that's where that goes.
Next up, we are going to specify our kind.
And so this is just the type of external table
that we want to create.
So we could use Storage or SQL or whatever we're looking at.
Next partition by
and we've talked quite a bit about partitions,
but this is just going to be our comma separated list
of columns that we want.
So I'm not going to dive too far into that.
You would do your partition by
and then include your path format.
And the backslash is going to be the default
if you don't do anything here.
So if you don't include that line at all,
it's just going to use that default.
And then finally down here at the bottom,
we need to specify our data format.
So there's a couple of different formats that we can use
and you would specify .avro,
or CSV, or JSON, or parquet, those are the most common ones.
There are more, but you would look and see what you wanted.
That's going to be there under that format.
And then we would just do our connection string
so that we know how to connect to our data.
Alright. So that's the basics. I'm going to jump over now
and I'm going to show you an example.
So what we just talked about is on the top
and then I did an example down here at the bottom,
so you can kind of see what it looks like.
So in the example, at the bottom, we did .create.
So we're choosing to create a table.
If it already exists, this is going to fail.
So we have our .create.
We have our table
and then we're just creating an external table here.
And then we are going to give it our schema,
we're going to use Timestamp and CourseName.
And so the Timestamp is going to be our column
and then the colon.
And then after that, we're going to have the column type.
So this is going to be datetime or string.
And so you would just specify all of the columns
that go into your external table that you want to create.
Kind of table we're creating. Next line, storage,
so we're just going to do our storage here.
And then we're going to partition this thing
and we're going to partition it
by month using datetime at the start of the month.
So we would specify our partition information here
and then below that you can see our path format. So this
is just going to be where we specify the path format.
So it's going to be year/month.
So you can see that there as well.
And then for our data format, I'm going to use parquet
and then below there in red, my files are located here.
That's just going to be that storage connection string
that we would include.
So that is the basics of the syntax
for creating external tables.
Couple of points to remember:
One, these are for tables that are outside of your database.
And then two, we are using a KUSTO schema
in order to create those external tables
in Azure Blob Storage or Azure Data Lake.
Now keep in mind, you can also do this in Azure Synapse.
So don't get thrown off on the DP-203
thinking you can only do this
in Blob Storage or a Data Lake.
If you got that down,
you should be just fine to move on to the next lesson.
Again, external tables - outside of your database,
Blob Storage, Data Lake, and Azure Synapse.
Alright, that's it for this lesson,
I'll see you in the next.