the goal of most data architectures is to bring your data into a single source
of truth that could be nosql maybe it's other relational databases or files the difficulty
is that these could be different data types different connection strings and apis that
you have to connect with which can really make it challenging to get it to one place
data factory is azure's way of simplifying this process to move your sources
into some sort of destination let's look at this a little more closely on how this actually works
on the left we have a source and on the right we have a destination which is also referred to as
a sync each of these will be programmed with some sort of connection think of that connection
string and then within data factory we're going to create what's called a linked service for example
blob storage or sql database next is a data set and that will be what's stored in memory
to be moved along the way it's going to take the data from that connection and that linked service
and put it into something that can be worked with and the same is true on the destination side so
we'll have that linked service and data set and the connection point here is what's called the
activity and that's really where the magic happens to connect those two data sets and those two
connection points together and do whatever activity you want to do so we know it's moving
from source to destination in that manner with that said let's think of an example we covered
some of this already but for example we could have a connection here on the left that's a json object
trying to get into a specific sql table the link service for the source connection could be blob
storage and for the database azure sql and what we can do as an example here is just copy that data
from the source to the destination and while this is a simple example the concepts are what you can
build on for any activity the other thing to point out here is we create these linked services but
they're actually reusable so just because in this case the blob storage link service is going to
that sql database there's nothing stopping it from being used in a different activity and going to a
completely different connection the other thing to understand is the context of how all of this stuff
is actually happening and when we think about this baseline flow here of activity this is
what's called the integration runtime so we'll see this as we go into the demo you can have different
activities happening within a specific runtime and again you'll see how that looks if you go up one
more level you're within a pipeline and finally above that is the data factory so you have the
factory pipelines within a factory and integration runtime within that with that said let's get into
the demo now and we'll create this actual flow that we see here so here i am now back in azure
we're in the portal home page what we'll do is say create a resource this is going to fall under
integration because think about what's actually happening we're integrating these different
sources and here we see data factory you can either click create right here or we can
click on data factory and it'll take us to the same initial page we'll go through the process
now to create the data factory again that high level container subscription we're going to do
the only one i have which is kds demo i did rename this from previous videos so if you're following
along that's why that's a little bit different resource group we have one already created
region and then now we need to give this factory instance a name version there's different versions
but you want to just do the one that's recommended next here we have git integration which is
actually a really great feature because it allows you to configure uh repository to version control
all of the stuff that you're doing we'll set this up later so we'll say configure git later
next is networking we're going to keep this as is for the sake of demo same with advanced tags
we'll get through this and go ahead and create our data factory this was now created we have our
factory created let's go to resource the first thing i'm going to do before we go in and build
is make sure my source and target are actually set up those different components so we have
the blob storage and the sql table i opened up a new tab here i'll go to my blob storage this
should be here but i'll just show you what it looks like and this was created in a previous
video we have demo container and here we have this person's list and it looks something like
this very simple name hometown that's it we want to take this and put it into a database table so
now if we go to our database and we can go to query editor i'll log in as my user and there's
only this test table but what i want to do real quick is actually create this person's table which
is where we're going to land that json object so here's the query that i'm going to run and
go ahead and run this and now if we refresh we can see this person's table is here it's empty and now
we have something where we can land that json file okay so back to our data factory what we're
going to do is open up the data factory studio and this will give us a canvas and all these different
options to now build out that pipeline and integration runtime
that we want to do right away here we have all these different options we'll just start with new
pipeline and here we are this is the main screen of data factory where if you're an engineer you're
likely going to be spending most of your time in here working and building stuff let's go ahead
and now create our first pipeline we'll call this demo pipeline and we can see it's here
here's all the activities you can see there's all different things you can do here we'll cover these
more extensively in different videos but what we want is copy data so we'll take this and just
drag it on here if you've worked with ssis it's a very similar field just with some more modern
functionality first thing we'll do is give this activity a name we'll call this copy person's data
you can add more descriptions timeouts meaning how long will this be able to run before it times
out how many retries how long between retries etc etc all sorts of options now we need to
configure our source which is going to be that json if we had a data set already set we could
select it here but we don't because this is our first time we'll go ahead and
click new here are all the different options you can see it's not just azure we have
amazon concur google ads all different things here but what we want to focus on right now is
azure blob storage because that's where that json object lives like that here's the format for us
it's json continue now what's the name of this data set and we'll call this person's locations
linked service we don't have a link service just to reset where we're at here we've added this
copy activity we just added the connection for this json now we're creating the link service
for blob storage and so that's where we're at right now this is the name of our data set now
let's create a new linked service now it's giving us a new window we'll call this kds blob storage
more options here that you can go through what's nice here is because it's within azure you can use
your subscription and built-in azure functionality to connect because it's all within the same
ecosystem we'll select our subscription and the storage account kds blob storage that's where
we found that file and what we can do now is test this connection connection successful go ahead and
create now we're back here to the data set we've created the link service and it's asking where
can we find this file if you know exactly what the container and directory is you can put that in or
you can browse and because it's connected it just brings it right up for you and here it is
and it's going to say look in this path for this import schema and now flipped to this option here
and we'll go ahead and click ok all right so now we have our source setup we have the data set we
created that linked service to the blob storage and now what we want to do is set up the sync
which again is another way of saying destination and we're going to go through the same process
here it's asking if we want to use that same data set like we said things are reusable so
you could technically use it here but obviously that's not what we want to do in this case we're
going to do an azure sql database we'll do a new linked service sql database subscription demo all
this is going to pre-populate because we're in the same ecosystem now here for authentication
you have different options for us what we're going to do is use the same user that i logged
in here which for me is mcon because that's when we know we'll have permissions and that's what
we set up for this account i'll go ahead and put that in here and then the password
you can do encrypted but if you do this you're not going to be able to test the connection
which i'd like to show in this example so we'll skip that and go ahead and test the connection
which brings up a good point here now let's take a look at this error message it's saying
there's a firewall issue because the integration runtime doesn't have connection this is another
really good example of how security works on azure what we have to do is go in and give permissions
to our database for this ip address to get there because it's the ip within azure of this data
factory we need to tell our database that hey it's okay that this ip comes in so let's go ahead and
do this we're back in the database here we go set server firewall rule name we'll call this
data factory start ip i'm going to give this a range just in case there's some other subtly
different ips later on so go ahead and save that we're done here we'll go back to the data factory
now let's try again connection successful it can get in we're good to go create all right we're in
table persons it's going to import again and okay all right so we have our source our sync data set
now let's go to mapping and what we can do here is import schemas and that's going to look at our
source and our sync and import all this in here we can see name and now we can pick a column here so
we have name full name hometown we'll do location city now we are all set let's go ahead and
try to run this first let's click validate just to make sure everything looks right no errors just a
quick check and what we'll do now is click debug and debug is going to run this pipeline for us in
debugger mode which will give us some nice outputs you see our pipeline run id refresh comes down
here successful we can look at the output it's got some metadata here this gives us some more visual
on what's happening which is pretty cool you know i don't know makes a nice little visual on exactly
what's going on now let's go back to our database and confirm that it's in there if we go to tables
persons let's run this and we see okay we have a record so technically it did move
but if you think about it we had three or four or maybe more records it wasn't just one and
while you may not get this if you're working with csv or maybe another file type the reason we only
got one in this case is because we're working with a json source and because it's json it's a
nested object it's a little more tricky so what we have to do is go to mapping and go to this
collection reference and this is specific for json and in this case because we have it nested under
a people array which we can see in order for it to know to iterate through all those we need to
select this collection reference and then it will know okay this is not just one record we
need to go through each of these in people and map them so with that set now let's go ahead and
run this again so we'll debug again another thing you can do is go to monitor over here
and you should see the different options so we have triggered which would be a little more real
time and debug is if you're running in debugger which is what we've done and the more recent one
here so let's take a look we're back here we can see all this information we're going to go back to
author so it was successful let's go back to our database run again and now we see we get the other
options but now we can also see that this doubled up it gave us steve twice in a row and that's
technically correct because we didn't say to do anything else but if we don't want that to
happen we want it to be basically a truncate and reload every time there's a quick way to do that
we click back on our activity to sync and one option here is pre copy script
and that's something you can run like it sounds before it copies if you want to update something
or log something whatever you want in our case we'll say truncate table dbo.persons so that it
is truncating that and it would just be a clean run we would just get one two three four records
so with that now there let's go one more time success let's run this again and now we have
four so we're truncated reloaded and we're all good similar to ssis you can also select
different paths if you wanted to create something for a success path and then put your next step
you would put something here here's the option to create something that happens on a failure
on completion etc and create that flow of your pipeline based on different outcomes all right
what we'll do now is say publish all and we're going to publish changes to a live environment
publish and publish was successful so now this is deployed on our data factory in a live setting
if we wanted to schedule this on some level we could go into manage go down to triggers
and add some different schedules we won't do this but this is where
you can add a schedule on a different time how often you want it to occur and trigger
these pipelines directly from data factory that'll wrap up this video i hope you now have a baseline
understanding of how this works and can start to build out your own projects thanks for watching