>> Join Andy Cutler and I,
as he takes us through how easy it is
to bring you SQL expertise to
the Data Lake with serverless SQL pools
on the next episode of Data Exposed,
MBP addition
[MUSIC]
Join Andy Cutler and I as he teaches us how to bring
your SQL expertise to the Data Lake with serverless SQL pools.
Hi, I'm your guest host Ray Merritt and welcome
to this edition of Data Exposed MVP edition.
Today I'm joined by Andy Cutler,
a data platform MVP since 2021.
Let's get started Andy by
telling me a little bit about what you do.
>> Hey Ray, It's good to be here.
I hope you well. My name's Andy Cutler.
I'm born and working in the UK and yes,
been a data platform MVP very recently.
I work predominantly with the Azure Data Platform
and Microsoft states platform products
in the realm of data warehousing.
>> I said today you're going to talk about
the Data Lake and serverless SQL pool.
Let's just go ahead and jump right in.
>> Great, well,
I've got the subtitle heading of serverless SQL pools,
which is one of the features within Synapse Analytics.
Just wanted to take a second just before we jump in and
start writing SQL to set the landscape a little bit.
Magically got all this data.
You've got databases, you've got CRM systems in the Cloud,
your clickstream data, and you've got On-premises data as well.
Plenty datacenters with data in there.
We've got it in a Data Lake.
Very common pattern now where we're loading
that raw data into the Data Lake.
People with SQL skills are wondering,
can I bring those SQL skills to the Data Lake?
Lot stuff lying around about Spark and about
Python and the answer's yes.
This is why I love serverless SQL pools because we can spin
up a Synapse Analytics workspace very quickly and
use the inbuilt serverless SQL pools
engine to query that data that's in the Data Lake
using SQL because it has
a SQL endpoint and we can connect in SQL clients.
Think about as your Data Studio,
SSMS Management Studio, Power BI.
Anything that can send a SQL query can connect into
serverless SQL pools and get at that data in the Data Lake.
The great thing about serverless SQL pools,
and one thing I always keep going back
to what I talk about serverless
is its support for your common garden every day SQL objects.
We've got store procedures to encapsulate logic.
We can create logins and we can create users.
We've also got SQL uses that we create in
serverless SQL pools as
well and views we're going to go into a little bit later on,
but we can create standard SQL views.
This is where it deviates a little bit.
We're not creating standard tables
where we import data into those tables.
We only have external tables and
that's to cost structure over that data.
In the Data Lake, we've got functions that we can create.
We've got access to dynamic management views as well,
so we can see system properties
within the serverless SQL pool servers.
Lastly, system tables say metadata about our objects
like external tables are all stored and we've got access to that.
>> I don't really see a whole lot here that I don't already deal
with in my life as a DBA so that's good.
>> It is, it just means that if you're
familiar with SQL and you can be a DBA,
you can be a Data Engineer or Data Warehouse developer that
deals predominantly with SQL or data analyst.
Just bring your SQL skills to server
SQL pools a few little functions to get used to,
but nothing you can't do in a couple of hours work really.
>> Fantastic so let's jump in.
>> Already have a Synapse workspace setup.
What we're going to do is we're going to jump
into the Develop tab,
which is where I have all of my SQL scripts that I've created.
Now on the right-hand side,
we have the Data Lake already visible.
Now in my data here,
I do have it partitioned by year, month, and date.
I'm just going to go into one of the folders and we'll see that we
have a file in there and this is a CSV file.
It's a delimited file that I can query.
First thing I'm going to do is I'm going to create a database.
It's the same syntax when you create a database in SQL Server.
I'll call it SQL Data Exposed.
What I'll do is I'm just going to make
sure I've got my master database selected.
Create that database.
I'm just going to bring up the messages.
Then I'm going to add a couple of extra objects that allows
me to create objects and query the Data Lake.
I've got a master key that I'm going to set up.
I'm just going to switch to my new database.
There we go SQL Data Exposed.
Then we can create a database scoped credential,
which is using managed identity.
It's not my user.
That's going to get passed down to the Data Lake.
It's the managed identity of
the synapse workspace itself so I run that.
The last thing I'm going to do is I'm dealing with Parquet files,
which is an analytical file formats.
It's really useful to be able
to query this parquet files and set the collation to UTF-8.
Once I've done that, I've got my database setup.
Actually I'm going to go away and I'm just going to query
a CSV file directly.
Suffer cracking query CSV.
You can see I've got an OPENROWSET command.
Again. This is a command that already exists,
is not unique to
serverless SQL pools and I've got a whole bunch of options,
because it's a CSV I've got to tell
serverless SQL pools where it says, does it have a header?
How is the data delimited?
Now, I've just got the entire address of that file.
I'm going all the way down to a specific date folder.
If I run that query,
so I'm just going to check then select that database.
I don't have to select that database, but we're doing it here.
>> I've got my results. That is
the data that sat in the data lake.
I haven't imported it anywhere.
It sits in that Data Lake and we can query it
directly using serverless SQL pools.
>> Let me ask you, is there any way of creating
these objects so that you don't have
to put everything and just type
into SQL to query data all the time?
>> Yes. We do because you're right there.
We've got a whole bunch of SQL here that
we'd have to type out every
time we wanted to query data in a data lake.
What we can do is, so firstly,
I'm going to create an external table.
I can create common SQL objects schema,
I'll call it LDW.
I always create schema called LDW logical data warehouse.
I can create an external data source.
It's essentially a pointer to the Data Lake,
so I don't have to keep entering
the URL of the Data Lake every time I want to query something.
I'm going to use that managed identity credential.
Every time I use that data source,
it's just going to pass through the managed identity
down into the Data Lake so I create that.
The last thing I can do is I can create a file formats that
tells serverless the file format
that we're dealing with when we create the external table.
Again, look very simple few commands,
they're just set the format type as
a parquet and create that external file format.
Then the last thing is we create the external table itself.
Now as you can see, it's like I'm
created a table in any SQL Database.
I've got the external keyword there, I need that.
I'm just going to select
everything and then we've got the Location.
Where is the data?
In my Data Lake?
What's the data source?
We created that data source earlier,
the pointer to the full URL of the Data Lake.
What file format is it?
They are required fields or
required options for creating an external table.
If I run that, and
now I can just select it as if it was a regular table.
They can expose that table to
any downstream applications like Power BI,
or uses data analysts,
psycho analysts and I've got my data nicely packaged up.
I don't have to keep writing out
the OPENROWSET to go and get that data every time.
That's one way we can do it.
The second way is creating a view.
Now, I like creating views because we can bring in
one of these new SQL functions
that comes with serverless SQL pools,
and that is to do with partitioned data in the Data Lake.
What I've done here, I'm going to use the OPENROWSET command.
I'm going to point to a location in my Data Lake.
Now, I've got three wildcards here.
Now, we're going to ignore the parquet wildcard.
I've got a wildcard for year, month, and date.
What I can do, I can use the file path function.
This is unique to serverless SQL pools and it
enables me to return the folder Partition Information.
As you can say, I've just got regular SQL functions to
cast the results of that to a small int for the year,
a tiny int for the month and a day for the dates.
Then what I can do is I can actually use those columns I've
created in that view to query and do something very important,
which is called partition pruning.
This is essentially only going and processing
the data from the specific folders that you want to process.
That is very important for two reasons is serverless SQL pools.
Number 1, we can reduce
cost because that's how serverless is costed.
It's the amount of data that you're processing and number 2,
is performance if it doesn't have to read a lot of
data to just filter out, it's going to be faster.
I can specify a schema. I'm going to do that now.
I'm going to select,
I'm going to create my view and
it's going to make sure that I've got my database is selected.
Once that's views created,
I can do the same thing that I was doing with external tables,
which is querying them.
But this time I'm going to use one of those columns that I
created using the file path function as a filter.
What that does is you can save
query comes back in a couple of seconds.
There is a lot of data and that Data Lake and I've gone to
a specific folder and I've just been able
to aggregate the data from that folder using.
I keep going back to this standard SQL syntax.
I'm aggregating, I'm using the group by,
I'm using where and that's all dataset in the Data Lake.
>> Awesome. I love it when I can
bring my skill set that I already have to something.
I just wanted to ask you to as we wrap things up.
This lighthouse architecture that you've talked about and
then I've heard about can serverless SQL pools play apart there?
>> Yes, it can. What's really emerged over the last 12 months,
18 months is serverless SQL being
used after the data has been processed.
What I'm going to do is I'm going to click into My Query Data.
Now, Delta is a technology
that is underpinning this lake house architecture.
There are other file formats out there as well.
What Delta does, Delta stores data,
it's stores schema information,
but it also has some transactional consistency as well.
Hence, the lake house,
whether the data lake into the Data Warehouse there.
Now what serverless SQL pools can do, again,
we can run SQL over the Delta data.
We've got support for Delta.
At the bottom there, we just specify format equals Delta.
What I love about using Server SQL pools Query Data is we don't
have to tell serverless SQL pools how it's partitioned.
We don't have to use that file path function over the Delta data.
We point the datasource.
We point the query at the location of that Delta table,
and it's going to automatically pick up a partition functions,
the folders in which the data is
partitioned and I can use that in a query to filter.
I'll just select top 10, I'm not aggregating.
I'm still using that filter there. I'm going to run that.
This we can say we get a result set back.
What's great is the event year,
the event month, and the event date.
They don't actually data that's in the data itself.
The position folders within
the Data Lake that are just visible by using Delta.
That's what I really love about set.
>> Nice. Now, I think this is fantastic.
Big data stored in Azure Data Lake can be
analyzed using Synapse Analytics,
serverless SQL pools and you make it look like
any SQL developer can bring
their expertise to analyzing data at scale.
>> Exactly. Just bring your SQL knowledge
and us going to open up opportunities for you.
>> Fantastic. Thanks for talking with us today Andy,
about analyzing your data at scale.
Don't forget to like this video and subscribe to the channel.
Thanks to all of you for tuning in,
you can find links and more details
about what he talks about today in the description below,
and we'll see y'all on
the next edition of Data Exposed in VP edition.
[MUSIC]