>> Learn how easily you can get started on your metadata driven
adventure using Azure Data Factory in this demo-only episode,
this week on Data Exposed MVP edition.
[MUSIC].
>> Hi, I'm Anna Hoffman and welcome to
this episode of Data Exposed MVP Edition.
Today I'm joined by Andre.
Andre thanks so much for joining us today.
Can you tell us a little bit about what you do?
>> I'm always confused what I do.
Sometimes I think I'm a data engineer
and sometimes a data architect,
but mainly I work with data.
I prefer it to be in Azure and lots of it,
that's pretty much it.
>> Awesome. You're a fan-favorite for coming on this show
because you like data and are preferably in Azure.
We're happy to have you on the show.
Today we're going to be talking about
Azure Data Factory and how you
can get started with your metadata driven adventures.
I wanted to understand a little bit more
about how you came up with this scenario or what it is.
>> I came up with it because we've actually
built on ourselves before we
started using this metadata driven option inside Data Factory.
At some point I thought,
I've never actually even seen it. Let's give it a try.
I've noticed that it actually does all the things
that were in the solution that we built ourselves.
In a hindsight, we thought we should have
gone with that would have been much easier.
>> Awesome. Good scenarios
because it sounds like you have a solution to
your old problem and also maybe
future problems that other people might face.
I would love to take look.
>> Let's, take a look then.
What I've done is, I've set up a very simple environment with,
I'm going to take some data out of a SQL database and I'm
going to land it into a data lake in JSON format.
Because every data warehouse,
it's just a JSON producer these days.
That's what I see a lot anyway.
It's a perfect portable format.
I've set these up already upfront.
Basically, the only thing I need to do is go through a wizard.
In the old days when we had SSIS inside SQL server,
you would create all the packages by hand.
Later we got something that is called demo,
where you could generate your SSIS packages.
I've also figured that one out after
creating thousands of SSIS packages by hand,
never doing that again.
Demo is perfect because it creates them for you.
But the disadvantage is it a little bit,
you cannot touch the packages after you've created them.
If you want to change something,
you have to set up your own metadata system or
recreate the whole solution through the Management Studio Wizard,
the Import and Export Wizard also creates
SSIS packages and doesn't use Uber.
Data Factory is a little bit different
because almost everything is parameterizable.
This Wizard helps you set up a system that is fairly simple.
It just uses three packages.
They call each other based on some metadata.
They can take quite a lot if you have
a big database with petitions and everything,
this Wizard just build guide you through it and we'll create
a repeatable process that we'll just grab all your data.
Let's show you how that works.
I'm just going to go to the home screen of Data Factory here.
It's no more complicated than clicking on
ingest and then selecting this option over here.
I need to create a control table.
At the control table is where it will store the metadata so I made
a linked service and it's called a metadata of course.
You can create a new control table.
You can actually for every source
that you copy into your data lake,
you can create a new control table.
It will keep everything separated.
Or you can reuse the same control table and just have the one.
That's what I'm going to do because I already have one.
I'm going to pick that one here.
It's going to look at the schema.
Schema is fine. Click "Next" and then it says,
what do you want to import?
Well, I'm going to pick
a SQL database and I'm going to take my source database here.
It says, here's the tables, do you want them all?
I can use the views as well.
I'm just going to select all of them.
For this one, I'm just going to say full load all tables.
You can if you want to and I have, if we have time some,
another one prepared where you can say, no,
there was a high watermark column that I want you to use.
Here's a change date,
for instance, that you can say,
here's the change date,
and then it will just produce
a new file for new data that comes in.
That works really well. I'm just going to say
full table and then destination I'm going to pick my storage.
Azure Data Lake Storage Gen2 obviously.
I've got data ex-post lake.
That just looks like this.
I've got some sourcedb is already here,
so I'm going to add a directory.
I'm going to call it, sourcedb05,
make a directory there to save my data in.
When I go here to browse,
I should be able to select it. There we go.
The files are all JSON,
so I'm going to know txt,
I'm just going to do json.
When I click "Next", it's going to ask me,
you can do whatever format you like and
you can also save it to a SQL db.
I'm going to go JSON format because it's nice and
portable and other popular option would be Parquet.
The only problem that I've seen with Parquet so
far in old databases,
not just SQL server, all the other ones as well.
If you create a table with a space in it,
Parquet format doesn't like it yet.
It's somewhere in the specs that is going to support it,
but I don't think it does yet.
I'm picking JSON as a file pattern here that I
choose and I always actually pick set of objects.
>> Why is that?
>> I'll show you. The other one,
array of objects creates one big JSON array.
I had just one big array that
has to be parsed every time you read it.
The set of objects basically does,
let me pick something that I already have here.
>> Sorry. Go back.
Then I have to scroll and then we go and do a view.
As you can see, it creates
a JSON object per line is called multiline JSON.
Scalable systems love this if you use Synapse Spark pools,
for instance, they can take
each line JSON and just start processing
all these lines bit though, but the whole cluster.
That doesn't work if you have just one JSON.
>> Objects.
>> Yeah, exactly. I'm going
to pick a set of objects compression type known Unicode.
Yes. You saw it go red for a bit.
Just wait a second and then it will read the metadata and go,
I'm going to guess you want this and we'll
just create the name.
I'm just going to SQL DB 05,
it'll be recognizable later on when it's created in.
After that, I have my pipeline.
If I go to the DB, Here we go.
What it did was it created
three pipelines and they call each other.
The system dataset I have to go here.
Three pipelines they call each other because
this is a metadata thing it grabs.
It does a look up and the max value of lookup is only 5,000, only.
If you have a database with more than 5,000 tables,
or if you're using partitioning and it
creates a query per partition so it can
read every petition in parallel.
That's why the level is 5,000.
It can go and it uses
the three pipelines here to call the other ones.
It can parallelize these two steps.
All I have to do is go to this one down here,
I mean the datasets here, the top-level,
and if I trigger that and give it a.
The cool part about this one is also you can say, hey,
I want you to grab all the
5,000 at one go and the max number of concurrent tasks.
If you want to live, cool it down a bit and
not blast your whole system with 20 threads.
You can take it easy and then just let it run.
Then you will see
these pipelines startup call each other and then in the end.
Let me jump to that because
we're not going to wait for the wizard to finish.
If I go to source DB 02,
you can see here that it just saves
all the JSON files and it's a really nice repeatable process.
This thing, and I've done that here.
I've taken a high watermark already half.
This is another pipeline so that you can
do a dynamic linked service if you want to.
You can say I've got a linked service.
This one is the database name and the fully qualified name.
It's dynamic so you can even change that on
the fly in your metadata because the metadata
is just something that sits inside a SQL DB,
which has tables you can just look at.
Here's a control table for instance.
I just have one, but you can
change this on the fly if you want to.
I actually have a system where they were moving
a separate Azure SQL databases to a pool.
The beginning was, yeah,
Andre, we're moving one tonight.
Can you switch it at five o'clock?
I did that twice and went no.
I wrote a different pipeline that would just check if there was
a new database in the pool
and then update this metadata automatically.
It would just, you can move whenever in whatever you want.
My pipeline will be up to date before it runs. All good.
>> Got it, that's pretty cool.
But you wouldn't need
a separate linked service for each database or?
>> You can.
What I did in the demo that I just showed,
that's not parameterized, it just points to
one separate SQL Server and one database
so then you'd have to make separate ones.
But with the parameterized one here,
I can just reuse this one.
>> That's what you are showing, how you can make
it a lot easier. Got it, cool.
>> This is basically because we created this
manually with a whole system
where we just had to update the tables all the time.
The one cool feature that I haven't shown you yet,
I'll show you right now that the metadata systems based on,
for instance, don't have,
is that you can go back in and change the setting.
You can go for element,
just take this one and go.
I'll click on here and go edit it control table and it asks me,
it knows this from the folder name.
You can change it, but I'm just going to go now
and that's correct. Let's go Next.
It will read the metadata.
It shows you all the tables again.
You can do a self-service with my colleagues.
I can set this up for them.
As long as they have access to
these pipelines and an analyst wants an extra table,
you can just go in at the table and save it again.
The metadata will be up to date.
>> Nice. Is these just tables or it looks like maybe also views?
>> Yeah. It's easy just click on
Show views and there should be views at the show.
All selected items have to unselect.
Here are the views. We actually use that a lot of views.
>> Awesome, cool this is really cool, Andre.
Anything else folks should know or want to see related to this?
>> Normally when you see a wizard to get you going in tools,
I'm thinking, that's nice to learn
a bit about how the tool itself works.
But very quickly, you have
to do things yourself and figure out how it
works and then what the Wizard produces is nice to learn.
But for me, it was the other way around.
We were building something
out sales and then discovered this wizard.
They went, that would have been enough.
That just works.
We're actually doing that now.
We're not maintaining the homegrown thing
anymore and we're just using this, it scales.
It works nicely out of the books. We love it.
>> Awesome. Well, cool Andre.
Thanks so much for coming on the show and sharing that with us.
I think a lot of viewers are going to
add hopefully the same realization that you had and give
a lot of benefits out of this demo and the guidance AS shown.
To our viewers, if you like this episode,
go ahead and check the description is going to be links for you to
learn more and how to get started with this, we have a comment,
and let us know what you think and we hope to see you next time
on Data Exposed. [MUSIC]