Now I can't run anything yet.
This is grayed out because I
haven't selected my connection.
I'm going to stop by selecting
"Built-in" as mentioned before,
this means I'm connected to serverless.
I'm going to stop again just
by running a basic query here.
We're going to run this and you'll see
here that this is a little
different than I did last time.
What this has done is it's got some wildcards.
It's actually got three wildcards in
it and that's because there's
the hierarchical structure under
here has multiple years.
The years are broken into months,
and then there's a name,
multiple files with a POC extinction at the end.
What this is telling serverless is
to look at the totality of
a hierarchical file and folder system,
effectively the root here with
yellow and then any wildcards that
match this pattern and just give me the top 100 rows.
That's what's happened here and so we get
the 100 results back here and you
can see what that looks like.
I'm going to scroll down here now
that we've got that and you can
see there's a couple of other examples
there. They're very similar.
One is for public holiday data,
one is for weather data but by large,
it's exactly the same query.
It's got the same wildcards.
Different amounts of wildcards,
but functionally the same thing.
I'm not going to run those.
I want to move down to something
a little more complex and
introduce a keyword called code file path.
What you can see here is
a slightly more complicated query
because we're adding some
group-bys and what have you.
At the very top, we've got standard SQL functions.
The year function to extract the year out
of a DateTime or a DateTime to type,
count is just count.
The other wild set remains consistent.
We've got the wildcards that we
just talked about above.
The format is parquet.
But this file path keyword is interesting.
This is not a column line.
This is a keyword in serverless that enables us to
leverage the partition elimination capabilities
of the file system in the Data Lake itself.
Now, partition elimination has for a long time,
particularly in analytics scenarios been
a fantastic feature for accelerating performance.
Partitions enable you to effectively
structure your ranges of data within a table
and the optimizer can use metadata to
understands the guarantees of what
data will exist within each partition
and query runtime can simply ignore
a number of partitions in a table which could
represent a lot of data
in a large amount of IO and zero on a partition.
In fact, tables and data warehouses in SQL Server are
a great example of where
you are partitioning was commonly applied.
They also gave you some malleability with
things like switching and merging and splitting,
which we won't go into here but
the great partitioning features as well.
Filepath does effectively enables
you to do the same thing.
But here we're telling the query to filter based on
a folder name instead of
metadata in the database itself.
What you can see here,
if I go run this query,
is that I've got everything that I just mentioned.
But I'm asking the file path with
a parameter of one
to look for everything greater than 2009,
and less than 2010.
With this scenario, this one right
here is mapping to the first instance of my wildcard,
which is probably intuitive
in the sense that 2009 is clearly a year,
but there's a couple of other
perimeters here as well.
Although in the storage system,
maybe we've got years 2000 through 2020,
we've been able to accelerate this query
by filtering in just
on 2009 and 2010 using the file path keyword.
Now, to give you
a better example of what this looks like,
what I'll actually going to do is show you filepath.
I can actually use it outside of
the ware clause in the select as well.
What I'd like to do is put in filepath.
I need to put the NYC
at the front of that as well.
I can run "Filepath" with no parameters,
or I can put in "NYC
Filepath" and give it
the one that we're referencing below as well.
Now, because I'm doing an aggregate, of course,
I just need to grab these,
and I need to put them into my "GROUP BY" clause.
I'll just put them right in here and we'll tidy it up
a little bit because I like to try to the limit,
but I can anyway keep my code tidy.
Where our previous results had showed this,
I'm actually going to go run this
now. This will run for a little bit.
What did I miss here?
I typed it slightly wrong here,
to show you that it's all real.
We'll fix that up and run it again.
While this runs, what you're going to
see is I'm going to get two additional columns back,
of course, for "Filepath" and "Filepath 1."
You'll see the value for filepath without
the parameter is effectively
the full path to the file.
Then the one will show you
hopefully just the value 2009.
It's important to note here
that it's really picking up
everything off to the equal sign in this asterisk,
it's what's running here, and so
that's the 2009 here.
Had I moved this and I won't run this,
but just to provide a better example,
had I moved this to change it to something like that
where I'm bringing the asterisk
in and eliminating equals,
all I'd really need to do then is put equals in here.
There's an element of concatenating
the value together to produce the string,
but it just gives you an example of what's
actually happening under the covers.
I won't go do that.
I'll put the equals back up here.
But it gives you tremendous flexibility.
You could imagine perhaps,
other patterns as well that you've got
multiple asterisks while I cast
in that single path location.
But as I mentioned, now we get
the full path name back, which is interesting.
That's where the data is,
which we already know.
But you can map the wild cards to it.
So pu year equals 2009, is right there.
We got the month, which is right there.
Then we've actually got the parquet name right here.
These are also the highly variable,
which is why the wild card is useful.
Two thousand nine is exactly what we
expect because we told that
"NYC Filepath 1" needs
to be greater than or equal to 2009.
That's what we have right here,
and then back over here as well,
with the value that we had before.
We changed the aggregate of course because now
I'm grouping by the file name.
Maybe that's not super useful.
Let me back this out a bit to where we were.
I'm going to go back to just the year,
and I'll back up my select statement as well.
I'm going to run this again,
because what I'd like to show you is how you
can also flip from
a tabular result set to
look at these things visually as well.
One of the things that we built
into Synapse Studio is
the ability to quickly
visualize data within the studio.
I'm personally quite guilty
of cutting and pasting result sets from
things like Management Studio and other tools
into Excel to visualize the data.
But at the bottom here, you can see we've got
this simple toggle from table or chart.
I'm going to click on this
and I'm going to lift it up here.
It defaults to a line chart.
Actually, it put both lines. This doesn't
really help me too much.
But on the right-hand side,
I can control this.
My current series, or my category I should say,
is really the current year.
That's what I want here.
Then my chart, let's just make this a column.
As I can quickly start to actually
see the boundaries here, they're very similar.
The columns look very similar.
It's given me the legends,
sorry, the y axis at a base of zero,
but you can mouse over and you can obviously see
the actual value of 170.8 million,
169 fraction over 169 million as well.
You can do more complicated charting of course,
if I had more complicated result sets,
but just an example
of how you can start to look at this.
It's not at all a replacement
for a more robust BI tool for visualization like
Power BI because as a developer who just wants to get
a really quick glance at
the data without some squinting at the numbers,
it's actually a very useful feature.
Switch back to the table.
That's pretty much everything I
wanted to show in this section.
This sample here has a set of
other examples as well still using the file path.
It provides some additional examples
on how to do different types of calculations over.
You can see here we're using widths or
common table expressions
supported within serverless SQL,
as well as still using
the open row set functionality,
still using file path as I mentioned.
Then there's something a little bit
more convoluted down here
where we're getting into additional filtering here,
your multiple where clauses
or multiple filters within
the where clause I should say.
Then a whole bunch of
aggregates being thrown to data.
I'm not going to run these right now,
but just to give you some examples that really,
once you've got that open row set,
bulk in format to find,
you've got tremendous flexibility with
the T-SQL language and the ability to,
of course then visualize that locally in
the studio or all three tools such as Power BI.
It's just SQL after that.