Azure Data Factory Full Course by a Sr Data Architect — Transcript
Full transcript
- 0:00Hi, I'm Narendra Kumar. I'm a senior
- 0:02data architect with 12 years of
- 0:04experience in IT. I'm a Microsoft
- 0:07certified Azure solutions architect. I'm
- 0:10a datab bricks MVP as well. And I have
- 0:12been working on Azure data factory for
- 0:14many years now. Even in our current
- 0:16project also we have been using it. In
- 0:19this video we are going to cover
- 0:21everything about Azure data factory that
- 0:24is there. Starting from what exactly is
- 0:26ADF, what are its features? Then we will
- 0:29go to linked service data sets
- 0:31integration runtimes activities. Then we
- 0:34will use all these things in pipelines.
- 0:37We will use parameters and dynamic
- 0:40expression to parameterize the things.
- 0:42We will also use global parameters. Then
- 0:45we will trigger the pipelines and
- 0:47understand all the types of triggers
- 0:49that are there. Along the way we will
- 0:52understand the incremental data
- 0:54injection from SQL DB event based file
- 0:57injection from ADLS. Email notification
- 0:59also we will set up using logic apps. We
- 1:02will understand the monitoring concepts
- 1:05and we will even integrate our own
- 1:07GitHub repository and understand CI/CD
- 1:09process. We will create our own key volt
- 1:12and then integrate the same in ADF. So
- 1:15all these things we will cover and
- 1:18everything that we will do in this video
- 1:20we will create everything from scratch
- 1:23including the Azure account itself. So
- 1:25whatever we will use I will explain it
- 1:28each and every step that so that you can
- 1:30do everything yourself and this video it
- 1:33is going to be part of a series. So this
- 1:36is the first video where I it will be a
- 1:38kind of a complete course on Azure data
- 1:40factory to understand everything about
- 1:42data factory that is there. Then I will
- 1:45publish the next video that will be
- 1:48about an end toend metadatriven project
- 1:51that will be using Azure data factory
- 1:53and datab bricks. After that I will
- 1:56create another video where I will
- 1:58publish interview questions related to
- 2:00Azure data factory and this project. So
- 2:03as you can see this is going to be very
- 2:05interesting journey. So feel free to
- 2:07subscribe if you want to stay up to date
- 2:10and if you want more upto-date
- 2:12information about data engineering and
- 2:14AI then I have created multiple
- 2:16playlists as well including different
- 2:18projects interview questions path
- 2:21playlist and generative AI related
- 2:23stuff. So feel free to check them out as
- 2:26well and if you are ready then let's get
- 2:29started.
- 2:31So first of all let's start by
- 2:33understanding what exactly is Azure data
- 2:36factory. So ADF it is Microsoft's fully
- 2:40managed serverless data integration
- 2:43service. This is a codefree ETL and ELT
- 2:47engine for Azure data platform. So
- 2:50basically it is a service on Azure which
- 2:53can which allows us to perform ETL or
- 2:56ELT kind of operation. It is code free.
- 2:59So we don't need to write any code for
- 3:01doing any operation and here we can do
- 3:03different type of operations that we
- 3:05will do discuss in more details now. So
- 3:09first of them first of them which is the
- 3:11most powerful feature it is data
- 3:14injection. So this is the I would say
- 3:16the heart of Azure data factory that it
- 3:19has 90 plus uh connectors. It can
- 3:23connect to different sources and the way
- 3:26it can connect the way it can ingest the
- 3:28data it has been made very easy very
- 3:31scalable and very powerful and no other
- 3:35service that I know it is able to match
- 3:37the simplicity and power of uh data
- 3:40injection in Azure data factory and that
- 3:42is the reason it is still being used a
- 3:44lot
- 3:46and then the second piece or second hurt
- 3:48it is orchestration
- 3:50so when we are ingesting or transforming
- 3:53any data. So whatever operations we need
- 3:55to do, let's say reading metadata table,
- 3:57updating some operation, sending email,
- 4:00adding dependencies, for loops, whatever
- 4:04things we want to orchestrate uh then
- 4:07those we can do very easily here using
- 4:09pipelines and activities. So it it has
- 4:12like really good tool uh it is a really
- 4:15good tool for orchestration as well. And
- 4:18the third pillar is transformation.
- 4:20Although it is not very famous in ADF.
- 4:23So it allows creating data flows as well
- 4:26where we can create the flow using AI
- 4:29and without writing any code and at the
- 4:32back end it will convert it to a spark
- 4:34job and perform the transformation. So
- 4:37although it is a powerful feature but it
- 4:39is not very famous because in almost all
- 4:42the projects we are using datab bricks
- 4:44to connect with ADF and all the
- 4:47transformations we do in datab bricks.
- 4:50And uh scheduling and trigger it is a
- 4:52like a common feature that is there in
- 4:54any tool. Uh in ADF also like uh we can
- 4:57schedule anything based on a schedule
- 4:59tumbling window and the event based
- 5:02trigger is also interesting. So all
- 5:04those anyway we will check them out
- 5:05later on. And the next important or like
- 5:09the useful feature is monitoring and
- 5:11alerts. So the way ADF provides
- 5:14monitoring of the pipelines and each and
- 5:16every activity I have not seen in any
- 5:19other tool. So this is also very
- 5:21powerful and we can configure alerts, we
- 5:23can configure log analytics those
- 5:26advanced features also we can use. And
- 5:29the last one but not like the I would
- 5:32say less important is security and
- 5:34DevOps. So in terms of security the way
- 5:38it can connect it it provide private
- 5:41networking with on-pre sources or
- 5:43private sources it it is very powerful
- 5:46and easy at the same time and also the
- 5:49way key volt is in integrated for
- 5:52storing our secrets and integration with
- 5:55CI/CD also it is very streamless and
- 5:58very easy at the same time. So in
- 6:00general ADF it is a bundle of all these
- 6:03features and why it has become very
- 6:06famous. One it is very powerful and at
- 6:09the same time it is very simple to use.
- 6:12So that is why it has become very
- 6:14popular over the period of time and so
- 6:17far like I would say like no other tool
- 6:19is able to beat it. Uh as far as I know
- 6:22I always prefer using this one. So this
- 6:26is an overview of ADF. Now let's go
- 6:28ahead and uh set up our Azure account
- 6:31for free and then we will uh create our
- 6:34Azure data factory instance and then we
- 6:36will proceed from there. So let's start
- 6:39by setting up our account. So here
- 6:42simply we will search for Azure portal.
- 6:45So we will open the very first link that
- 6:47will come up and here we will select
- 6:50this try Azure for free.
- 6:53And here I already have some accounts
- 6:55but we will create another one. So I
- 6:57will click uh use another account. Then
- 7:00we will uh create a new one. As we are
- 7:03saying we don't have a account
- 7:06here. I will simply add any email
- 7:08address that I want to use for signing
- 7:11up.
- 7:18And then it has sent a verification code
- 7:20on the email. Let me enter that.
- 7:23So after entering the code it is asking
- 7:25some basic details.
- 7:27Now it is asking my name and I don't
- 7:31want like emails from them. So I will
- 7:33uncheck this one.
- 7:36And then it asked me to solve some
- 7:38puzzles. Uh so I don't know like why it
- 7:40asked me to solve 10 puzzles. Uh it was
- 7:43kind of frustrating but I don't know if
- 7:45same will be there for you or not. But
- 7:48after solving those quick puzzles now it
- 7:51will allow us to sign in I think. And
- 7:54now it is asking some more basic
- 7:56information. So I will provide it.
- 8:07Let me see if I can skip some of these.
- 8:10Uh looks like uh company name and phone
- 8:13number is mandatory.
- 8:17And after adding those basic details, it
- 8:21is asking for to add a payment
- 8:23information. But as it says like we
- 8:25won't be charged unless later on we
- 8:28decide to go pay as you go pricing. It
- 8:30is kind of for verification purpose that
- 8:33we need to enter a credit card
- 8:35information and all. Uh so we can enter
- 8:37this information but they will not
- 8:39charge anything.
- 8:42And after entering that payment
- 8:43information as you can see like it is
- 8:46charging a very like a minor amount just
- 8:49to verify the card. So it this also
- 8:52probably they will refund or even if
- 8:53they don't I don't care it is very minor
- 8:56amount
- 8:57and after verifying that payment
- 8:59information now it will allow me to sign
- 9:01up
- 9:04and now it is asking that I shall enable
- 9:06multiffactor authentication.
- 9:09So let's see what options it is
- 9:11providing.
- 9:12So looks like directly did not provide
- 9:14those options. It was kind of a
- 9:16guidance. So we can set up that
- 9:18multiffactor authentication later on.
- 9:21And here like they are saying like now
- 9:24we are ready to start for free. So here
- 9:26we can see that they will give us $200
- 9:31free credit. So that we can use here. So
- 9:34we will start with this free version
- 9:36here.
- 9:39And after that it has opened this new
- 9:41page. So I will click on sign in.
- 9:44And now finally we are welcomed to
- 9:47Azure. So I will simply click continue.
- 9:51And here uh we will start by creating a
- 9:54resource.
- 9:57And here we will click on this all
- 9:59services.
- 10:00And here we will search for uh data
- 10:03factory.
- 10:06So here it comes.
- 10:09Now we will click on this create.
- 10:13And here this subscription it is free
- 10:15version or something. It is selected by
- 10:17default. Resource group there is
- 10:19nothing. So we will need to create new.
- 10:22Uh we can name it anything. Let's say
- 10:24test
- 10:27and name. This will be the name of our
- 10:30Azure data factory. So we can name it
- 10:33anything. Let's say demo ADF.
- 10:37and the region.
- 10:39Uh looks like this name is already
- 10:41taken. So this name has to be unique
- 10:43worldwide I think. So we need to mention
- 10:47some name like uh that is unique. So let
- 10:49me mention maybe this one
- 10:53and also it doesn't allow uh underscore
- 10:58it uh it will allow hyphen only.
- 11:02So now this name is available. So I will
- 11:04use this one and uh region we shall
- 11:07select whatever region is nearest to us.
- 11:10So that way you will get better speed
- 11:12when you will use it. So I will select
- 11:14this central India version we will use
- 11:17V2 only. Only one option is there
- 11:19anyway. And then uh other options we
- 11:22will leave them default. We won't touch
- 11:24anything. Simply review and create we
- 11:27will do. And now it has reviewed that
- 11:30information and it is ready to create.
- 11:32So let's create it.
- 11:35Now it is initializing the deployment.
- 11:37It will take a few minutes. So let me
- 11:39pause it. And this one was fast. So it
- 11:43is saying like deployment is complete.
- 11:45Let's go to the resource.
- 11:49So this is our resource group. This is
- 11:51our data factory instance. So let me
- 11:53click it. And now uh using this launch
- 11:57studio button, we will be able to launch
- 11:59our Azure data factory.
- 12:02And here comes the homepage. So this
- 12:05code repository we will set up later on.
- 12:08And it is suggesting to migrate to data
- 12:10fabric like Microsoft is trying to
- 12:12migrate everybody to fabric. Uh but for
- 12:15this demo we don't need that. And this I
- 12:19will suggest that you bookmark this URL
- 12:21as well so that you don't have to open
- 12:23Azure portal and then like launch the
- 12:25studio again and again directly using
- 12:28this you will be able to open it. So now
- 12:31we are ready to explore Azure data
- 12:33factory. So first of all let's
- 12:35understand what all components are there
- 12:37in the ADF and then we will start
- 12:39building them one by one.
- 12:41So to understand the components let's
- 12:44say if we have some data sources that we
- 12:46will we want to ingest some data right
- 12:49it could be cloud-based sources like SQL
- 12:52DB Azure SQL ADLS storage account or it
- 12:55could be on-prim sources as well like
- 12:57SQL server and then we have our Azure
- 13:00data factory which can ingest the data
- 13:03from here transform the data orchestrate
- 13:06everything all those things it can do so
- 13:10uh first of All this Azure data factory
- 13:12it need to make some connection with
- 13:14source to ingest anything or even to
- 13:17connect with it right. So to make the
- 13:19connection uh there are different uh
- 13:22entities that we need to create in Azure
- 13:24data factory. So first of all it is a
- 13:27linked service. So linked service it is
- 13:31a as the name suggest it is a link or
- 13:33link uh service that provide a link
- 13:36between source and Azure data factory.
- 13:38And so we create a linked service
- 13:40instance where we will be providing
- 13:43let's say the username, password or any
- 13:45other type of authentication we want to
- 13:47provide to actually connect with the
- 13:50source and then once let's say we have
- 13:52set up some uh connection then we need
- 13:56to create a data set. A data set is the
- 13:59entity where we provide like the exact
- 14:01element in the source that we will want
- 14:04to connect. So uh let's say it it can
- 14:07it's if it is a database it will we will
- 14:10provide the database or table name. If
- 14:12it is let's say a storage account or a
- 14:14file system then we will be providing
- 14:16the container name folder name file name
- 14:19all those things we can provide in a
- 14:21data set. So using these two we create
- 14:24we configure the connection details and
- 14:28then uh this connection will actually
- 14:31work only if we have some path to
- 14:33connect. So whenever a we need to
- 14:36connect with any source so we need to
- 14:39set select a path and that path it is
- 14:42called as integration runtime. So
- 14:45integration runtime it is the it is I
- 14:48would say the path in terms of
- 14:50networking and some basic compute also
- 14:52if required which provide the exact path
- 14:55how we can connect with any source and
- 14:57integration runtimes it can be of
- 15:00different types depending on which type
- 15:03of source we are connecting. So let's
- 15:06say if you're connecting with a source
- 15:08which is there in in cloud and directly
- 15:10accessible in a public network then we
- 15:13can use Azure integration runtime and if
- 15:16our source either it is on premise which
- 15:20is let's say publicly not available
- 15:22anyway or if it is a if it is available
- 15:25in a private networking that directly we
- 15:29can't reach out publicly then we need to
- 15:31set up another type of integration
- 15:34runtime that is called is self-hosted
- 15:36integration runtime. So Azure
- 15:39integration runtime we don't need to set
- 15:41up anything. It is automatically
- 15:42available for cloud services. Uh for
- 15:45self-hosted integration runtime it is
- 15:47kind of a software that we install on a
- 15:50virtual machine where we have
- 15:52connectivity with the source and there
- 15:54we provide connectivity with ADF also.
- 15:57So that is how we set up a self-hosted
- 15:59integration runtime as well. So by
- 16:03configuring all these elements now let's
- 16:06say we have the real connection with
- 16:09source we have the path and we have a
- 16:12linked service with connection details
- 16:14and table data set folder all that
- 16:16information.
- 16:18So then we are ready to let's say do
- 16:21something in data factory and whatever
- 16:23we do uh anything in data factory we
- 16:27create a pipeline for it and within a
- 16:30pipeline uh we create multiple
- 16:33activities for depending on the
- 16:35different operations like we can have a
- 16:37lookup activity to read list of tables
- 16:40or something. We can have a for for each
- 16:43activity to loop through things. We can
- 16:45have a copy data activity to move data
- 16:48from source to target. We can have a web
- 16:50activity to send email. Likewise, there
- 16:53are different different activities which
- 16:55are available. We will go through all of
- 16:57them later on. But these entities within
- 17:00a pipeline, these are called as
- 17:01activities.
- 17:03So these are all the main components
- 17:06that I wanted to cover initially. There
- 17:08are more components also that we will
- 17:10cover along the way but this is the core
- 17:12part of it to get started. So just
- 17:15remember integration runtime is the path
- 17:18linked service and data set we uh
- 17:20specify the connection with the source
- 17:22and then we create a pipeline with
- 17:24activities to do any operation and these
- 17:27linked service and data set these are
- 17:29used for target as well if you want to
- 17:32write some data to any target as well.
- 17:34So now let's go to the Azure data
- 17:37factory portal and uh let's create all
- 17:40these things.
- 17:41So here first of all we will uh go over
- 17:45this like a highlevel points quickly. So
- 17:48this is the home location. We don't need
- 17:50to come here frequently. This is the
- 17:53author part where we will be like
- 17:55working as a developer. This is where we
- 17:57will be developing any everything. This
- 18:00is the monitor section where we can
- 18:02monitor each and everything. Then there
- 18:04there is this manage section where we
- 18:07configure things like linked service,
- 18:09GitHub configuration, triggers, global
- 18:12parameters. So highlevel things we need
- 18:14to configure those are there in the
- 18:16manage section and then there is this
- 18:18learning center if you want to like go
- 18:21over the official documentation and all.
- 18:24So we will start with this manage
- 18:25section and there we will start by
- 18:28creating a linked service. So here let's
- 18:32click on this create linked service and
- 18:34here we can create different type of
- 18:36linked service depending on the type of
- 18:38source or target that we want to connect
- 18:41and here we will start with Azure SQL
- 18:45database. So I will search for Azure SQL
- 18:48and then I will click here. Then I will
- 18:51click continue and then this is the name
- 18:54of this linked service that we are
- 18:56creating. We can give a description as
- 18:58well. This is the integration runtime.
- 19:00So for anything in Azure we can use this
- 19:03auto resolve integration runtime. So
- 19:05that automatically it will resolve and
- 19:08then this is a version. We will use the
- 19:10latest version
- 19:12and then this is where we will select
- 19:14our uh SQL database and connection
- 19:16details. So first of all we will need to
- 19:19create a Azure SQL database so that we
- 19:22will connect it here. And for that I
- 19:25will go back to the Azure portal from
- 19:27where we had launched the studio.
- 19:30And now I will click on this all
- 19:32services.
- 19:34And then we will search here for Azure
- 19:37SQL database.
- 19:41And here we will click on this create.
- 19:43And we can see we are getting two
- 19:44options uh SQL DB and free offer. So
- 19:48although like we have those $200 free
- 19:50credits but still uh this SQL database
- 19:53Azure allows it creating even for free
- 19:56we don't need to pay anything uh like uh
- 19:58after that limit also so this is
- 20:00something we can always use for free so
- 20:03we will use this one and subscription
- 20:06and resource group are automatically
- 20:08selected a database name we can change
- 20:11it if we want to do
- 20:13so here uh we will change it let's say
- 20:17demo
- 20:20uh SQL DB and then uh this database it
- 20:25has to be attached to a database server
- 20:27like SQL server. So we don't have any so
- 20:31we will click on create new and here we
- 20:34need to give it any server name. So
- 20:36let's say we can give it uh
- 20:41demo
- 20:45and then let's say dash data belly. So
- 20:49this number al this name also has to be
- 20:51unique worldwide and then here we will
- 20:54select the location which is nearest to
- 20:56us
- 21:00and then here we need to select a
- 21:02authentication mechanism. So uh there
- 21:05are these three types we can use like
- 21:07use both or entra or uh like only use
- 21:11SQL. So we will select this both SQL and
- 21:14entra so that we can use anything that
- 21:16we want. And once we enable SQL it will
- 21:19ask for a username and password that we
- 21:22are going to use there. So I will give
- 21:24this as a username and I will set up
- 21:27some password
- 21:36and then I will click okay. So those
- 21:40were the details we selected for SQL
- 21:42server. So that is reflecting here. Now
- 21:45it will create this SQL DB. So we will
- 21:48click on review and create uh like uh
- 21:51prices price anyway it is free. So we
- 21:53will go ahead and create it.
- 22:01So now the deployment is started. Let's
- 22:04wait for it to complete. So now the
- 22:07deployment it is complete. If we go to
- 22:09the resource group or check out the
- 22:11details here also. So it has deployed a
- 22:14server a SQL database and some firewall.
- 22:18So if we go to our resource group. So
- 22:20here we can see our data factory and
- 22:22this SQL server and database as well. So
- 22:26now if I click on this database
- 22:29and then here we can go to this query
- 22:31editor on this left side
- 22:34and here to login here we have two
- 22:36options either we can use this current
- 22:38ID uh this will also work or we can use
- 22:41SQL authentication and enter the
- 22:43username password that we used. So we
- 22:46can use this default one because we only
- 22:48created it. And here looks like it is
- 22:51saying like my current IP it is not
- 22:53allowed to access this here. So I will
- 22:56click this.
- 22:58So now it has allowed it and then I will
- 23:01click on connect. So now I'm inside and
- 23:04I'm able to uh like uh check out the
- 23:07table or database if there is anything.
- 23:10So currently we don't see anything here.
- 23:13So here first of all we will create a
- 23:15table so that we can read the data from
- 23:18that table in our Azure data factory. So
- 23:21I will click on this new query here
- 23:24and then I have prepared this query. I
- 23:26will be sharing all these queries. So
- 23:29this query it will create a customer's
- 23:31table and insert some sample data into
- 23:33it. So I will paste that query here and
- 23:36simply run
- 23:39and with that it has created that table.
- 23:41So if we go to the table section, we can
- 23:44see the customers table has been created
- 23:46and some data also has been inserted. So
- 23:49now this SQL DB it is ready to connect.
- 23:52So let's go back to our ADF and try to
- 23:55connect this one. So now I'm back in our
- 23:58Azure data factory. So here subscription
- 24:01we will select we have only one server
- 24:04name. Let me refresh it.
- 24:07Now it is coming. Then database name. Uh
- 24:11so master is some default one that is
- 24:13created. This is the one we created. So
- 24:15we will use this one. Authentication by
- 24:18default it is saying it we can use SQL
- 24:20like username and password we can enter.
- 24:23And we have these other options as well
- 24:25like uh managed identity service
- 24:27principle and all but we will use SQL
- 24:31authentication for simplicity. So we
- 24:33will enter the username here and then
- 24:36password here. So for password we have
- 24:39two option either we can use directly
- 24:42put it here which is not used in
- 24:44production ready projects in there we
- 24:46use this key volt to like uh read a
- 24:49password this part we will cover later
- 24:52on when we will go to security for now
- 24:54for simplicity I will enter my password
- 24:57here
- 25:03okay and then let me see so all these
- 25:06options we can Leave them as is and then
- 25:09we will do a test connection first.
- 25:13And now we can see the connection is
- 25:15successful. You may get some error also
- 25:17like if you are doing something uh like
- 25:19wrong password or something but normally
- 25:21it will be successful. So here let me
- 25:24give some good name for this one. So
- 25:26linked service we can use let's say ls
- 25:30then it is a SQL server connection. So
- 25:33we will say SQL server and then I will
- 25:37click on create
- 25:39and with that this linked service has
- 25:41been created and if I click it it will
- 25:44show those details we can edit or do
- 25:46anything with this. So now we now we
- 25:50were in manage section. So now I will go
- 25:52to the author section so that we can
- 25:55start using some of the things there.
- 25:57And currently the next thing we will do
- 25:59is we will be creating a data set. So
- 26:02here I will click on this uh this icon
- 26:06here and click new data set because we
- 26:08don't have any. And then again we need
- 26:11to select the type of data set we want
- 26:13to create. So we want to create it for
- 26:15SQL DB currently. So I will select this
- 26:18one and click continue here. And the
- 26:21name we can say it let's say it is data
- 26:24set. So we will say data DS then let's
- 26:27say SQL server or SQL DB also we can
- 26:29mention. Then here we need to select the
- 26:32linked service that we just created. And
- 26:34then here we need to select the table
- 26:37that we have we want to ingest. So these
- 26:40things like uh in actual projects we do
- 26:43this like dynamically we will go to
- 26:46those sections also. But for simplicity
- 26:48for now we will select the table here.
- 26:51So now we have selected our linked
- 26:53service and within from that connection
- 26:56uh this is the database and table that
- 26:58we want to read. So we have uh mentioned
- 27:01this one. So we will click okay now.
- 27:06And with that now this database uh sorry
- 27:08this data set it has been created. And
- 27:11these are the same details like just
- 27:13another screen. So this is the link
- 27:15service the table we can do a test
- 27:17connection and all here as well. And we
- 27:20can give a description also for like
- 27:22future use. And currently like this dot
- 27:25here it means like it is not yet saved.
- 27:28So we will click this publish all and
- 27:31then we will click on this publish. So
- 27:33now what it will do is it is kind of
- 27:35like it will publish this data set to
- 27:38the main instance so that like anybody
- 27:41can use it. So once like we integrate
- 27:45GitHub later on then we will get a save
- 27:47option as well where we can save it in a
- 27:49branch or something but for now like we
- 27:52can directly use this publish all option
- 27:54as well. So it is published now. So now
- 27:57like to interact with this data set we
- 28:00need to create a pipeline and then some
- 28:03activity there. So I will click here and
- 28:06click on new pipeline. So now this is
- 28:09the pipeline screen that has come up. So
- 28:12I will minimize this left side for
- 28:14simplicity. And this is the name we can
- 28:17provide here. Let's say we can say
- 28:19pipeline. We can say PL and then we can
- 28:22give it any name. Let's say demo. And
- 28:25then here we see the list of activities
- 28:28that we can use. So depending on
- 28:30different categories we have different
- 28:32activities which are available.
- 28:35So first of all we will cover the lookup
- 28:37activity. So I will search for lookup
- 28:40and then I will drag it here in the
- 28:42canvas. So now this activity has been
- 28:45dragged up and we can uh change its name
- 28:48if you want. Let's say I can change
- 28:51let's say uh read
- 28:56customer table.
- 29:02So it is a good idea to like change its
- 29:04name and put some description depending
- 29:06on what it is doing. And then all the
- 29:09other options we will cover. First like
- 29:11let's go to settings and then here we
- 29:14need to select data set which data set
- 29:17we want to connect it to. So this is the
- 29:19data set we want to connect here and
- 29:22then here we get all these options. So
- 29:24by default it will read only one row
- 29:27from the table. So I will untick it so
- 29:29that it can read all the rows and then
- 29:33uh like by default it will read the
- 29:36table that is there in the data set or
- 29:38if you want we can type a query also
- 29:40here or a stored procedure also we can
- 29:43run but we will leave table for now.
- 29:46Timeout we will leave default for now.
- 29:48So with this uh this uh activity is
- 29:52ready to be used. So to use this we have
- 29:55this debug option. So while we are
- 29:58working in the development so then there
- 30:01we always use this debug option or if it
- 30:04is published then we can run the
- 30:06pipeline as well. Uh but during
- 30:08development we always use this. So I
- 30:10will click on debug.
- 30:14And now you can see below here like uh
- 30:17this new section has appeared which is
- 30:19output. And this right side section we
- 30:22can minimize from here. So if I click
- 30:24it, it will go away or come back. And
- 30:27currently it is ceued. It will start
- 30:29doing its job. And now we can see its
- 30:32execution. It is complete. It has
- 30:34succeeded. And uh when we go here like
- 30:38in this activity name so we see input
- 30:40and output that we can check out. So
- 30:43input part like uh it it is showing like
- 30:46what is the source that was used which
- 30:48data set was used first row only was
- 30:51false those kind of things. And if we go
- 30:54to output so this is where we will get
- 30:56real output. So if I maximize it here we
- 31:00can see it has read 10 rows from the
- 31:02table and these are the values it has
- 31:05read like this is row one row two and if
- 31:08you notice here so the output we are
- 31:10getting here is JSON and apart from this
- 31:13normal output like we are getting some
- 31:16other parameters as well uh which are
- 31:19inbuilt to ADF like uh how many uh like
- 31:22DIUs were used how much time it took to
- 31:25run which integration runtime time was
- 31:27used and everything but this is the most
- 31:30important part for us. So when it comes
- 31:32to Azure data factory so while working
- 31:35with inputs and outputs we will always
- 31:37be working with this uh JSON kind of
- 31:40output only and here you can see in the
- 31:43like this is a JSON which has a value uh
- 31:46and which has a value and it is a uh
- 31:48dictionary here sorry not dictionary it
- 31:51is a like kind of JSON array which is
- 31:54there and in the array we have these as
- 31:56the different items.
- 31:59So this is how like just now we created
- 32:01a pipeline we ran our own activity
- 32:05and now let's try out like the same uh
- 32:09uh activity with the setting like this
- 32:12first row only and let's see how it
- 32:14behaves. So I checked that and I'm
- 32:16running it again in the debug mode. So
- 32:19this is also successful and this time if
- 32:22we check the output here we will see
- 32:24that instead of getting that entire JSON
- 32:27array we are getting only one entity
- 32:29like first row and it is only giving one
- 32:32row. So depending on our scenario like
- 32:35we may want to like read one row or
- 32:38entire data. We will go through those
- 32:40scenarios as well. And now if I go back
- 32:43to settings here this activity and then
- 32:46settings. So this time we read the table
- 32:49but let's say if you want to run a
- 32:51query. So here we can use a query as
- 32:54well. So for example select star from
- 32:56dbo dot customers
- 33:01let's say where
- 33:06customer ID is greater than two. Let's
- 33:10say those customers only we want. So
- 33:12first let me confirm if this is a valid
- 33:14SQL query. I will go to the SQL uh DB.
- 33:19Let me clear this previous one and let
- 33:21me confirm. So yes, this is a valid
- 33:24query. So now uh I will run it again in
- 33:28the debug mode. And this time I will
- 33:31like disable this so that we can see if
- 33:33we are getting the same results with
- 33:35that filter.
- 33:38And now this is also running successful.
- 33:40So if I check its output, we can see
- 33:43this time it has returned eight records
- 33:45because of that filter greater than two
- 33:48and those eight records are coming
- 33:50properly here. So here we covered our
- 33:54first activity that was lookup activity.
- 33:57So as you can see this lookup activity
- 33:59it is able to read some data from any
- 34:02source or any metadata table and
- 34:04normally in projects we used to let's
- 34:06say reading a list of tables which are
- 34:09stored in the table or like reading
- 34:12parameters from the metadata table for
- 34:14those kind of scenarios we use this
- 34:16lookup but this one like it is not used
- 34:19to actually ingest the data. So to
- 34:22ingest the data uh we we need a copy
- 34:26data activity. So first like let me
- 34:28publish this one. So now it is saying
- 34:31that it is going to publish this
- 34:33pipeline. So I will allow it.
- 34:37So now this pipeline is published. I
- 34:39will close this one. I will close this
- 34:41data set and let me expand this section
- 34:44again. So now let's create another
- 34:47pipeline where we will ingest some data.
- 34:50So I will click on this new pipeline and
- 34:53then this time we can name let's say PL
- 34:56demo
- 34:58injection
- 35:00and to ingest any data we have the
- 35:03activity named copy data activity. So
- 35:06this is the most important activity in
- 35:08ADF and very frequently you will use it
- 35:12definitely but understanding this
- 35:14activity in detail it is the I would say
- 35:17heart of ADF. So it is very very
- 35:19important. So as the name suggest it is
- 35:22like uh copy data. So it can copy data
- 35:24from source to target. In simple words
- 35:27here we can provide its name. So we can
- 35:30say like ingest
- 35:33SQL
- 35:35DB data.
- 35:38We can give it any description as well.
- 35:41These options we will leave as default.
- 35:43The most important piece it is this
- 35:45source and sync. So if I go to source
- 35:48here we need to select a data set that
- 35:51we want to ingest and as you can see
- 35:54these other options are similar what we
- 35:56got in lookup activity and then so this
- 35:59is where from this source it will read
- 36:01the data that is how it works and then
- 36:04in the sync we need to select another
- 36:07data set where we are going to land our
- 36:10data. So as of now like we have not any
- 36:13sync where we can land the data. So
- 36:15first we are going to create a Azure
- 36:18data link storage account and in that
- 36:20account we are going to land our data.
- 36:23So for that I will again go to the Azure
- 36:25portal. I will click on all services
- 36:28here and then I will select this storage
- 36:32accounts here
- 36:34and here we will create a new storage
- 36:36account
- 36:38and this we will leave default. Here we
- 36:41need to give any name. Let's say demo
- 36:45datab belly adls. So this one it doesn't
- 36:48allow like hyphens also. So it has to be
- 36:51just uh digits and like numbers and
- 36:54characters. Region is fine. Service we
- 36:58will select Azure data lake storage. Uh
- 37:01these options we will uh leave them as
- 37:04is. And in case of like ADLS uh there is
- 37:07this one option we have to be carefully
- 37:10select. If you by default review and
- 37:12create that will not be enabled but
- 37:14there is very important option. If I
- 37:16click on next we have this enable
- 37:19herical name space. So without this
- 37:22option a lot of features will not be
- 37:25enabled in our account like creating
- 37:27folders. It will only store files as a
- 37:29blobs and all. So selecting this option
- 37:33it is very important. So we shall always
- 37:35select this and then only we shall
- 37:37review and create. Now it has reviewed
- 37:40everything. So let's go ahead and create
- 37:42it. And now we can see that it is
- 37:46created. So we will go to the resource.
- 37:50And here like uh this storage account.
- 37:53So to create any files and folders there
- 37:56we need to go to containers.
- 37:59So by default like this is some inbuilt
- 38:01container. We will create our own
- 38:03container
- 38:05and we will give it any name. Let's say
- 38:09demo container.
- 38:12And this one it doesn't allow like
- 38:13underscore. It will allow hyphen. So I
- 38:16will create it with this name.
- 38:20And inside any container like we can
- 38:22create files and folders. So here we
- 38:25will create a new folder with the name
- 38:28let's say output.
- 38:34So in real projects like we create
- 38:36multiple container for different purpose
- 38:39and then we use like different nested
- 38:42directory structure as well. So this is
- 38:44where we will try to land our data now.
- 38:47So now let me go to ADF and then we will
- 38:51create our data set now. So I will click
- 38:53on new and here we need to select this
- 38:56data lake storage gen two. Don't select
- 38:58this blob storage because these are
- 39:00these two are slightly different. Uh
- 39:02select this gen two option. Then click
- 39:05on continue. Uh these are the different
- 39:08file formats that we can select. So
- 39:10depending on the selected file format,
- 39:12it will land the data accordingly. So in
- 39:15actual projects we use park format
- 39:17because it is very compressed. It is
- 39:19very efficient while reading and writing
- 39:21the data. Uh but for now we will select
- 39:24this CSV format because it is very easy
- 39:26to read the data. just by looking at the
- 39:29file and then I will click on continue.
- 39:33Here we need to select a linked service.
- 39:35So we don't have yet. So I will click on
- 39:37new
- 39:39and now we will create our linked
- 39:41service. So we will name it as ls
- 39:45adls
- 39:47and then authentication type there are
- 39:49different types which are available.
- 39:51Account key normally it is not that
- 39:53safe. So this time we will use the
- 39:55system assigned managed identity
- 39:58and then here we will select our storage
- 40:01account
- 40:02and then if we do a test connection
- 40:04here. So first time it will definitely
- 40:08fail. It is saying like it is forbidden
- 40:11and why it is happening because uh like
- 40:14this Azure data factory it does not have
- 40:17permission to read or write any data to
- 40:19that storage account. And here we have
- 40:22selected this managed identity and we
- 40:24can see like this is the name of this
- 40:26managed identity that it is using. So
- 40:29for this id we need to give permission
- 40:31on our storage account so that this can
- 40:34read and write data there. So for that I
- 40:37will go to this storage account and here
- 40:39I will click on this uh storage account
- 40:42page. Uh okay go to resource
- 40:48and then we need to go to this access
- 40:50control
- 40:51and now click on this add role
- 40:54assignment
- 40:56and here we will see that there are so
- 40:58many roles. So for allowing the data
- 41:01read and write we have this uh storage
- 41:05uh uh storage blob data contributor. So
- 41:09if you read this it is saying like it
- 41:11allow for read, write and delete access
- 41:14for Azure storage blob container and the
- 41:16data inside it. So this is the role we
- 41:19are going to use. So I have selected
- 41:22this. I will click on next.
- 41:25Sorry I think I selected wrong one. Let
- 41:27me select this uh uh data contributor.
- 41:32Then click on next. Here we need to like
- 41:35select the ID to whom we want to give
- 41:37the access. So I will click on this
- 41:40managed identity and then select member
- 41:44and here we will select this managed
- 41:47identity. So we are we want to give it
- 41:49to our data factory. This is the data
- 41:52factory. So we will select this and then
- 41:55we will review and assign.
- 41:58So now it will give permission for Azure
- 42:00data factory to read and write any data
- 42:03to our ADLS account. So I will go here
- 42:07now and then uh I will do a test
- 42:10connection again
- 42:13and this time it is saying connection
- 42:15successful. So uh it may takes a minute
- 42:18or two also to reflect it. So if it is
- 42:21showing like same error so you shall
- 42:23wait and then do a test connection. And
- 42:26now we are ready to finally create our
- 42:29linked service. So this linked service
- 42:32it will be created now. And now we are
- 42:34back on this data set page. So this is
- 42:37the name of the data set. So we will say
- 42:40like uh this one CSV demo
- 42:43and we can say it was like for ADLS. So
- 42:46let me put that also in the name here.
- 42:49The linked service is already there.
- 42:52And here we need to provide let's say
- 42:54the file system which is like the name
- 42:57of the container then the folder or file
- 43:00name also if we want to provide. So
- 43:03either we can provide these options
- 43:04manually or we can browse it also. So if
- 43:07we browse it, it is the container it is
- 43:10showing
- 43:11and then here is the folder it is
- 43:13showing.
- 43:15So now I will click on okay. So we can
- 43:18see the container name and folder it has
- 43:20put up. File we will leave blank for now
- 43:24and then I will click on okay.
- 43:27And with that our data set it is created
- 43:30now. So now we can see that source and
- 43:33sync both are ready. So let's give it a
- 43:36try. Let's click on debug here.
- 43:40And now it is starting our pipeline.
- 43:43And here uh this time it failed. So if
- 43:46we look click here, this is where we can
- 43:49see what exactly is the error. So we can
- 43:52see it is some like user configuration
- 43:54issue it is saying and SQL failed to
- 43:57connect.
- 43:58And here it is saying like check the
- 44:00linked service configuration and make
- 44:02sure that SQL database it allows the
- 44:05integration runtime to access and
- 44:07everything. So to fix this issue or to
- 44:10debug it further I will click on this
- 44:12copy data and in the source part
- 44:16uh here in our data set uh let's click
- 44:19on this open button
- 44:21and here is a connection to our linked
- 44:23service. So first let's confirm if our
- 44:25linked service is still working.
- 44:28So looks like it is still working. So
- 44:31that error it is strange. So that means
- 44:33that error is probably related to uh the
- 44:37firewall or networking related stuff. So
- 44:40for that one let's go to our SQL
- 44:43database and server. So now I'm back in
- 44:46Azure portal. And if I scroll below here
- 44:49we can check our recent used services.
- 44:53So we have the SQL database and SQL
- 44:55server. So networking related stuff are
- 44:58configured on the server level. So let
- 45:00me go here and here what we need to do
- 45:03is we need to go to this security then
- 45:06networking
- 45:08and this is where if you remember like
- 45:10we had added our IP manually like when
- 45:12we were first trying to login. So by
- 45:15default like the public access it is
- 45:17disabled. And to enable like access from
- 45:20any IP in the world simply we need to
- 45:23like click this here like add add a
- 45:27firewall rule. We can give it any name
- 45:30like all IP.
- 45:33So we will say 0.0.0.0
- 45:38and then in the end we will say 255.
- 45:43So with this what we are doing is like
- 45:45we are giving it access to all the IPs
- 45:49that are there in the world. So it will
- 45:51open everything to the public network
- 45:54like in real projects like we don't use
- 45:56this public range we they set up like
- 45:59private managed endpoints and some and
- 46:02all those details which are set up by
- 46:04the admin teams but here we will open it
- 46:07to the whole world all because this is
- 46:09for demo purpose. So I will save it now.
- 46:14So now it is successfully updated. So
- 46:16let's go back to our ADF. And now let me
- 46:19go to our pipeline and give it a try
- 46:22again. So I will click on this debug
- 46:25option.
- 46:26And this time you can see it was
- 46:28successful. So that networking thing was
- 46:31only blocking it.
- 46:34So if we see here, so we have again like
- 46:37input and output. Input are here generic
- 46:39things only. If you go to output. So
- 46:42this time you will notice it instead of
- 46:44showing the actual data it is showing
- 46:47like data read data written how many
- 46:50files were written and all these kind of
- 46:53information it is showing. Why it is
- 46:55because the actual data it has gone into
- 46:58our ADLS account and here we are getting
- 47:00these statistics like what was the
- 47:03volume in bytes that was read and
- 47:06written and how many rows were read, how
- 47:09many rows were copied, how much time it
- 47:12took. So all these kind of things we can
- 47:14see here and specific to copy data apart
- 47:18from just this output we get this detail
- 47:22section as well. So if I click here, so
- 47:25this is where we can see the data was
- 47:27read from our SQL database. It went to
- 47:30our Azure data link storage gen 2
- 47:32account. Uh and those similar details we
- 47:35can see like how much data in bytes was
- 47:38read and written. How many rows were
- 47:40written. Uh here we can see one file is
- 47:43created in the target with these many
- 47:45rows. And it has to use only one
- 47:47connection because this was very small.
- 47:50In like bigger tables it can create
- 47:52multiple connections as well. Uh what
- 47:54was the total copy duration? Uh what was
- 47:57the speed that was there and then there
- 48:00is this lot of additional information
- 48:02also we are getting. So all these can
- 48:04help like uh in in debugging also for
- 48:07performance like if you want to. So if
- 48:09you are ingesting something very big, we
- 48:12can come here, we can see uh see like uh
- 48:16how many parallel connections are
- 48:17working, what is the speed we are
- 48:19getting and then for how much time it
- 48:22was cued. So all these details help in
- 48:25debugging in real scenarios. So uh this
- 48:29is how we can monitor everything here
- 48:31and as it is successful. Now let's check
- 48:34out our target ADLS also so that we can
- 48:37confirm if our data is landed properly.
- 48:40So for that I will go back to our uh
- 48:43Azure portal. Then I will go to our
- 48:45storage account.
- 48:47Then go to containers.
- 48:51Let's open our container and then our
- 48:53folder. So here we can see a txt file
- 48:56has been created. So if I click it, it
- 49:00is showing all these details. I can
- 49:02download it directly or I if I click on
- 49:05edit. Let's see. Yeah, directly it is
- 49:07showing the data here as well. So here
- 49:10we can see like these were the column
- 49:12names and this was the data. So this way
- 49:14this data has been landed here
- 49:16successfully. So we just now like we
- 49:20completed the quick demo where we read
- 49:23the data from our SQL server and we
- 49:25landed the data into our ADLS storage
- 49:27account. And now if we go back to our
- 49:30Azure data factory and then if I click
- 49:33on this copy data. So here we can see
- 49:36that there are lot of options related to
- 49:39copy data and this is I would say the
- 49:41most powerful activity that is there in
- 49:44ADF. So knowing more options it is
- 49:46really helpful. So here we can see that
- 49:49we can in the source side we can select
- 49:52the table or query or stored procedure
- 49:54depending on the uh source that we are
- 49:57using. Time out means like if it is uh
- 50:01if it is let's say not able to uh read
- 50:03our complete data in 120 minutes then it
- 50:06will time out. We can change it. Uh this
- 50:09one normally we don't use this option.
- 50:13And then here we can add new columns as
- 50:16well on the fly if we want to. So here
- 50:19we can define some expression and then
- 50:21uh create like more columns. Uh normally
- 50:24we don't use this option. And on the
- 50:26sync side if we go there so we can
- 50:29select different copy behaviors which
- 50:31are there. How many concurrent
- 50:34connections we want to set up. We
- 50:36normally leave it default so that it can
- 50:38decide if you want to add any metadata
- 50:41there.
- 50:43And file extension also if you want to
- 50:45change. So this dot txt that is why the
- 50:48file was created as dot txt and per file
- 50:51how many rows we want to write. So all
- 50:54these things we can control and if you
- 50:56want to control further. So if we go to
- 50:59mapping section here we can define new
- 51:01mapping as well like we can we can
- 51:05select any column in the source and in
- 51:07the destination if you want to let's say
- 51:09rename the column give it any other name
- 51:12or using expression we can even like do
- 51:14some minor operations here and there as
- 51:17well. So all these options these are
- 51:19very advanced option and uh normally in
- 51:22most of the projects like those are not
- 51:24required like here also we can uh check
- 51:27we can customize the file and timestamp
- 51:30formats and everything. Uh so these are
- 51:33different options which are available
- 51:35but uh when in normally we work in the
- 51:38projects so there like we simply read
- 51:41the data and then we write the data into
- 51:43park format and then uh like the later
- 51:47of let's say converting the columns
- 51:49casting things all those transformation
- 51:52we take care on the data brick side and
- 51:54if we go to the settings section here
- 51:56here also there are different options
- 51:58available like how many maximum imum D I
- 52:03use that like the processing units we
- 52:05want to use how much parallelism we want
- 52:08to use. So normally leaving these kind
- 52:10of options as default I is fine in copy
- 52:14data. So this covers the core concepts
- 52:17related to copy data. So now let me
- 52:20publish it so that it is saved
- 52:23and it is showing this error metadata
- 52:26name cannot be empty. I think while
- 52:28demoing I created this meta data.
- 52:32So let me simply remove this.
- 52:38So now this is published. So now let me
- 52:41go ahead and close it. So so far we
- 52:44understood like how we can ingest a
- 52:46particular table to a particular
- 52:48location. But when we work on real
- 52:50projects there we will not be like just
- 52:53creating a separate data set and uh
- 52:56separate separate pipeline for each and
- 52:58every table right. So that is where we
- 53:00need some kind of like dynamic or create
- 53:04variables so that like same pipeline or
- 53:07same activities we can use for different
- 53:09tables and different kind of processing
- 53:12and that is where the dynamic expression
- 53:15parameters and variables come into
- 53:17picture in Azure data factory. So now
- 53:19let's understand how we can use them. So
- 53:23now what we will do is we will
- 53:25parameterize our injection process based
- 53:27on table name so that this same pipeline
- 53:30we can use it for like ingesting any
- 53:33table from our SQL server. So to uh
- 53:36create parameters what we will go go is
- 53:39like because we need to parameterize
- 53:41table name. So table name we had
- 53:44specified in our data set. So we will go
- 53:47to our data sets. We will open our SQL
- 53:49server data set. And here we can see
- 53:52that we had selected or kind of
- 53:54hardcoded our table. So instead of that
- 53:57we will parameterize this table name
- 53:59now. So for that we will go to this
- 54:01parameters
- 54:04and then here we will click on new. So
- 54:07here we will say schema and then we will
- 54:10also say table name.
- 54:15So so that these are the default values
- 54:17we don't need to provide for now. If I
- 54:20go to our connection, so these are the
- 54:22two parameters we are creating. Schema
- 54:24and table name. And in our case, DBO is
- 54:27the schema name. Customers is the table
- 54:30name. So instead of selecting the table
- 54:33from here, I will say enter manually.
- 54:36And in the enter manually, if I remove
- 54:39this, this is where we get the option
- 54:41like add dynamic content. So if I click
- 54:45this and this is where we can select any
- 54:48parameter. So if we select so now when I
- 54:52click on this schema we can see here it
- 54:55is entered automatically like the rate
- 54:57data set dot schema. So this is called
- 55:00as dynamic expression in ADF. So we will
- 55:04later on play around these expressions
- 55:06as well. But here we can see by default
- 55:08it is entered automatically once we
- 55:10click it. So I will click on okay. And
- 55:13here we can see now it will be picked up
- 55:15dynamically. Similarly, same thing we
- 55:18will do for table. So I will click this
- 55:20add dynamic content. I will select the
- 55:22table name and click on okay. So now our
- 55:26data set it has been parameterized.
- 55:29So now what I will do is I will go to
- 55:32our pipeline that we had created. So if
- 55:35we go to the pipeline here in the copy
- 55:38data and if we go to the source section
- 55:40here here we can see that the schema
- 55:43name and table name are both are coming
- 55:46automatically.
- 55:47So here for now we will provide it
- 55:49dynamic let's say dbo and then table
- 55:53name we will give it as customers
- 55:56and then similarly on the sync side also
- 55:59we want to parameterize it. So for that
- 56:02we will open this ADLS data set and here
- 56:05we can see that this container we want
- 56:07to keep the same container and uh let's
- 56:11say in the container we want to create a
- 56:13folder based on the table name. So what
- 56:16we will do is we will click on this
- 56:18parameters here we will create this uh
- 56:21parameter let's say table name
- 56:24and then in the connection here instead
- 56:27of this output folder
- 56:30uh we will put this add dynamic content
- 56:33and here we will select this parameter
- 56:35click on okay. Now this data set is also
- 56:39parameterized. So if I go to my pipeline
- 56:42again, if I go to sync here, here we
- 56:45will be able to provide a table name.
- 56:47Let's say I'm providing it customers
- 56:49here. So now as it is parameterized, now
- 56:53let's give it a try. So I will click on
- 56:55this debug option.
- 56:58And now we can see it was successful. If
- 57:00we go to this detail section, we can see
- 57:03same 10 rows has been written. And now
- 57:06let's go to our ADLS account. And there
- 57:09let's check it out how how it is
- 57:11written. So if we are in the output
- 57:13folder if I refresh it now
- 57:17uh here it is not created. I think we
- 57:19created in container directly. Yes. If
- 57:22we go to container and refresh this is
- 57:24where that dynamically provided table
- 57:26name it has created this folder. Now the
- 57:30file is created inside this folder. So
- 57:33that is how based on that parameter we
- 57:35will able to land this data. And next
- 57:39the question comes is like how will this
- 57:42parameter actually help because still if
- 57:44we have to ingest another table we have
- 57:46to come here and then like provide a
- 57:50different table name here. So for that
- 57:53we have something called as pipeline
- 57:55parameters. So for that if I click
- 57:58outside this copy data then here it is
- 58:01showing pipeline related information. So
- 58:03here if I click on this parameters here
- 58:06I can create a parameter for this
- 58:09pipeline. So here also we will create it
- 58:12with this name table name and we can
- 58:14give it a default value also so that
- 58:17like if we are not giving any value then
- 58:19this value will be used. So this
- 58:22parameter we have created now. Now if I
- 58:25click on this copy data and if I go to
- 58:28this source. So now instead of hard
- 58:30coding this table name here also I can
- 58:34use this dynamic content and select the
- 58:36table parameter sorry this pipeline
- 58:39parameter. So I have selected this and
- 58:42this time you will notice that the
- 58:44expression is coming is either pipeline
- 58:47dotparameters dot table name because it
- 58:50is a pipeline parameter this time. So I
- 58:52will click on okay. And if we go to sync
- 58:56then here also we will pass it
- 58:58dynamically and we will uh select this
- 59:01table name here. So now both source and
- 59:04sync has been parameterized. If you want
- 59:07similarly we can parameterize schema
- 59:09also that we don't want for now. But now
- 59:12we have this single parameter. So if I
- 59:16click on debug now it will first ask me
- 59:18for a parameter. So we can leave this
- 59:21default value or change it. So for now I
- 59:24will simply go ahead and run it.
- 59:27And here we can see that it is
- 59:29successful now. But to confirm if this
- 59:32dynamic parameter are really working as
- 59:34expected or not. So we shall be try
- 59:37trying to inest another table. So let's
- 59:40go to our SQL DB and there we will try
- 59:42to create another table. So I will go to
- 59:45our database and then our query editor
- 59:49and here we will be creating another
- 59:51table. So for that I have prepared this
- 59:54another script. This time we will create
- 59:57a table named products. So I will copy
- 1:00:00this script and here I will come and run
- 1:00:03that script. So now the table it is
- 1:00:06created. So if we expand this tables
- 1:00:09products we can see products table it is
- 1:00:12ready now. So now if I go to Azure data
- 1:00:15factory and now without making another
- 1:00:18single change I will simply click on
- 1:00:20debug and this time instead of customers
- 1:00:23I will mention products and let's see if
- 1:00:27these parameters are working as
- 1:00:28expected.
- 1:00:32So it is uh running now and this time
- 1:00:35also we can see it is successful. So if
- 1:00:38I click on this monitor, we can see this
- 1:00:41time also it has written 10 rows. But to
- 1:00:44actually confirm that it has like uh uh
- 1:00:47written the right data in the target,
- 1:00:49let's check out our ADLS account. And on
- 1:00:52the ADLS side, we can see automatically
- 1:00:54it has created this products folder. And
- 1:00:57then this is the file it has created
- 1:01:00here. And if we go to the edit section,
- 1:01:03we can see now it has really ingested
- 1:01:05the products data here.
- 1:01:08So this way like this parameterization
- 1:01:10of the pipeline and data set it really
- 1:01:12helped and this way we can use this same
- 1:01:16pipeline to ingest any table that we
- 1:01:19want. So now next what we are going to
- 1:01:22try out is uh let's say whenever this
- 1:01:25table is ingested we want some automatic
- 1:01:28email on our email id as a notification.
- 1:01:32So to do that first of all we will need
- 1:01:34something called as logic apps and then
- 1:01:37we will use it in this use that logic
- 1:01:39apps in our Azure data factory. So now
- 1:01:42we will go back again to the Azure
- 1:01:44portal and search for logic apps. So
- 1:01:48here it comes. Uh we will start by
- 1:01:51creating a logic app.
- 1:01:53And here we are getting all these
- 1:01:55options. So the easiest one to get
- 1:01:58started is this one. So we will select
- 1:02:00this one and click on select. Now I will
- 1:02:04select a resource group. Then I will
- 1:02:06give a name to our logic apps.
- 1:02:10So let's say it is a demo logic app.
- 1:02:16And then we will select our region
- 1:02:22and other options we will leave them
- 1:02:24default and simply review and create it.
- 1:02:28Now it has reviewed it. So let me go
- 1:02:30ahead and create it. And now it is ready
- 1:02:33to be used. So let's go to the resource.
- 1:02:36And here this is the homepage. Uh for
- 1:02:39developing anything we will go to
- 1:02:41development tools and logic apps
- 1:02:43designer.
- 1:02:44And here first of all we need to add a
- 1:02:46trigger. So we will use this request
- 1:02:49trigger. We will call it via HTTP. And
- 1:02:52once we select that we are getting this
- 1:02:55here.
- 1:02:56And here we need to create a parameter
- 1:02:59also to pass our table name. So we will
- 1:03:02use this sample payload. And here we
- 1:03:05will simply like uh type let's say table
- 1:03:08name as the parameter
- 1:03:12and then in the value part we will type
- 1:03:15as customers.
- 1:03:18So using this it will generate the
- 1:03:20parameter automatically. So now it is
- 1:03:22parameterized. Now I will click on this
- 1:03:25plus icon and add an action
- 1:03:28and action that we want to use is email.
- 1:03:32And in email also I want to specifically
- 1:03:34use Gmail because I have that account.
- 1:03:37Feel free to use any other kind of
- 1:03:39email. And we will use this send email.
- 1:03:42So first of all we will sign into our
- 1:03:44account.
- 1:03:48And here it is asking for some
- 1:03:50permissions. So I will allow it.
- 1:03:57And now it is connected and ready to
- 1:03:59send the email. And here we need to
- 1:04:03mention two like to whom we want to send
- 1:04:05the email. So I will give my own email
- 1:04:08id here.
- 1:04:10And then we need to add more parameters
- 1:04:13like subject and then body we want to
- 1:04:16use here. So subject we can say let's
- 1:04:19say uh table injection
- 1:04:24status and body we can say that the
- 1:04:27table
- 1:04:29uh then with the table name. So for the
- 1:04:32table name like we select this icon we
- 1:04:35select this table name parameter and
- 1:04:38then we can say has been ingested. So
- 1:04:41this is the email we will be getting. So
- 1:04:44that's it. Now let me save it.
- 1:04:48And now let me go ahead and run it. And
- 1:04:50we will run it with payload because we
- 1:04:53want to pass our table name. So here we
- 1:04:56will pass on like the same thing table
- 1:05:00name
- 1:05:02and then colon let's say customers. So
- 1:05:06let's give it a try.
- 1:05:08So here we got this status code as 202.
- 1:05:11It means it is accepted. So if I go to
- 1:05:14my email id uh this is the email we have
- 1:05:17received with the right subject and the
- 1:05:20table name it is coming properly. So now
- 1:05:23to actually use this from ADF I will
- 1:05:25close it from here
- 1:05:28and then what I will do is when we went
- 1:05:30here in the run with payload section
- 1:05:34then this is the URL that we had
- 1:05:37received and as you can see some more
- 1:05:39things the method is post content type
- 1:05:42is application/json
- 1:05:44and all. So I will copy this URL and
- 1:05:47then these are the other details we will
- 1:05:49provide in ADF. So now I'm back in my
- 1:05:53ADF and let me publish these changes
- 1:05:56first and here looks like the lookup it
- 1:05:59is not allowing to publish because there
- 1:06:01we have parameterized some of the
- 1:06:04things. So if we go there if I click
- 1:06:07this
- 1:06:09and here it is saying this value cannot
- 1:06:11be empty. I will put some dummy values
- 1:06:13for now.
- 1:06:16Now it will allow me to publish
- 1:06:19and after it is published so for the
- 1:06:22email part like let's create another
- 1:06:24pipeline. So new pipeline this time we
- 1:06:28will name it as
- 1:06:30uh email demo
- 1:06:40and then the activity that we are going
- 1:06:42to use is a web activity. So web
- 1:06:45activity it is to make any API call that
- 1:06:48we want to make. It can be any API call
- 1:06:50not only logic apps. So whatever API we
- 1:06:53want to call we use this activity and
- 1:06:55this activity we will name it as send
- 1:06:59email and then if I go to settings
- 1:07:02section here. So here it need the URL.
- 1:07:07Uh I need to click on the activity to go
- 1:07:09there. And here I will paste the URL
- 1:07:12that I had copied from the logic apps.
- 1:07:15Then in the API method we will use post
- 1:07:19and then uh here we had this header also
- 1:07:22content type application/json.
- 1:07:25So I will provide this same I'm copying
- 1:07:28there. Let's see if it is working. So if
- 1:07:31I add this header, yes, it seems to be
- 1:07:33working. So I will copy this value also.
- 1:07:37This is the value. And then in the body,
- 1:07:41this is where we provide the table name
- 1:07:43that parameter. So let's provide it from
- 1:07:46here. Let's say table name. Then colon
- 1:07:51uh then the value let's uh keep maybe
- 1:07:54products for now. It is only for
- 1:07:56testing. So we will keep this value.
- 1:07:59And now let's give it a try.
- 1:08:04And here we can see it is successful in
- 1:08:07the first go. I was expecting like some
- 1:08:09failure at least once but it is good
- 1:08:12that in first go it went successful. We
- 1:08:15can see the same kind of status code we
- 1:08:17got here. And if I go to my email id.
- 1:08:23So this is the email we have received
- 1:08:25with the table name as products. So this
- 1:08:28is how like we were able to send an
- 1:08:31email using web activity and now the
- 1:08:34same thing that here we have hardcoded
- 1:08:36this table name. So let's say if you
- 1:08:39want to parameterize this then what we
- 1:08:41will do is I will click on this uh first
- 1:08:45of all like I will create a uh pipeline
- 1:08:48parameter. So I will click outside go to
- 1:08:51parameters create a new parameter with
- 1:08:54the name again table name
- 1:09:00and then uh I will go to the web
- 1:09:02activity and click on this dynamic
- 1:09:05content here. Here also I will paste
- 1:09:07that same thing that was there this
- 1:09:10thing but this table name now I want to
- 1:09:13pass it dynamically. So I will delete
- 1:09:15it. uh click here and uh uh look like by
- 1:09:21default it is replacing it this way. So
- 1:09:23I will press CtrlG here. Uh looks like
- 1:09:27Ctrl G doesn't work. So let me cancel
- 1:09:30it. Uh let's give it another try.
- 1:09:38Okay. So it will not allow this way. So
- 1:09:40what we will do is like uh this time
- 1:09:43here we will type it. So what I will
- 1:09:46type is at the rate
- 1:09:49pipeline dot
- 1:09:52parameters dot table name. So it came
- 1:09:56automatically and then I will close that
- 1:10:00uh JSON again. So
- 1:10:04like this
- 1:10:06but if you see this is throwing an
- 1:10:08error. Why? Because this this this way
- 1:10:11like directly it will not be supported.
- 1:10:15So if I put a curly bracket here in for
- 1:10:18this parameter also then it will allow.
- 1:10:22So this is how actually it works. So if
- 1:10:24we have something static here and then
- 1:10:27if we pass this at the rate then the
- 1:10:29actual value in this curly bracket then
- 1:10:32it will work. So this is how this table
- 1:10:34name we can pass dynamically and this
- 1:10:36dynamic expression once you work more
- 1:10:38and more then you will be more
- 1:10:40comfortable. I have worked a lot so I
- 1:10:42know these things. Uh so I will click on
- 1:10:45okay now. And now this table name has
- 1:10:48been parameterized. So now let's give it
- 1:10:51another try. And this time for the table
- 1:10:54name let's pass let's say test one or
- 1:10:56something.
- 1:10:58And this time also as expected it is
- 1:11:00successful. Let me go to my email ID.
- 1:11:04And here is our email with test one. So
- 1:11:07this way we have now created a
- 1:11:09parameterized web activity that can send
- 1:11:12email based on the table name. And now
- 1:11:15let's say what we want to do is we want
- 1:11:17to combine this email pipeline and our
- 1:11:20injection pipeline. So for that what we
- 1:11:23will do is I will go to our injection
- 1:11:25pipeline and then that is where we have
- 1:11:28this uh special activity called as
- 1:11:31execute pipeline. So if I search for
- 1:11:33execute. So and if I drag this uh here.
- 1:11:37So this is the pipel here using a
- 1:11:40activity also I can call another
- 1:11:42pipeline. So here we are building a
- 1:11:44nested pipeline where this injection
- 1:11:47pipeline it will call the email
- 1:11:49pipeline. So here we can say like
- 1:11:52execute we can rename it. Let's say
- 1:11:55execute
- 1:11:58send email
- 1:12:00pipeline. And if I go to settings here,
- 1:12:03we can select the pipeline that we want
- 1:12:06to execute. So I will select the email
- 1:12:08demo. And as expected, it is asking for
- 1:12:11a table name. And table name also we
- 1:12:14will add dynamic content here. So that
- 1:12:16like we can pass this pipeline parameter
- 1:12:19again. So now what what will happen is
- 1:12:23this injection pipeline it has this
- 1:12:26parameter table name. The same parameter
- 1:12:28will be passed to this copy data source
- 1:12:31and sync. And the same parameter will
- 1:12:34also be passed to this uh table name
- 1:12:37here while calling this pipeline. And
- 1:12:39then this pipeline will be called using
- 1:12:41this parameter and that will call uh
- 1:12:44this web activity with that same
- 1:12:47parameter and send the email. That is
- 1:12:49how the flow will work. Now only one
- 1:12:52thing is pending that uh we want to send
- 1:12:56this email only after this execution is
- 1:12:59complete. So what we want to do here is
- 1:13:02that we want to connect it. So we have
- 1:13:05this option whether we want to send it
- 1:13:07based on success uh based on failure or
- 1:13:11based on completion. So here we will
- 1:13:13select success because if injection is
- 1:13:16success then only we want to send the
- 1:13:18email that it is success. So now it is
- 1:13:21like a kind of ready end to end. So give
- 1:13:24it a try. Let's run it in the debug mode
- 1:13:27and let's try to ingest our products
- 1:13:29table and send the email at the same
- 1:13:32time.
- 1:13:38And now we can see that injection
- 1:13:40process is complete. And now it will
- 1:13:42trigger the second pipeline here. And
- 1:13:45that is also complete here. And if we
- 1:13:48see the input parameter of this execute
- 1:13:50pipeline. So here we can see in the
- 1:13:53parameter section that the products has
- 1:13:55been passed automatically.
- 1:13:58And because it was a pipeline. So if I
- 1:14:00click on the output, it will give me
- 1:14:02another run ID. So run ID, it is a
- 1:14:05unique ID of the pipeline uh that is
- 1:14:08like attached. So for every run, a new
- 1:14:10run ID is created. And now we are here.
- 1:14:13Here we can see that this email also has
- 1:14:15been sent successfully. If I go back to
- 1:14:18my email, we shall be finding the email
- 1:14:20here. Here it is. So this way now this
- 1:14:24is like a complete uh like a full load
- 1:14:27pipeline that we have created which can
- 1:14:30ingest any table just by giving its
- 1:14:33name. So now let's understand something
- 1:14:36different. So uh so far like we have
- 1:14:40been running everything that goes well
- 1:14:42but let's say if we are running some
- 1:14:44pipeline and we give it a a table name
- 1:14:47that doesn't really exist then let's see
- 1:14:51like how the failure look like and here
- 1:14:54as expected like it failed and we can
- 1:14:57see say that we are getting an error
- 1:14:59message as well that this object it
- 1:15:02doesn't exist and if we check output
- 1:15:05also so there also we can See there is
- 1:15:08nothing written and we are in the errors
- 1:15:10like we have this uh error message also
- 1:15:14that we are getting. So now let's say
- 1:15:17what we want to do is in case of failure
- 1:15:20also we want similar email because
- 1:15:22anybody would want that right. But to do
- 1:15:25that we have this problem that the email
- 1:15:28id like recipient email id we have
- 1:15:31hardcoded that also let's say we want to
- 1:15:33parameterize and let's say the subject
- 1:15:36also and the body also we want to pass
- 1:15:39it dynamically so that for success and
- 1:15:41failure we can send different type of
- 1:15:43email. So now let's understand how we
- 1:15:46can do all this like dynamically.
- 1:15:49So for that what we will do is first we
- 1:15:52will make the relevant changes in the
- 1:15:53logic apps and then we will pass all
- 1:15:56those things from our ADF side. So I
- 1:15:59will close this. I will click on the
- 1:16:02send email or like first let's create
- 1:16:04our parameters properly. So
- 1:16:08uh this time it was receiving only one
- 1:16:10parameter that was the table name. But
- 1:16:13now let's say what we want to do is
- 1:16:17uh let's say we want to send multiple
- 1:16:19parameters. So the first parameter that
- 1:16:22will be like uh recipient
- 1:16:26email recipient let me maybe name it
- 1:16:29that way only. So like what is the
- 1:16:31recipient ID that also let's say we want
- 1:16:34to parameterize
- 1:16:36let's say it is uh datab 14
- 1:16:41and then the second parameter that we
- 1:16:44want is the subject that also we want to
- 1:16:46send dynamically.
- 1:16:49So subject
- 1:16:53and then the value of the subject let's
- 1:16:56say uh what we want here is let's say
- 1:16:59table name maybe let's say customers
- 1:17:02dash then let's say status it can be
- 1:17:05let's say success or failure so we will
- 1:17:08set it dynamically later on first we are
- 1:17:10just preparing our body so that like we
- 1:17:13can create those parameters and then we
- 1:17:16want to like create our send our body
- 1:17:19also. This body also we want to send
- 1:17:21dynamically. So the body let's say it
- 1:17:24says the
- 1:17:26table
- 1:17:30uh then the table name let's say
- 1:17:32customers
- 1:17:34has been
- 1:17:37ingested successfully.
- 1:17:41So let's say these are the three
- 1:17:43parameters we want to send. So I will
- 1:17:46copy this and then we will go back here
- 1:17:50and we will generate it again. So I will
- 1:17:52upload this sample that we have prepared
- 1:17:55and then I will click on done. Now we
- 1:17:58can see that everything it has set it as
- 1:18:01parameter. So now these three parameters
- 1:18:03are available and I will go to this send
- 1:18:06email. So now this two part I will
- 1:18:09remove I will select from this variable
- 1:18:12like email recipient and similarly for
- 1:18:15subject also I will select the subject
- 1:18:18and for body also I will delete
- 1:18:20everything and select the body.
- 1:18:24So these are the parameters we have
- 1:18:26selected now. So let me go ahead and
- 1:18:28save it. And now let's confirm if it
- 1:18:31works as expected. So let's go ahead and
- 1:18:34run with payload.
- 1:18:38And here the body part uh yes the same
- 1:18:41we can use. So let me simply run it
- 1:18:46and it is success. Now let's confirm the
- 1:18:49email.
- 1:18:52Yes, we have the email with customers
- 1:18:54dash success success. And then this is
- 1:18:57the body that is there. So now this new
- 1:18:59logic app like parameters are ready to
- 1:19:02be used. So to use these parameters now
- 1:19:06we will go to our ADF
- 1:19:08and then we will go to our email web
- 1:19:11activity and here so we were just
- 1:19:15sending the table name here but now we
- 1:19:18want to send those uh three elements. So
- 1:19:21what I will do is I will cancel it. Let
- 1:19:24me click cancel here. So now our goal is
- 1:19:28that in this body we want to send this
- 1:19:32kind of body where like we can configure
- 1:19:35the email subject and body and if you
- 1:19:38look at the subject and body. So let's
- 1:19:41see what things are dynamic here. So
- 1:19:44this we may need to change in future. Uh
- 1:19:47this table name we may need to change in
- 1:19:49future. This we can change to success or
- 1:19:51failure. And in this line only this name
- 1:19:55we need to change dynamically. other
- 1:19:57body it can remain the same. So from
- 1:20:00this what we understand is that for the
- 1:20:03email pipeline we we will need to
- 1:20:06parameterize the recipient table name
- 1:20:09and status that will be enough. Rest of
- 1:20:12the details we can prepare within this
- 1:20:14pipeline so that we don't have to
- 1:20:16prepare again and again. So let's go to
- 1:20:19our ADF and now if we go to the
- 1:20:22parameter section. So table name is
- 1:20:24already parameterized. Additionally, let
- 1:20:27me add the uh status here and then the
- 1:20:31recipient as well.
- 1:20:35So these are the parameters we are going
- 1:20:37to use.
- 1:20:39And now to use these parameters so we
- 1:20:42have various options. So uh like I would
- 1:20:46say the first option that comes in mind
- 1:20:48is that this entire body that is there
- 1:20:51that we can prepare dynamically here
- 1:20:54using a dynamic expression like this but
- 1:20:56preparing everything in let's say one
- 1:20:59dynamic expression normally it is like a
- 1:21:02difficult to understand and use and for
- 1:21:04that we create different variables
- 1:21:07prepare there and then finally use that
- 1:21:09variable here and for that very same
- 1:21:12purpose we have diff activity called as
- 1:21:16set variable. So if we search for
- 1:21:19variable we have this activity set
- 1:21:22variable. So if I bring in and we can
- 1:21:25say let's say prepare email body and
- 1:21:30then if I go to settings icon and here
- 1:21:34it it will like uh uh here we need to
- 1:21:37like create a variable. So currently
- 1:21:39there is no variable. I will click on
- 1:21:42new. we need to give a name. So let's
- 1:21:45say we can say email
- 1:21:47body
- 1:21:49or not email body we we shall call it as
- 1:21:52let's say logic caps logic appaps body
- 1:21:56because that includes like uh recipient
- 1:21:59subject and body everything. So we can
- 1:22:01name it as logic apps body and it will
- 1:22:05be a string. There are different
- 1:22:06variables we can create. It will be a
- 1:22:08string. And here in the value, this is
- 1:22:12where like we need to prepare this body
- 1:22:15so that we can pass uh this body to the
- 1:22:20uh logic apps. So now in the web
- 1:22:23activity here instead of this kind of
- 1:22:26expression we have this variable section
- 1:22:29here. So I will go to variable and
- 1:22:32simply select this logic apps body. So
- 1:22:35this way all the logic that is required
- 1:22:37to prepare the complete body it is we
- 1:22:40don't need to put it in the web activity
- 1:22:42it is simplified and that logic we can
- 1:22:45bring it from this set variable. So now
- 1:22:48in this variable we need to prepare this
- 1:22:51body and here as you can see currently
- 1:22:54like it is hardcoded. So let me copy
- 1:22:57this content and let's prepare it
- 1:22:59dynamically.
- 1:23:00So if I go there and uh go here. So one
- 1:23:04option is that here we can use the
- 1:23:06parameters like uh we were like we we
- 1:23:09had parameterized previously only the
- 1:23:11table name or there is a better option
- 1:23:14as well that here instead of preparing
- 1:23:16the complete subject and complete body
- 1:23:20these also we can prepare in another set
- 1:23:22variables and then bring in here. So
- 1:23:25that is how like we use in real
- 1:23:27projects. So let's quickly do that it is
- 1:23:29very easy. So what we need is we need
- 1:23:32let's say one set variable for let's say
- 1:23:35prepare
- 1:23:38email subject
- 1:23:41and then in the settings we will need to
- 1:23:43create a variable. So here let's say we
- 1:23:46can create it with the name subject. It
- 1:23:49will also be a string and this is where
- 1:23:51we will prepare only the subject part.
- 1:23:55So by that what I mean is this is the
- 1:23:57subject we were planning to use there.
- 1:24:00So if I come back here, so this is the
- 1:24:04table name. So I will remove this and I
- 1:24:07will uh like if you want to enter a
- 1:24:10parameter, we need to use this at the
- 1:24:12rate then these brackets and then we
- 1:24:15need to select this uh table name. So
- 1:24:19now this will be put here. Now I want to
- 1:24:22remove this. So same I will do add the
- 1:24:24rate curly brackets and then go inside
- 1:24:28use this status. So now this will
- 1:24:31prepare only the uh subject of the email
- 1:24:35and I will connect here. Similarly we
- 1:24:39will create another one for preparing
- 1:24:41the body of the email. So here we can
- 1:24:44say that prepare email body
- 1:24:49and then if I go to settings again we
- 1:24:51will create another variable let's say
- 1:24:54email body it will also be a string and
- 1:24:58this is where we will prepare this email
- 1:25:01body. So
- 1:25:04here again we will prepare this dynamic
- 1:25:07expression and we can select the table
- 1:25:10has been uh has been ingested
- 1:25:13successfully and as we are going to use
- 1:25:16it for both let's say success and
- 1:25:18failure. So this also we need to change
- 1:25:20a bit. Let's say the table uh this uh we
- 1:25:24can say injection
- 1:25:28has been completed
- 1:25:31and then we can say and the status is
- 1:25:36and then we can mention the actual
- 1:25:38status. So here we can parameterize the
- 1:25:41status. So status and then uh the table
- 1:25:45name also we can parameterize it the
- 1:25:48exact same way. So like this. So now
- 1:25:52here also the table name and status it
- 1:25:54will pick up automatically. I will click
- 1:25:57okay. I will connect this also. And now
- 1:26:00we have two separate I would say
- 1:26:03variables. One for subject one for body.
- 1:26:06And uh the recipient we have a table
- 1:26:10parameter. We don't need to modify it.
- 1:26:12So same we will use. So now let's
- 1:26:14prepare the exact logic apps body here.
- 1:26:17So let me copy this and then uh here we
- 1:26:22need to parameterize these things. So
- 1:26:24let me remove this then add the rate
- 1:26:27curly brackets then email recipient and
- 1:26:30then I will remove this add the rate
- 1:26:33curly brackets and here the variable
- 1:26:36like the email subject. So whatever
- 1:26:39subject we had prepared here that will
- 1:26:42automatically come here and same thing
- 1:26:45we will do for the email body. So add
- 1:26:47the rate this thing and then the email
- 1:26:51body we will use here. So now this will
- 1:26:54prepare that entire logic apps body
- 1:26:58automatically with these three
- 1:26:59parameters.
- 1:27:02So this is how like we can use multiple
- 1:27:04set variables to configure some things
- 1:27:07properly and if and using this way we
- 1:27:10can break up a complex expression in
- 1:27:12steps so that like we can use it
- 1:27:15accordingly. So now let's give it a try
- 1:27:17by running it. So here let's click on
- 1:27:21debug. Uh let's give it a table name. It
- 1:27:23is only for testing. So let's say uh
- 1:27:27test two status we can say success.
- 1:27:33And then uh recipient uh let's say it is
- 1:27:35my same ID.
- 1:27:42Let me copy this one. if you want to try
- 1:27:44again. So this is fine. Let me go ahead
- 1:27:47and run it.
- 1:27:50And this time we can see it was
- 1:27:52successful. So if I sort it based on
- 1:27:55this time, so we can see first it
- 1:27:58prepared the subject. If I check the
- 1:28:00output, it prepared the subject like
- 1:28:02test to success. Then it prepared this
- 1:28:06body. This is the body it has prepared
- 1:28:08for the email body. And then it prepared
- 1:28:11the uh like email body. I think we shall
- 1:28:15rename it to logic apps body. It is
- 1:28:17confusing. We will do it. So here it
- 1:28:19prepared this logic apps body. And here
- 1:28:22here you will notice that automatically
- 1:28:24it has put some backslashes and back
- 1:28:26slash n. So these are escape characters.
- 1:28:29So here like because normal JSON also it
- 1:28:33use double quotes. And if the value it
- 1:28:35also need to include a double quote then
- 1:28:38it has to be prefixed with backslash. It
- 1:28:41is called as escaping of the quotes. So
- 1:28:44this is also like prepared successfully
- 1:28:47and the email has been sent
- 1:28:48successfully. So if you check it out and
- 1:28:52if you run it.
- 1:28:54So here is our email and now what we
- 1:28:57will do is we will integrate these new
- 1:29:00changes into our main injection
- 1:29:02pipeline. So if I go there. So here what
- 1:29:06we will do is if I select this uh email
- 1:29:08pipeline. Now let me minimize this left
- 1:29:11section. And here we can see the new
- 1:29:14parameters are coming automatically. So
- 1:29:16table name we were already passing. And
- 1:29:19now let me pass the recipient
- 1:29:22and then a status like in this case it
- 1:29:25is success because it is on completion
- 1:29:28of success and as you might have guessed
- 1:29:31so on failure also we can call it again
- 1:29:34and there we can pass it as failed. So
- 1:29:37for that instead of like bringing
- 1:29:39execute pipeline again we have this
- 1:29:42option of cloning any activity. So I
- 1:29:45will press this. It has cloned this. And
- 1:29:48I will remove this uh copy here. And
- 1:29:51here we can say for
- 1:29:55failure. And this time I will attach it
- 1:29:58based on on fail. So that whenever it
- 1:30:01will fail then it will come here and it
- 1:30:03will call this entity here. So if I go
- 1:30:06to settings and uh this success I will
- 1:30:08change to a failed. Other details we can
- 1:30:12keep the same. So now let's try to debug
- 1:30:16again in case of failure and this time
- 1:30:19also let's give some wrong table name
- 1:30:21and let's check it out.
- 1:30:24So now the execution is starting. Let's
- 1:30:27wait. So we can see copy data has been
- 1:30:30failed and now this failure pipeline is
- 1:30:33being called
- 1:30:37and this failure pipeline it has been
- 1:30:39executed successfully. That means we
- 1:30:42should have our email now sele. So let's
- 1:30:44go ahead and check it out. So yes, we
- 1:30:47have the failed email. So this way we
- 1:30:50are able to uh send the email in both
- 1:30:53case of success and failure as well. And
- 1:30:56now let's move on to the next concept
- 1:30:59which is about global parameters. So the
- 1:31:02use case is for example here we see that
- 1:31:06this email recipient. So in most of the
- 1:31:09projects it is going to be the same or
- 1:31:11maybe there could be different group of
- 1:31:13recipients which could also be there. So
- 1:31:16if we create some kind of a global
- 1:31:18variable somewhere so that that we can
- 1:31:20use it everywhere in and in every
- 1:31:22pipeline we don't have to mention those
- 1:31:25details again and again. So to create
- 1:31:27those kind of global variable we have
- 1:31:30this concept called as global
- 1:31:32parameters. So for that we go to this
- 1:31:35manage section and maybe before that let
- 1:31:37me publish this to save the progress
- 1:31:41and now let's go to the manage section
- 1:31:44and in here we have this section global
- 1:31:47parameters. So currently there is no
- 1:31:50parameter. Let's create a new one. We
- 1:31:52can name it same like email uh recipient
- 1:31:57type can be string only and the value
- 1:32:00here we can provide the uh email id. So
- 1:32:03it can be one id or by we can provide
- 1:32:06multiple also separated by semicolon
- 1:32:09that is how Gmail supports. So but in
- 1:32:11our case one is enough. So this is uh
- 1:32:14the global parameter we have created and
- 1:32:17now this is the parameter we will use in
- 1:32:20our pipeline. So if we go there and in
- 1:32:24here so wherever we are passing it let's
- 1:32:27say here we need to pass it. So instead
- 1:32:29of passing a hard-coded value we will go
- 1:32:32here and then if we go to parameter so
- 1:32:35here we can see it is a global parameter
- 1:32:38now. So this global one we will select
- 1:32:41now and then click okay. And uh here
- 1:32:44also like before clicking we can say see
- 1:32:46in this time instead of add pipeline.p
- 1:32:49parameters it is global parameters. So
- 1:32:52this also confirm that this is a global
- 1:32:54parameter. So I will select okay here
- 1:32:58and same we will do for failure as well.
- 1:33:01So if we go here, I will remove this,
- 1:33:04open this, select this and press okay.
- 1:33:09So now if we will do the debug, so same
- 1:33:12email will be sent and but this time
- 1:33:15like it will use our global parameter.
- 1:33:18So this way we can set up any global
- 1:33:20parameter. Here we took example of the
- 1:33:23email but there could be other glo
- 1:33:25global parameters as well like normally
- 1:33:27for environment also we create a global
- 1:33:30parameter
- 1:33:31and sometimes like even for let's say
- 1:33:35this URL that we had for logic apps. So
- 1:33:38this URL also we can store in a global
- 1:33:41parameter and then use it here. So that
- 1:33:44in other places also if we have to use
- 1:33:46logic apps then we don't have to like
- 1:33:49repeat it or we don't have to paste it
- 1:33:51again. So this also we can use by
- 1:33:53putting in global parameters. So that is
- 1:33:55the use case for global parameters.
- 1:34:00And this time also we can see the email
- 1:34:03has been sent successfully.
- 1:34:05And next what we are going to do is so
- 1:34:08far we have been doing only the full
- 1:34:10load. Now we will work on the
- 1:34:13incremental load and there also we will
- 1:34:15understand like what additional
- 1:34:17activities are required. So for that to
- 1:34:20do any incremental load like we will
- 1:34:23need to first create some kind of
- 1:34:25metadata table where the information
- 1:34:27will be there that for this table until
- 1:34:30this date and time the data has been
- 1:34:32loaded and now we need to load the data
- 1:34:34from this point onwards next time. So
- 1:34:37for that what we are going to do is we
- 1:34:40are going to create a new table. Uh let
- 1:34:43me let me run this script anyway. I will
- 1:34:45provide it and then I will tell you what
- 1:34:47I have created. So now we will go to our
- 1:34:50SQL DB and run this script.
- 1:34:54So it is executed. So this time apart
- 1:34:57from this DBO schema this additional
- 1:34:59metadata schema has been created. This
- 1:35:02is specially for the metadata. So this
- 1:35:04DBO it is currently acting as our actual
- 1:35:07source. This is from where we are
- 1:35:09actually ingesting the data. This is our
- 1:35:12metadata table space. So here we will be
- 1:35:15creating the tables related to metadata.
- 1:35:18In real projects these two will be
- 1:35:20created in uh two different places like
- 1:35:23two different SQL databases and all. But
- 1:35:25here for demo purpose we are utilizing
- 1:35:28only one SQL DB. So here we can see that
- 1:35:32for this table like it has entered two
- 1:35:34table names here
- 1:35:37and for both the tables it has inserted
- 1:35:39the watermark value as current time and
- 1:35:44now from this time onwards whatever data
- 1:35:47will be inserted into these two tables
- 1:35:50that is something we will try to read.
- 1:35:53And here one thing I forgot so these
- 1:35:56table names it is better if we have them
- 1:35:58in lower case. So that like in actual
- 1:36:01scenarios if we are using upper case,
- 1:36:03lower case or a combination then it will
- 1:36:05be handled automatically. We will see
- 1:36:08how for now like let me update uh this
- 1:36:11SQL query
- 1:36:15and then uh let me like drop this table
- 1:36:17and then we will create it again.
- 1:36:20So uh let me open a new query
- 1:36:25drop table.
- 1:36:29So the table it is deleted now and now
- 1:36:32we will run it again only this part we
- 1:36:36will run again. So let me copy it from
- 1:36:39here
- 1:36:44and let me run it. So now the table is
- 1:36:47created. Uh I think it is showing the
- 1:36:49cash value. Let me close it and try to
- 1:36:52open it. Yes, now it is showing in the
- 1:36:54lower case. And let me update here as
- 1:36:57well so that I don't uh forget when I
- 1:37:01share for share with you. So now it is
- 1:37:05done. And now if we go to the actual
- 1:37:08customers table. So here we have this
- 1:37:11modified date column. And here we can
- 1:37:14see the values currently are of only
- 1:37:162024. After that we don't have any data.
- 1:37:19So here if we will insert any new data
- 1:37:22which is greater than this watermark
- 1:37:24value then that is the data we will try
- 1:37:27to read. And now what we need is uh real
- 1:37:31actual new data here. So for that also I
- 1:37:35have prepared these two SQL queries. So
- 1:37:37it will insert two new rows in the
- 1:37:39customers and some new rows in the
- 1:37:41products table. So let me copy it. Let's
- 1:37:44go ahead and let me run these queries.
- 1:37:49So the queries has been executed. Now
- 1:37:52let's check the data in customers. So
- 1:37:55here we can see now we have this new
- 1:37:57data and in products also we have this
- 1:38:00new data now. So now when we will do
- 1:38:03incremental load so it will read these
- 1:38:05rows because we will put a condition
- 1:38:07where what this modify date it shall be
- 1:38:10greater than the value which is stored
- 1:38:12in this table or the customers or
- 1:38:15products table. So now let's go ahead in
- 1:38:18the ADF and implement the same. So now
- 1:38:21I'm back in our Azure data factory. So
- 1:38:25here in the copy data here we see that
- 1:38:28in the source part so far we were using
- 1:38:31the directly the table name but this
- 1:38:34time we need a query like select star
- 1:38:36from table where where the modified date
- 1:38:39is greater than that value. So this time
- 1:38:42we will be using here as a query and
- 1:38:45here uh to pass the value. So table name
- 1:38:50we have as a parameter but the actual
- 1:38:52value that we need to put here. So that
- 1:38:54also we need to read it first from that
- 1:38:57metadata table and then we will be able
- 1:38:59to pass it in the query here. So now to
- 1:39:02read the value from that metadata table
- 1:39:06what we will do is if you have guessed
- 1:39:08we will use the lookup activity again
- 1:39:11because we need to re uh read something.
- 1:39:13So we will say read
- 1:39:16note metadata we can say read watermark.
- 1:39:23So here uh in the settings we will go
- 1:39:26data set we will select the same SQL
- 1:39:28server and here also like uh what we
- 1:39:32will do is here itself we will need to
- 1:39:34write a query as well. So first we will
- 1:39:37write a query. So let's build the query
- 1:39:40quickly. If you go to our query section,
- 1:39:43we can say like select star from dbo dot
- 1:39:48customers
- 1:39:50where modified
- 1:39:52time uh it is modified date
- 1:39:58it is not coming automatically. Let's
- 1:40:00see
- 1:40:03modified date is greater than
- 1:40:07uh this value for customers. This is the
- 1:40:10value. Let me copy this.
- 1:40:17So let me try to run it.
- 1:40:20Yes, it is working. So this is the query
- 1:40:23we need to use in our lookup. So I will
- 1:40:26copy it. Then I will go here and here.
- 1:40:31And this time also we will use this add
- 1:40:33dynamic content. Uh and here I will
- 1:40:36paste this value.
- 1:40:39Uh sorry I I think this is the wrong
- 1:40:42place I placed it. Let me cancel it. So
- 1:40:45this query we will be this query will be
- 1:40:47useful for this one because we need to
- 1:40:50read the incremental data from the
- 1:40:52table. And here the table name and this
- 1:40:56watermark value. These two we will
- 1:40:58parameterize.
- 1:41:00So for now I will leave it as it is. And
- 1:41:02the query that we need to use here. So
- 1:41:05that will be a different one. Let's
- 1:41:07build that one. So it will be like
- 1:41:09selecting from this table watermarks. So
- 1:41:12this one it will be like this uh select
- 1:41:15star from
- 1:41:18metadata dot table watermarks where
- 1:41:24table name
- 1:41:27is equal to customers.
- 1:41:32So this way it will be able to read this
- 1:41:35watermark value. So this watermark value
- 1:41:37this is what we are planning to use and
- 1:41:40instead of uh star we will say watermark
- 1:41:44value here
- 1:41:49so that we will only get the value part.
- 1:41:52So now this is the query I will copy I
- 1:41:55will go back and this is something we
- 1:41:57need to use in our lookup now. So here I
- 1:42:00will put it as dynamic
- 1:42:05and in here uh the only thing we need to
- 1:42:07parameterize is this table name. So this
- 1:42:10customers I will remove then add the
- 1:42:13rate then these brackets and then I will
- 1:42:16click this table name parameter. So now
- 1:42:19it has been parameterized and this time
- 1:42:23we will keep this one checked. We will
- 1:42:25not remove this because the watermark
- 1:42:28value that will be there for any table
- 1:42:31it will always and always return only
- 1:42:33one value. So that is why we will be
- 1:42:36using this. So that only one row we will
- 1:42:39get and that we will use in the next
- 1:42:41step. So before adding the details into
- 1:42:44next steps first let's understand how
- 1:42:46this watermark value is being returned
- 1:42:48here. And as you can see we have not
- 1:42:51connected here and the other details are
- 1:42:54also like not configured properly into
- 1:42:56these sections. So for that this kind of
- 1:42:59testing when we need to do where only
- 1:43:02one thing we want to run other things we
- 1:43:04want to hold on for now. So there we
- 1:43:07have this option if I right click and
- 1:43:09then click deactivate. So it will not
- 1:43:12run this section anymore. So this I will
- 1:43:14deactivate all of them and then when I
- 1:43:17will do debug it will run only this
- 1:43:20section. So that is the purpose of like
- 1:43:22how that is how we can deactivate some
- 1:43:25of the activities for debugging or any
- 1:43:27actual execution as well. So I will
- 1:43:30click debug now and it is saying no
- 1:43:34value provided for parameter schema and
- 1:43:36the table name. Let's see. So here
- 1:43:40although we are using this query uh it
- 1:43:43is asking these two parameters because
- 1:43:45these were mandatory. So although
- 1:43:47actually these will never be used we can
- 1:43:49provide any value here. So this will not
- 1:43:53be actually used. So just let me give
- 1:43:55any value here and then uh let's run it
- 1:43:58in the debug again. And the table we
- 1:44:00will use it will be customers only. So
- 1:44:03let's go ahead and use it.
- 1:44:08And the execution it is complete now. So
- 1:44:11we can see it is able to read the
- 1:44:12watermark. And if you see the output it
- 1:44:15is saying like uh output we are getting
- 1:44:17as the first row and then we have the
- 1:44:20watermark value. And this is the value
- 1:44:22we want to use.
- 1:44:24So now the purpose will be that we want
- 1:44:27to use this output of this lookup
- 1:44:29activity as input into this copy data
- 1:44:32activity because this watermark value we
- 1:44:34need to use there. So for that what we
- 1:44:37will do is we will go to this copy data
- 1:44:40and we will go to our query here and
- 1:44:43this is where we need to pass that value
- 1:44:46dynamically. So what we will do is so
- 1:44:49again add the rate these brackets and
- 1:44:52for that uh like the uh the expression
- 1:44:56that is there. So that is uh it is
- 1:44:59called as activity
- 1:45:08and here looks like it is not coming. Uh
- 1:45:12okay the reason is that like we can use
- 1:45:15its output only when these are
- 1:45:17connected. We are we have not yet
- 1:45:19connected this. So that was the reason.
- 1:45:22Now let's go back after connecting this
- 1:45:26and now let's do the same.
- 1:45:32So it is now it is coming automatically
- 1:45:34once I type the rate. So this activity
- 1:45:36watermark dot first row only. So this is
- 1:45:40saying like this activity dot output dot
- 1:45:45first row dot we had the watermark
- 1:45:49value.
- 1:45:52So that is how we will be able to access
- 1:45:54it and this extra at the rate I need to
- 1:45:56remove it from here. So now what what it
- 1:45:59will do is it will select star from
- 1:46:01customers where modified date is greater
- 1:46:03than uh this value that it will read
- 1:46:06from the previous activity. So that is
- 1:46:09how it will work now and also like about
- 1:46:12the lower case uh we can add one more
- 1:46:16thing here in this read part. So here it
- 1:46:19will directly pass the table name and if
- 1:46:22somebody has entered this table name in
- 1:46:24the upper case or different cases then
- 1:46:27it will not be able to find the right
- 1:46:29name. So here what we will do is we have
- 1:46:32this function
- 1:46:34uh lower. So let me search for that
- 1:46:37function here. Here we have this
- 1:46:38functions section. So if I search for
- 1:46:41the function is tool lower.
- 1:46:44So if I click this function here, it has
- 1:46:46entered this function and within this
- 1:46:49function we need to pass this entire
- 1:46:52parameter for the table name. So now
- 1:46:54whatever table name we provide, it will
- 1:46:56first convert to lowerase and then it
- 1:46:59will get the watermark value.
- 1:47:02And after reading the watermark value,
- 1:47:04this is where like we are selecting only
- 1:47:07incremental data.
- 1:47:09And then uh there is like two more
- 1:47:12things we need to do still. So what is
- 1:47:16that is? So if we go to sync here and in
- 1:47:19the sync like we are only providing the
- 1:47:21table name here. We are not providing
- 1:47:24any file name. So what will happen is it
- 1:47:27will use the same file name in the
- 1:47:29target and it will try to like replace
- 1:47:32the same file name again and again with
- 1:47:34the incremental data. So we don't want
- 1:47:37that. So what we will do there is I will
- 1:47:40open this data set now and in here we
- 1:47:43can see like uh this is the demo
- 1:47:45container. This is the folder table name
- 1:47:48and by default it was creating the
- 1:47:50tables like dbo.c customers.txt
- 1:47:53automatically. So here uh the file name
- 1:47:57also we can provide like our own name.
- 1:47:59So what we will do here is that I will
- 1:48:02add this dynamic content and then we
- 1:48:05will try to provide the name as let's
- 1:48:07say the table name underscore the
- 1:48:10current time or something txt so that
- 1:48:13whatever incremental data we will get
- 1:48:16with that time it will create a new file
- 1:48:18instead of replacing the original file.
- 1:48:21So for that we have here a concat
- 1:48:24function I think let me search for it.
- 1:48:28Yes concat is there. So we need to
- 1:48:30concat two things here. The first thing
- 1:48:33is the table name. So in the parameters
- 1:48:36we already have a table name. Then comma
- 1:48:40we want to pass a underscore. So in
- 1:48:42double quotes I will put a underscore.
- 1:48:45And then third thing we want to concate
- 1:48:47is the
- 1:48:50uh okay I think here like it uh let me
- 1:48:54see if it is able to take a double quote
- 1:48:56or it is something else.
- 1:48:59So here I think like it is not double
- 1:49:01quote it take like single quote as the
- 1:49:04uh as the string enclosure. Normally in
- 1:49:06Python we use double quote. So that is
- 1:49:08what we remember but here it take this
- 1:49:10single quote. So now this is what it is
- 1:49:13taking. And now the third thing we want
- 1:49:16to combine will be a time. So if we go
- 1:49:19to the function and then we search for
- 1:49:23let's say UTC. So we have this UTC now.
- 1:49:26So this will automatically provide the
- 1:49:29current time in UTC. So now it will
- 1:49:31create the file with this name with the
- 1:49:34incremental data. So this is another
- 1:49:37thing we have fixed now. And now there
- 1:49:40is one last piece we need to do before
- 1:49:42we we run the pipeline. And uh what that
- 1:49:45is so this will read our watermark. This
- 1:49:48will ingest the data. But after
- 1:49:50ingesting the data what we need to do is
- 1:49:52we need to update the watermark value
- 1:49:55again with the new value that will be
- 1:49:57the current time. So that after that
- 1:49:59value when it will go read read the
- 1:50:02water mark value next time then uh it it
- 1:50:06can use like the only the new record the
- 1:50:08the record that will ingest in current
- 1:50:10run it it doesn't bring them again. So
- 1:50:13after reading watermark in the start
- 1:50:15ingesting data we need to update back
- 1:50:17the watermark value with the new value.
- 1:50:20So for that what we need to do is we
- 1:50:23have another activity named script
- 1:50:25activity. So first maybe let me activate
- 1:50:28this uh we are not like running
- 1:50:32isolation mode now. So now we will bring
- 1:50:35this uh activity and we will name it as
- 1:50:38update watermark
- 1:50:44and in here like we want to update it
- 1:50:47obviously only based on success. So we
- 1:50:50will drag this success here.
- 1:50:54Okay. Uh it is some tricky. Let me zoom
- 1:50:57in.
- 1:51:02Yes, now it is attached.
- 1:51:04And here we need to uh run a update
- 1:51:06query. So we will use the same SQL
- 1:51:09server. And here we will run a update
- 1:51:11query. So update query how we it will be
- 1:51:14first let's build that query. So we will
- 1:51:17say that update we want to update this
- 1:51:21table.
- 1:51:23Uh we can say like then set watermark
- 1:51:27value
- 1:51:32equal to we want to set it up as the
- 1:51:34current time. Let me see if I have
- 1:51:37somewhere. Yes, this is the function
- 1:51:39that is used for current time. This one
- 1:51:43and where table name is customers. So
- 1:51:45what it will do is it will update the uh
- 1:51:48table. it will update this table where
- 1:51:51the table name is this customers with
- 1:51:53the current time. So let's uh check it
- 1:51:56out once before running. So I'm just
- 1:51:58checking the value here. So currently we
- 1:52:01can see it is like uh 10 1026.
- 1:52:05Okay. And uh if I run this,
- 1:52:08it is run now. And now I think I need to
- 1:52:13open it again to check the updated
- 1:52:15values. Yes, it is 10 3401. So this
- 1:52:19query is working fine. I will copy this
- 1:52:22query now. And this is the query we will
- 1:52:24use in our update script. And if you
- 1:52:27have guessed obviously we will use
- 1:52:29dynamic expression and this table name
- 1:52:32we need to parameterize it. So we will
- 1:52:35use the parameters table name here. And
- 1:52:38with this this incremental load like end
- 1:52:41to end it shall work fine now. So this
- 1:52:45time maybe let me save it or publish it
- 1:52:47so that I don't lose the progress just
- 1:52:49in case
- 1:52:52and the publish is complete now.
- 1:52:55So now let's go ahead and debug it. So
- 1:52:59let's run it for customers only first.
- 1:53:01Give it a try.
- 1:53:06And if I click on any empty space then
- 1:53:08it will show this output. It is
- 1:53:11currently cued.
- 1:53:13So yes, read mark watermark it is
- 1:53:16expected to run now. It is in progress.
- 1:53:20So it is success. We can see the
- 1:53:22watermark value it is uh coming properly
- 1:53:25now. And now copy data also it has run.
- 1:53:30So if we see this uh ingest copy data.
- 1:53:34So here okay we can see like it has it
- 1:53:36is not reading any data and uh I don't
- 1:53:40know if you have guessed the reason
- 1:53:41because I think I have. So the reason is
- 1:53:43that when we were preparing this query
- 1:53:46so we updated this watermark uh like to
- 1:53:49some later value. So this data that we
- 1:53:51had inserted here the watermark value is
- 1:53:54bigger than this value that we ran
- 1:53:57manually here. That is why it is not
- 1:53:59able to read that incremental data and
- 1:54:02it is only reading like zero rows. Still
- 1:54:04like execution wise it is going fine and
- 1:54:07even update watermark has been run
- 1:54:09successful. So just let's check it's if
- 1:54:12the new value of the watermark at least
- 1:54:15was updated properly. So here it was
- 1:54:181034. Let me close it. Let me open it
- 1:54:23and yes 10:35. So this logic is working
- 1:54:26fine. So let's try the same for products
- 1:54:29table because this value we had not yet
- 1:54:31updated. For this one it will work fine.
- 1:54:34So let's go back. Let's click on debug.
- 1:54:37Let's run it for products table. And
- 1:54:39this time let me enter in the uh upper
- 1:54:43case so that we can confirm that the
- 1:54:45lower logic is also working fine.
- 1:54:51Let me zoom out a bit.
- 1:54:57So the watermark it is ready now.
- 1:55:01Okay, it has read it.
- 1:55:04Now the copy data shall be running. Yes,
- 1:55:06it is running.
- 1:55:09So copy data it is complete. Now if we
- 1:55:12check this thing. Yes, here we can see
- 1:55:15that it has read two rows. Now uh but I
- 1:55:18think in the products table we had more
- 1:55:20data. So I think I may have missed
- 1:55:22something again. We will figure it out.
- 1:55:25But at least some data it is reading. We
- 1:55:27will see.
- 1:55:29So looks like the pipeline execution it
- 1:55:32is success now. So first like let's see
- 1:55:35why it read only two rows. So it read
- 1:55:39the watermark value which is uh 10 10 it
- 1:55:42is fine and then it is running the copy
- 1:55:45data. If you see the input of the copy
- 1:55:47data it is showing the query and here
- 1:55:51okay. So even though we provide the
- 1:55:53table name as products it uh it read
- 1:55:57customers table. So I think we forgot to
- 1:56:00remove this hard coding this time. So
- 1:56:02let's go to copy data and here we forgot
- 1:56:05to parameterize this table name here. So
- 1:56:08this one also let's use the parameter.
- 1:56:14Okay. And now like it shall be fine end
- 1:56:17to end but again like watermark it has
- 1:56:20updated again. So if we run it again
- 1:56:22probably it will uh run the it will
- 1:56:25ingest the it will not ingest any new
- 1:56:27data. If we check the query being used
- 1:56:29in watermark we can check in the input.
- 1:56:32So yes for the table name products it
- 1:56:36has updated and again here also one more
- 1:56:39problem I missed. So for this products
- 1:56:42here we can see it was in upper case. So
- 1:56:45while reading the data we changed to
- 1:56:47lower case but while updating the
- 1:56:49watermark we did not change it. So uh
- 1:56:52most probably it is it has not updated
- 1:56:56the uh right products data. So first
- 1:57:00let's uh confirm that. So this is the
- 1:57:03table and if I open it again.
- 1:57:06So
- 1:57:08so here if we look at it uh surprisingly
- 1:57:12it seems like it has updated it. I think
- 1:57:14so. I don't remember the actual value.
- 1:57:17Uh but let's give it a try. I was
- 1:57:19expecting it to not to be updated. But
- 1:57:21let's try this thing manually here. Like
- 1:57:24if I type products here, this time the
- 1:57:27value is 10 3812. Let's uh give it a
- 1:57:30try.
- 1:57:32It has run. Let me close it and open it.
- 1:57:36Oh, so looks like this is also not case
- 1:57:39sensitive, which is bit surprising to me
- 1:57:41actually. uh because I was expecting
- 1:57:43this to be a like case sensitive because
- 1:57:45this is kind of a vare which is a string
- 1:57:48but this is some behavior even I was not
- 1:57:50aware. So uh fine uh this piece like we
- 1:57:54are good. So our watermark it is updated
- 1:57:56now. So now what we need to do is uh we
- 1:57:59need to like run it one more time to
- 1:58:01confirm if it is working end to end. So
- 1:58:04for that we will insert some new data
- 1:58:07and uh maybe before that let's check one
- 1:58:09more thing uh so that there also there
- 1:58:11is no surprise. So that is about the
- 1:58:14actual table that was landed like actual
- 1:58:16file that landed into our ADLS. So for
- 1:58:20that let's go to our ADLs.
- 1:58:23Let's go to the containers.
- 1:58:27Let's go to products and here we can see
- 1:58:29like uh again the lower case uppercase
- 1:58:32thing. So even though I mentioned like
- 1:58:34we are using that to handle everything
- 1:58:37but uh as you can see like changing the
- 1:58:40case what all places it can create
- 1:58:42problem right so this is very important.
- 1:58:45So we were using upper case so this is
- 1:58:47where like uh it has landed the table.
- 1:58:50So this is the name it has used and then
- 1:58:55if we edit it so here we can see like it
- 1:58:58it read the customer's data the
- 1:59:00customer's incremental data. So now we
- 1:59:03know there is one more place to fix that
- 1:59:05lower thing in the file name and in the
- 1:59:08folder name as well. So let's go back.
- 1:59:11Let's go to our sync and let's open
- 1:59:14this. So here also let's put the lower
- 1:59:18the rate to lower. Let me drag it so
- 1:59:22that I don't make any spelling mistakes.
- 1:59:26So two lower
- 1:59:29and I need to put it inside this. Let me
- 1:59:33copy this. And then in the file name
- 1:59:36also we will put the same in the table
- 1:59:38name. So to lower this table name.
- 1:59:46Okay. So now it shall be working fine.
- 1:59:50Let me save these changes first.
- 1:59:53So it is published now. And now to
- 1:59:56insert more incremental data I have
- 1:59:59prepared again another insert script and
- 2:00:02these scripts I will like keep putting
- 2:00:05in a folder. So this is how like I'm
- 2:00:08planning to share. So step by step you
- 2:00:10will see. So now we are running this uh
- 2:00:13incremental insert incremental data
- 2:00:16round two. So I have copied the script.
- 2:00:19Let's go to our SQL DB and let me run it
- 2:00:23here itself.
- 2:00:27So now we have like two more records in
- 2:00:30customers and three more records in
- 2:00:32products table. So this time hopefully
- 2:00:35the incremental data will flow end to
- 2:00:37end. Let's give it a try.
- 2:00:42So let me click on debug and this time
- 2:00:46let's try with customers.
- 2:00:52And this time like I'm using these two
- 2:00:53upper case then lower case to confirm
- 2:00:56like our actual functionality.
- 2:01:00So it is starting now.
- 2:01:03Okay. The watermark it is successful
- 2:01:05now. So it has read the value.
- 2:01:13Copy data it is running now.
- 2:01:17So by default like this will refresh
- 2:01:18after 10 seconds or something. But we
- 2:01:21can click this button here like uh uh if
- 2:01:25you want to check the progress earlier.
- 2:01:27Yes, copy data it is successful and from
- 2:01:30customers as expected two records has
- 2:01:32been read and written and now like it
- 2:01:36will be sending the email watermark also
- 2:01:39it has updated. So if we go to the email
- 2:01:43first so let me refresh it. These are
- 2:01:45the emails we have been getting. So for
- 2:01:48customers we have got this email and
- 2:01:51again you will notice that uh even in
- 2:01:54the email like we need to put that lower
- 2:01:56or upper case whatever we want but at
- 2:01:58least this is working fine. So good
- 2:02:00enough for now. For now we will not
- 2:02:02change it. And let's look at our target
- 2:02:05data now. So
- 2:02:09if we go to the container here.
- 2:02:13So now like it has uh it is like writing
- 2:02:16the data inside customers hopefully.
- 2:02:18Yes. So this is the data it has written
- 2:02:22and the latest one it is 1047 this one.
- 2:02:26So let's have a look at it. So yes it
- 2:02:29has read only the incremental data and
- 2:02:32then it has uh like created the file
- 2:02:35with that.
- 2:02:37And this time one more thing to notice
- 2:02:39here is that it did not put that
- 2:02:41extension here automatically the dot
- 2:02:43txt. Why? Because like the file name we
- 2:02:46have provided from our side. So if you
- 2:02:49are providing from our side then in that
- 2:02:51same expression also we might concate
- 2:02:54let's say a dot CSV at the end as well
- 2:02:56if you want let's say dot CSV or
- 2:02:58whatever extension we want to provide
- 2:03:00but that doesn't really matter much so
- 2:03:03we will leave it as it is. And then the
- 2:03:05final piece, the watermark shall be
- 2:03:07updated now for customers. So let's go
- 2:03:10ahead and check it out. So customers
- 2:03:131035 is the old watermark. Let me close
- 2:03:16it. Open it again. And yes, the
- 2:03:20watermark is also updated now. So this
- 2:03:24is how we are able to ingest incremental
- 2:03:26data end to end for a single table.
- 2:03:29Similarly, if you want to ingest
- 2:03:31incremental for products, so we will
- 2:03:32just mention products here. Run again
- 2:03:35and it will work fine. I hope you
- 2:03:37believe me for that.
- 2:03:39And now the next piece we are going to
- 2:03:41understand is applying if condition. So
- 2:03:45let's say if the functionality that we
- 2:03:47actually want is for example if we are
- 2:03:50running this incremental load and there
- 2:03:53is no incremental data in the actual
- 2:03:55source then let's say we don't want to
- 2:03:57run this copy data and we want to run it
- 2:04:00only when we have incremental data in
- 2:04:02the source. So for that we can use if
- 2:04:05else kind of condition there. So to use
- 2:04:08that what we will do is we will simply
- 2:04:11search for if here
- 2:04:14and then this is what we will bring in.
- 2:04:18So now as you can see this if condition
- 2:04:21it has two part. If it is true then do
- 2:04:24this. If it is false then do this. So
- 2:04:28now like before we like decide what to
- 2:04:31put in true and false we will need one
- 2:04:33more thing. Here we are just reading the
- 2:04:36watermark but uh now we will need to
- 2:04:39check like whether the records are there
- 2:04:41greater than this watermark or not. So
- 2:04:44for that we will use another lookup
- 2:04:47activity to simply get the count of
- 2:04:49records where the watermark is greater
- 2:04:52than that value. So this one we will uh
- 2:04:55rename it let's say check
- 2:05:00count in source
- 2:05:03and here the condition it will be like
- 2:05:05simply select count star from the table
- 2:05:08where the watermark value is greater
- 2:05:10than that. So here let me copy okay this
- 2:05:14is reading the watermarks not the actual
- 2:05:17table.
- 2:05:19So what we will do is let's build our
- 2:05:21query in the SQL DB and here I have
- 2:05:25built this query like select count star
- 2:05:27from this table where modified date it
- 2:05:30is greater than this value. So this is
- 2:05:32how like uh it is returning zero records
- 2:05:36and if I for example puts let's say 2020
- 2:05:40so now it will return all the records.
- 2:05:42So this is how this query is working. So
- 2:05:45let me copy this query. Let's take it to
- 2:05:48ADF and in this check count part and in
- 2:05:52here in the settings we will first
- 2:05:55select our SQL DB and then here also we
- 2:05:59will be getting count. So only first row
- 2:06:02is fine. We will go to query and then we
- 2:06:06will say select count star. I will put
- 2:06:09as as well as count so that like later
- 2:06:12on we can use it easily and we will need
- 2:06:16to use dynamic expression here. So let
- 2:06:18me go there and here I will paste the
- 2:06:21same. So is count
- 2:06:27and then this customers we will need to
- 2:06:30parameterize it as the table name. So
- 2:06:33this I have done and also in here uh we
- 2:06:38will uh watermark value we will get it
- 2:06:40from the read watermark part. So here
- 2:06:43also we will do the same that add the
- 2:06:47rate activity
- 2:06:51uh read watermark
- 2:06:54uh then dot
- 2:06:58output or this thing I think we were
- 2:07:00using in copy data. So let me directly
- 2:07:03get it from there.
- 2:07:05So if you go to copy data,
- 2:07:08if you go here, then this is how we can
- 2:07:11use it as a parameter.
- 2:07:15So now let me paste it back here.
- 2:07:26So yes, we have used it now. So now this
- 2:07:29will give us as a count and then this
- 2:07:33count we will use in the if condition.
- 2:07:36So in if condition what we need to do is
- 2:07:39if we come here this is the name. So
- 2:07:42name we will can select check
- 2:07:45uh source count
- 2:07:48or yeah this is what we will leave. And
- 2:07:51then if we go to the activity so here we
- 2:07:53need to specify the condition. So if it
- 2:07:56is true it will do this. If not true
- 2:07:58then do this like that. So here also we
- 2:08:01will use this dynamic expression. So
- 2:08:04basically we need to check if that count
- 2:08:06is greater than zero or not. So to check
- 2:08:09it greater than zero I think we have a
- 2:08:12function. Let me check. Yes we have this
- 2:08:15greater. So greater
- 2:08:19uh then in the first value we will
- 2:08:22provide like this. So instead of read
- 2:08:25watermark uh we are now checking the
- 2:08:28output value of the uh that source count
- 2:08:32check here. So let me copy its activity
- 2:08:35name this one.
- 2:08:38So I'm copying this and then if I go
- 2:08:42back here. So this is the activity that
- 2:08:45we are reading now. And then dot output
- 2:08:48dot first row dot count. We are using
- 2:08:52count this time. and then comma now the
- 2:08:55second parameter for this greater second
- 2:08:58parameter is zero. So if this count is
- 2:09:01greater than zero or not that is what it
- 2:09:03is checking now and then if it is true
- 2:09:07then do this if false then do this. So
- 2:09:10if it is true then what we need to do is
- 2:09:13we are planning to use this copy data
- 2:09:16and uh then like update watermark and
- 2:09:20emails like we will send it uh
- 2:09:22irrespective. So if even if count is
- 2:09:24zero we can still send the email. So I'm
- 2:09:27draw I'm deleting this link selecting
- 2:09:29and then pressing the delete key. So
- 2:09:32these we will still send. So like this.
- 2:09:35So if this is success then send success
- 2:09:38mail failure then send failure email and
- 2:09:41these two we need to move it inside the
- 2:09:43copy. So what we will do is I will right
- 2:09:46click and do a cut and I will click this
- 2:09:49edit icon. If I click this and now I'm
- 2:09:52inside the true part of the if else
- 2:09:55condition. So I will do right click and
- 2:09:57then paste. So now this is now inside
- 2:10:00that. If I go back, I will do the same
- 2:10:04for the update watermark. So, right
- 2:10:06click, cut, go inside here, then paste
- 2:10:11and then connect. So, now like uh this
- 2:10:15we have put it as the inside this uh
- 2:10:18inside this if condition. So, this is
- 2:10:21how like first we can check the count
- 2:10:23and then use a if else condition. So, if
- 2:10:26the count is greater then only it will
- 2:10:28do the injection. So let's give it a try
- 2:10:31if it is working fine. So okay it is
- 2:10:35saying no value provided for schema and
- 2:10:37table name. So this is about this one
- 2:10:39the default values we have not provided.
- 2:10:43So this doesn't matter but let's provide
- 2:10:45something.
- 2:10:48Now let's do a debug again. Let's try
- 2:10:52for customers only.
- 2:10:58And now it is running. So if we check
- 2:11:00this execution now so read watermark it
- 2:11:03is giving the watermark value uh
- 2:11:05checking the count it is returning count
- 2:11:08as zero and then uh like this is our if
- 2:11:11else condition. So in the output it will
- 2:11:14not show anything but as we can see like
- 2:11:17after this the send email has been
- 2:11:19executed but the copy data and these two
- 2:11:22activities has not been executed. Why?
- 2:11:25because this condition it went into
- 2:11:27false and in false we are not doing
- 2:11:29anything. So this is how we can use if
- 2:11:31else condition in ADF and similar to if
- 2:11:34else we have a switch statement as well.
- 2:11:38So if I bring in so it is very similar
- 2:11:41it is like we give a expression and like
- 2:11:44if let's say we are expression I'm
- 2:11:46mentioning as count and then we can add
- 2:11:49cases if count is let's say case one it
- 2:11:52is greater than zero case two let's say
- 2:11:54negative count although it won't be
- 2:11:56there just as example I'm telling case
- 2:11:58two let's say count is zero so different
- 2:12:01things if we want to do we can use this
- 2:12:04case statement as well and other use
- 2:12:07case for this case uh sorry this switch
- 2:12:10activity it can be like uh to
- 2:12:13parameterize even uh copy data kind of
- 2:12:15thing so let's say currently we are
- 2:12:18using only SQL server as a source but in
- 2:12:20future let's say we have any other other
- 2:12:23SQL based source like data data oracle
- 2:12:27those kind of sources so there same copy
- 2:12:30data activity cannot be used for
- 2:12:32multiple sources but let's say this read
- 2:12:35watermark lookup email all these things
- 2:12:38are common only because these are no
- 2:12:40depending on our source itself. So in
- 2:12:43those cases what we do is we create
- 2:12:46another parameter like source type and
- 2:12:48then in the switch condition we put this
- 2:12:51copy data. So case one if source type is
- 2:12:53SQL server then this thing if it is
- 2:12:56let's say uh any other source let's say
- 2:12:59Oracle then case two. So like that we
- 2:13:02create these cases in the switch
- 2:13:03statement and then use it. But the using
- 2:13:07wise it is very similar to if condition.
- 2:13:10So I will not directly show
- 2:13:11implementation.
- 2:13:13Uh so this is the switch condition or
- 2:13:15switch activity that is there. And now
- 2:13:18the next thing we will understand is
- 2:13:21let's say this pipeline we have created.
- 2:13:23So this pipeline we are able to run it
- 2:13:26for any table that we want. But uh the
- 2:13:28thing is that we can run this pipeline
- 2:13:30only for one table at a time. But in
- 2:13:33real scenarios we have let's say
- 2:13:35hundreds. of tables. So how can we
- 2:13:37ingest all the tables in one go or on a
- 2:13:40daily basis? So to do that we have a for
- 2:13:43each activity
- 2:13:45for each. So this is what we will
- 2:13:47understand next and using this activity
- 2:13:50what we will do is we will create
- 2:13:52another pipeline that somehow will call
- 2:13:56like this in this pipeline that we have
- 2:13:58created for each and every table. So
- 2:14:01first let me save these changes
- 2:14:05and now I will expand this pipeline
- 2:14:07section again and then we will create a
- 2:14:10new pipeline
- 2:14:12and we will name it as let's say PL
- 2:14:17uh
- 2:14:19PL
- 2:14:22for each demo
- 2:14:25and then here we will call our for each
- 2:14:31So this is the activity that we will
- 2:14:33use. So let me minimize this part. Let
- 2:14:37me zoom out a bit.
- 2:14:41And up here the name we can say like
- 2:14:45inest
- 2:14:47all tables
- 2:14:50and within here we can provide some
- 2:14:51activity. So I will if we go to settings
- 2:14:55uh these things we will go maybe later
- 2:14:57on. If I go inside here uh then here we
- 2:15:01will call that pipeline we have created.
- 2:15:04So I will call this execute pipeline and
- 2:15:07then I will say execute
- 2:15:10injection
- 2:15:12pipeline
- 2:15:13and then if I go to settings then here
- 2:15:16we will call this demo injection. This
- 2:15:19one as we know it takes a table name as
- 2:15:21a parameter. So now uh like this is how
- 2:15:25like high level we will use. Now the
- 2:15:28thing is that because it takes a table
- 2:15:30name as input. So we will need to find a
- 2:15:33way that first we get somehow list of
- 2:15:36tables that are there and then in this
- 2:15:38for each we pass that list one by one.
- 2:15:41So for that we need to create our list
- 2:15:44of tables as a metadata table in our SQL
- 2:15:47DB. So let's go ahead and do that. And
- 2:15:50here is a simple SQL script that I have
- 2:15:53created. So it will simply create a
- 2:15:56table named table list and then insert
- 2:15:58both of our tables as entry
- 2:16:02and now I'm back in our SQL DB part. So
- 2:16:05this is the query I will run and the
- 2:16:09query it has been run now. So from here
- 2:16:12we will uh read this list of tables. So
- 2:16:15currently we have only two but in real
- 2:16:17scenarios we can have hundreds and in
- 2:16:19real projects there will be more columns
- 2:16:21as well. uh but for now to keep it
- 2:16:24simple we will go step by step. So if I
- 2:16:27go back to ADF so now we need to read
- 2:16:30those list of tables and if you have
- 2:16:33guessed already so we can read that list
- 2:16:35using the lookup activity. So this is
- 2:16:38how this lookup activity it comes really
- 2:16:40handy. So here we can say read
- 2:16:44list of tables and if we go to settings
- 2:16:48then here we can select this SQL server
- 2:16:52and here the schema we can provide
- 2:16:54anything uh because this is uh just a
- 2:16:58placeholder here
- 2:17:00and then this time we don't need only
- 2:17:02one row. So I will untick this and we
- 2:17:05will run run a query here. So here we
- 2:17:09will say select or let's build that
- 2:17:12query in the SQL DB to avoid any error.
- 2:17:15So if I go to query section, we will say
- 2:17:18select
- 2:17:20table name from
- 2:17:23a metadata dot table list. So this is
- 2:17:27the query we need. If I run it, yes, we
- 2:17:30are getting the table name. So I will
- 2:17:32paste that query here.
- 2:17:36So now these will be the this will be
- 2:17:38the output that we will be passing to
- 2:17:41the for each. But first let's confirm if
- 2:17:44this is working fine. So let me quickly
- 2:17:46deactivate this and debug this
- 2:17:55and the execution it is complete now. So
- 2:17:58if we see its output here we can see
- 2:18:01count is two and value it has this list
- 2:18:04of tables. So if we see this value part
- 2:18:07this is what is a JSON array it is
- 2:18:09there. So this value we want to pass as
- 2:18:13the input to the for each and then
- 2:18:15inside the for each we want this table
- 2:18:18name. So let's see how it will work. So
- 2:18:21first of all let me activate it and then
- 2:18:24if I go to the uh settings here
- 2:18:29and in the item this is where we need to
- 2:18:31pass that array. So here we will use
- 2:18:33this dynamic expression and we will say
- 2:18:36add the rate activity the previous
- 2:18:39activity
- 2:18:41uh this is the activity
- 2:18:44uh dot output dot count or value we want
- 2:18:48the value. So that's it. This is how we
- 2:18:51will pass it. So now that list has been
- 2:18:54passed and if I go inside this for each.
- 2:18:58So now that list we saw. So like table
- 2:19:01name this table name this that is how it
- 2:19:03will be received here as item by item.
- 2:19:07So how we will pass it here is if I go
- 2:19:09here if I go to the table name and if I
- 2:19:13come here. So here we are seeing this
- 2:19:15like custom uh sorry current item. So if
- 2:19:19I click this it is saying add the rate
- 2:19:21item and now this add the rate item it
- 2:19:25contains let's say table name customers
- 2:19:27table name products whenever it is
- 2:19:29running only one at a time. So if I say
- 2:19:32at the rate table at the rate item dot
- 2:19:35table name so then it will get the table
- 2:19:38name. So that is how we will be able to
- 2:19:40pass that table name that we got the
- 2:19:43output from the uh our lookup activity.
- 2:19:48So I will press okay. So just to like uh
- 2:19:51understand it again end to end here we
- 2:19:54are reading list of tables. Then the
- 2:19:57value array that we are getting that
- 2:19:59value array we are passing as a input in
- 2:20:02the item here. And then if I go inside
- 2:20:05this then uh this is how like we are
- 2:20:08accessing that item and from that we are
- 2:20:10getting the table name and passing to
- 2:20:12this pipeline. So that is how it is
- 2:20:15working now. So let's give it a try.
- 2:20:17Let's see if it is working fine. So I
- 2:20:19will click on this debug now. And now we
- 2:20:23can see it is running. So it has read
- 2:20:25this list of tables. And for one table
- 2:20:28it has executed that pipeline. So if I
- 2:20:31check its input. So here we can see this
- 2:20:34is the parameter that has been passed
- 2:20:36table name customers and this second
- 2:20:39instance with the table name products it
- 2:20:41is currently running. So that is how it
- 2:20:44read those list of tables. Then this
- 2:20:46inside this for each it is running these
- 2:20:49two one by one. And now that also it is
- 2:20:52complete. So if I check out my email so
- 2:20:55I can see I have received email also for
- 2:20:58both customers and products as well. So
- 2:21:01this is how we were able to use this for
- 2:21:03each and as we saw here it was running
- 2:21:06both the tables one by one. And if you
- 2:21:09want to control that then there are some
- 2:21:11of the settings that you shall be aware.
- 2:21:13So if I click this for each and if you
- 2:21:16go to the settings. So one is this
- 2:21:19sequential. So if this option is checked
- 2:21:22now it will run it one by one. If it is
- 2:21:25unchecked then it will run like in
- 2:21:28parallel. That is one setting this batch
- 2:21:31count. So this is like how many how many
- 2:21:34instances we want to run in parallel. So
- 2:21:37let's say we have hundreds of tables and
- 2:21:39if you want to run five tables in
- 2:21:41parallel. So I will mention five here.
- 2:21:44So that parallelism also we can control.
- 2:21:47And then there is this third uh thing
- 2:21:49that is very important. So when we are
- 2:21:52using this in the debug mode. So if I
- 2:21:54use this debug uh then uh in debug it
- 2:21:58cannot run the things in parallel
- 2:22:00because this is expected to be working
- 2:22:02in the for the debugging only. And that
- 2:22:05is where we will see this warning also
- 2:22:07on this right side that in the debug
- 2:22:09mode things will run in sequential even
- 2:22:12if you have configured like parallelism
- 2:22:14here. And if you have not specified any
- 2:22:17batch count here by default it will it
- 2:22:20will decide what parallelism it can use.
- 2:22:22But in debug it will never be in
- 2:22:24parallel. That is the thing. So uh while
- 2:22:28working on the development we normally
- 2:22:30don't run so many things in parallel and
- 2:22:32everything for the very same reason. So
- 2:22:34in debug mode we test all these things.
- 2:22:37After that we publish all our changes or
- 2:22:40save them and then in this add trigger
- 2:22:44we have this trigger now option. So when
- 2:22:46we use this trigger now option then only
- 2:22:49like it will trigger everything in
- 2:22:51parallel. That is how it works. So
- 2:22:54currently if you see like this first one
- 2:22:56it is running now and only one was
- 2:22:58triggered and once this is completed
- 2:23:01then only the second one it is triggered
- 2:23:04and now it is complete. So let's say if
- 2:23:07you want to really use the parallelism
- 2:23:09then let me first publish all
- 2:23:12and then I will go to add trigger and
- 2:23:15then click on this trigger now and click
- 2:23:18okay. So now it will actually like
- 2:23:21trigger it kind of a production run. So
- 2:23:23if I click this view pipeline run then
- 2:23:25only it will take us to monitor screen
- 2:23:28and there only we will be able to
- 2:23:30monitor. In that editor section it will
- 2:23:32show the things only for the debug part
- 2:23:35but for actual trigger it will
- 2:23:37automatically take us to monitor. We can
- 2:23:39manually come here also and then do it
- 2:23:41and this monitor part we will cover in
- 2:23:43lot more detail later on but for now
- 2:23:46let's monitor only one pipeline. And
- 2:23:48this time if you see after reading the
- 2:23:50list of table it has triggered both in
- 2:23:53parallel. So that is how this trigger
- 2:23:55now works and and that is where this
- 2:23:58understanding is very important. So
- 2:23:59debug it will always run sequential
- 2:24:02whatever settings are there and in
- 2:24:05trigger now only it will run in parallel
- 2:24:07and that also only if we have node
- 2:24:10selected sequential and then we are
- 2:24:13using some batch then it will run things
- 2:24:15in parallel.
- 2:24:17And here let me uh tell you about some
- 2:24:19limitation also in ADF. So the the main
- 2:24:24limitation that we usually come across
- 2:24:26it is about nesting of the activities.
- 2:24:29So this is a for each activity. If I go
- 2:24:32inside this and try to add another for
- 2:24:35each then inside for each we cannot use
- 2:24:38for each. It will not allow. So this is
- 2:24:40what is not allowed. And also if we have
- 2:24:43a if condition let's say here we have a
- 2:24:45if condition. If I go inside this if
- 2:24:48condition and then here if I try to use
- 2:24:51a if condition then this also it will
- 2:24:54not allow. So this way nested if
- 2:24:57conditions are also not allowed and
- 2:24:59within if if I try to add a for each
- 2:25:02loop then that is also not allowed. So
- 2:25:04this kind of nested structure it is
- 2:25:06currently not allowed in ADF and uh this
- 2:25:09is also due to some reason and there is
- 2:25:12a workaround to it which which is uh
- 2:25:14like why which is why it is like really
- 2:25:16not a limitation. So when we use this
- 2:25:19then how do we come is how do we come
- 2:25:21over this limitation is by using like
- 2:25:24nested pipelines. So as you saw here we
- 2:25:27had this pipeline which had a if
- 2:25:30condition
- 2:25:31and similarly we can have one pipeline
- 2:25:34which has a let's say for loop and that
- 2:25:37pipeline we can call here. So that way
- 2:25:40nested pipelines we can create but
- 2:25:42within one pipeline we can cannot create
- 2:25:45nested if and nested for each
- 2:25:47activities. But due to this nesting
- 2:25:49pipeline we can use any nested level as
- 2:25:52we can and that help us creating like a
- 2:25:55logical segregation as well at the same
- 2:25:57time. So now let's go to our next
- 2:26:01activity that is going to be append
- 2:26:04variable. So what this will do is first
- 2:26:07let me tell you a use case. So let's say
- 2:26:10if we have hundreds of tables. So in
- 2:26:12current scenario what we what will
- 2:26:15happen is we will every day when our
- 2:26:17table is ingested we will get let's say
- 2:26:19100 emails but we don't want it. We want
- 2:26:22a simple summary which in the end in the
- 2:26:25email let's say it can have list of
- 2:26:27tables. Then how do we solve that is
- 2:26:30using a append kind of variable. So
- 2:26:33let's see how it will work. So first of
- 2:26:35all what we will do is we will go to our
- 2:26:38injection pipeline and these emails like
- 2:26:42let me disable these two pipeline
- 2:26:44because these email we don't want it. So
- 2:26:47I have deactivated these and now uh I
- 2:26:51will come out here in our for each uh
- 2:26:54pipeline and then if I go inside this
- 2:26:57for each so normally append like most of
- 2:27:00the time we use it inside a for each. So
- 2:27:04let's let me drag it and then let me go
- 2:27:07to settings and here we will create a
- 2:27:10new variable. So it is saying uh that
- 2:27:14append variable it only support adding
- 2:27:17to array type of variable
- 2:27:20and that is exactly what we will do. So
- 2:27:23let me minimize this left side. Now here
- 2:27:26we will create a new variable. We will
- 2:27:29name it as let's say table status and
- 2:27:33confirm. So now this variable has been
- 2:27:35created and here we need to provide like
- 2:27:39uh what do we want to append to this
- 2:27:42variable. So I will connect it here
- 2:27:44first and then here I will add a dynamic
- 2:27:47expression and what I will append is I
- 2:27:50will go to parameters
- 2:27:53and then uh okay here we don't have the
- 2:27:57table name I was looking for the table
- 2:27:59name but as we know like table name we
- 2:28:01can access it this way so let me copy
- 2:28:05the same thing and then in here I will
- 2:28:08provide the table name.
- 2:28:13Okay, looks like there is some UI issue.
- 2:28:16Let me cancel and do it again.
- 2:28:22Okay, and then what I also want is uh
- 2:28:24dash then status as success
- 2:28:29and because like we are combining two
- 2:28:32things so this expression I will have to
- 2:28:34put it in the curly brackets and then
- 2:28:36dash su success. So let me copy this
- 2:28:39also.
- 2:28:41So now we have appended here like table
- 2:28:44name dash su success. Similarly I will
- 2:28:47clone it and I will move this failure
- 2:28:50part and there we will what we will
- 2:28:53append is like table name dash failure.
- 2:28:57So or like failed whatever we want. So
- 2:29:00this is what we have appended here. And
- 2:29:03now uh this one like uh we can rename
- 2:29:06this activity also. Instead of append
- 2:29:08variable we will say store
- 2:29:12uh table
- 2:29:15success
- 2:29:20and then here we can say store table
- 2:29:23failure.
- 2:29:27So now what will happen is this append
- 2:29:30variable that we have created for every
- 2:29:32table it will have multiple entries like
- 2:29:34table success table failure like that
- 2:29:37and this append variable the good thing
- 2:29:40is that uh it will be accessible outside
- 2:29:43this for each and then this is where we
- 2:29:45can use it and send our consolidated
- 2:29:48email and how we will do that is because
- 2:29:51our current email pipeline it only take
- 2:29:54like uh it can only ingest or send the
- 2:29:57email for one table at a time. So let me
- 2:30:00quickly clone it
- 2:30:02and then we can say email demo then we
- 2:30:06can say multiple
- 2:30:09and it will be just that we need to uh
- 2:30:12here we need to like uh change some of
- 2:30:14the things. So recipient it will be the
- 2:30:17same status we don't need it anymore
- 2:30:20because in the same variable we will be
- 2:30:22getting status of everything table name
- 2:30:25also it will not be there and then I
- 2:30:28will add a new parameter table
- 2:30:31status and this is like the exact status
- 2:30:34we will send it there and now if we
- 2:30:37check out our activities so this is
- 2:30:40email subject so in the subject instead
- 2:30:42of saying like table and table status we
- 2:30:46will simply say injection completed
- 2:30:48that's it and then in the body part uh
- 2:30:52here we were say setting this for table
- 2:30:55wise so we will say like the injection
- 2:31:00process has been completed
- 2:31:05for all tables
- 2:31:08and below is the status
- 2:31:16like this and then we will provide the
- 2:31:19value of that table status variable.
- 2:31:25So that one let me go to variable
- 2:31:30uh
- 2:31:31uh sorry that is a parameter. So this is
- 2:31:33table status. This we will pass. And
- 2:31:37additionally we will pass one more thing
- 2:31:39here. Uh but before that let me double
- 2:31:42check if this one I created as a string.
- 2:31:45Yes, this one I created as a string.
- 2:31:48Uh but we want to create it as array
- 2:31:51because there also that append variable
- 2:31:53will be an array. So the same I am using
- 2:31:55here now. And if I go here so or sorry
- 2:32:00for the to the body part. So after this
- 2:32:03status what I want to also send in the
- 2:32:06email is a pipeline run ID. So if I go
- 2:32:09here pipeline run id equals to. So
- 2:32:14pipeline run ID. It is a unique ID given
- 2:32:16to every pipeline run. So this this
- 2:32:19helps like when we want to debug
- 2:32:21something if something goes wrong we go
- 2:32:23to the monitor section and we can find
- 2:32:25it using this pipeline run ID that we
- 2:32:28will understand more when we go to the
- 2:32:30monitoring part but this is a like just
- 2:32:33thing that when a pipeline runs it
- 2:32:35generates a unique ID that is called as
- 2:32:37pipeline run ID. So and how we can get
- 2:32:41its value is add the rate. Then we can
- 2:32:44say uh pipeline
- 2:32:50dot
- 2:32:52uh it is not coming. Let me try this
- 2:32:55way. Dot
- 2:32:57run id. Yes. So we this way like it will
- 2:33:01provide that run ID automatically here.
- 2:33:04So this is the body new body that we
- 2:33:06have prepared here. So subject and body
- 2:33:09we have modified now and now this will
- 2:33:11be the new email we will be sending. So
- 2:33:14if I go to our for each pipeline and
- 2:33:17this is where we will call it. So on
- 2:33:19completion of it. So we will be sending
- 2:33:22that email. So this is where we will
- 2:33:24call the execute pipeline now. And then
- 2:33:28this is what I will connect and then we
- 2:33:30will name it as uh like send
- 2:33:36consolidated email
- 2:33:40and if I go here in the settings. So
- 2:33:42here we are going to invoke this
- 2:33:44multiple email pipeline and this uh
- 2:33:48recipient part as we know like we have
- 2:33:50this global parameter. Now, so email
- 2:33:54recipient and for this table status we
- 2:33:57will be sending the append variable. So
- 2:33:59we have this table status. So this is
- 2:34:02what we will send. So now this is ready.
- 2:34:05Uh let's give it a try and let's confirm
- 2:34:08if it is working as expected. So I will
- 2:34:11simply do a debug for now.
- 2:34:15And this time it has failed. Uh let's
- 2:34:17check out the error. So if I click here,
- 2:34:20we will see the error here. It is saying
- 2:34:23hybrid delivery exception message cannot
- 2:34:25connect to SQL database. So I think it
- 2:34:28was some temporary problem. So let's
- 2:34:31give it another try.
- 2:34:33And yes, it was a temporary problem. So
- 2:34:36this time it has read the list of
- 2:34:38tables. Uh first one it is complete.
- 2:34:41It's checking for the second one. And
- 2:34:44here uh everything worked fine. But the
- 2:34:46email part it got failed.
- 2:34:49So here uh if you look at the error
- 2:34:53let's see it is saying like uh the
- 2:34:57request content is not valid and could
- 2:34:59not be deserialized.
- 2:35:02So something is wrong in terms of
- 2:35:04special character or something probably.
- 2:35:06So if you look at our append variable so
- 2:35:09these are working fine. Product success
- 2:35:11is appended and then customer success is
- 2:35:15also appended. So if I open this in the
- 2:35:18output, it will take us this is the run
- 2:35:21id it will take us inside the uh email
- 2:35:24pipeline and here also we can see until
- 2:35:27body everything was fine just the send
- 2:35:30email did not work here. So if we see
- 2:35:33here prepare email body the output part.
- 2:35:37So this is the content it is sending
- 2:35:41and then if we look at the uh failure
- 2:35:44part. So this is the failure that is
- 2:35:46happening. It is saying like there is
- 2:35:49this unexpected character encountered.
- 2:35:52So what it means is that something is
- 2:35:54wrong in terms of the special character
- 2:35:56or something. So if we look at it uh let
- 2:35:59me have a look at this thing. So
- 2:36:04we have this like uh recipient subject
- 2:36:07and body. So this part was already
- 2:36:10there. It was running without any
- 2:36:12problem. And this is like something we
- 2:36:15have added. And then this uh append
- 2:36:17variable is something specially we have
- 2:36:19added. And here we see that it is having
- 2:36:21like these double quotes. So because
- 2:36:24this JSON also is having these double
- 2:36:26quotes, this is also having these double
- 2:36:29quotes. So this may confuse it. It is a
- 2:36:31guess. But uh in projects I have faced
- 2:36:34this issue. So I know like this kind of
- 2:36:36issue might be happening. So these
- 2:36:38double quotes we will simply replace it
- 2:36:40with something different. uh so that
- 2:36:42like this problem will go away. Let's
- 2:36:44give it a try if it works. So what we
- 2:36:47will do here is that
- 2:36:50variable we are using that in the body
- 2:36:52part. So this is where it is coming.
- 2:36:57So okay this is subject and body. Before
- 2:36:59that uh this is where we are preparing
- 2:37:01email body and in here this is the uh
- 2:37:06status where we are getting those double
- 2:37:08quotes. So we will use the replace
- 2:37:10function here. So to replace that with
- 2:37:13blank or something. So what we will do
- 2:37:15is we will go to functions we will
- 2:37:19search for replace that can replace
- 2:37:21something with a new thing. So replace I
- 2:37:24have used it
- 2:37:27and then if you look at its settings it
- 2:37:29takes three parameters. The old string
- 2:37:32then what we want to replace and with
- 2:37:34what we want to replace. So here what we
- 2:37:37will do is replace then the entire
- 2:37:41string this is table status comma uh
- 2:37:44then here like what we want to replace
- 2:37:47comma with what thing we want to replace
- 2:37:50that will be our entire replace function
- 2:37:55and here it is saying that replace does
- 2:37:57not support array function like any
- 2:37:59parameter as array because this is a
- 2:38:02array so we will try to convert it to
- 2:38:04string first
- 2:38:08then we will probably be able to use it.
- 2:38:11So yes now it will convert this array to
- 2:38:13string then replace it and in here I
- 2:38:16will provide that double quotes. So now
- 2:38:19that double quote was there it will
- 2:38:21probably replace it with blank and then
- 2:38:24that problem may not be there. Let's
- 2:38:26give it a try. So I clicked okay and now
- 2:38:30let me go back there and let's run it
- 2:38:32one more time.
- 2:38:36So it has started running. Let's wait
- 2:38:39for it completion
- 2:38:42and here it comes. So everything ran
- 2:38:44successfully. So the issue was with that
- 2:38:47character only. So as we can see the
- 2:38:50consolidated email it has been sent now.
- 2:38:52So if I go to my email id here we can
- 2:38:55see our email and here we can notice
- 2:38:58that this customer success and product
- 2:39:01success has been logged in successfully.
- 2:39:03and pipeline run ID also has been
- 2:39:06provided properly here. And you might
- 2:39:09think like how come I was able to come
- 2:39:11up with that solution so quickly. So the
- 2:39:13only reason or the explanation is
- 2:39:16experience. I have faced this kind of
- 2:39:18problem so many times in the projects.
- 2:39:20So I know like how to fix it and then
- 2:39:23what can work or what cannot work. Once
- 2:39:25you try out different things with these
- 2:39:27expressions then you will also get that
- 2:39:30much of like ideas
- 2:39:33and here if you noticed like this run id
- 2:39:35also is being passed here. So if you see
- 2:39:38this uh
- 2:39:40uh 0 b and then ending with 2 f. So here
- 2:39:45it is 53 note like ending with 2f. So
- 2:39:49looks like I mistakenly passed maybe
- 2:39:51another run ID. So uh if you look at it
- 2:39:55so the run id we are using here in the
- 2:39:58body part. So we used here like uh a
- 2:40:02pipeline dot run id. So what happens is
- 2:40:05it is currently passing the run id of
- 2:40:07this email pipeline. Note this outer
- 2:40:10pipeline. So because every pipeline they
- 2:40:12have over their own run id. [snorts] So
- 2:40:15when whenever this send email pipeline
- 2:40:17is being called. So this is the run ID
- 2:40:20which belongs to this email pipeline.
- 2:40:22This ID is currently being sent there.
- 2:40:25But if you want to let's say send this
- 2:40:26pipeline run ID then simply we can add a
- 2:40:29new parameter here. And that also we can
- 2:40:32send as a parameter here. And here we
- 2:40:35can pass at the rate pipeline dot run
- 2:40:37ID. Then it will pass this run ID. That
- 2:40:40is how like it works. Send to end. And
- 2:40:43now coming to our next activity. So that
- 2:40:47is called as fail activity. So we have a
- 2:40:50activity whose name is also like fail.
- 2:40:54So you might think like why this
- 2:40:57activity exist and what could it do. So
- 2:40:59its functionality is very simple. If I
- 2:41:02go to the settings part, it only say
- 2:41:04like failure message and error code. So
- 2:41:08whenever this activity will be called,
- 2:41:10it will make the entire pipeline as
- 2:41:13failure. So it will mark its status as
- 2:41:16failed. And this is the error message it
- 2:41:18will show whatever we enter here and the
- 2:41:20error code we mention here that error
- 2:41:22code also it will show but why we may
- 2:41:25need is uh so in some scenarios it it
- 2:41:28could be helpful as well. So for example
- 2:41:31if I see current pipeline and this is on
- 2:41:35completion. So let's say if there was
- 2:41:37any uh execution that was failed any
- 2:41:40execution that was success but because
- 2:41:43this is on completion and this will
- 2:41:45always execute. So this entire pipeline
- 2:41:48status this will always say succeeded
- 2:41:51and when we will go to monitoring
- 2:41:53section we will see that this pipeline
- 2:41:54is exe executed successfully even though
- 2:41:57there was a failure. So to to set up
- 2:42:01let's say this kind of overall status as
- 2:42:03failure or in some other cases we might
- 2:42:05need to use it. So here if I do
- 2:42:09something like this like on this case of
- 2:42:11failure and here if I enter any error
- 2:42:14message. So here we can enter anything
- 2:42:17that this pipeline has failed due to
- 2:42:23previous activity and error code also we
- 2:42:27can give it any error code like 500 is a
- 2:42:29code for internal error we can use it.
- 2:42:32So now what will happen is whenever
- 2:42:34there will be a failure in any of these
- 2:42:37internal pipeline. So this will send the
- 2:42:40consolidated email but because we are
- 2:42:43using this fail activity so this overall
- 2:42:45status it will show as failure. So to uh
- 2:42:49include those kind of status like we can
- 2:42:51use uh this uh failure activity and
- 2:42:55another scenario could also be there
- 2:42:57that let's say we are using this inner
- 2:42:59uh pipeline and then in this inner
- 2:43:02pipeline like let's say here if we have
- 2:43:04something on completion or on failure
- 2:43:08then if currently we have disabled it
- 2:43:10but if we had some activity that was
- 2:43:12doing something let's say logging the
- 2:43:14failure in a log table or something Then
- 2:43:17also for this inner pipeline it will
- 2:43:20always say success even though if this
- 2:43:22copy data fails and that there is a
- 2:43:25insert statement that is successful. So
- 2:43:27then when it will come out here it will
- 2:43:30store that as success. So this is
- 2:43:32another problem. So in this case also
- 2:43:34let's say we can add a fail activity
- 2:43:37here on failure. So that whenever there
- 2:43:39is a failure it will fail this pipeline
- 2:43:42and the outer pipeline will know that
- 2:43:44that is a failed pipeline and then it
- 2:43:47will be able to store the failure
- 2:43:50properly. So these are the kind of
- 2:43:52scenarios where this fail activity can
- 2:43:54also be very useful.
- 2:43:57So these these are the all the main
- 2:44:00activities related to that I wanted to
- 2:44:02cover like the SQL server part. there
- 2:44:04are some more activities that are
- 2:44:06related to uh the injection process
- 2:44:09which is related to files and other
- 2:44:11things. So now we will go to that part.
- 2:44:15So now to play around with files first
- 2:44:17of all we need some files that we will
- 2:44:19be using. So we will go to our Azure
- 2:44:23data storage account. Here we will
- 2:44:25create two containers first. So let's
- 2:44:28say we have a source container
- 2:44:31and then we have another container named
- 2:44:34target.
- 2:44:38Okay. And uh in real projects like there
- 2:44:40this these could be two separate storage
- 2:44:42accounts as well. But here we have we
- 2:44:45will use the same one for demo purpose.
- 2:44:47And inside this source uh storage let's
- 2:44:50say uh we have a folder named let's say
- 2:44:55source folder
- 2:44:59and then here we can upload some files
- 2:45:02and to upload the file we have this
- 2:45:04upload button here. So to before
- 2:45:07uploading I will start by creating a
- 2:45:09quick file. So here what I will do is I
- 2:45:12will create a new text file. I will name
- 2:45:14it as A1 and I will open it. And in the
- 2:45:19content also I will mention only A1
- 2:45:21because this time like content part we
- 2:45:23will not worry much we will be mainly be
- 2:45:26focusing on how file migration and all
- 2:45:29these things work out. So this file I
- 2:45:31will upload there.
- 2:45:34So now now I will drag the file here and
- 2:45:37simply click on upload. So now let's say
- 2:45:39I want to uh let's say copy or move this
- 2:45:43file from the source container and then
- 2:45:45my target is target container. So from
- 2:45:48here we want to ingest the data there.
- 2:45:50In real project source will be a
- 2:45:52separate ADLS or separate file system
- 2:45:54target will be different. So that is how
- 2:45:56it will work. So for that let's go to
- 2:45:59our ADF again and let's create a new
- 2:46:03pipeline. So here let's me go ahead
- 2:46:06create a new pipeline. This time we will
- 2:46:09say PL file
- 2:46:13and in here we will start with the uh
- 2:46:16same copy data because we want to we
- 2:46:19will start by just copying the data and
- 2:46:22then we will name leave the name as
- 2:46:24default in the source. This time we will
- 2:46:28create a new data set and uh data set
- 2:46:31type it will be like ADLS only.
- 2:46:35So Azure data link storage gen 2 only
- 2:46:38and this time file format we will be
- 2:46:41using will be a binary. So whenever we
- 2:46:44want to copy the file as it is without
- 2:46:46making any single change without any
- 2:46:48format change nothing we use this binary
- 2:46:51format. This is very frequently used.
- 2:46:55And then the linked service we will use
- 2:46:57the same because the connection details
- 2:46:59are going to be the same only. And this
- 2:47:01one we can say like DS binary
- 2:47:06and these things we will leave as it is
- 2:47:08for now. So now this data set is there
- 2:47:12and then we get these options like file
- 2:47:15path whether it is there in the data set
- 2:47:18or we want to use some uh wild card here
- 2:47:22and file for file name and folder or
- 2:47:25let's say we want to pass any list of
- 2:47:28files by like through as a list here. So
- 2:47:31these are the different options we are
- 2:47:33getting. So what we will do here is
- 2:47:37uh we will first parameterize the
- 2:47:39container name because we are using if
- 2:47:41these options like wild card and all the
- 2:47:44container like it is we are not getting
- 2:47:46any option. So I will edit it. Click
- 2:47:49open and then the file system what we
- 2:47:52will do is we will create a parameter
- 2:47:54here. We will name it as container name
- 2:48:01and then uh
- 2:48:04I will go to connection and then here I
- 2:48:07will add that parameter container name.
- 2:48:10So this is parameterized. Now if I go
- 2:48:13back to the pipeline
- 2:48:16and now here we can provide the uh
- 2:48:18container name. So container name it was
- 2:48:21source for us and within this container
- 2:48:25we can provide the uh folder and file
- 2:48:28name here. So in our case the folder was
- 2:48:31source folder and file name it was A1
- 2:48:37txt.
- 2:48:38So these are the source details we have
- 2:48:40configured. If I go to sync here then
- 2:48:44sync also we can use the same data set
- 2:48:46because we are using the same ADLs
- 2:48:48account and everything. So the target
- 2:48:51container for us it is target and there
- 2:48:56and here we can see that for folder and
- 2:48:58file name it is not providing any
- 2:49:00option. So for that also let's add the
- 2:49:03parameter
- 2:49:05or let's do one thing like just to keep
- 2:49:07things simple let's create a new data
- 2:49:09set itself. So let's click new this gen
- 2:49:132 binary
- 2:49:16and then we can name it as like DS
- 2:49:20bin binary target
- 2:49:24and linked service we will keep it as
- 2:49:26the same. I will click okay
- 2:49:30and then if I click on this one open. So
- 2:49:34here we can parameterize it. So for file
- 2:49:38system because it is only for target. So
- 2:49:40here itself we can provide it. No need
- 2:49:42to parameterize it.
- 2:49:45And for directory and file name also let
- 2:49:47me add parameter. So here we can say
- 2:49:51that folder
- 2:49:54and then file name
- 2:49:58and these we will provide here like
- 2:50:01folder
- 2:50:03and then the file name.
- 2:50:07And that's it. With this the second data
- 2:50:09set is also ready now. And now in this
- 2:50:12target also we are getting these
- 2:50:14options. So we will say target folder.
- 2:50:18And then the file name we can keep it
- 2:50:20the same. Let's say A1. TXT.
- 2:50:25Now let me run this pipeline in the
- 2:50:27debug mode. It is saying binary copy
- 2:50:30does not support copying from folder to
- 2:50:34a file. So if I go here,
- 2:50:39so here if target side it is file here
- 2:50:43although like we are saying file but
- 2:50:45normally what happens is like using this
- 2:50:47name although we are giving it one name
- 2:50:50but it it supports like ingesting
- 2:50:53multiple files as well. So that is why
- 2:50:56it is not suggesting to add any file
- 2:50:58name in the target. So let's remove this
- 2:51:01file name option from here. I will open
- 2:51:03it. I will simply delete it. Parameters
- 2:51:06also let's remove this parameter. Now
- 2:51:09let's check it out. Yeah. So we will
- 2:51:11give only the folder name. Now let's
- 2:51:13give it a try.
- 2:51:16And now it is successful. So first let's
- 2:51:18confirm if our file is landed. So if I
- 2:51:21go to ADLs, this is our source only. So
- 2:51:24I go back there. This is our target
- 2:51:27container. Yes, it has created a folder
- 2:51:29named target folder and our file has
- 2:51:32been copied here. We can see if I go to
- 2:51:35edit the content is also there.
- 2:51:38And now let's see some other options. So
- 2:51:41let's say here if I go in the source uh
- 2:51:45we have these additional options as
- 2:51:47well. So one of them is like delete file
- 2:51:49file after completion. So if I tick it
- 2:51:52then it will delete the file from source
- 2:51:55after it has copied it to the target. So
- 2:51:57we can use this option or not depending
- 2:51:59on our usage. And let's say if we have
- 2:52:03two files let me create another file.
- 2:52:07So let me maybe copy the same file here.
- 2:52:10It name is let's say uh a2.xt.
- 2:52:16And then let's say we have another file
- 2:52:19named
- 2:52:21uh let me copy it again
- 2:52:24and its name is let's say b1
- 2:52:27txt. So first let me upload these files.
- 2:52:31So I go there. Uh we will go to this
- 2:52:34source again. In the source folder I
- 2:52:37will upload those two additional files.
- 2:52:40So I will click on this upload and then
- 2:52:43this A2 and B1 I will upload here.
- 2:52:47And now let's say my requirement is that
- 2:52:49if this folder has so many files and I
- 2:52:52want to ingest only the files whose name
- 2:52:55it starts with a. So to support those
- 2:52:59kind of things we have this wild wild
- 2:53:02card. If I remove this it is saying wild
- 2:53:04card file name. So what it means is it
- 2:53:07supports a regular expression kind of
- 2:53:10thing. So if I say a star txt. So this
- 2:53:15star it means like whatever it is here
- 2:53:17it will pick up and uh what so what it
- 2:53:21means is if any file name is starting
- 2:53:23with a ending with dot txt those files
- 2:53:27it will pick up and we have the other
- 2:53:29file like whose name starts with b. So
- 2:53:32that is something it will not pick up
- 2:53:34because we are saying a star here. So
- 2:53:36let's give it a try and this time sync
- 2:53:39we will say target folder uh two so so
- 2:53:43that we will know only those files are
- 2:53:45copied and now let me run it again using
- 2:53:47debug
- 2:53:49and now it is successful. So let's go to
- 2:53:52our target and let's confirm that piece.
- 2:53:56So target target folder 2 has been
- 2:53:59created and if you notice it has
- 2:54:01ingested only A1 and A2 B1 file it has
- 2:54:04note ingested because it was note
- 2:54:06matching with that wild card name. So
- 2:54:09that is how like these wild card names
- 2:54:11can be uh can come really handy and if
- 2:54:15you notice similarly for folder also it
- 2:54:17supports wild card folder name. So if we
- 2:54:20have let's say different folder with
- 2:54:22let's say file names dash some time
- 2:54:25stamp and all. So that folder name or
- 2:54:27some name we can give dash star those
- 2:54:30kind of things we can use to pick up all
- 2:54:32those folder also in one go. And then we
- 2:54:36have these other options as well like
- 2:54:38start time and end time all as well.
- 2:54:40This is a filtering based on the last
- 2:54:43modified time. So let's say if we our
- 2:54:46source it has thousands of files or
- 2:54:49millions of files but we want to load
- 2:54:51the file like which were modified since
- 2:54:54yesterday only those then we can use
- 2:54:57this parameter. So whatever start time
- 2:54:59we will specify here. So that time
- 2:55:02onwards whatever will be the modified
- 2:55:04time of the file only those it will pick
- 2:55:06up and load. Similarly, end time of this
- 2:55:09file modified also we can specify and we
- 2:55:12have these more settings also like
- 2:55:14maximum concurrent connections also we
- 2:55:16can set up if we have a source which is
- 2:55:18heavily loaded and then we can control
- 2:55:21it by but leaving it as a blank it it
- 2:55:25automatically decide and that is always
- 2:55:27the best case. So here we check the copy
- 2:55:31data related to files. Now let's check
- 2:55:33some more activities which are related
- 2:55:35to files. So one of them is about get
- 2:55:38metadata. So for here for now let me
- 2:55:41deactivate this copy data. So get
- 2:55:44metadata as the name suggest it can
- 2:55:46bring some metadata
- 2:55:49a metadata of a file or folder. So let's
- 2:55:53give it a try. So here if I go to
- 2:55:56settings and here first we need to
- 2:55:58select a data set. Let's say this is our
- 2:56:01data set. container name let's say it is
- 2:56:03source only
- 2:56:06and here you will notice that it is not
- 2:56:08providing any option for the folder or
- 2:56:11file name. So these activities these
- 2:56:14have like slightly different uh
- 2:56:17different structure but uh this
- 2:56:19flexibility also it helps depending on
- 2:56:22scenarios here and there. So for now
- 2:56:24what we will do is we will create a new
- 2:56:27data set for this purpose. So we will
- 2:56:30select this storage uh two again then
- 2:56:33binary
- 2:56:35and then again like we will name it as
- 2:56:37let's say DS
- 2:56:40binary we can say we are using for meta
- 2:56:44data
- 2:56:47and then we will select the same linked
- 2:56:50service
- 2:56:51and the file file system like maybe let
- 2:56:54me click okay then we will add the
- 2:56:56parameters here. So this uh container we
- 2:57:00will use as source only. Folder
- 2:57:02structure we can parameterize it. So let
- 2:57:05me add the parameter. I will add the
- 2:57:08name folder. For now we will
- 2:57:10parameterize it only for the folder. So
- 2:57:13I will come back here. Here I will add
- 2:57:15that parameter.
- 2:57:17Now I will go back to our pipeline
- 2:57:21and then we will select that data set.
- 2:57:24Now,
- 2:57:27so the folder we are referring here is
- 2:57:29source
- 2:57:31uh folder.
- 2:57:33So now for this folder, it will try to
- 2:57:35read the meta data. And then this is
- 2:57:38another mandatory field that is
- 2:57:40required. So if I click new, these are
- 2:57:43the options we are getting what exactly
- 2:57:46we can get the like metadata about. So
- 2:57:49child names means if it is a folder it
- 2:57:52will get its child name like what files
- 2:57:55it has and this one it will check if it
- 2:57:58is existing or not. So it will return
- 2:58:01true or false value. Item name means if
- 2:58:04it is a file or folder it will just give
- 2:58:07its name nothing else. Item type it will
- 2:58:10provide whether the provided entity it
- 2:58:12is a folder or file. last modified it
- 2:58:15will give the uh last modified time of
- 2:58:19that uh file whatever we are providing
- 2:58:21here. So that way depending on what we
- 2:58:24are selecting here then accordingly it
- 2:58:26will provide the output. So for the
- 2:58:29folder let's say we want to just get the
- 2:58:32uh list of child items. So I will only
- 2:58:35select it and let's give it a try first.
- 2:58:38So it is complete now. Now let's see the
- 2:58:41output section here. If you see in the
- 2:58:44child items we are getting the name like
- 2:58:46it is a name is this and this type is
- 2:58:49file. So it will also return like if it
- 2:58:52has any folder then with the type folder
- 2:58:55that also it will return. So this is how
- 2:58:58it works for a folder. Similarly let's
- 2:59:01give it a try for a file as well. So for
- 2:59:05the file part what we will do is file
- 2:59:07name also we will parameterize it. So
- 2:59:10let me add a folder name here. we will
- 2:59:12say file name
- 2:59:17and then uh I will add that file name as
- 2:59:20a parameter here
- 2:59:24and now if we go back to our pipeline
- 2:59:27so here we will provide the file name
- 2:59:30let's say a1 dot txt and then for file
- 2:59:35name there there are no child items so
- 2:59:37this will not work so for file name
- 2:59:39let's say we can fetch things Like let's
- 2:59:42say item name it will return the name
- 2:59:45anyway name we are providing still we
- 2:59:47can have a look we can add multiple
- 2:59:49items as well let's say if item type if
- 2:59:52you want to apply something there
- 2:59:56and last modified also we can get it
- 2:59:59similarly size also we can get it so all
- 3:00:02these things we can fetch so it was not
- 3:00:05selected let me select again yes so now
- 3:00:09let's give it a I
- 3:00:12so now this is also complete. So let's
- 3:00:15say here if I'm getting check the output
- 3:00:18section of it. So here we can see we are
- 3:00:21getting the name then the modified type
- 3:00:24and then size. S size if you see it is
- 3:00:26saying two. So the size it is always in
- 3:00:29bytes. So if we check here in our file
- 3:00:32if I bring my mouse so size it is saying
- 3:00:35two bytes. So it is returning the size
- 3:00:37as well.
- 3:00:39So that is how like these activities
- 3:00:41work related to files and folders. Now
- 3:00:45for the next activity, let's say we have
- 3:00:48some scenario where we want to ingest
- 3:00:51the files which are greater than some
- 3:00:53particular size only. So first maybe let
- 3:00:56me simply create such file. So let's say
- 3:00:59here let me copy paste this file and I
- 3:01:02will name it as let's say
- 3:01:05big one
- 3:01:07and then in the content also it is not
- 3:01:09that big we will create but let's say
- 3:01:11some randomly let me type some of the
- 3:01:13things so that it will be greater than
- 3:01:15two bytes. Now let me copy paste this
- 3:01:18and this one I will name it as big two
- 3:01:21and let's paste these two files and
- 3:01:24their size this is 38 bytes. So let's go
- 3:01:27to our ADLS. Let me go to the source and
- 3:01:32in here same folder. Let's upload those
- 3:01:34files now.
- 3:01:41So some file are existing. So we can
- 3:01:44override as well simply if it is already
- 3:01:47there. So now it has uploaded. Here we
- 3:01:49can see their size as well. So now based
- 3:01:52on the size we noticed that the copy
- 3:01:55data it does not allow anything based on
- 3:01:57size only wild card and those kind of
- 3:01:59things we can use. So to filter or to
- 3:02:03use things based on size. So first we
- 3:02:06have to use this kind of get metadata to
- 3:02:09get the list of items.
- 3:02:11So how we will build it step by step is
- 3:02:14in this get metadata we will be passing
- 3:02:18only the folder name and then we will be
- 3:02:21getting just the list of items that's
- 3:02:23it. So let me delete it. So I will keep
- 3:02:28only the child items first uh and we
- 3:02:30will name it as like uh list of files.
- 3:02:33We will say get list of files.
- 3:02:43And then uh let's give it a try first.
- 3:02:45It is saying no value provided for the
- 3:02:48file name.
- 3:02:50So we cannot leave it blank. So what we
- 3:02:53will simply do here is that we will give
- 3:02:55it star. So star means everything. So
- 3:02:58everything it will pick up.
- 3:03:01And now what we need to do is we have
- 3:03:03this child items. So for each of the
- 3:03:06file we need to check their size. So for
- 3:03:09that one if you have guessed already so
- 3:03:11we have a for each activity. So we will
- 3:03:14bring the for each activity connect it
- 3:03:17and in the for each we will pass all
- 3:03:19those child items. So if I go there and
- 3:03:23by default also it is showing these
- 3:03:25options because these are very
- 3:03:26frequently used. So child items we will
- 3:03:30pass there and if you see in the child
- 3:03:33item like inside child item we will be
- 3:03:35having name that will be the name of the
- 3:03:38file. So if I go inside here and now if
- 3:03:42I again use get metadata. So this time
- 3:03:45we will be able to fetch the data for
- 3:03:47the file. So now what we will do is
- 3:03:50again we will bring the same one
- 3:03:52metadata thing and then folder name it
- 3:03:55is same let's say source
- 3:03:58folder and file name we will pass
- 3:04:00dynamically this time so we will say
- 3:04:03current item dot name
- 3:04:08it was name or file name I forgot uh let
- 3:04:10me double check it so if I go outside if
- 3:04:14you check it out output yes it was name
- 3:04:17only so name will work. Now this get
- 3:04:20metadata it will return the uh it will
- 3:04:22like check the metadata for only that
- 3:04:25file and now here we can get the size of
- 3:04:28it if required. So here if you see size
- 3:04:31so now we will be getting size of each
- 3:04:33and every file. So let's give it a try
- 3:04:36first.
- 3:04:39So here we can see it has fetched the
- 3:04:41list of files and now it is running this
- 3:04:44get metadata.
- 3:04:47Now this is also complete and here we
- 3:04:50can see the size it is returning for
- 3:04:53each and every activity. Somewhere it is
- 3:04:55two somewhere it is 38. And now I hope
- 3:04:58you have guessed that based on the
- 3:05:01output of this get metadata here we can
- 3:05:03put a if else condition based on the
- 3:05:06size and if it is greater than let's say
- 3:05:08two then inside that only we can put the
- 3:05:11copy data activity. So this way we can
- 3:05:13implement the kind of scenarios where we
- 3:05:16can ingest only the files which has size
- 3:05:19greater than specific things or
- 3:05:21depending on their name or depending on
- 3:05:24their modified date something if you
- 3:05:26want to play around we can do everything
- 3:05:28here and then we have one uh another
- 3:05:31activity that is not specific to files
- 3:05:34but in general uh that is called as
- 3:05:36filter activity. So if I bring this
- 3:05:39filter and if I connect it here. So
- 3:05:42filter as the name suggests it is a
- 3:05:44filter. So we can pass an array to it.
- 3:05:48It will apply that filter on each and
- 3:05:50every item and whatever item like
- 3:05:54mention whatever item
- 3:05:57satisfy that condition it will keep. If
- 3:05:59it doesn't satisfy it will remove the
- 3:06:01item. So here if we go to settings of
- 3:06:05this filter we can [snorts] see it takes
- 3:06:07only two things. So items it is like the
- 3:06:10items. So we can pass the child items to
- 3:06:13it and condition is that what condition
- 3:06:15we want to apply on each of the child
- 3:06:17item. So here if I click on this here so
- 3:06:21it will take like similar to for each it
- 3:06:24can also take current item. Then we can
- 3:06:26say dot name. And now let's say we want
- 3:06:29to keep only the files whose name starts
- 3:06:32with a. So for that we have these
- 3:06:34functions also like starts with. So
- 3:06:37inside this a rate if I select this
- 3:06:40starts with and then here if I specify
- 3:06:43it like this is what we are checking.
- 3:06:46Then comma we want to check if it is
- 3:06:49starting with a. So it will check if the
- 3:06:51file name starts with a it will keep it
- 3:06:54otherwise it will not keep it. So let's
- 3:06:57give it a try. Let me deactivate this
- 3:07:00one. We are just checking the filter
- 3:07:01here.
- 3:07:04So it has run successfully now. So if we
- 3:07:08are checking this. So first it got the
- 3:07:10list of files. It fetched all the files.
- 3:07:13If you see this filter output. So it is
- 3:07:16saying total item count was five. Five.
- 3:07:19A filtered out filtered item count is
- 3:07:22two. So as we can see it is now
- 3:07:24returning only two items which satisfied
- 3:07:27that condition that was its name shall
- 3:07:29be starting with a. So that is how we
- 3:07:32can use this filter activity.
- 3:07:35And this filter activity it is not only
- 3:07:37related to file names in general. It can
- 3:07:40be applied to any array that we have.
- 3:07:43And now the next activity we are going
- 3:07:46to cover is wait. So as the name suggest
- 3:07:49it's very simple. It will just wait. So
- 3:07:52let's say if I remove this relationship
- 3:07:54and after getting the list of files due
- 3:07:56to some reason let's say I want to wait
- 3:07:58before doing the next activity. So if I
- 3:08:01go to settings it takes only one
- 3:08:04parameter wait time in seconds. So
- 3:08:07whatever seconds we supply here that
- 3:08:09much time it will wait then it will go
- 3:08:12to the next activity. So in real
- 3:08:14scenarios we may have to wait between
- 3:08:16API calls or if we are doing something
- 3:08:19and we need to wait for some time before
- 3:08:21doing something else then that is the
- 3:08:23kind of scenarios where we can simply
- 3:08:26wait. So it will do nothing but wait for
- 3:08:29that that many seconds and then it will
- 3:08:31proceed further.
- 3:08:34So here currently it is running.
- 3:08:38Let's wait for it.
- 3:08:41I'm just refreshing it continuously just
- 3:08:43to see that wait. Oh, now wait is
- 3:08:45running. So as you can see so it is
- 3:08:48waiting for 5 seconds and after that it
- 3:08:50it executed the filter activity. So it
- 3:08:53if you see it it took 6 seconds here one
- 3:08:56was to spin up the activity or
- 3:08:58something. So wait activity it is pretty
- 3:09:00simple. So if you want to add some delay
- 3:09:03we will add it. And then the next
- 3:09:06activity we are going to cover is
- 3:09:08specific to file and that is called as
- 3:09:11delete. So if I bring in bring this one.
- 3:09:17So as the name suggest like it can
- 3:09:20delete anything. So what we will do is
- 3:09:23maybe like let me delete it from here.
- 3:09:28And then let's say we want to delete the
- 3:09:30output of this filter activity. Whatever
- 3:09:32files we are getting as output those we
- 3:09:35want to delete. So what I will do is for
- 3:09:38that I will first bring in a for each
- 3:09:41loop quickly. So this is what I what is
- 3:09:44coming and here I will the list of items
- 3:09:48I want to pass is the list we are
- 3:09:50getting output of this filter. So if I
- 3:09:53go here so filters activity output
- 3:09:58uh this is what we will use and then
- 3:10:00output also like it is not direct output
- 3:10:03it is output a dot value. So this value
- 3:10:06we need to pass there. So here I will
- 3:10:10say like this output
- 3:10:12dot value.
- 3:10:15So this is what we will pass here. And
- 3:10:18now if I go inside this for each uh loop
- 3:10:22I go inside this is where we will use
- 3:10:25the delete activity.
- 3:10:27So to delete only those files. So here
- 3:10:31if I go to source I will select the data
- 3:10:34set
- 3:10:38and in this data set we need to pass a
- 3:10:40container name. So we will say source
- 3:10:44and then uh file and folder path we need
- 3:10:47to pass here.
- 3:10:49So I think here we had not parameterized
- 3:10:51files and folder uh it was probably this
- 3:10:54one. Yes, here the source container was
- 3:10:58source. We had kind of hardcoded and
- 3:11:01folder name. Let's say we want to use
- 3:11:03only the source folder. That's where our
- 3:11:06files are. And file name, this is what
- 3:11:08we will pass dynamically. So the current
- 3:11:11item that is item in the output of the
- 3:11:15uh filter activity and there we had the
- 3:11:18name of the file. So dot name. So if you
- 3:11:21pass it this way then it will pass the
- 3:11:24name of the file and then we have this
- 3:11:27something additional thing also that is
- 3:11:29mandatory.
- 3:11:31So it need to store some logs as well.
- 3:11:33So that is why this is mandatory. So I
- 3:11:35will select this storage account here.
- 3:11:38And then we need to provide a folder
- 3:11:40path. Uh so I will simply browse it
- 3:11:45and let's say the folder I we can give
- 3:11:47it as let's say target
- 3:11:50and then maybe target folder two. So
- 3:11:52this is the folder we can provide for
- 3:11:54logging there. It will probably store
- 3:11:56some kind of logs what is deleting and
- 3:11:58all or simply we can disable it as well
- 3:12:01if we don't want those logs. So I will
- 3:12:03disable it for now and then we will run
- 3:12:07our activity.
- 3:12:09So let me run this pipeline now. And as
- 3:12:13we can see here first it got the list of
- 3:12:15files which had all these five files and
- 3:12:19then it waited for 5 seconds. Then it
- 3:12:22applied the filter for the files
- 3:12:24starting with a then it ran the for loop
- 3:12:27and then it run ran the delete activity
- 3:12:29which probably deleted those files. So
- 3:12:32here we can see the file name as well
- 3:12:34which was deleted. So let's confirm it
- 3:12:37by going to the source if okay we are
- 3:12:40here. Let me refresh it. So here we can
- 3:12:43see the A1 and A2 files are gone now. So
- 3:12:46this is how we can use this delete
- 3:12:48activity.
- 3:12:49And for this delete activity also if you
- 3:12:52look at its parameters that are
- 3:12:54supported here. So we can see it also
- 3:12:57supports like wild card file name if you
- 3:13:00want to delete based on start txt or
- 3:13:02something the modify time also it
- 3:13:05supports recursive also if it support
- 3:13:07like if it it it has to go into
- 3:13:09subfolders and everything. So all those
- 3:13:12kind of things we can configure here.
- 3:13:15So these were all the main activities
- 3:13:18when it came to file related operations.
- 3:13:21Now let's cover some other activities.
- 3:13:23So if I go to our injection pipeline
- 3:13:26that we had built. So using this
- 3:13:29activity we were able to ingest the data
- 3:13:32into our storage account and this
- 3:13:34process we call it a source to landing
- 3:13:37part and after landing in standard data
- 3:13:39engineering practice we load the data
- 3:13:41from landing to bronze, bronze to
- 3:13:43silver, gold all those things we do. So
- 3:13:46after the data is landed then in almost
- 3:13:50every project at least the ones I have
- 3:13:52worked on there we transform the data
- 3:13:55using datab bricks. So for that we have
- 3:13:58a datab bricks related activities.
- 3:14:01So here if I expand this datab bricks
- 3:14:03section so we can see that uh there are
- 3:14:06four activities related to datab bricks.
- 3:14:08So this one can run a notebook, this one
- 3:14:11can run jar file, this one can run a
- 3:14:14python file and this one can trigger a
- 3:14:16datab bricks job. So the most common one
- 3:14:19it is running a datab bricks notebook.
- 3:14:22So how do we use it? We drag it over
- 3:14:24here. Then we trigger it like we uh plan
- 3:14:29it based on the previous activity on
- 3:14:32success. And in the notebook
- 3:14:34configuration if you see the options
- 3:14:36here.
- 3:14:38So in here like the first option
- 3:14:40obviously it is a linked service.
- 3:14:43So this it allows to connect to a datab
- 3:14:45bricks workspace. So what workspace will
- 3:14:47be connected and then like what cluster
- 3:14:51shall be used. So those are configured
- 3:14:53as part of this datab bricks linked
- 3:14:55service. So for example if I click on
- 3:14:58new here. So we can see all these things
- 3:15:00we can configure. Although I have not
- 3:15:03yet create created any datab bricks
- 3:15:04workspace but we can see like we can
- 3:15:07select a workspace we can select a
- 3:15:09cluster we can even uh create a new job
- 3:15:12cluster if you want all type of clusters
- 3:15:14we can use we can authenticate using
- 3:15:17access token managed identity
- 3:15:20and then more cluster related
- 3:15:22configurations we can connect here
- 3:15:25and then if we go to the settings
- 3:15:27section so this is where we can specify
- 3:15:30a path from the datab bricks workspace.
- 3:15:32We can also browse from here and select
- 3:15:34any notebook and using these BA base
- 3:15:38parameters. This is where we can pass
- 3:15:40any parameter to the notebook whatever
- 3:15:42we want to pass. So this is how like
- 3:15:45from here itself we can trigger a datab
- 3:15:47bricks notebook as well and pass the
- 3:15:49parameters and setting up a datab bricks
- 3:15:52workspace of our own it takes some time.
- 3:15:54So we will cover it in the next video
- 3:15:57where we will cover create end to end
- 3:15:59project there it will make more sense
- 3:16:02but from ADF perspective like uh the
- 3:16:04things we shall be knowing is that we
- 3:16:06can drag this activity we can select all
- 3:16:08these things and then call a notebook
- 3:16:10and pass these parameters
- 3:16:13and uh like apart from notebook there
- 3:16:16are other ways also to transform the
- 3:16:18data.
- 3:16:19So if we are working on Azure only
- 3:16:22environment where datab bricks is not
- 3:16:24there then in many project synapse also
- 3:16:26is used and similar to uh datab bricks
- 3:16:30notebook we have an option to synapse
- 3:16:32notebook as well. So this also works
- 3:16:34pretty much same way. So here like we
- 3:16:37need to create a synapse linked service
- 3:16:39and then select that and in the settings
- 3:16:42we need to select a notebook and then we
- 3:16:44can pass the uh parameters to it and
- 3:16:48pass the cluster related configuration.
- 3:16:50So these two are very similar depending
- 3:16:52on which service we are using and
- 3:16:55similar to datab bricks job in synapse
- 3:16:57also we have a a job we can define
- 3:16:59directly. So that also we can call it
- 3:17:02from here. And the other way of
- 3:17:05transforming the data is also here like
- 3:17:07inside this move and transform. So about
- 3:17:11moving the data we already saw how good
- 3:17:14copy data is and how frequently it is,
- 3:17:16how special it is. And then second thing
- 3:17:19it is about transforming the data. This
- 3:17:21is what is a data flow. So data flow it
- 3:17:24is also a good and integrated part of
- 3:17:26ADF. But the thing is it is not used
- 3:17:29that frequently to transform the data
- 3:17:32and the reason for that is like we have
- 3:17:34things like pi spark where people are
- 3:17:36familiar with writing pispark code. So
- 3:17:39the most common way to transform the
- 3:17:41data it is using datab bricks notebook
- 3:17:43or synapse notebooks but still for like
- 3:17:47knowledge purpose we shall be knowing
- 3:17:48what data flow is. So this activity it
- 3:17:51is just to call a data flow. Here we
- 3:17:54drag it and then we select the data flow
- 3:17:57that we want to call. To create a data
- 3:17:59flow from start we can create it from
- 3:18:02here or like we can create it directly
- 3:18:04from here as well because data flow it
- 3:18:07is a like kind of a separate small
- 3:18:09pipeline of itself. So if we create a
- 3:18:12data flow here. So here we can do
- 3:18:15different things like we can uh select a
- 3:18:17source first of all here and once we
- 3:18:21select the source of data let's say if
- 3:18:22we connect it to SQL server then if I
- 3:18:25select this plus icon then we can let's
- 3:18:28say do all these things which are there
- 3:18:29in SQL like select aggregate uh window
- 3:18:34rank cast so join so all these things
- 3:18:38like we can do through UI here so for
- 3:18:41example if you are doing some group by
- 3:18:43we can do it through UI
- 3:18:45and whatever other transformations we
- 3:18:47want to do let's say deriving some
- 3:18:49column and then let's say anything else
- 3:18:53let's say window operation we want to do
- 3:18:55join we want to do whatever we want to
- 3:18:57do we can do this through UI and finally
- 3:19:01after completing everything we create a
- 3:19:03destination sync so we select a source
- 3:19:06from where to read the data then we
- 3:19:08apply all these transformations using UI
- 3:19:12and then we write the data to a sync. So
- 3:19:14these data flows although I have used
- 3:19:16them in one of the project but I would
- 3:19:19say as a pi spark developer I don't
- 3:19:21prefer it and these are not frequently
- 3:19:23used and what these do is like when we
- 3:19:26run this data flow at the back end it
- 3:19:28will convert it to kind of pi spark code
- 3:19:31and run it on a spark cluster itself. So
- 3:19:34these are not frequently used. So we
- 3:19:36will not go into too much detail
- 3:19:38otherwise like uh this will be too long
- 3:19:40and this itself can be a course which is
- 3:19:43not that useful. So I will close it for
- 3:19:45now and if you go through so I so all
- 3:19:49the major activities we have covered now
- 3:19:52that are used let's go through this list
- 3:19:54now and have a glance like what things
- 3:19:56are pending so that you are aware like
- 3:19:59what other things are there in the ADF.
- 3:20:01So this we have covered synapse we
- 3:20:04already saw Azure data explorer this is
- 3:20:07a separate Azure service used for time
- 3:20:09series data. So if we are playing around
- 3:20:12any time series then we use this service
- 3:20:15and then using this connector like we
- 3:20:17can run anything on this ADX service and
- 3:20:20Azure function. This is also another
- 3:20:23service where we can run Python kind of
- 3:20:25custom code. So we we create a Azure
- 3:20:28function similar to logic apps. Logic
- 3:20:30apps also behind the scene they use
- 3:20:32Azure function. So like Azure function
- 3:20:35from here directly we can call them and
- 3:20:38there is a separate service named Azure
- 3:20:41batch service. So that is also we can
- 3:20:43use or this is not frequently used data
- 3:20:46bricks we already saw. General section
- 3:20:48this contains like the control flow kind
- 3:20:50of things. So uh append variable delete
- 3:20:54execute pipelines we covered SSIS this
- 3:20:57is a very specific like service related
- 3:20:59to SQL studio. So then we can use this
- 3:21:02package uh these things we already
- 3:21:05covered. If you want to call any stored
- 3:21:07procedure, we can use this
- 3:21:10uh script we covered this we covered and
- 3:21:13similar to lookup if we are doing some
- 3:21:15validation if this thing is true or not
- 3:21:17this condition is matching or not
- 3:21:20although we can use through other
- 3:21:21activities but this validation activity
- 3:21:24also we can use if required a web
- 3:21:26activity we used for logic apps web hook
- 3:21:29also we can use so let's say if we want
- 3:21:32to attach some web hook kind of thing to
- 3:21:35trigger something else then like this is
- 3:21:37used. I have also never used it. Uh then
- 3:21:40wait we already used HD insight like
- 3:21:43this is a separate Azure service. So
- 3:21:46they are like these these things we used
- 3:21:48to use in older days. So still these
- 3:21:50activities are supported. We don't use
- 3:21:52them nowadays anymore.
- 3:21:55And then iterations and condition. So
- 3:21:58filter for each if condition and switch
- 3:22:00we already create covered. Similarly
- 3:22:03there is until loop as well. So this is
- 3:22:05like similar to programming for each and
- 3:22:08while loop. So until that condition is
- 3:22:10true or like note false it will keep
- 3:22:13doing something. So that kind of logic
- 3:22:15is there. Then for machine learning
- 3:22:18related stuff we can use this activity.
- 3:22:21And then for power query which is like
- 3:22:23uh a service used by data analysts.
- 3:22:26Normally in data engineering I have
- 3:22:28never used it. So this is also
- 3:22:29available. So these are all the
- 3:22:32activities that are there in the Azure
- 3:22:34data factory. And now visiting back our
- 3:22:38main slide. So if you look at what we
- 3:22:41have covered so far. So data injection
- 3:22:43piece we have covered where we mainly
- 3:22:45used copy data activity. Orchestration
- 3:22:48we covered like how we can connect
- 3:22:49different activities create nested
- 3:22:52pipelines all the control flow we can
- 3:22:55use if else all those things. And then
- 3:22:57transformations we can do using like uh
- 3:23:01data bricks or synapse notebooks and
- 3:23:03also and also we can create data flows
- 3:23:06as well that piece also we saw in the
- 3:23:08related to transformations
- 3:23:10and next we will go to uh scheduling and
- 3:23:13triggers. So we will see like what are
- 3:23:15the different ways using which we can
- 3:23:18trigger our pipelines.
- 3:23:20So before moving further first let me uh
- 3:23:23discard these changes because here
- 3:23:25randomly we picked up some of the
- 3:23:27things. So let's say this was our main
- 3:23:30pipeline and for example if I want to
- 3:23:33trigger it on daily basis at some
- 3:23:35specific time or on weekly basis at some
- 3:23:37time. So that is the most common way of
- 3:23:40triggering these pipelines. So how do we
- 3:23:43do that? We go to this add trigger and
- 3:23:46then a trigger. Now it is kind of a
- 3:23:48manual trigger that we saw previously.
- 3:23:50It will trigger the pipeline and nothing
- 3:23:52else. And this is where we add any new
- 3:23:55trigger. So if we click on this new
- 3:23:57trigger. So if we have any trigger it
- 3:24:00will come up here. But normally we
- 3:24:02create this new. We can give it any
- 3:24:04name. So we normally give it like TZR.
- 3:24:07Then let's say I will name it injection
- 3:24:10for now. And then here are four types of
- 3:24:13triggers that we can use. So let's go
- 3:24:16over them one by one. So first one it is
- 3:24:19a schedule based trigger. So as the name
- 3:24:22suggest it runs on a particular
- 3:24:24schedule. So by default let's say it
- 3:24:26runs every 15 minutes here. But let's
- 3:24:29say if you want to run it on a daily
- 3:24:31basis. So here we have the options. We
- 3:24:34can run on few minutes, hours, days,
- 3:24:37weeks or months. So if I select day here
- 3:24:40which is most common. So if I say let's
- 3:24:43say every one. So every 1 day it will
- 3:24:46run 8 this time. So if I say 4:30.
- 3:24:52So every day now it will run 4:30 in the
- 3:24:55morning. And which morning it is that is
- 3:24:58decided by this. So currently it is
- 3:25:00showing my local time zone. But the most
- 3:25:03common way it is like doing it based on
- 3:25:05the UTC time stamp. That is the best way
- 3:25:08like to schedule the thing. So that we
- 3:25:10don't have to worry about any time zone.
- 3:25:13So if I search here universal
- 3:25:16so universal coordinated universal time
- 3:25:19this is the most common used but
- 3:25:22depending on our like own time zone like
- 3:25:24sometimes we schedule it both based on
- 3:25:26est time zone as well so we can select
- 3:25:29the time zone and then any time we want
- 3:25:31to run similarly if we want to run
- 3:25:33something every one week then we can
- 3:25:36select whether we want to run it only on
- 3:25:38Sunday only on Sunday and let's say
- 3:25:41Friday or on all the days. So these kind
- 3:25:44of things we can select here. Similarly,
- 3:25:47if we are running it based on monthly,
- 3:25:49we can say like if you want to run it
- 3:25:52only on let's say the uh 2nd and 30th
- 3:25:56date of every month at this time then we
- 3:26:00can schedule it. So within this schedule
- 3:26:03based trigger these are all the options
- 3:26:05that we have. So let's say this is
- 3:26:08injection and then let's say schedule.
- 3:26:11So like this we can select all these
- 3:26:13options and then this is like as soon as
- 3:26:16I save it this will be started. So what
- 3:26:19it means is that next time whenever
- 3:26:21there will be the uh date to uh the 2nd
- 3:26:24of the month then the pipeline will run
- 3:26:27at this time that is what it means. So
- 3:26:29if I click okay so this pipeline doesn't
- 3:26:32have any parameters but if there are any
- 3:26:34parameters so from here also we can pass
- 3:26:37those and that's it. The trigger is
- 3:26:40created now and now let's have a look at
- 3:26:43other triggers as well. So now I will
- 3:26:46create a new trigger and apart from
- 3:26:50schedule the second one is a tumbling
- 3:26:52window. So tumbling window is like
- 3:26:55something like as the name suggest it is
- 3:26:57a tumbling window. So if you want to run
- 3:27:00something let's say every 15 minutes,
- 3:27:02every 15 hours, every 15 months. So
- 3:27:06those kind of things we we use this
- 3:27:08tumbling window. But normally in real
- 3:27:11scenarios the schedule based is more
- 3:27:13preferred because normally we would want
- 3:27:15to run something on a particular time on
- 3:27:17a particular day. And this option if you
- 3:27:20noticed these were there in the schedule
- 3:27:23based also. So the these these three
- 3:27:25options are kind of overlapping and same
- 3:27:27only. And the next type of trigger that
- 3:27:30is the storage events which we will
- 3:27:32cover at the end. And then there is this
- 3:27:35custom events as well. So custom events
- 3:27:38it is like we if we want to trigger it
- 3:27:40based on our custom events which we have
- 3:27:43configured in a event grid service. So
- 3:27:46this is some very rare scenario where we
- 3:27:49create a separate event grid there we
- 3:27:51send some event and then trigger a
- 3:27:53pipeline. So there if you want to like
- 3:27:55build some kind of a uh a special flow
- 3:27:58then only it is used but practically I
- 3:28:00have also not used it but it is there
- 3:28:03and then then the other interesting one
- 3:28:06it is storage events. So this one also
- 3:28:08it is important from uh from file
- 3:28:12monitoring perspective. So what it does
- 3:28:14is it allows like uh checking any file
- 3:28:18on any storage account and whenever
- 3:28:20there is a new file added or deleted or
- 3:28:23those kind of events like added or
- 3:28:25deleted then we can trigger this uh
- 3:28:28event. So that is the kind of flow that
- 3:28:30is supported here. So this one we will
- 3:28:33give it a try because there is one more
- 3:28:35thing that is interesting here that we
- 3:28:37shall be aware. So what we will do is we
- 3:28:40we will uh create a quick use case here
- 3:28:42to understand that scenario and the use
- 3:28:45case is let's say we have our storage
- 3:28:48account and my target is that whenever
- 3:28:52somebody is placing a file in a
- 3:28:54particular location only that one file
- 3:28:56shall be picked up and then ingested
- 3:28:58into my system. If somebody is putting a
- 3:29:01new file then only that new file shall
- 3:29:03be picked up immediately and then put it
- 3:29:06into my target location. So that is the
- 3:29:08kind of uh use case that we want to
- 3:29:10build here. So let's see how we can
- 3:29:13quickly build it. So if I go to our ADF
- 3:29:16and there copy data we already like
- 3:29:19tried for this uh copying the file. So
- 3:29:22same one I will right click and copy
- 3:29:24from here and I will create a new
- 3:29:27pipeline
- 3:29:29and this pipeline we will name it as
- 3:29:31let's say PL file event and here I will
- 3:29:36write right right click it and paste it.
- 3:29:39Now let me minimize this left side and
- 3:29:43then let me activate it as well. So now
- 3:29:46if we go to this source part. So here we
- 3:29:49are saying any file with the name A will
- 3:29:52be paste sorry A will be picked up and
- 3:29:54then processed and sync is the same. So
- 3:29:58first like we will simple simply add a
- 3:30:00event based trigger and see how it is
- 3:30:03being processed. So for that we will go
- 3:30:06to this add trigger new then we will
- 3:30:09create a new trigger
- 3:30:11and this one we will name it as let's
- 3:30:14say tR
- 3:30:16event simple
- 3:30:19and [clears throat] then uh we will
- 3:30:21select storage events here we will
- 3:30:24select our store the subscription the
- 3:30:26storage account name then the container
- 3:30:29name so the source container we want to
- 3:30:32monitor and then here we need to specify
- 3:30:35like with what name the blob or the file
- 3:30:38are starting. So this shell also include
- 3:30:41the folder name as well. So it is like
- 3:30:44source
- 3:30:46of folder. Uh let me confirm the name
- 3:30:50here. So source is the container so that
- 3:30:54we have selected and then source folder
- 3:30:58is the name and we normally our files
- 3:31:01are ending with dot txt. So ending part
- 3:31:04although it is optional so but still we
- 3:31:07can provide it uh depending on the
- 3:31:09requirement we may or may not provide
- 3:31:11it. So now if I select this so now this
- 3:31:15trigger it will be triggered whenever
- 3:31:18there will be any new file created in
- 3:31:21this container in this folder and if the
- 3:31:23file name contains dot txt at the end
- 3:31:26and we can trigger it based on creation
- 3:31:28of the file or even deletion of the file
- 3:31:30using this one or both as well.
- 3:31:33And by default it will ignore empty blo
- 3:31:36which is a good thing. And then we it
- 3:31:39will start as soon as we uh create it.
- 3:31:41So that is a good thing. So let's go
- 3:31:44ahead and create it. And right now it is
- 3:31:47saying that these are the files that
- 3:31:49match based on this trigger. So this
- 3:31:52also gives us confidence that things we
- 3:31:54have configured those are like as
- 3:31:57expected. And if we continue there are
- 3:32:00no parameters. We will check parameters
- 3:32:02later on and then okay. So now let me
- 3:32:05publish it and let's publish this thing.
- 3:32:09And now it actually failed to activate
- 3:32:12the trigger. We are getting this error.
- 3:32:15So let's have a look at it.
- 3:32:18So this is the error we are getting. So
- 3:32:20looks like it has some dependency. I was
- 3:32:23also not aware about it because normally
- 3:32:25these are set up by the admin team. So
- 3:32:28let me pause the video and let me figure
- 3:32:30out what is going on. So after searching
- 3:32:33the error simply on Google, it is
- 3:32:35suggesting that we need to register
- 3:32:37event grid in our subscription. Then it
- 3:32:40works. So this is a service that it
- 3:32:42internally uses. So it can only use it
- 3:32:44if it is officially registered. This is
- 3:32:47I would say bit strange but uh that is
- 3:32:49how it is. So we need to go to the
- 3:32:51settings of our subscription and then
- 3:32:54search for this and register it. So if I
- 3:32:58go to Azure portal here then we need to
- 3:33:01go to our subscription. So here I can
- 3:33:03search for subscription or directly it
- 3:33:06will come. This is our subscription and
- 3:33:09if I go to settings inside these
- 3:33:11settings we have this resource
- 3:33:14providers. So if I go here
- 3:33:18and now these are lot of different
- 3:33:20providers which are there. So if I
- 3:33:22search by that name Microsoft.Event
- 3:33:25event grid. This is the one. So if I
- 3:33:28select it then I register it.
- 3:33:32So now it is registering that provider.
- 3:33:35So now it seems to be registered. Let me
- 3:33:38try to refresh here.
- 3:33:40Yes, now it is registered. So now let's
- 3:33:43go back to ADF and try it again.
- 3:33:45Hopefully this time it will work. So if
- 3:33:48I close it, if I go to triggers and then
- 3:33:52new or edit. So we will edit this
- 3:33:54trigger. So I will click the same
- 3:33:56trigger and if you see by default it is
- 3:33:59stopped because it was not able to start
- 3:34:01our trigger. So I will select started
- 3:34:04again and then click on continue
- 3:34:07then continue and click okay. Let's see
- 3:34:10if it is able to like uh start it this
- 3:34:13time.
- 3:34:14Uh I think it will try after publishing.
- 3:34:17So let's try to publish it.
- 3:34:21So now publish has been finished without
- 3:34:24any error and if we go here like so all
- 3:34:28the triggers that we create so there are
- 3:34:30two places we can check them out. One is
- 3:34:33whatever pipeline we created there we
- 3:34:35will get the same option and actually
- 3:34:38the same trigger can be used for
- 3:34:40multiple pipelines as well. So based on
- 3:34:42that same event if I want to trigger
- 3:34:44another pipeline there also I can use
- 3:34:47the same trigger if I want. So that way
- 3:34:50for each pipeline we can see what
- 3:34:52trigger is there and all the triggers
- 3:34:55that are being used. So those are
- 3:34:57available in the manage section as well.
- 3:34:59So if I go to manage then triggers. So
- 3:35:02here also we can see all the triggers
- 3:35:05and we can see both the triggers are in
- 3:35:07started state. So these will be running
- 3:35:09now. So now it shall be working fine.
- 3:35:12Let's give it a try. So we will go to
- 3:35:15our source folder here. Here we had
- 3:35:17deleted those files. starting with name
- 3:35:20A. So let's upload a file here and then
- 3:35:24let's upload this A1.txt again. Let's
- 3:35:27see if it is being triggered with this.
- 3:35:30So it is uploaded successfully. If we go
- 3:35:33to ADF now, so whatever trigger is
- 3:35:36happening, so any new trigger that will
- 3:35:39happen or pipeline trigger, we will be
- 3:35:41able to see them uh see them in this
- 3:35:44monitor section. So if I go to this
- 3:35:46monitor section. So here we can monitor
- 3:35:49the pipeline as well as the triggers. So
- 3:35:52if I go to the trigger, we can see the
- 3:35:54trigger has been triggered. And if I go
- 3:35:56to the pipeline, so we can see the file
- 3:35:59event pipeline has been triggered and it
- 3:36:02took only 20 seconds. The execution is
- 3:36:04also complete. So if I click this, we
- 3:36:07will be able to see that the copy data
- 3:36:09was successful. If I click this icon
- 3:36:12here, we can see one file was read
- 3:36:14successfully. So that file has been
- 3:36:16written to target now. So if I go here
- 3:36:20and if I go to the target
- 3:36:26uh the location, let me see which one
- 3:36:28was configured here. If we go to our
- 3:36:30pipeline sync, then it was target folder
- 3:36:342. So if I see target folder 2. So yes,
- 3:36:38we have this a A1 file which was
- 3:36:41replaced. It was already there. But we
- 3:36:43can see between these two its time is
- 3:36:46newer. So this file has been
- 3:36:48successfully ingested here. So as soon
- 3:36:50as I placed the file in source location,
- 3:36:53it was able to bring it here in the
- 3:36:55target. Now this kind of scenario can
- 3:36:58work fine. But it can be enhanced
- 3:37:01further or there can be more sub
- 3:37:02scenario for this specific one. So
- 3:37:05currently what will happen is let's say
- 3:37:08if I for example if I delete this just
- 3:37:11for information and then target folder 2
- 3:37:15is empty now. So now if I go to source
- 3:37:19folder and if I upload some file here
- 3:37:22let's say if I upload the file A2 this
- 3:37:25time.
- 3:37:28Uh okay, I need to click this upload.
- 3:37:32And if I upload it now, so now what will
- 3:37:36happen is it will trigger the pipeline.
- 3:37:39But the way the pipeline is currently
- 3:37:41configured is that it will pick up any
- 3:37:43file starting with that this name a txt.
- 3:37:47So what it will do is it will copy both
- 3:37:50the files A1 and A2. So if we go to the
- 3:37:53sync here, let's see like uh how fast it
- 3:37:56is. Is it already done or not? So if you
- 3:37:58go to this location, yes, both of them
- 3:38:01are copied. So now whenever a new file
- 3:38:03will be pasted there, all the files
- 3:38:05matching that name will be brought in
- 3:38:07here. So if we want to do something,
- 3:38:10let's say as soon as any file is being
- 3:38:13dropped only that file shall be picked
- 3:38:15up based on its name or something and
- 3:38:17then ingested here. So how we can do
- 3:38:20that? So for that one we have this very
- 3:38:22specific thing that we can pass any
- 3:38:25parameter from a trigger to the pipeline
- 3:38:29as well. So how we can do that is so
- 3:38:32here I can create a parameter name let's
- 3:38:35say file name and then this file name we
- 3:38:39will use here in this file name to pick
- 3:38:42up the file. So here I will pass it as a
- 3:38:45dynamic expression the pipeline
- 3:38:47parameter and this parameter name we
- 3:38:50will pass it through the trigger. So in
- 3:38:52this trigger if I go here new or edit
- 3:38:55and the same trigger if I go click on
- 3:38:58next then click on next. So here we can
- 3:39:02pass the trigger value. So let's say if
- 3:39:04I specify here A1.txt then it will
- 3:39:08always pick up the file named A1.txt.
- 3:39:11But let's say if I want to pass uh the
- 3:39:15file based on which it is being
- 3:39:17triggered directly that file name I want
- 3:39:20uh then for that one like we need to
- 3:39:22pass a expression here. So that
- 3:39:25expression let me if it is coming okay
- 3:39:28directly it is not coming so let me copy
- 3:39:31it from mother place.
- 3:39:34Yes. So I found it on Google because we
- 3:39:36can't remember everything. So this is
- 3:39:38how we will be able to pass it. So this
- 3:39:41way whatever file is triggering this
- 3:39:43trigger that file name will be passed in
- 3:39:46the value that will be passed to the
- 3:39:48pipeline and it will take pick up the
- 3:39:51file and then process it. So let me save
- 3:39:54it. Now it is done. Let me publish these
- 3:39:57changes.
- 3:39:59And while it is being published let's
- 3:40:01create a new uh file this time. So let's
- 3:40:05say I name it as let's say uh until C we
- 3:40:11had no so C1.txt.
- 3:40:14So this is the file we will upload. So
- 3:40:16it is publishing it.
- 3:40:19So now the publish is complete. So we
- 3:40:22will go to the container now our source
- 3:40:24container and let's upload the file
- 3:40:26again.
- 3:40:31So here let me brought in this file.
- 3:40:35Okay, it has been uploaded successfully.
- 3:40:38So let's go to our ADF. Let's go to
- 3:40:41monitor section and let me refresh it.
- 3:40:45So here we can see it has been
- 3:40:46triggered. If I open it
- 3:40:51and then copy data, it is running now
- 3:40:53and we can see the file name. It has
- 3:40:56successfully been passed the uh C1.txt.
- 3:41:00And if you want to specifically see only
- 3:41:02the parameter of pipeline that also we
- 3:41:05can see here in the parameter section.
- 3:41:08So we can see the file name parameter it
- 3:41:10has been passed C1.txt.
- 3:41:13So only this file it will be moved to
- 3:41:16the target location. So if we go to the
- 3:41:19target here, target folder 2, then our
- 3:41:23C1 file is here. So this way we can use
- 3:41:26the event based trigger and even pass
- 3:41:29any particular file as well. We can pass
- 3:41:31its name and only process that file
- 3:41:34based on that event. So this is the
- 3:41:36eventbased trigger how it works end to
- 3:41:38end.
- 3:41:40And now let's go back to this. So this
- 3:41:42covers all the different type of
- 3:41:44triggers which were there. And now let's
- 3:41:47understand something about failure
- 3:41:48handling. How do we handle? What kind of
- 3:41:51options are there? So some of the
- 3:41:54options those are part of the activity
- 3:41:56itself. So if we open any activity here
- 3:42:00we have these options.
- 3:42:03Uh looks like I cho chose the wrong
- 3:42:05activity. Uh so if I select let's say
- 3:42:08web activity here. So we have these
- 3:42:10options like timeout, retry and retry
- 3:42:13interval. These options are there in
- 3:42:15almost every activity. Set variable, it
- 3:42:18is just setting a variable inside ADF.
- 3:42:20So it will never fail. That is why these
- 3:42:22options are not there. [snorts] But
- 3:42:24let's say if you are doing any
- 3:42:26operation, let's say web activity. If I
- 3:42:28go to for each then lookup has it. If I
- 3:42:31go to execute pipeline, uh then execute
- 3:42:35pipeline is it is not having but
- 3:42:38activities inside that it will have
- 3:42:40those. So in these options uh here we
- 3:42:44can set the time out. So if we are using
- 3:42:47let's say copy data kind of activity
- 3:42:49then sometimes the one activity can also
- 3:42:52run for multiple days. We have these
- 3:42:54scenarios. So by default it will time
- 3:42:57out and fail after 12 hours. But if you
- 3:43:00want to increase it we can increase it
- 3:43:02to let's say 1 day, 12 hour, 3 day. So
- 3:43:05in real projects we have used until 7
- 3:43:07days as well. So this helps us after how
- 3:43:11much time it shall time out and then say
- 3:43:13it is timed out and failed and if some
- 3:43:16failure is happened do we want to do a
- 3:43:19automatic retry or not? By default it is
- 3:43:22no. So but in most of the lookup or all
- 3:43:26the main activities we set it up to two.
- 3:43:29So if it fail in one go then it will try
- 3:43:32two more times for the same activity to
- 3:43:34do something. So that is how this retry
- 3:43:37option works. So this will if it is
- 3:43:39successful it will only runs run once
- 3:43:42and move forward. If it fails it will
- 3:43:45try again fails try again. So if it is
- 3:43:47successful move forward that way it will
- 3:43:50try three times and when it is trying
- 3:43:52like three times or whatever times we
- 3:43:55configure here in between in between
- 3:43:57each uh retry how much gap we want to do
- 3:44:01and wait for that. So that wait time it
- 3:44:04is called as retry interval. So that is
- 3:44:06what we configure in terms of seconds
- 3:44:08here. So these are the like options we
- 3:44:11get within activity to retry and time
- 3:44:14out things which help us like handling
- 3:44:16any error. And the other way of handling
- 3:44:19error is also like using this on fail
- 3:44:21thing. So on failure of any pipeline,
- 3:44:24any activity whatever we want to do
- 3:44:26let's say if you want to make an entry
- 3:44:29to a metadata table we can use a script
- 3:44:31activity and use a insert into
- 3:44:33statement. And if you want to send an
- 3:44:35email like this then we can send an
- 3:44:38email. So this is another way of
- 3:44:40handling failures.
- 3:44:41And then one more option we get about
- 3:44:44failure is that we can trigger any
- 3:44:47failed pipeline directly as well. So
- 3:44:49here if something has been failed, we
- 3:44:52can rerun the entire pipeline as well.
- 3:44:55And if I open it, so in the rerun like
- 3:44:58we get these additional options as well.
- 3:45:00So we can select any particular activity
- 3:45:03and then we can rerun from that selected
- 3:45:05activity as well. Here it is only one
- 3:45:08activity but and if we have any failed
- 3:45:11activity if there are lot of activities
- 3:45:13which are there and it failed in between
- 3:45:15somewhere then we can rerun from only
- 3:45:18that failed activity onwards as well. So
- 3:45:21these are also some options we get
- 3:45:23related to failure.
- 3:45:25So these were all the main things I
- 3:45:27wanted to explain about uh failure
- 3:45:29handling in ADF. Now let's understand
- 3:45:32the monitoring part. So as you can see
- 3:45:35we are already in the monitoring
- 3:45:37section. So if I let's say go back here
- 3:45:40come to monitoring. So these are the
- 3:45:42different things we are getting here in
- 3:45:44the monitoring. So by default it will
- 3:45:46come to pipeline and most of the times
- 3:45:48we will monitoring we will be monitoring
- 3:45:50pipelines only and here we can monitor
- 3:45:53the pipeline runs and pipelines also
- 3:45:56they have two type. So these are the
- 3:45:59pipelines that were actually triggered
- 3:46:01using any trigger or like whenever we
- 3:46:04like go to trigger and click on run now
- 3:46:07then those pipelines will be coming
- 3:46:09here. So if you are manually clicking
- 3:46:11run now it will show manual trigger. If
- 3:46:14the pipeline it is being triggered
- 3:46:16through any other pipeline when we are
- 3:46:18running manually or any other trigger
- 3:46:20then that pipeline run ID will be
- 3:46:23showing here that it is triggered from
- 3:46:25another pipeline and if it is directly
- 3:46:28being triggered based on any trigger
- 3:46:30then the name of trigger will appear
- 3:46:32here. That way we can know how it is
- 3:46:34triggered and apart from this triggered
- 3:46:36pipeline whatever we ran in the debug
- 3:46:39mode. So those all those pipelines will
- 3:46:42appear in the debug section. So here
- 3:46:45also like we will see all these kind of
- 3:46:47options.
- 3:46:49And if I go back and if I go to the
- 3:46:52triggered section and here like I was
- 3:46:55saying so any parameters which are being
- 3:46:57passed we can monitor here. This is
- 3:47:00where we can see the run ID. We can
- 3:47:02search any of the pipeline by its name
- 3:47:05or run ID or different filters are
- 3:47:07there. we can find anything and if we
- 3:47:10open any pipeline let's say this is the
- 3:47:12one that was triggered here we can see
- 3:47:15everything very similar to what we saw
- 3:47:17in the debug section so all the inputs
- 3:47:20outputs everything we can monitor here
- 3:47:23so here we have multiple activities so
- 3:47:25let's say if I select this from this
- 3:47:27activity I can let's say rerun from here
- 3:47:30on so these are the options we are
- 3:47:32getting and by default it is showing all
- 3:47:35these things as list this is like very
- 3:47:37much useful. And then we have this Gant
- 3:47:40option as well. If I click it, it will
- 3:47:42show timeline wise like this. It ran
- 3:47:44from this time to this time. This
- 3:47:46activity ran from this time to this
- 3:47:48time. So this is also very useful. And
- 3:47:51these things we can export them as CSV
- 3:47:54as well. If we want to export into CSV,
- 3:47:57analyze in Excel, share with somebody.
- 3:47:59So the these things are also available
- 3:48:01directly here. So these are all the main
- 3:48:04options related to pipelines and
- 3:48:08triggers we will be able to see them
- 3:48:10here. So what trigger was triggered,
- 3:48:12what time it was triggered, it type and
- 3:48:16what pipeline was triggered. So these
- 3:48:18all options we can see here. And then
- 3:48:21this CDC it is something new. It is
- 3:48:23currently in preview. Integration
- 3:48:26runtimes. So by default this is coming
- 3:48:28auto resolve. But if we have set up our
- 3:48:31own integration runtime then that will
- 3:48:34also come here. We can monitor many
- 3:48:36things related to integration runtime
- 3:48:38like what is the CPU usage of the
- 3:48:40machine, how much memory is being used,
- 3:48:43how much parallelism we can use. So all
- 3:48:45those things we can check it out here
- 3:48:50and then uh this options this option is
- 3:48:53related to data flow. We don't use it
- 3:48:55normally. Here we can see some of the
- 3:48:58important metrics if we want. So this
- 3:49:01will take it to a separate Azure monitor
- 3:49:03screen. Normally we don't need it. Here
- 3:49:06we can set alerts as well based on any
- 3:49:08criteria if we want to send send alert.
- 3:49:12But this also we normally don't use it.
- 3:49:14We use our own email notification like
- 3:49:16we already used. So these are all the
- 3:49:19different options we are getting in the
- 3:49:21monitor section. And then this dashboard
- 3:49:23is also there which I just forgot. Here
- 3:49:26we can monitor the things at high level
- 3:49:29that in for example last 24 hours how
- 3:49:32many runs were there? How many were
- 3:49:34successful? How many were failed? How
- 3:49:37many triggers were triggered. So these
- 3:49:39kind of things we can monitor at a high
- 3:49:41level here. And next let's go to the
- 3:49:44manage section. Let's see what other
- 3:49:46options are there.
- 3:49:49So here in the linked services we can
- 3:49:51see the linked services that we had
- 3:49:53created. From here also we can create
- 3:49:56any new linked service if we want. And
- 3:49:59integration runtime we can create a new
- 3:50:01one and manage integration runtime. Here
- 3:50:04in monitor section we could only monitor
- 3:50:06what is happening. But this is where we
- 3:50:08create any uh like kind of integration
- 3:50:11runtime that we want to create. And here
- 3:50:14if you want to connect it with Microsoft
- 3:50:17perview or fabric then we use these
- 3:50:19options. Git integration also we can do
- 3:50:22this we will uh do it like after some
- 3:50:25time. I want to explain that that this
- 3:50:27it is very useful. This is for admin
- 3:50:30team ARM template and all. We normally
- 3:50:32don't use it. Triggers we already saw.
- 3:50:36Global parameters we saw here. We can
- 3:50:39create any parameter that we can use
- 3:50:42anywhere in our pipeline. Data flow
- 3:50:44libraries we can use but this I have not
- 3:50:47at least used. And then in the
- 3:50:49credential in the security section like
- 3:50:51we can set up credentials, keys, managed
- 3:50:55private endpoint. So these are infra
- 3:50:57related items. Normally we don't use
- 3:50:59this. So now there are two more
- 3:51:02important things I want to cover. One is
- 3:51:05like storing credentials in key volt and
- 3:51:07then using them here and second one how
- 3:51:10we can uh use GitHub configuration and
- 3:51:13how it helps with real development work.
- 3:51:16So first we will start with this git
- 3:51:18configuration.
- 3:51:20So here let's try to configure it first.
- 3:51:23So if I go to configure it is asking me
- 3:51:25to save my changes first. So here okay
- 3:51:29here we had we were trying to show some
- 3:51:31changes. Let me save it anyway.
- 3:51:35And now let's go to manage again. Let me
- 3:51:38click configure here. So here we can use
- 3:51:42two type of repositories either from
- 3:51:44Azure DevOps or directly from GitHub.
- 3:51:48And here setting up a new Azure DevOps
- 3:51:50repo is a bit longer process. So we will
- 3:51:54go with GitHub which is easy to set up
- 3:51:56and both work the exact same way. So for
- 3:51:59that first of all we will need a need a
- 3:52:01repository in GitHub. So here is my
- 3:52:04GitHub account and here we will create a
- 3:52:07new repository. So here we can create
- 3:52:09this free account on github.com using
- 3:52:12any email id and then we need to come to
- 3:52:15this repository section and create a new
- 3:52:17repository. Here we can give it any
- 3:52:20name. So I will give it let's say adf
- 3:52:23demo and all the options we will leave
- 3:52:26them as default and simply create
- 3:52:28repository.
- 3:52:30And now if you look at this top URL so
- 3:52:33we have this github.com
- 3:52:35then this datab 14 this is called as
- 3:52:38owner of the github and then adf demo.
- 3:52:42This is the name of the repository. So I
- 3:52:44will copy this name of the owner. This
- 3:52:47is what we will provide first in our ADF
- 3:52:50side. So now I will go to ADF. I will
- 3:52:53select the GitHub option and this is
- 3:52:55where we need to provide the name of the
- 3:52:57owner. And then once we click on
- 3:53:00continue it will ask for authorization.
- 3:53:03So I will authorize it
- 3:53:07and once authorization is complete uh
- 3:53:09then uh here we will be able to see the
- 3:53:12list of repositories that are available
- 3:53:14for that particular account. So this is
- 3:53:16the repo that we had created
- 3:53:20and then uh there are no branches
- 3:53:22currently because we just created a
- 3:53:24fresh uh repo there.
- 3:53:27So here we will create a new branch.
- 3:53:29Create new. And the most common name
- 3:53:32used for a new branch is main branch.
- 3:53:34That is our final branch.
- 3:53:36And here again it is showing some error
- 3:53:39that it failed to create a new branch.
- 3:53:41Make sure the repository is initialized
- 3:53:44in GitHub and it is saying like the
- 3:53:46GitHub repository is empty. So maybe
- 3:53:49like a blank file or readme file we need
- 3:53:51to create first before using it here. So
- 3:53:54let's go to our repo again and here it
- 3:53:58is it is saying like get started by
- 3:54:00creating a new file. So I will click on
- 3:54:02this create new file
- 3:54:05and this new file we will name it as
- 3:54:08readme.md.
- 3:54:11This is the most common like a generic
- 3:54:13file that is created and we can say this
- 3:54:16repo is created for demo
- 3:54:20purpose and then we will commit the
- 3:54:23changes. So here we need to give any
- 3:54:26okay any message it is filled
- 3:54:29automatically. So we will simply commit
- 3:54:31it now.
- 3:54:34So with that like now our branch main
- 3:54:36branch is ready and it has a single file
- 3:54:39in the repo now. So now we can say it is
- 3:54:41initialized. So we will go back to ADF
- 3:54:44now and try again. So here I will click
- 3:54:47back. And now I will click continue
- 3:54:50again. I will select the repo again. And
- 3:54:53here in the collaboration branch now
- 3:54:55main is coming automatically and this
- 3:54:58will be the branch where from which we
- 3:55:00will create feature branches then merge
- 3:55:02our code. Then we have a publish branch.
- 3:55:05So what is a publish branch is that so
- 3:55:08earlier whenever we were clicking this
- 3:55:10publish all button. So all the artifacts
- 3:55:13were published somewhere internal to ADF
- 3:55:16and when we use this publish branch. So
- 3:55:18when we will click that publish button
- 3:55:20all the all the artifacts will be
- 3:55:23published to this branch. This will be
- 3:55:25like kind of a final branch which we
- 3:55:27cannot edit from our side but whatever
- 3:55:30we will finally publish that will go
- 3:55:32there. And root folder means like where
- 3:55:35we want to create our files in the root
- 3:55:37directory or inside some folder. Here we
- 3:55:40will create in the root directory only.
- 3:55:42And this option by default it is ticked.
- 3:55:45So I will untick it. It is like custom
- 3:55:47comment or do we want to provide custom
- 3:55:50comment every time we save some item. So
- 3:55:52we don't want it. We want to save it
- 3:55:54automatically. And import existing
- 3:55:57resources. So this means whatever
- 3:55:59resources we have created as part of
- 3:56:01this demo. So those resources shall be
- 3:56:04copied to our main branch or not. So
- 3:56:06yes, we want that. And then
- 3:56:10here we want to select like in which
- 3:56:12branch the resources shall be copied. So
- 3:56:15we want to we want them to be copied in
- 3:56:18main branch only. And now these changes
- 3:56:20has been selected. So we will apply.
- 3:56:25And it took a few seconds. And now we
- 3:56:27got this option that repo is connected.
- 3:56:30So now our repository has been connected
- 3:56:33and if you look at it here like this uh
- 3:56:35option has changed now. So earlier we
- 3:56:38were not getting all these options. So
- 3:56:40now by default we are in the main branch
- 3:56:43and as expected publish branch we cannot
- 3:56:45select anything and then we can create a
- 3:56:48new branch we can create a pull request
- 3:56:50or we can switch to live mode. So how do
- 3:56:53we work on the development is first uh
- 3:56:56let's go to this author section and here
- 3:56:59like first of all we will publish
- 3:57:01everything. So now let's try to publish
- 3:57:04uh if it is working fine or not and
- 3:57:08currently it is not showing any pending
- 3:57:10changes. So I will simply click okay and
- 3:57:13with that like publish is completed. So
- 3:57:15that means like there are no issues uh
- 3:57:17with this branch. Everything is
- 3:57:19published and all the pipelines the data
- 3:57:22sets we created those are also part of
- 3:57:24our uh repo. Now now what we will do is
- 3:57:28let's say I want I want as a developer I
- 3:57:30want to work on any new feature
- 3:57:32development. So what I will do is I will
- 3:57:35create a feature branch from this new
- 3:57:37branch and then I will say let's say
- 3:57:40feature one
- 3:57:42I want to create it based on the main
- 3:57:44branch
- 3:57:47and now we are in the feature branch
- 3:57:49here and in this feature branch like I
- 3:57:51can work on any development any changes
- 3:57:54that I want. So for example, let's say
- 3:57:56if I come here and I'm putting some
- 3:57:59description let's say reading customer
- 3:58:02table. So this is some development I
- 3:58:05have done. Now uh if you notice earlier
- 3:58:08we were getting only publish option. We
- 3:58:10were not getting this save all or save
- 3:58:13options. So when we click this save so
- 3:58:16what will happen is this changes that we
- 3:58:18have done these will be saved in the
- 3:58:20repository GitHub repository in this
- 3:58:23branch. So that is what this save means.
- 3:58:26And if we have made multiple changes
- 3:58:28then we can use this save all option as
- 3:58:30well. So that like uh it will save all
- 3:58:33those changes in one go. And here you
- 3:58:36can see that the publish option it is
- 3:58:39disabled here. And why is that? Because
- 3:58:42uh it is a functionality in ADF that
- 3:58:44publish option we can use only from the
- 3:58:47main branch not from any feature branch.
- 3:58:50So that is how the workflow works. So
- 3:58:52here let's say we have done some
- 3:58:54development and then uh now I want to
- 3:58:58take my changes to the uh main branch.
- 3:59:01So how we will do is we will create a
- 3:59:04pull request. So I will click on create
- 3:59:06pull request
- 3:59:10and now here it it will allow us to
- 3:59:12create a pull request. This is the only
- 3:59:14change we put a description to one of
- 3:59:17the activity. If there are multiple
- 3:59:18changes those will also appear here. And
- 3:59:21then we will say create pull request.
- 3:59:25And we can give any description here if
- 3:59:27required. I will simply create it.
- 3:59:33And now the pull request has been
- 3:59:35completed. And now what will happen is
- 3:59:38any senior person like a architect like
- 3:59:40me or somebody else they will review all
- 3:59:42these changes and then they will
- 3:59:45complete the pull request by saying like
- 3:59:47uh confirming this merge operation.
- 3:59:51So now once it is merged now our changes
- 3:59:54has been taken to the main branch. So
- 3:59:57now if I go back to ADF and if I go to
- 4:00:00the main branch there also I will be
- 4:00:03able to see the change that we had made.
- 4:00:05So if I close it, if I open it again, I
- 4:00:09can see the description has has been
- 4:00:11coming to this main branch also. And
- 4:00:14from this main branch now we are getting
- 4:00:16this publish option. So whatever we want
- 4:00:19to publish then we need to publish from
- 4:00:21main branch only. So that is how the
- 4:00:24flow works. So from here we will be able
- 4:00:26to publish anything that we want. And
- 4:00:30this time it is saying that only one
- 4:00:32pipeline was edited. So once we click
- 4:00:35okay the changes will be published.
- 4:00:38So it is saying publishing completed and
- 4:00:41if you want to know like what want to
- 4:00:43know what exactly has been published or
- 4:00:45how does the published ADF looks like
- 4:00:48then we can from here we can switch to
- 4:00:51live mode. So if I switch to live mode
- 4:00:53we will not be seeing any repo anything
- 4:00:56uh and we will kind of return to the
- 4:00:58that original UI that we were previously
- 4:01:01working on. So this is what has been
- 4:01:03published. From here also we can see any
- 4:01:05published version as well. But the thing
- 4:01:08is that from this live mode or published
- 4:01:10version we won't be able to make any
- 4:01:12change and save it. That will not be
- 4:01:14allowed once we have enabled GitHub
- 4:01:17because all the changes are expected to
- 4:01:19come from a feature branch to main
- 4:01:21branch and then those shall be published
- 4:01:23from there. Then only those will be
- 4:01:25coming to this live mode. That is how
- 4:01:27the flow works. And then there is this
- 4:01:30one more thing that we shall be aware.
- 4:01:33So let's say if I switch to GitHub again
- 4:01:35and let's say I'm in the main branch. So
- 4:01:37we can make any changes to a feature
- 4:01:39branch or in the main branch also
- 4:01:41directly that is possible. So uh let's
- 4:01:45say if I go to this manage section and
- 4:01:47if I go to triggers now let's say I
- 4:01:50don't want to use this trigger anymore
- 4:01:53and I have stopped this trigger for from
- 4:01:56here. So I press this uh stop icon and
- 4:02:00it will be stopped now. Yes, now it is
- 4:02:03stopped. So the thing is like although
- 4:02:06here it is showing that it is stopped
- 4:02:09but the it will it is currently not
- 4:02:11effective. And by effective what I mean
- 4:02:14that in actual ADF this trigger will
- 4:02:16still be working. And why is that?
- 4:02:19Because these changes are currently in
- 4:02:21the main branch only. These has not been
- 4:02:24published yet. So, so the final final
- 4:02:27effect will come into picture only after
- 4:02:30we publish everything. So, even if you
- 4:02:32have saved something in the main branch
- 4:02:34is it does not mean that is final and is
- 4:02:37effective also it will become effective
- 4:02:40once you publish everything.
- 4:02:42So once you publish then this trigger
- 4:02:46stop thing it will go to the live mode
- 4:02:48and then it will be effective. So that
- 4:02:50is how like this entire process works in
- 4:02:52ADF. And now if we go to the uh
- 4:02:56repository side and there if you want to
- 4:02:59have a look like how does it look like
- 4:03:01now. So I will refresh it from this top
- 4:03:05and now we can see in our repository
- 4:03:08automatically this folder has been
- 4:03:09created. So we have this publish config
- 4:03:12and all in the pipeline like we will be
- 4:03:15able to see all the pipelines that we
- 4:03:18had created. We have our linked services
- 4:03:21here in the factory. uh we will have
- 4:03:24some factory related configurations
- 4:03:26here. We will have data sets and if you
- 4:03:28notice like each and everything whatever
- 4:03:31we had created those has been stored as
- 4:03:33a JSON here. So if I open any of the
- 4:03:37file let's say this demo JSON here. So
- 4:03:40this is how like the actually the
- 4:03:42underlying configurations of the
- 4:03:44pipeline is stored and everything is
- 4:03:46stored as a JSON in ADF and this JSON it
- 4:03:49can also come really handy in some of
- 4:03:51the scenarios. Let me explain one
- 4:03:53example. So let's say if I'm working on
- 4:03:56Azure data factory side and here I have
- 4:03:59created a let's say big uh pipeline
- 4:04:02maybe I will use email one.
- 4:04:06So here let's say I have created this
- 4:04:08pipeline and then uh here let's say we
- 4:04:11had this parameter email recipient and
- 4:04:14let's say I want to rename this
- 4:04:16parameter to recipients. I want to put a
- 4:04:18s in that. So the normal way would be
- 4:04:21that here I put s then I go to the
- 4:04:24entire pipeline check all the places
- 4:04:26where I'm using this and then put as s
- 4:04:29there. So that will be a manual effort.
- 4:04:32So the better way to do that is so on
- 4:04:34this right side along with these
- 4:04:36properties we have this view as JSON. So
- 4:04:39if I click it, it will show that entire
- 4:04:42JSON that was stored in GitHub. So here
- 4:04:45also we can see that. So if I copy to
- 4:04:48clipboard from here, if I go to a
- 4:04:51notepad and then I paste it there.
- 4:04:56So here if I paste that entire JSON. Now
- 4:04:59what I will do is so this JSON also it
- 4:05:02has really good structure. Here this is
- 4:05:04the name of the pipeline. These are some
- 4:05:06properties. These are all the activities
- 4:05:09which are there. Then these are the
- 4:05:11parameters. These are the variables. So
- 4:05:14once we go through this we will be able
- 4:05:16to understand each and everything. So
- 4:05:18this is the parameter that was created.
- 4:05:20So if I copy this name and I select
- 4:05:22everything I press Ctrl H and let's say
- 4:05:26this same thing I want to replace and
- 4:05:28put a S there. So here if I do replace
- 4:05:31all. So here we can see it was there in
- 4:05:34three places and everywhere it has
- 4:05:36replaced now. So this way manually we
- 4:05:39don't need to replace anything. Now I
- 4:05:42will copy this new JSON body again. I
- 4:05:45will come back to ADF. This old thing I
- 4:05:48will remove and the new one I will paste
- 4:05:50here. And then click okay. And just like
- 4:05:53that I have put this S everywhere. So
- 4:05:56now like this is one place and if it was
- 4:05:59being used in any other place let's say
- 4:06:01in the body part also it was being used.
- 4:06:04So here also you can see this s appeared
- 4:06:06automatically.
- 4:06:08So that is how like uh we can replace
- 4:06:12anything that we want automatically
- 4:06:14using this JSON thing. So in real
- 4:06:17projects we have like so big pipelines
- 4:06:19and changing something can become
- 4:06:21tedious manually checking what all
- 4:06:23places it is being used and all that is
- 4:06:25where this trick can come really handy.
- 4:06:28So these were all the details I wanted
- 4:06:30to share about GitHub and CI/CD
- 4:06:33configuration in ADF. Now let me discard
- 4:06:36these changes. Now moving on to our last
- 4:06:39piece that is about uh configuring
- 4:06:41secrets in key volt. So let's check that
- 4:06:44out. So what we will do is first of all
- 4:06:47maybe let me do a save all so that
- 4:06:49everything is saved here.
- 4:06:52And now uh to use key volt obviously we
- 4:06:56will need a key volt. So for that I will
- 4:06:58go to Azure portal and then we will need
- 4:07:01to create a key volt service there. So
- 4:07:04if I search for key volt it will show
- 4:07:06us. So here I will click create
- 4:07:11and uh these things we will use the same
- 4:07:15key volt name we can say it let's say uh
- 4:07:18demo only.
- 4:07:22So this name is already taken. So let's
- 4:07:24say demo
- 4:07:27databy. Let's see if it allows. No it
- 4:07:30doesn't allow.
- 4:07:33Okay. Yes, this will be allowed. Region,
- 4:07:36we shall select whatever is nearest to
- 4:07:38us. And here all these other things we
- 4:07:41will leave them as default. So we will
- 4:07:43click simply review and create. And now
- 4:07:46it has reviewed. It is ready to create
- 4:07:48it. And now it has been created. So
- 4:07:52let's go to the resource.
- 4:07:55And now our key volt it is ready. So now
- 4:07:58to use this key volt we will need to
- 4:08:01create a linked service for key volt
- 4:08:03also in Azure data factory like we need
- 4:08:06to do for everything else. So if we go
- 4:08:08to linked service here click on new and
- 4:08:12if I search for key volt so it will show
- 4:08:15it if I continue. So from here we can
- 4:08:19select it. We can select it. Then here
- 4:08:22we have this authentication mechanisms.
- 4:08:25So again we will use this system
- 4:08:27assigned managed identity and this is
- 4:08:30the ID that is there and for this ID we
- 4:08:32need to provide access to the key volt
- 4:08:35so that it can use everything from
- 4:08:37there. So I will go to the key volt
- 4:08:39again I will go to access control here
- 4:08:43and here I will click on add
- 4:08:46add ro assignment
- 4:08:48and here we have so many roles. So for
- 4:08:51simplicity we will search for key volt
- 4:08:54here and we will use the administrator
- 4:08:56role so that each and every permission
- 4:08:59is assigned. Then in the members we will
- 4:09:02uh select the managed identity and then
- 4:09:05we will select the managed identity of
- 4:09:07our data factory. So this is the data
- 4:09:10factory that we have. So we will select
- 4:09:13it and then we will click on review and
- 4:09:15assign.
- 4:09:17So now the access has been assigned. Uh
- 4:09:20so if I go back to ADF site and there I
- 4:09:23click on test connection here. So here
- 4:09:27connection is successful. So I will
- 4:09:28click on create.
- 4:09:31So now this linked service has been
- 4:09:33created. And now what we can how we use
- 4:09:36this key volt linked service here is
- 4:09:38that for example if we are making any
- 4:09:40other connection like this SQL server
- 4:09:43and previously we had entered the
- 4:09:46password directly here. So this is not a
- 4:09:48secure way to uh store our password
- 4:09:51directly. But what we do is we need to
- 4:09:54store our password in the key volt and
- 4:09:56then read it from there. So I will click
- 4:09:59this Azure key volt here. I need to
- 4:10:02select the key volt name the linked
- 4:10:04service that we have created and then we
- 4:10:07need to select the secret that is
- 4:10:09created there. And as of now we have not
- 4:10:11created any secret there. So let's go
- 4:10:14ahead and create our secret there. So
- 4:10:16now I'm back in our key world and here
- 4:10:19if I go to this objects then secrets
- 4:10:24here I will click on generate or import.
- 4:10:27So here the name of the secret this is
- 4:10:29just a name. So I will say it is here we
- 4:10:32will be providing SQL DB password. So
- 4:10:35let's say SQL DB
- 4:10:38password. This is the name of our secret
- 4:10:41and this will be the value the actual
- 4:10:43value that is there. So here I will type
- 4:10:45the real password for the SQL server
- 4:10:48that we had created.
- 4:10:53So now I have typed the password. We can
- 4:10:55paste the value here as well. And now I
- 4:10:58will click on create.
- 4:11:01So now our secret
- 4:11:04uh secret creation looks like it failed.
- 4:11:07It is said like it is it failed here
- 4:11:09while creating it.
- 4:11:13And here it is saying like the operation
- 4:11:15is not allowed by role based access
- 4:11:17control. So I think why it might be
- 4:11:20happening is like uh it is probably
- 4:11:22saying that I don't have access to
- 4:11:24create secret there even though it is my
- 4:11:26own key volt. So if I go to uh access
- 4:11:30control here and let's try to give
- 4:11:33access to ourself. So here we will
- 4:11:36search for the same key volt admin role.
- 4:11:39So key volt admin then click on next.
- 4:11:43Here I will select the user although
- 4:11:45there is only one user
- 4:11:49and now I will select it and I will
- 4:11:52review and assign.
- 4:11:58So now the role has been assigned. Now
- 4:12:01let's give it a try. So if I go here
- 4:12:05name again we will say SQL DB password
- 4:12:11and I will type the password
- 4:12:16and I will click on create
- 4:12:21and this time it has been created
- 4:12:23successfully. So if I refresh it this is
- 4:12:26where it has been created now. So I'm
- 4:12:29not like cutting the the the parts where
- 4:12:32I'm getting these errors because you
- 4:12:34also obviously will get these errors. So
- 4:12:36you shall also be knowing all the
- 4:12:37things. So if I go back to ADF now and
- 4:12:41here if I refresh this button here. So
- 4:12:44now it shall be like showing that
- 4:12:46secret. Yes, it is showing the secret
- 4:12:49here now. And then the version uh we
- 4:12:51will leave it latest here. Latest
- 4:12:54version. And then other things we will
- 4:12:56leave as default. So now uh what benefit
- 4:13:00we are getting is that uh that the
- 4:13:03password for this user it is not stored
- 4:13:06here anymore. It is stored in this key
- 4:13:08volt now and in future also if the
- 4:13:11password is changed by someone. So we
- 4:13:13don't need to come to this linked
- 4:13:15service or do anything do everything we
- 4:13:18just need to go to the key volt and
- 4:13:20there itself like we can put a new value
- 4:13:23to the same secret and that way the new
- 4:13:26password will be picked up
- 4:13:27automatically. So here this was our
- 4:13:29secret. So if I click on new version
- 4:13:32here so keeping the same name I can
- 4:13:34provide new secret here and then the new
- 4:13:37secret will be picked up automatically.
- 4:13:39That is how these things work. So that
- 4:13:41is the benefit we get and also we get a
- 4:13:45additional security because key volt is
- 4:13:47very secure. So now it is using the
- 4:13:50password from key volt. Let's give it a
- 4:13:52test connection. And now the connection
- 4:13:55is successful. So what it means is uh
- 4:13:58this linked service it is able to read
- 4:14:00this secret from this based on the
- 4:14:02secret name from the key volt and then
- 4:14:05use it. So that is how we can integrate
- 4:14:08key volt and then use in our linked
- 4:14:10service in Azure data factory. And now
- 4:14:13with all these changes I will save
- 4:14:15everything and then publish everything
- 4:14:17as well. So it is saved. Now let me go
- 4:14:20ahead and publish it.
- 4:14:23And here it goes. It will be published
- 4:14:26now.
- 4:14:27And now the publishing it is complete.
- 4:14:31So that is all I wanted to cover in this
- 4:14:33video. I hope you got really good
- 4:14:36details here and I will be publishing
- 4:14:38two more videos like said earlier. So in
- 4:14:41the next video we will be creating an
- 4:14:43exhaustive metadatriven framework based
- 4:14:46project. So that will be an end toend
- 4:14:48projects on ADF plus datab bricks and
- 4:14:51then I will create another video about
- 4:14:53uh uh interview questions related to
- 4:14:56Azure data factory and that project and
- 4:14:58scenario based questions we will cover.
- 4:15:00So stay tuned for more videos and if you
- 4:15:04have any queries, anything specific that
- 4:15:06you want me to cover in the project,
- 4:15:08then put it in the comment section. I
- 4:15:10will try to answer everything. So that's
- 4:15:12all for now. Uh thank you. Bye-bye. Say
- 4:15:15happy learning.
About this transcript
This page contains the full transcript of Azure Data Factory Full Course by a Sr Data Architect by DataBeli, generated from the public captions YouTube serves with the video. The transcript has 40,797 words across 5,508 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.
What you can do with it
Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to choose a caption track available on the video.
Free YouTube transcript tool
YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, caption language selection, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.