You're reading from Azure Data Factory Cookbook Build ETL, Hybrid ETL, and ELT pipelines using ADF, Synapse Analytics, Fabric and Databricks

Product type Paperback

Published in Feb 2024

Publisher Packt

ISBN-13 9781803246598

Length 532 pages

Edition 2nd Edition

Tools

Azure

Concepts

Data Engineering

Authors (4):

Tonya Chernyshova

Xenia Ireton

Dmitry Foshin

Dmitry Anoshin

View More author details

Table of Contents (15) Chapters

Preface

1. Getting Started with ADF

2. Orchestration and Control Flow FREE CHAPTER

3. Setting Up Synapse Analytics

4. Working with Data Lake and Spark Pools

5. Working with Big Data and Databricks

6. Data Migration – Azure Data Factory and Other Cloud Services

7. Extending Azure Data Factory with Logic Apps and Azure Functions

8. Microsoft Fabric and Power BI, Azure ML, and Cognitive Services

9. Managing Deployment Processes with Azure DevOps

10. Monitoring and Troubleshooting Data Pipelines

11. Working with Azure Data Explorer

12. The Best Practices of Working with ADF

13. Other Books You May Enjoy

14. Index

Using the Lookup, Web, and Execute Pipeline activities

In this recipe, we shall implement error-handling logic for our pipeline similar to the previous recipe, but with a more sophisticated design: we shall isolate the error-handling flow in its own pipeline. Our main parent pipeline will then call the child pipeline. This recipe also introduces three very useful activities to the user: Lookup, Web, and Execute Pipeline. The recipe will illustrate how to retrieve information from an Azure SQL table and how to invoke other Azure services from the pipeline.

Getting ready

We shall be using all the Azure services mentioned in the Technical requirements section at the beginning of the chapter. In addition, this recipe requires a table to store the email addresses of the status email recipients. Please refer to the Technical requirements section for the table creation scripts and instructions.

We shall be building a pipeline that sends an email in the case of failure. There is no activity in ADF capable of sending emails, so we shall be using the Azure Logic Apps service. Follow these steps to create an instance of this service:

In the Azure portal, look for Logic Apps in the Azure services. Then, use the Add button to create a new logic app.
Name your logic app ADF-Email-Logic-App and fill in the Subscription, Resource Group, and Region information fields.
It’s better to choose Consumption plan type as we’re not going to run this logic app regularly. Click on Create and wait until your logic app is deployed. Then, click on Go to Resource.
In the Logic App Designer, select the When a HTTP request is received trigger:

Figure 2.27: HTTP trigger

In the displayed tile, click on Use sample payload to generate schema, and use the following code block:
```
{
“subject”: “<subject of the email message>”,
“messageBody”: “<body of the email message >”,
“emailAddress”: “<email-address>”
}
```
Enter the code in the box as shown in the following figure:

Figure 2.28: Configuring a logic app – The capture message body

Click on the + Next Step button and choose the email service that you want to use to send the notification emails. For the purposes of this tutorial, we shall use Gmail.

NOTE

Even though we use Gmail for the purposes of this tutorial, you can also send emails using Office 365 Outlook or Outlook.com. In the See also section of this recipe, we include a link to a tutorial on how to send emails using those providers.

Select Gmail from the list of services and Send Email from Actions. Log in with your account credentials:

Figure 2.29: Configuring a logic app – Specifying an email service

From the Add new parameter dropdown, check the Subject and Body checkboxes:

Figure 2.30: Configuring a logic app – specifying the Body, Subject, and Recipient fields

Place your cursor inside the To text field and enter @{triggerBody()['emailAddress']}.
In a similar way, enter @{triggerBody()['subject']} in the Subject text field.
Finally, in the Body text box, enter @{triggerBody()['messageBody']}. You should end up with something similar to the following screenshot:

Figure 2.31: Configuring a logic app – Specifying the To, Subject, and Body values

Save your logic app. In the first tile, you should see that HTTP POST URL was populated. This is the URL we’ll use to invoke this logic app from the Data Factory pipeline.

How to do it…

First, we shall create the child pipeline to retrieve the email addresses of the email recipients and send the status email:

Create a new pipeline and name it pl_orchestration_recipe_5_child.
From the Activities pane, select a Lookup activity and add it to the pipeline canvas. Configure it in the following way:
1. In the General tab, change the activity name to Get Email Recipients.
2. In the Settings tab, select AzureSQLTables as the value for Source dataset, and specify EmailRecipients for tableName.
3. Also, in the Settings tab, select the Use Query radio button and enter SELECT * FROM [dbo].[EmailRecipient] into the text box. Make sure to uncheck the First row only checkbox at the bottom. Your Settings tab should look similar to the following figure:
Figure 2.32: The Get Email Recipients activity settings

Next, add a ForEach activity to the canvas, connect it to Get Email Recipients activity and configure it in the following way:
In the Settings tab, enter @activity('Get Email Recipients').output.value into the Items textbox.

Click on the pencil icon within the ForEach activity. This will open a new canvas. Add a Web activity onto this canvas.
We shall now configure the Web activity. First, go to the General tab, and rename it Send Email. Then, in the URL text field, paste the URL for the logic app (which you created in the Getting ready section):
- In the Method textbox, select POST.
- In the Headers section, click on the New button to add a header. Enter Content-Type into the Name text box and application/json into the Value textbox.
- In the Body text box, enter the following text (be sure to copy the quotes accurately):
```
@json(concat(‘{“emailAddress”: “‘, item().emailAddress, ‘”, “subject”: “ADF Pipeline Failure”, “messageBody”: “ADF Pipeline Failed”}’))
```
Your Settings tab should look similar to Figure 2.33:

Figure 2.33: The Send Email activity settings

Run this pipeline in Debug mode and verify that it works. You should have some test email addresses in the EmailRecipients table in order to test your pipeline. You can also verify that the email was sent out by going to the ADF-Email-LogicApp UI in the Azure portal and examining the run in the Overview pane:

Figure 2.34: Logic Apps portal view

We are ready to design the parent pipeline, which will invoke the child pipeline we just tested. For this, clone the pipeline we designed in the Chaining and branching activities within your pipeline recipe. Rename your clone pl_orchestration_recipe_5_parent.
In this pipeline, delete the On Failure Stored Procedure activity, and instead add an Execute Pipeline activity to the canvas. Connect it to the red cross in the Copy From Blob to Azure SQL activity.
Configure the Execute Pipeline activity as follows:
- In the General tab, change the name to Send Email On Failure.
- In the Settings tab, specify the name of the invoked pipeline as pl_orchestration_recipe_5_child.
The parent pipeline should already be configured with the incorrect table name in the Copy activity sink (we deliberately misconfigured it in order to test the On Failure flow). Verify that this is still the case and run the pipeline in Debug mode:

Figure 2.35: Parent pipeline after modifying the On Failure activity

Verify that the email was sent to the recipients.
Publish your changes to save them.

How it works…

In this recipe, we introduced the concept of parent and child pipelines and used the pipeline hierarchy to incorporate the error-handling functionality. This technique offers several benefits:

It allows us to reuse existing pipelines.
It makes it easier to design/debug parts of the pipeline separately.
Finally, it allows users to design pipelines that contain more than 40 activities (Microsoft limits the number of activities per pipeline).

To craft the child pipeline, we started by adding a Lookup activity to retrieve a list of email recipients from the database table. This is a very common use for the Lookup activity: fetching a dataset for subsequent processing. In the configuration, we specified a query for the dataset retrieval: SELECT * from [dbo].[EmailRecipient]. We can also use a more sophisticated query to filter the email recipients, or we can retrieve all the data by selecting the Table radio button. The ability to specify a query gives users a lot of choice and flexibility in filtering a dataset or using field projections with very little effort.

The list of email recipients was processed by the ForEach activity. We encountered the ForEach activity in the previous recipe. However, inside the ForEach activity, we introduced a new kind of activity: the Web activity, which we configured to invoke a simple logic app. This illustrates the power of the Web activity: it enables the user to invoke external REST APIs without leaving the Data Factory pipeline.

There’s more…

There is another ADF activity that offers the user an option to integrate external APIs into a pipeline: the Webhook activity. It has a lot of similarities to the Web activity, with two major differences:

The Webhook activity always passes an implicit callBackUri property to the external service, along with the other parameters you specify in the request body. It expects to receive a response from the invoked web application. If the response is not received within the configurable timeout period, the Webhook activity fails. The Web activity does not have a callBackUri property, and, while it does have a timeout period, it is not configurable and is limited to 1 minute.
This feature of the Webhook activity can be used to control the execution flow of the pipeline – for example, to wait for user input into a web form before proceeding with further steps.

The Web activity allows users to pass linked services and datasets. This can be used for data movement to a remote endpoint. The Webhook activity does not offer this capability.