>> Learn how you can do data engineering
with Azure Synapse Serverless Pools.
This week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman and welcome to
this episode of Data Exposed MVP Edition.
Today I'm joined by Alexander.
Alexander, thanks so much for joining us today.
Can you tell us a little bit about what you do?
>> Anna, thank you for having me. I'm Alexander,
I am from Sweden.
I'm a data platform MVP,
which means that I spend most of my days with databases,
some sequel server, RBI,
Azure Synapse Analytics, and the like.
>> Awesome, cool. Today you're going to be showing us
quite a few new things that I've never seen before.
One of those things is around Azure Synapse Serverless pools.
Now for our viewers and for myself,
can you tell us a little bit about what actually are
serverless pools and how that relates to SQL and synapse?
>> Oh sure, I'll tell you what it is and I'll walk you
basically through how to set it up and give you some examples.
The synapse on-demand pools,
it's a SQL-based queries service.
It's part of the Azure Synapse suite.
It allows reading and writing,
more about in a bit, of text-based data stored in a data lake.
Setting it up is pretty easy considering it's already built-in.
All I pretty much have to do is point it at
a data lake and off to the races I go.
>> It sounds pretty cool. Would love if you
have some scenarios or examples to show us
of how to get started or what we can do with
synapse pools or serverless pools. That would be great.
>> Funny you should ask because I have
everything prepared as a good TV chef.
I am from Sweden and Sweden and TV chefs, you know.
What do I have here is the Azure Synapse Studio.
I need to apologize [inaudible] because I will say something
about a serverless pool or
an on-demand pool or God knows what? It is the same thing.
Serverless pools or on-demand pools,
we're talking about the same thing.
This is a data lake and I have a dataset
with parking tickets. Here we have those.
They are from New York and we have parking tickets from 2015-2017.
These are stored in CSV files.
Fairly half the CSV files,
this one is 2.7 gigs.
What I can do here is in a normal day,
I would take all these files and dump them into
a SQL database just to see what it's all about.
But I don't have to do that because I can take one of
these files and basically just to do a right-click,
create a script,
that gives me something to work with.
I can press play on tape.
It's going to go out and fetch me
some data or I should say it's supposed to be doing exactly that,
which it finally did.
What it's doing is it's doing an OPENROWSET and I am
pointing it to the directory,
if you will, the URL to my data lake.
It just whipped up my first 100 rows. It's pretty quick.
I did 100 rows and it didn't scan very much as 135 megabytes.
What I have here is I have the headers
because I specified that I want to have a header row.
Otherwise, it is reading
CSV files as if I was just doing normal SQL.
>> That's pretty cool.
>> It is really cool and it's really, really useful.
The thing would CSV files are that they're big and there are
better ways to store text data
that are way more compressed and way faster to use.
One such is parquet.
I can just do this.
What I'm basically doing is that I'm creating an external table.
I'm explaining to synapse that I want to store this as a parquet.
Otherwise I'm just doing the same select from the same CSV files.
Now, we're going to run into a bit of an issue here because of
the way the parser in version two works.
It behaves somewhat differently than one
might expect when it comes to dates because
it's going to think that this date is
a varchar and that makes it hard to do date logic on it.
But there is a way to sort that just
as long as one is aware of what we're doing.
That is to just add a convert as we go along.
We are converting from 101,
which is the American format
to putting it into another column like so.
Let's run this.
I think it's going to be about
30-ish seconds because we are going to
be spending quite a long time scanning data.
I think it's about 7.5 gigs of data.
Again, I am not reading this in the database.
I am just using the serverless pools to
attach to a text file, or in this case,
a number of text files which is
pulling data without having to do and oh,
there we go, 25 seconds.
What's even more is,
we can take a peek and we can see that
we just scanned 7.3 gigabytes.
I wrote 199 megabytes.
Yes, parquet is a tad more compressed. Kind of neat thread.
>> Very cool.
>> Then we can start to see what we have in here.
Just checking what dates we have.
Remember that we had 2015,
'16, and '17,
but everybody knows that data quality,
well, kind of a moving target.
I need to make sure that I don't have anything strange in here,
which I do because I'm sure that
2047 has yet to be and I mean 2020 was bad enough,
we don't need 2047 on top of that.
There's a lot of weird data in this. What do we do?
Well, create a new parquet file
with basically just a where statement.
Again, remember, I am not
running this in a relational database,
I am running this in the serverless pool.
It is going to this text files and doing all this magic on top,
just as if it was a relational database.
That's neat.
>> Is it using PolyBase behind
the scenes to push down those queries, or how does this work?
>> Well, it comes from the idea of PolyBase,
but we are using OPENROWSET instead.
But it's under the hood.
If you will, it works pretty much the same way.
I'm specifying a file format and I'm specifying a directory.
Then it's going to use the OPENROWSET just to
attach to that data. There we go.
>> Awesome.
>> How many states do you have in the United States?
>> Fifty I believe.
If you're not counting territories it's 50.
>> If you're not counting territories,
now, that's right.
Sixty-nine is probably a tad too high.
>> Yes.
>> I think there might be some strangeness in the data,
because I'm adding all the states and I'm adding
on the territories as well just to see what I'm looking at.
Remember, I'm working through
7.5 gigabytes of data and I'm doing this on the fly.
There are no indexes and there
is nothing in memory, nothing at all.
It is reading everything from the data lake and still
I'm able to do this in roughly 30 seconds.
This would definitely hurt any database,
just a long list of characters, 35.
We'll see how long it takes.
>> This is really cool. I think what's cool here is you're
using T-SQL to query parquet files,
which is also a very cool thing.
>> It is. There we have it.
I have 17 records.
I've scanned 25 megabytes,
and I can tell that there are some interesting numbers.
The data has some issues and that's
something that we need to take care of upstream.
It's outside of the scope of this.
But now when we have the data here,
let me show you something pretty neat.
There's an MVP called Andy Mallon and he wrote
a great blog post on bucketing back in 2019.
I've pretty much stolen that wholesale.
What I'm doing here is I am specifying
a bucket size for the age of my tickets.
That means that I get the days old,
I have the months old and I have the bucket floor.
This says the bucket ceiling
depending on the number of my buckets.
Suddenly I know the dates.
Well, when I have the dates and how many months and so on.
Taken that to the next level [inaudible] would say.
I am able to do,
which is basically a report.
Here I am doing bucketing, and with this,
I can see that 30-36 months I have 239 tickets.
As the number of months go up, again,
because this is a historical dataset,
the number of tickets go up.
Of course if we want to, there's also this kind of
cool chart button that I can use
to visualize my data to get a feel for it.
All this again,
there is no normal SQL Server anywhere in this demo.
It is just parquet file or CSV
files and I can do so much fun with this.
This is definitely the go-to tool for a lot
of both simple and then medium heavy lifting data engineering.
>> Awesome. Cool Alexander. This has been really cool.
As someone who's never seen serverless pools before,
it was really cool how you were able to
just get started really easily,
use T-SQL you already know,
and go from CSV to parquet and then even to
some cool reporting and charting all in one place.
Very cool. As a wrap up,
do you have any final tips
for folks that are just getting started?
Anything they should keep in mind,
any advice or anything like that?
>> This is a fairly new technology.
There are some idiosyncrasy.
That's a hard word. There are some weird stuff.
I'll try not to show my ignorance in English.
There are some weird stuff and
some things don't always work the way you think they will.
But apart from that, it is an exceptional tool and I
can't wait to see how this will be developed over time.
There are quite a few great blogs.
One of my favorite blogs is Md Cutler.
It does a lot with data warehousing
and serverless pools in general.
That's a great blog.
Otherwise, go play with it.
It's really, really cheap.
You're paying per the amount of data you are accessing.
You're paying basically per terabyte.
If you're just playing around with it,
you can do quite a lot before it even cost to a dollar.
>> Awesome, cool. Great tips and for
our viewers we'll be sure to put a link to that blog and any
other resources Alexander you think would
be useful so folks can go learn more.
Thanks so much Alexander for coming on in MVP Edition.
To our viewers, if you like this video,
go ahead and give it a like,
comment and let us know what you're playing around with
serverless pools and we hope to see you next time on Data Exposed.
[MUSIC]