live_tv
Livestream Starting Soon
00
Hours
:
00
Minutes
:
00
Seconds
Up next in 10
Azure User Group Sweden: A Case Study - Data Driven Workflow using Azure Data Factory and Logic Apps
Nov 1, 2023
About Session:
Meetup Registration: https://www.meetup.com/azureusergroupsundsvallsverige/events/289763448
In this session, our guest speaker Alpa will provide basic overview of using Azure Data Factory, Azure Function and Logic Apps. She will show us a demo using Azure Data Factory, Azure Key Vault, Azure Blob Storage and Azure Data Lake.
There will be a presentation of a case study for moving data from Azure SQL Server to Azure Blob Storage that customized notification using Azure Logic Apps.
At the end of session , you will get basic ideas about following Azure services.
1. Azure Data Factory
2. Azure Function
3. Azure Logic Apps
4. Azure Key Vault
5. Azure Storage Account
6. Azure SQL Server.
About Speaker:
Alpa Buddhabhatti is an Azure Consultant with a passion for data and technology at Cluster Reply. Alpa has around 12 years' experience as software developer and have worked in a variety of projects.
Show More Show Less View Video Transcript
1:00
joining us again this Saturday for another learning session with us. And how are you
1:08
And hi, Hogan. Hi, Juna. Hi, how are you? I'm great. I'm great. It's good to be here again
1:16
It's been a while. Yes. Yeah. All right. I hope everyone are doing great today. And before we
1:27
start our session today, which is about data-driven workflow using Azure Data Factory and Logic
1:35
Apps, I would like to introduce my co-leader here, Håkan Silvernagel. He is a Microsoft
1:43
MVP for AI, and he works as an AI manager and big data manager at Miles in Norway. And he is
1:53
a community leader of different user groups like ai42.net user group in oslo and also public speaker
2:07
and a lot of great things yeah so i'm i'm honored to work with you how can
2:14
yeah likewise so i would also like to introduce jonah so jonah anderson is the founder of our
2:22
user group, Azure User Group Sweden. And she is also a Microsoft Azure MVP, Microsoft certified
2:27
trainer. And she is a mentor, a public speaker. And in her daily life, she works as a cloud and
2:36
DevOps engineer at Forefront Consulting. And she's also in the process of writing her first book
2:43
So what is the book about, Jonah? It's about Azure, of course. It's called
2:49
Learning Microsoft Azure Cloud Computing and Development Fundamentals with Aurelie. So I look forward to share it with everyone in the community
3:01
Thank you for the introduction, Håkan. Yeah, thank you. So that's a brief introduction about each of us
3:09
And before we start and bring our speaker on stage, we would like to remind everyone about our code of conduct
3:19
So our code of conduct, we expect everyone to be nice and friendly, listen with purpose and be thoughtful, be respectful to others, seek to understand and not criticize, be curious and open to share your ideas and be respectful and inclusive in your comments and questions
3:42
And if you have questions about our code of conduct, feel free to reach out to me on Håkan or read more on the links that you're seeing in this page below
3:56
Yes, that's our code of conduct, Håkan. And maybe we can share that we're going to have like a FICA or those that want to join us later as well
4:06
We do have a session. Go ahead, Håkan. Yeah, so the FICA is a concept where we will invite you for a Zoom meeting after this session so that you can speak directly to our speaker
4:19
Maybe there are things that you would like to have more explained or maybe you have some questions in your projects that you would like to discuss
4:28
So we will share the link in the end of the session
4:32
Yes, that's correct. And to those that are learning with us, we will be sharing an Azure Heroes QR code as well later
4:44
All right, it's time to shine. Should we introduce our speaker, Håkan
4:50
Yes. So let us just invite her to our stream here. Welcome, Alpa
4:58
Thank you. Let me introduce our speaker here. Alpa Budabati is an Azure consultant with a strong passion for data and technology
5:11
She works at Cluster Reply and she has around 12 years experience as a software developer
5:16
and has worked in a wide variety of projects. She is also a speaker at various events like PaaS Summit, Data Saturdays, SQL Saturday
5:26
and Azure Community Conference. And she is also a certified data scientist, data engineer, and a Microsoft certified trainer
5:36
Welcome. Thank you. Welcome. Yes, Alfa. So what will we be hearing about today
5:48
Today we are hearing about Azure Data Factory, Azure Function, and Azure Logic Apps
5:55
Yes, it's great. I'm looking forward to learn from your session, Alpha
6:01
So, yeah. Are you ready to rock the stage? Yeah, yeah, sure
6:07
Yeah, okay. Yeah, thank you. Okay. So can you see my screen
6:13
Yes. Okay. Okay. Then let me start now. So thank you all and welcome to my session
6:19
our case study data-driven workflow using Azure Data Factory, Azure Functions, and Azure Logic Apps
6:25
here at Azure user group Sweden. So in this session, we're going to see a demo
6:31
using all three services as well. And it's based on a data driven
6:37
So, yeah. So this is me. I think you know now me
6:42
But I can share this link. So you can find me in Twitter or you can find me in LinkedIn
6:48
You can find few blogs in a media maze field using these users
6:53
and you can find a presentation and GitHub repo for this session at GitHub in here
7:01
Azure users we done 2022. In that repository, you can find that
7:07
So now let's start with today's agenda. So today's agenda is divided into four main parts
7:15
So first three parts, we're going to see a very quick overview of these three Azure services
7:21
and then we going to see a demo using all of them three all three of them so let start with first one what is azure data factory so for that we can see that it is a cloud data integration service that orchestrates and automates the
7:40
movement and transformation of your data at a scale it is also azure platform as a service
7:47
so you don't need to worry about computing for such your software installation or web server
7:53
configuration or you can you don't need to implement your own monitoring tool as well
7:58
it is also a serverless and you only pay for what you have used so for example you have azure data
8:08
factory and you have more than one pipelines or more than 100 pipelines but if your pipeline is
8:16
not running then you won't get any charge for execution however you get a small amount of
8:22
charge for a storage which can be negligible. So it is a cost effective. And last but not least
8:29
it's a low code or no code solution. So you can quickly build your pipeline by just a drag and
8:35
drop components. You can do data movements, data transformation, you can orchestrate your pipeline
8:43
you can automate your pipeline. All you can do it by just a drag and drop components
8:48
so you can find more information in this link if you'd like to know more about a geodata factory
8:54
and now we're going to see a key components of a geodata factory so you get idea when you create
9:02
your geodata factory how or what it should be so first one is a linked services so linked services
9:11
is like a connection holder. It holds a connection strings of your data source
9:17
And it authorizes your data source. So it can be used by your Azure Data Factory
9:22
with other components can use it. Next one is a data sets
9:30
So data sets is a metadata of your data or reference of your data or a structure of your data
9:36
So for example, if your link service hold a connection string of azure SQL database then your data sets in a form of the table
9:45
but your link service is holding the connection string of rest api then your data sets in form
9:53
of the json file so data set can be a file folder or a table format so that is a data set so you
10:02
create your link service you create your data sets and then you can create your pipelines so
10:07
So pipelines is a logical group of your activities or tasks or steps which achieve the business outcomes
10:18
So in pipeline can have activities and activity can be in a parallel or sequential
10:24
So that is a pipeline. Fourth one is activities. So when you create your pipeline, inside you add steps and for that you use activities
10:34
So activities do a specific task. For example, copy activity perform the copy data
10:42
Machine learning activity perform the machine learning data inside your ADF. And as we see that Azure Data Factory is a no-code or local solution
10:53
So if you want to do some logics, for example, you like to declare your variable, set your variable
11:01
you write down for each loop like you do in your programming language or database language
11:06
So if you want to do same thing in here, activity help you. So for that, Azure Data Factory have like
11:13
for each loop activity, if activity, switch activity, and there are so many other activities
11:18
available. I will going to show you during the demo. So that is activity, which is a specific
11:25
task. I do a specific task in Azure Data Factory. And it's like ready-made components for you. So
11:31
you just need to drag and drop and you need to set few properties and last but
11:38
not least it's triggers so once you have a pipeline ready you need to execute
11:43
your pipeline you can execute your pipeline by manually clicking on debug
11:47
button or you can trigger it so your pipeline can be triggered in various
11:52
different way it can trigger based on a time it can trigger based on when file
11:57
created or deleted from your blob storage or when custom activity like you can write down your own
12:03
code for example you can write down your code in a azure function as your logic apps powershell
12:10
or c sharp code so that those custom activity you write down your code and it will trigger your
12:16
pipelines and your pipeline will execute so these are the five main components uh in azure data
12:23
factory uh so now let's move into next one so i think we covered a quick overview of azure data
12:31
factory so do we have any question on that otherwise i can move to next topic which is azure function
12:42
so i think it's no yes no no questions so far yeah yeah okay thank you so let me start with
12:49
Azure Functions. So what is Azure Functions? So I'm sure all of us have used the function in
12:56
either programming language or maybe in a database language or t-SQL language
13:02
Functions is a small piece of code. We write down a code in a function because we use it
13:09
So same way this function in a cloud, Azure cloud, we call it the Azure Functions. It's a
13:15
very similar one. So let's see a definition. So Azure function is a small is a solution for
13:23
easily running a small piece of code in a cloud or a function in a cloud. It's a very simple
13:30
independently you can write down your code in Azure and then it can be used by any solution
13:37
It can be used by IoT solution. It can be used by data integration solution or it can be used by
13:44
AP integration solution. So function has a few characteristics because when we write down a code
13:52
in on-premise system, we need to call that function somewhere in a code. But here it triggered based
13:59
on an event. So event-tribent means your function is run, start executing based on some condition
14:07
or based on a trigger. So once that condition arrives, your function will execute
14:12
so here you have like HTTP trigger timer trigger blob trigger queue trigger even in a SQL SQL
14:22
trigger as well so when something is changed based on that your function will execute automatically
14:28
next one its function is a serverless and is a platform as a service so here also you don't
14:37
need to worry about computer infrastructure or software installation or web server configuration
14:42
and many more. Azure will do it for you. So, you can fully focus on implementation and you can build
14:49
it very quickly It a scalable So when demands comes it scales up resources and when demand down it scales down the resources so it just save your cost so for example christmas time uh online shopping uh demand will high so that why in
15:09
that situation azure will add more resources so it can achieve quickly output and when
15:16
there is a like after christmas maybe january or february there is no demand or less demand
15:24
at that time you scale down the resource so it save your cost so it's a cost effective as well
15:30
and it to easily scale up and scale down and last but not least it's lightweight because function is
15:38
lightweight and it's do a pacific task for example it do addition or it do multiplication
15:44
or like that so it do some calculation and it give you a result so this is the four main
15:52
characteristic of Azure function. Sorry, you can find more information regarding Azure function
16:03
if you'd like to know more information using this link and you can see it look like this symbol
16:11
So this symbol is three things, code plus data plus event. So this is Azure functions
16:17
so now we going to see how you can integrate your Azure function with Azure data factory
16:24
very easily so for that you have Azure data factory in your subscription you have Azure
16:32
function as well and inside your Azure function apps you have Azure functions and you have a
16:39
because you have Azure function apps you have a URL as well function URL so now you need to call
16:47
using the azure data factory so for that azure data factory have activities as we know and it
16:53
has activities for running the azure function so it has a function activity so in this function
17:00
activity we need to set few things like we need to tell which function apes which methods
17:07
messages and many things so these are the main things you need to pass in your function ap
17:14
function activity when you calling the function so you passing url function key name
17:20
methods and message and i guess it's a post method and then function will execute independently
17:27
and it's written back to azure data factory so it's very easily you can integrate it by
17:33
few button clicks away and you need this integration in your data solution for example
17:41
something is not feasible using the Azure Data Factory or Azure Synapse Data Integration Pipeline
17:49
So at that time you can use Azure Function. So in for example I have used Azure Function in my
17:54
data solution because I have some requirements which is not at the moment feasible using the
18:01
Azure Data Factory. I will show you later on this session. So this is the Azure Data Factory
18:07
and Azure Functions integration. So now we're going to move into next topic
18:13
which is Azure Logic Apps. So if you have any question now, then I can take it
18:18
Otherwise, I move into next topic, which is Azure Logic Apps. So I'm assuming no question
18:28
So now let's start with Azure Logic Apps. So what is Azure Logic Apps
18:33
So basically, Azure Logic Apps is interface between your new system in a cloud and your old system. So let's see a definition
18:44
So it is a cloud-based integration service for creating and running automated workflows
18:51
that integrates your application, data, services, and systems. So it easily integrate with
18:58
on-premise system or cloud system or both of them it's like hybrid as well and if you like to know
19:07
example where you can use azure logic apps then for example when file is created or deleted
19:14
from your sftp folder like as soon as the file arrive into your sftp folder you would like to
19:22
do some calculation or would like to process your file then you can use azure logic apps
19:28
another example is for example you have a product and you would like to know sentiment behind
19:37
that products like how your customer feels about your products based on our tweeters so like for
19:45
example as soon as the tweet regarding your product is posted in a twitter you can call a
19:54
and logic apps can apply a sentiment ysis on that feedbacks and based on that you will get idea
20:04
that you need to improve your service products or which service you can improve and how you can
20:09
improve so it will like you give you you will get some sorry you will get very good idea about your
20:19
about sentiments of your customer for your products and you can improve as well so for
20:26
logic apps you can see that logic app has around 200 connectors available which you can use for
20:34
connecting easily connecting in your logic apps because logic apps also no code or local solution
20:40
so for example sending email you can have like Gmail Outlook Office 365 SMTP and send grid and
20:51
many more so these all connectors are ready made available in a logic apps you just need to drag
20:57
and drop them and then you can send email easily so you can see this is a symbol of logic apps and
21:04
And you can find more information if you'd like to know more about logic apps than using this link
21:11
And now we're going to see integration. So like how you can integrate your logic apps with Azure Data Factory
21:19
So for that you have Azure Data Factory services and you have Azure Logic Apps
21:26
So you have two services available. So now you have a logic apps and your logic apps has a triggers and
21:32
it has URLs and in another side you have Azure Data Factory and Azure Data
21:40
Factory have a pipeline and inside it has a web activity. So in this web
21:45
activity you would like you need to set few properties like you need to set URL
21:50
of your logic apps methods and message in the case of the post method. So you
21:56
you set this information here and based on that it will execute your logic apps and logic apps
22:03
will do or that task for example sending email logic apps then it will send email and it come
22:08
back to here in a azure data factory so you can very easily integrate that in few buttons
22:16
away as well and in your data solution there are few things is not possible using the azure data
22:23
a fake click directly For example sending a customized email at the moment is not possible However you have an alert which sends an email but it not user
22:36
So if you need a customized email or you'd like to attach some files
22:40
sorry, or reports while sending the email, then you can use Azure Logic Apps
22:48
So this way you can integrate Logic Apps and Azure Data Factory
22:52
Now let's move into fourth one which is a demo. So in this demo we are using all three services
23:01
And before we going into demo let's see a problem statements for that
23:08
So we have a on-premise systems and in on-premise system have a following features
23:15
For example on-premise existence systems do a data movements and data transformation
23:22
It apply your image resizing. It's generate a manifest file having the ETEC, record count, file name, and many more things
23:34
And it send a customized email. So these are the four main features we have in our existing system
23:42
So if you like to migrate this project or system in Azure Cloud, then is it possible only Azure Data Factory
23:52
we will see so this is now second topic second part is migrating the old system into azure cloud
24:01
so for that first one is data movements and data transformation so for that we can use
24:06
easily azure data factory or azure synapse ytic data pipeline second requirements is
24:13
image resizing so you can do image resizing using azure function here at the moment this is not
24:20
feasible using Azure Data Factory itself so we using the Azure functions
24:26
third requirement is creating a manifest file again this is not possible
24:32
directly using Azure Data Factory so that's why we using Azure functions for
24:38
that and fourth one is sending a customized email so these requirements
24:45
again is not directly feasible using the Azure Data Factory because we may need attachment or we
24:52
need some formats. So for that we can use Azure Data Factory. So you can see that this is your
24:58
old system or existence on premise system and if you migrate into cloud then you need these
25:05
services. So you need Azure Data Factory, you need Azure Function and you need Azure Logic Apps
25:11
so these are the things we going to see in a demo so for image resizing and
25:20
manifest file we have two different pipelines so let's start with first the
25:26
pipeline which generate a manifest file so here you can see that I have Azure
25:32
block storage it has a file disk in container and it contains the CSV files
25:39
It can contain the JSON file or mix of both of them
25:43
But for this demo, I have only considered CSV file. So we have one service ready and we have another service which is Azure SQL database
25:53
So this database holds the final results of data. So when you have three files in your Azure block storage, when I run my pipeline
26:05
you will see that I will have three tables in here created automatically
26:10
If I have 100 files, then it will create 100 tables automatically using the pipelines
26:17
So that is Azure SQL service, which hold the final results or as a sync
26:24
Then we have Azure Key Vaults to provide the security and store the connection string in a central place
26:35
So here you can see that keywords is very useful. For example, if you have a Azure SQL database and you need to change a password, but your
26:50
Azure SQL database has used in a more than one system. So if you want to change a password, then you have to change all three systems
26:59
But if you use a keywords, then you only need to do a change in one place, which is in a
27:04
Without changing any systems, you only need to change key vaults and it will automatically reflect all other place
27:12
So that is another benefit to using Azure Key Vault Service. Then I have used Azure Function to send or to generate a manifest file once it transferred from source to sync
27:26
from blob storage to Azure SQL Database. When you successfully move all files, after that, I'm creating a manifest file using the Azure Functions service
27:36
And I have Azure Logic service. So once my manifest file is created and my pipeline has run successfully, I would like to send an email, either success email or failure email
27:49
So that's why I have user logic apps. And then I have Azure Data Factory
27:55
So these are the six services we have used for this demo. And in Azure Data Factory, I have a pipelines
28:03
So now my pipelines is reading the data from this source CSV files in a file desk in container
28:12
So pipeline has a 4-inch loop. So it's read file like how many records you have
28:19
So like if I have three files here, then my 4-inch loop run for three times
28:24
and it copy data from CSV file into a particular table. So if I have three files, four each will run three times
28:33
and it create three tables in Azure SQL Database and it loaded data as well
28:39
So after doing that, you can see that it will load in a second step
28:45
And after loading the data, Azure Function will execute because pipeline has a function activity
28:51
and it's called that Azure function. And end of the execution, it come back here
29:00
But when it's executed, it will generate a manifest file and it will store that file into a blob storage
29:08
It can use the same blob storage or it can use other blob storage or you can have another mechanism as well
29:15
And it saves that file based on an extension. So it stores that new file with a .chk extension
29:24
And it has a summary of the transformation. So if I have three files, then this new file has three records
29:33
like file name, file name, dates, and etix, and a few more things
29:39
So it has a summary. It's like metadata of your transformation. And once it's done, Azure Functions have another pipeline or activity which execute and it's called Azure Logic Apps
29:54
And Azure Logic Apps send an email and it's come back to here
29:58
So this is a high-level. level design of creating a manifest file for demo one. So now let's go back to demo. So here you can
30:10
see that. Sorry. So here you can see that demo, but I will like to show you one by one each resources
30:22
So this is my keywords and keywords hold the secret because I have Azure Blob storage
30:27
I have Azure SQL database and I have Azure function. So I have three secrets here
30:34
And if you like to use the secrets in your Azure data factory
30:39
then there are main step is access policy. So you have to have give access policy here
30:48
And here you just selecting your Azure data factory and you giving them permission for secrets or key or certificates
30:57
that's it so once you apply that you can then and then you can successfully use
31:02
secrets in your Azure data affecting and you can very easily create that by
31:08
clicking here and selecting your data factory and selecting permissions that's it and even a creating secrets also you can do very quickly by clicking here and
31:19
you giving a name and you providing the key so here you can have like your
31:25
database password or connection sync or your blob storage access key or connection syncs or
31:31
function key or like that so we are not going to create that so this is azure key vaults
31:40
let's close it out because we don't need that now so next service is azure blob storage so
31:48
you can see that this is a blob storage and we have this three container file this in container
31:54
input container and output container. But for this demo, we are only considering file
31:59
as in container. So this is my container. And you can see that I have a file scene here
32:06
Let me delete this file. So I'm deleting this file because that is output of execution
32:14
So you can see that in my container at the moment, I have three files
32:18
so it should create a three tables in when i run pipeline if i have 100 files then it create 100
32:26
tables and it can use a different business outcome so at the moment i'm using pacific container which
32:33
is file this in container but i can work the same pipeline can work for another business outcome for
32:39
example business outcome two or business outcome three so it will work based on same things you
32:46
just need to pass different container and it's look different container instead of file this in
32:51
container. So for that I have parameterized my pipeline as well. So that is my source and this
33:00
is my sink or destination. So at the moment you can see that I have only three tables and when I
33:07
run my pipeline you will see here three more tables because I have three files in my blob storage here
33:14
so it will create three more tables here and this is a third fourth here service
33:22
which is a Azure function app and you can see that this is a quick overview of
33:28
Azure function apps you can see this is function apps URL and here you can see
33:35
many more things you can see your functions you can see this function key
33:39
is very important because this key you needed when you using your function so
33:43
can use your function in maybe azure data factory or as your cnet ytics or other place then you
33:49
need to give this function key and a function name and this url function url so this is the
33:59
quick overview so now we're going to see a functions so in my function apes i have a four
34:06
functions and this is azure portal and you can create your functions here you can test your
34:13
functions here you can monitor function you're here so this is function already here but you can
34:18
create by clicking here and you can do it very quickly as well by selecting however this is not
34:24
a recommend way so you can create your functions here but you better to use using the code maybe
34:32
C sharp or Navigate studio or visual code so I have already function so now let's
34:43
test our function so here you can see that you can go to the code plus taste
34:49
and it a little bit low today but yeah so it will function which appears here
35:02
like this and you can test your function by just about clicking on that button you
35:08
select methods and you pass required methods so for here for example it's
35:15
parts because it's needed a name and for example let's pass a sure user group
35:31
We done. Some passing required message and I'm running that. So you can run your function here and you can see that it responds to 100
35:42
And here you can see that hello Azure Azure User Group Sweden
35:47
And you can see logs here as well. So this way you can execute or test your function in portal
35:53
However, this is not a recommend way. You can do it by triggers, event-driven triggers
35:59
so this is a functions you can see here integration as well like how they are coming like which trigger
36:08
it has used and inputs and outputs and here you can monitor your functions using Azure portal so
36:14
you can see here history as well sometimes it take a little bit time so you can see that and
36:23
you can trace it if it's failed then you know why it's failed so you can go to here and you can see
36:28
that it's invalid information that's why so and you can see success as well and
36:35
app integration this is very powerful things and if you go to app integration
36:41
and you can see more information here because in a main monitor area you can
36:46
see not all functions after a certain limit so here you can see everything for
36:52
success fail and many things and you can even see exception as well and even you can have a trace
36:59
as well if you like to trace one by one then you can use that trace as well based on log you have
37:04
provided in your code so this is app insights but you don't need to do anything this is
37:12
automatically created for you once you create your function and this is a function key as well
37:18
if you want to give only for this function. So you can see by clicking here and then you can copy this function key and you give to whoever is using your function So they will call your function like that way So this is a quick overview of functions
37:36
Now we're going to see a logic apps. So this is our logic apps
37:40
This is a no code or lockout solution. And here you can see the designer of your logic apps and your logic apps must have a
37:49
one and only one trigger. I have HTTP trigger but you can have a timer trigger so reoccurrence triggers and many more
37:56
and I have any slice so here I'm calculating few message email message body as well
38:04
here you will get URL and this is a URL you need to call in your Azure function
38:13
so this is a very important URL so you need to copy this URL that's it
38:17
so here I'm calculating a message body and here I'm calculating that my pipeline is success or
38:27
fail if it's failed then I have a failure email and if it's success then I have a success email
38:32
and I have used here gmail connector so you can see that that's it so I have used gmail containers
38:38
or connectors so I don't need to write down any code for that and I just passing the to whom I
38:44
need to message I'm using myself and message body which we have calculated
38:48
here and title message title and you can have a CC and attachment as well so
38:55
those thing you can set it here so this is a logic apps and here you can see the
39:02
code view of your logic apps as well and this code help you to do your continuous
39:08
integration and continuous delivery for as arm templates if you like to do that
39:13
you need to slightly change it but this code will help to you and this is a
39:20
connectors API connectors as I see they have more than 200 connectors available
39:25
so I have used a Gmail connectors here for sending the email so this is a
39:32
logic apps and let's go to here I want to show you one more things so and here
39:37
you can see that run history so if someone as your data factory has used
39:41
logic gaps and if logic apps fail or success you can check here if it's failed and I failed
39:50
and wise fail and you will get idea as well here and when it's run as well you can check time as
39:57
well okay so now we have see of five services now six one is our sure data factory so this is my
40:05
Azure Data Factory and I have a link service. So I have a link service for Azure Key Vaults
40:10
So you can see that I'm just specifying URL of your keywords and that's it
40:15
So once I have that one, then and then I can use that keywords in here in my Azure SQL
40:20
database connection. So I have used Azure Key Vaults to with the secrets
40:27
So you can see that here I'm not specifying which Azure SQL database I have used, which
40:33
Table I have used or which user I have used or which password I have used nothing you can see you only see secret name
40:40
Because secret has a connection string So this is my azure SQL database link service
40:48
This is a function link service so you can see this is function URL and here also function key comes from keywords
40:57
That's it. And then I have Azure blob storage and And this is also connected with the keywords
41:05
And you don't know here which blob storage we are using because all information stored
41:10
in the secrets. So it's secret and secret. It gives you security as well
41:15
So this is a link service. And here you can see code as well, but you don't need to worry about this code because
41:21
this code is automatically generated for you. And here you can see that who is using your link service and you can delete as well here
41:30
this is a link service now we going to move into pipelines so this is my Azure
41:37
data factory pipelines this is my branch and my branch hold the pipeline for that
41:43
so I have two pipelines because I have two demo so we going to see first demo
41:50
here which is manifest file creation so I'm a while I'm doing that I would like
41:59
to run that so parallelly I can explain you as well but you can see that I have
42:04
three files here and I don't have any I have only three tables here but after
42:10
execution you will see that it will generate three more tables here so now
42:15
let's go to pipeline so I'm running this pipeline while explaining you more
42:19
things so I'm using here specific business outcome which is a file
42:23
related but but you can have a different business outcome then you can pass your
42:32
business outcome one or two or three so based on that it will pick that specific
42:38
container from your blob storage so I'm running this pipeline and while I'm
42:43
explaining as well so you can see that I have used your metadata activity so
42:48
metadata activity read all file from that specific container so if that
42:54
container has here three files then metadata file give me three rows for
42:59
each file and it's give it three rows and it's passed that three to four each
43:05
loop and then four each loop execute three times and whatever you have here
43:10
it will execute three times so I have copy activity which execute three times
43:14
and open variable have some calculation for that so it's three time copy from
43:20
blob storage to a jurors database and here it will hold the entry each time
43:25
so if three files you have three entries here as well so once I run my for each
43:31
loop so at end of this I have now data in my
43:36
Azure SQL database so if I refresh now you will see there are three more tables
43:41
will created and it's related to event g info and movie DB so stay a table name
43:51
also same based on this one is just removing this CSV extension and it
43:56
create three tables so if you go here you can see that there are three tables
44:00
DBO events DBO g info and DBO movie DB so this three table created and you can
44:06
view information as well here because it's more data as well so let's go back
44:14
here so this one is done so after creating after transferring data and
44:20
applying some modification or whatever you would like to do it called the Azure
44:25
functions so you can see that this is my Azure functions and I'm passing few
44:30
information I'm passing the link service holding the URL of your functions and
44:35
name of the function key of the function. I'm passing here function name, which is my create manifest function
44:46
So you can see in my function, I have this function create manifest function
44:51
So I'm passing this function. And it is a post method So I need to pass a message body So here I passing the required information in JSON format So here I have a variable
45:05
I have open variable holding the required information. So I will show you what it has later on
45:11
So that's it. We're passing this information. And based on that Azure function will execute
45:17
And it do some specific task. So in our case it's generate a new file
45:21
and it come back to Azure function, Azure Data Factory, sorry. So once it's come to Azure Data Factory
45:32
here you can see that I have two web activities in case of the failure of this or success of this
45:40
So using the logic apps, you can see that I have web activity
45:46
and I need to do few settings. So this is a URL, as I said to you
45:51
that your logic apps has a URL which is a trigger URL
45:57
So you get this URL you need to copy and you need to paste that URL here
46:02
and you need to select method. And in case of the post, you need to pass the required information
46:07
So I'm passing here my pipeline name, data factory name because I have three different environments
46:13
So I need a data factory name as well, run ID like which pipeline is using
46:17
because most of all pipelines is using the email functionality. So if in case of the failure, you will see here error message and you see time as well based on a trigger
46:28
And this I just send a green color for successful, but you can apply some other logics as well
46:33
So this information I'm passing, that's it. And based on that, logic apps will execute
46:38
So you can see that it already executed my logic apps because here is a green
46:43
So if I'm going to see here and if I'm going to see a history of the logic apps
46:49
Hopefully this is the time. So Logic Apps has executed today. And you can see here as well
46:58
In case it's success or fail. You will see here information. And you can see that to whom it has sent
47:06
So this is a message information. Which I have passed from my Azure Data Factory
47:11
And this is a person who getting the email. And if I am going to check here
47:17
you can see that I got email today and you can see it's three minutes before ago
47:23
you can more customize but I have a very less information here so in case of the success or
47:31
failure you will get email for both scenario so now let's see here my pipeline has executed
47:38
this function should create a new file in a blob storage here so if I refresh here you will see
47:46
one new file so this is a new file which is a manifest file which having the dot
47:55
chk extension you can see here and it appending the timestamp as well late
48:01
and times okay so now let's open this file and edit this file so you see that
48:10
it has a three records it says that because I have three files in in my
48:15
blob store it's a file one and the counts how many records it has and this is a
48:20
text which is very important and this is the things we it's not possible using
48:24
Azure data factory itself because this is a blob property and this is a timestamp
48:30
again here for each file you can see that so if I scroll it down so I just
48:37
would like to show you for even take for this it take for this so this is a file
48:43
which is event CSV file and you can see this is a etek and you can see this is
48:48
the value of this so if I copy this value and if I go back to my manifest
48:53
file if I edit this file if I search here then you will see there is one
49:01
record for that so you can see that this is a file and this etek is for this event or CSV file
49:11
same way you can match with this as well why we need this because we have a
49:15
third-party system and they are using this file and they need it in that
49:20
specific way so that's why we need to create a manifest file because it's in
49:25
on-premise system and we migrating it we don't want to change it functionality
49:29
device so that's why we have to use Azure function for this so this file has
49:36
created end of this demo and this is a manifest file so now I just would like to show you here few
49:44
things so this is a parameters I have a spec parameters you can have more parameters here
49:53
this parameter I have used because I would like to copy when my pipeline will run I would like to
50:01
use only a specific container for Pacific business outcome so at the moment I
50:09
focusing on file but if I type here business outcome 2 and if I run this
50:14
pipeline and if I have a folder here for example yeah so if I pass feedback
50:24
then it will create a new if I run same pipeline. I will run later on
50:32
but I will show you here few things after that. Then same pipeline will work and it look for the specific container
50:39
Here look up activity, we can quickly look one by one. This is Outlook
50:48
You can see that this is my metadata activity and it has
50:54
children's child items and it has a three child item because i have three items in
51:00
blob storage but if that specific container but if i have a hundred then you will see here 100 entries
51:08
so let's close this so this is my for each loop for each loop running for three times
51:14
because it has three entries in my metadata activity my for each loop run for three times so you can see that and here you can see that it's moved
51:29
data from blob storage to sorry it's move data from blob storage to a juryscore database and
51:41
and here you can see that which file so you can see that it's event CSV file so
51:48
it create one table which is even db or events table and same way you can see
51:55
that here is for g info CSV and it create new table for that and for same
52:01
way for here as well an append variable is hold the information like file name
52:06
and concatenation with accounts how many count it has and this manifest file
52:14
creation you can see that it has this information it has a function name
52:20
methods and which container they are interested and here is a message body with a value so this is a value I passing to calling my manifest function File name concatenation with accounts
52:38
And here you can see that it's success or fail. It's okay
52:42
So it's success. And you can see that logic apps. So this is a message body of logic apps
52:50
And so here you can see that it has a URL of your logic Apes
52:56
It has a methods and there is no header at the moment, but you can have header as well
53:01
And this is a message body. So let's close it out. So this is the logic Apes one
53:09
And you even quickly see here that this is my blob storage
53:14
And here I'm passing this, looking for this specific container. and this is for each loop you can see that here for each loop is using a for each loop is using the
53:30
actually I think it's used instead of for each loop so this is a for each loop and
53:40
you will see that here that I'm passing the this upend which I have here this is upend area at the moment is empty but it will calculate
53:58
based on inside the foreach loop but I'm passing this metadata activity so because I have settings
54:06
here and you can see that this is the child item from my metadata because we
54:12
see there are three child items so I'm just passing it here and this is the
54:18
here metadata activity and you can see that for each loop has a copy activity
54:29
and append activity and here you can see that it's my source is a blob storage
54:34
Here I have some calculation that remove the .csv from a file and create same name as a table
54:43
And here you can see that it's removing it and it's creating as a table
54:48
Same name as a file name as a table name. And you can see mapping as well if you want to explicitly explain here
54:56
And that's it. And then append variable will have a few things like append variable have some settings for that file name and concatenation with count
55:11
So that's it here. And now let's see in here. And this is for it
55:20
I think I already show you how it looks. I already see
55:27
we have already seen it how it looks now this one and this is a logic apps also we have seen
55:36
logic apps that it has urls and few things you can see it here as well and that's it so this is my
55:45
metadata the pipeline which generate a manifest file this pipeline can work for one business
55:55
outcome or more than one and this pipeline can look for specific containers and it run for
56:05
one file or 100 files or more than that. So now if you want to run that same pipeline with
56:13
another container which is feedback container then it will look for that. So let it run but while
56:20
it's running we going back to see a different demo and we come back here later. So we're going
56:28
to come into presentation. So we have seen this demo which generates a manifest file and it moves
56:35
the data from blob story to Azure SQL table. So now let's see a second demo for resizing your image
56:42
So here I have used Azure function but I have used a blob trigger of Azure function because I
56:48
I would like to show you a different flavor of the Azure function. So without calling the function directly into Azure Data Factory, Azure function will automatically
57:03
run as soon as the file arrives into that particular container. So let's see what I have
57:09
So I have Azure Blob Storage and here also I'm using file-based in container
57:14
And in my file design container, I have a one CSV file which holds the metadata about
57:20
of a JPG file and I have a list of JPG files
57:24
So for example, if I have three JPG files, then I have a three entry in my CSV file
57:30
like file name and a few more information. So that is a blob storage service
57:37
Then I have Azure database service, sorry, Azure data factory service. Then I have Azure Key Vaults for the same purpose which I explained earlier
57:47
Then I also have Azure Blob Storage and it has output container which holds only the JPG files
57:55
I have Azure Blob Storage with input container which hold the JPG files again but different purpose
58:04
Here I will show you when it's used this and when it's used this
58:09
Then I have Azure SQL database, which hold the data, which hold this CSV file or metadata
58:17
file in our table as a table. And I have Azure functions, which resize my image
58:24
As soon as image, image arrive into this container, input container, this function will automatically
58:31
triggers and resize the image in a medium and small size and place into different container
58:36
which is output container. And I have a here logic as well
58:40
So this first one is Azure Data Fictive, a pipeline and pipeline has activity
58:48
a 4-H loop activity, lookup activity, all like that. So it's read this file CSV file and it's move each file from file this in container to a
59:02
input container. So, for each loop will run for three times if I have three files, but I will show you
59:09
I think I have two files in execution so it will run for two times and it move that jpg
59:15
file into a blob storage input container. And then finally it will move the CSV file into Azure SQL database as a table
59:26
And as soon as image you arrive into input container, Azure function will automatically
59:32
triggers because it has a blob trigger and it resize the image and it store
59:40
image into this output container so if I have a two image here it will generate a
59:45
four image one because small size and medium size so each image has a two size
59:51
small size and medium size so if you have two file here I have a four images
59:56
and then logic apps will generate emails after so this is a higher level design of demo 2 which is resizing image and now we
1:00:17
going to see same you know here so before demo we have execute this one
1:00:26
anyway because of some different reason is failed but that's fine but it work
1:00:32
for a another business outcome as well because we have used a instead of file
1:00:36
those in we have looked for fit best design and here you can see that I will
1:00:41
get fail email as well okay so now we closing this day more one and we going
1:00:47
to see a demo to which is a this is a demo to which resizing the image so here
1:00:55
you can see that I am not calling Azure Function activity directly because
1:00:59
indirectly it calls when file arrives into the input container. Okay so let me
1:01:10
go to my input container which is here that file is in container so I am using
1:01:18
same container for both demo so I'm just deleting these files because I need to
1:01:25
add files for a related to image so now I'm uploading files here and I'm
1:01:37
uploading for this demo which is this file so I have two CSV file sorry one
1:01:44
CSV file and I have a two image apg image and here you can see that you have a CSV file which is cars and you will have a
1:02:05
two entries because you have two image outside so like a customer ID and car
1:02:10
type and car name this is a very simple data but in real time you have millions
1:02:20
of million data and this is my file this in container and now let's see our
1:02:27
input container so this is a input container and you can see that it's empty
1:02:31
but when I run my demo you will see here two entries and after end of the demo
1:02:37
output container is empty as well here but it have a four entries okay because
1:02:43
two images I have so it will be four entries and let's just open this and here you can see that
1:02:53
Azure SQL database so I don't have a table for a cars dot car name dbo.cars but you will see here
1:03:01
one table as well so now let's run this second demo so I'm passing the file so I
1:03:13
have a concatenation with desin in here let me show you because so in data set
1:03:23
I am concatenating with original container with less in container so here
1:03:29
you can see that I'm concatenating it. So it's looking for file disk in container
1:03:33
because I have given some naming standards. So like original container is file
1:03:39
staging container is file disk staging container and input container is file disk input container
1:03:45
Same way for business outcome one, two or three, they will same structure
1:03:50
So that's why I have used this standard. So now let's run my pipeline
1:03:55
So it's looking for that file-design container which having the CSV file having the two records
1:04:02
and two jpg files and this is a CSV file. So I'm running this demo while it's running we can check
1:04:12
here so this is my lookup activity and lookup activity will look that particular spec code which is file this in container and that particular file which is a CSV file so if CSV file have a 10 records then it passed 10 records to
1:04:28
for each loop if it says 100 records then it passed 100 and it has a 2 then it passed 2 so
1:04:34
in our scenario it has a 2 number 2 records so it will pass 2 to for each loop so here you can see
1:04:42
for each loop is running and here you can setting you can see that it items
1:04:48
offer this lookup activity so you can see that and here also you don't need to
1:04:54
remember anything so you just need to hear which activity you need to you can
1:04:59
specify so I am using the lookup activity so you just need lookup activity
1:05:04
that's it so let me paste it here So for each loop run now two times and what it will do, it just a copy of data
1:05:17
So it's copy a binary copy because I have a jpg file. So that I need to move from that file is in container to a input container
1:05:26
So here you can see that it has a binary copy. I'm using binary data set and it doing binary copy one by one
1:05:33
It's read that CSV file and copying one by one jpg from blob storage file is in container to input container
1:05:40
and this is a sync settings as well of that that's it and once for each loop
1:05:48
finish it will copy a final file which is a CSV file so here you can see that my
1:05:57
source is a blob container and it's using that CSV file you can check it
1:06:03
here as well which we passing it because initially I have passed a file this in a CSV file card wrote
1:06:12
CSV file as a parameter and this is my sync which is a sure as called database you can see it here
1:06:20
so it's creating table based on a file name by just removing CSV from that file parameter and
1:06:27
then it's concrete in our t as well And at end of the pipeline execution, you can see that it's sending the email here
1:06:43
And you can see that this is URL of logic apps methods and the same thing which I showed
1:06:48
you earlier, all other informations, like what you're going to pass. So my pipeline is successfully executed
1:06:56
So what you will expect in input container. you expect to jpg file because you have a two files in your source input container so if i
1:07:06
refresh here you will see i have two files which is image two cars image so two files but if i go
1:07:16
to my output container i will have a four files here because as soon as the file arrive into input
1:07:22
container my function execute and function will create a two extra two files for each file so
1:07:30
here's original file name and space and AMD original file name space SM SM is
1:07:36
small size and this is a medium size same for these two file as well medium and
1:07:41
small and you can open here as well like how it will look so this is a image
1:07:48
file so yeah so this is a demo to we can okay yeah we need to see at one new
1:07:55
table in here so he were here if I refresh you will see one new table which
1:07:59
is car.csv this is a new table and you can see that it has a two entries as well
1:08:18
it's a bit slow but you will see that it has a two entries like customer ID type
1:08:23
and file name and I you can see that I have a few messages will because one
1:08:32
pipeline failed pipeline sucks and here you can see the same thing that this is returning two records lookup activity returning two counts and for each loop
1:08:49
will execute two times, copy activity move jpg file to binary copy from jpg to
1:08:59
blob to blob so you can see here blob to blob and same way here as well and this is a csv file from blob to azure database
1:09:13
and here you can see that send email because we are not calling azure function here directly but
1:09:18
it will automatically trigger so here you can see that logic apps as well so now let's go back to
1:09:26
presentation so this is the end of the demo 2. in order if you would like to create this demo same
1:09:37
in your site then you need to create six resources which is azure data factory and storage
1:09:47
sql server azure sql server azure keywords azure functions and azure logic apps
1:09:51
Now, I would like to conclude this session. So we have seen Azure Data Factory
1:09:57
which is a cloud-based data integration service, which is Azure Platform as a Service and serverless
1:10:03
so you don't need to worry about many other things. It's a no-code or low-code solution
1:10:08
It helps you to do your data movements, data transformation. It helps you to orchestrate your pipeline and automate your pipeline
1:10:16
You can easily integrate Azure Data Factory with other Azure services, For example, Azure Databricks, Azure Machine Learning, Azure Function, Azure Logic Apps, and many more
1:10:26
So this is Azure Data Factory. Then we have seen Azure Functions, which is a small piece of code in Azure, which is a very good service
1:10:36
And it's a part of the integration solution you can use for your IoT integration solution, data integration solution, or application integration solution
1:10:45
And it is event-driven. So based on that particular event, your function will trigger
1:10:51
And it uses a serverless computer as well. So that is Azure Function
1:10:57
Then we have seen Azure Logic Apps, which helps you to automate workflow, create your automated workflow
1:11:05
And it helps you to connect with apps, data systems, and many other things
1:11:11
It is like an interface between your old system and new system. It is a local solution
1:11:17
It is also part of the integration service. And it's a serverless as well
1:11:23
So then we have seen two demo. We have seen first demo to create a manifest file
1:11:27
where we moving the data from Blob storage to Azure SQL database
1:11:36
And then we create a manifest file. And that file is with .chk extension, which we save into Blob storage
1:11:44
And then we sending your email. And in second demo, we have an image in an input container, in storage container
1:11:52
So we resizing them based on a metadata file or CSV file
1:11:56
And then we resizing them using the Azure function, which is automatically based on block trigger
1:12:03
And then we sending emails as well. And we have that CSV file in Azure SQL as a table
1:12:10
So with that, I would like to thank you for attending this session
1:12:14
and I am now open with a question. And if time has, then I can show you the C-Sharp code
1:12:23
for Azure Function as well. All right. Thank you so much, Alpha
1:12:33
for the great session that you delivered today. And I'm very impressed
1:12:41
And I really appreciate that even though you're having tiny colds, you're here to deliver and sharing knowledge
1:12:50
So I am sure our audience understand that and appreciate it as much as I do and for sure H as well
1:13:01
Thank you. Yes, yes. It seems that we... Yeah, here we actually have a question here
1:13:10
So first we have a comment. I have a comment here from Ashish, who says
1:13:15
excellent day, my session today, thanks a ton. And he also has a comment there
1:13:21
I have read somewhere that Azure Data Factory is an expensive service
1:13:26
What are your thoughts on this? Actually, I think it's a very good service
1:13:31
and I find it's a cost-effective service. But everything in a cloud, in any cloud, it depends on how we are using
1:13:41
So if we're not using carefully, then it's costly. Otherwise, it is cheaper
1:13:46
Yeah, so I find it's cheaper and cost-effective service. okay otherwise i think we don't have any more questions right okay then i can quickly show
1:13:58
c-sharp code if anyone would like to uh i think i think we're out of time right now but uh you
1:14:07
can that's fine i can share later yeah and then we also have a 15 20 minutes uh
1:14:14
Zoom meeting after the session. Well, for those speakers who want to join and ask more questions with you or with us today
1:14:26
And we really appreciate the great session. And I have a question, but I think I'll reserve it for our Zoom meeting
1:14:37
So we respect others' time as well. But to our listeners today, audience, our community, please, let me just show you the Azure Heroes Badger, Learner Badger
1:14:54
So Microsoft Azure Heroes is a digital badger that Microsoft provides to those that are learning and increasing their technical knowledge about Azure
1:15:13
So feel free to scan the QR code to get your learner badge and use the tag Azure Heroes if you want to share it on your social media
1:15:23
And I did share the Bitly link for our Zoom meeting. So feel free to join us as well on this link
1:15:35
Do you have any final words, Håkan? No, yes. Thank you so much
1:15:40
It was a very extensive demo with many moving parts. So I really appreciate that you took the time here
1:15:47
And especially since you have a bit of a cold here, Alta
1:15:53
so that's appreciated yeah yeah thank you and thank you all for attending the session and
1:15:59
very apology for these irritating things but yeah no we do everything to share knowledge and i really
1:16:07
we really appreciate that so uh your time uh with us all right so it's time to say goodbye again
1:16:15
okay right it goes so quick the one r but uh looking forward to meet everyone thank you so
1:16:22
much for your time and looking forward to meet you in our after
1:16:26
session, Fika. Otherwise, we wish everyone a happy weekend, especially in Sweden. I know we have the first event
1:16:34
as well to those that are celebrating it. So enjoy your weekend with your family and
1:16:40
friends and a great weekend from me and Håkan and Asher user group
1:16:44
Sweden and also from Alva. See you later. Bye-bye. Bye-bye. Thank you