Rajendra Gupta
the blob container

Azure Automation: Azure Logic Apps for face recognition and insert its data into Azure SQL Database

June 15, 2021 by

Today, we will implement an Azure Logic App for face recognition and insert its data into an Azure SQL database.

Introduction to face service

Face recognition is a modern way of implementing security measures. In general usage, most mobile phones come up with a face identification system for authorizing access. We get a CCTV camera across all public places such as Airports, social gathering places. Similarly, you get image detection techniques in Facebook where it lists out the photos similar to a person’s photo. You can use these techniques in many places such as Criminal Identification, Surveillance, Defense Services, online payment, Airport services.

Suppose you want to analyze the audience in a social gathering based on their gender, age, emotion, glasses, and facial attributes. In the below screenshot, we have two different persons. The face recognition service detects the person’s gender and age. Here, we see a rectangle for both faces detected in the image.

Introduction to face service

Image Reference: Microsoft docs

Azure provides cognitive services with embedded facial recognition in a seamless user experience. Usually, these image identification system requires you to have machine learning expertise. Azure image service provides features such as face detection, person identification, and capturing personal emotions such as sadness, happiness, facial attributes, and grouping images.

Before we implement the Azure logic apps for face recognition, browse to the Azure face service URL and upload an image. It detects the image (blue rectangle) and returns image attributes in JSON format.

Azure logic apps for face recognition

Similarly, in an image with multiple persons, it detects all possible faces with their attributes. You can hover the mouse over the image and in the tooltip, you get necessary details such as Gender, Age, Glasses, and Emotion.

detects all possible faces

In this article, we require to do the following tasks,

  • Upload the image in the Azure Storage Container
  • Use Azure Cognitive Services for face recognition
  • Import image attribute data for each identified person using the face API of Azure cognitive service into the Azure SQL Database
  • Validate image attributes using Azure face AI (web portal) and Azure database data

Steps to implement Azure Automation using an Azure Logic App for face recognition and to insert image data into Azure SQL Database

You can use the following steps for configuring the Azure logic app based on the scenario defined earlier.

Necessary information about Azure Logic App

We assume that you already have an understanding of Azure Logic Apps and workflow configuration. You can refer to the TOC at the bottom for reference purposes.

Azure Storage Container

You have an Azure Storage container for storing the image blobs. I already have configured the storage containers. Here, we use the Output folder for storing our source image files.

Azure Storage Container - Azure Logic App

Open the container and upload an image from your local system. We will examine these image properties using the Azure Cognitive Service.

upload an image

Azure Cognitive Service

To work with face analysis, we require to create an Azure Cognitive Service account. In the Azure portal, navigate to Cognitive Service and search for Face AI service in the marketplace.

Azure Cognitive Service

In the Create Face app, enter the subscription, resource groups, region, name and the pricing tier. In this article, I use Free F0 (20 calls per Minute, 30 k calls per Month) for illustration purposes.

enter the subscription, resource groups

In the cognitive service app, note-down the access key and endpoint as shown below.

cognitive service app

Create a table to store face attributes in the Azure SQL Database

In this article, we stored image analysis data in an Azure SQL Database table. We create [imagedata] table for storing the person Gender, Age, Beard, Glasses and Smile attribute in the below script.

If you do not have an Azure SQL Database, you can refer to the SQL Azure category for reference articles.

Create a logic app with a blank template

Logon to the Azure portal (https://portal.azure.com) with your credentials and create a logic app with a blank template. In the app designer, add a Recurrence trigger. As shown below, the trigger runs every 3 minutes.

Azure Logic App with a blank template

Create variables

In the logic app, search for Variables connector and Initialize variable.

Create variables

We initialize two variables in the logic app.

  • Blob path: It is a string type and stores the blob container URL. I stored the endpoint URL in this variable
  • File Name: In the file name variable, we do not store any values. Its values will be updated during runtime with the blob (image) display name

Blob path

List Blobs

Create a new step and add the Azure Blob Storage connector. In the folder, enter your blob container path. You can browse to a storage container similar to the below image.

List Blobs

Insert a For each block for face detection and insert record into Azure database

This central part of the logic app adds a For each control block and renames it as Detect Face Block.

Insert a For each block

Get blob content

Inside the detect face block, add a get blob content and select the dynamic content – Path. It asks the output from the previous step- here, select the value from dynamic content.

Get blob content

Set Variable

Click on Add an action inside the for each block and select Set Variables. Previously, we have set specified any value for the file name variable. Here, we set the blob’s display name in the file name variable, as shown below.

Set Variable

Detect faces (preview)

In the Choose an operation section, select the Face API.

Detect faces ( preview)

In the actions list of Face API, set the expression for the image blob. We will generate the image URL similar to the URL obtained from the Azure Storage container blob.

set the expression for the image blob

In the detect faces, set an expression for the CONCAT function.

concat(variables(‘Blob Path’),’/’,variables(‘File Name’))

CONCAT function

Add a SQL Connector for inserting a row into Azure SQL Database

In the previous step, detect faces (preview) captures image properties, metadata in the JSON format, as shown at the beginning of the article.

We already created a SQL table in the Azure database. Therefore, add a new action with the SQL connector and insert a new row. Here, we do the following configurations using the dynamic content.

  • Select an output from previous steps: Body
  • Gender: Dynamic content: Gender
  • Age: Dynamic Content: Age
  • Beard: Dynamic Content: Beard
  • Glasses: Dynamic Content: Glasses
  • Smile: Dynamic Content: Smile

Add a SQL Connector

Validations

We have implemented the face detection for the image stored in the Azure Storage container. To validate the face detection, save and run the Azure logic app. All steps are completed successfully, as shown below.

Validations

It inserts a new row into the [imagedata] table. It has the person’s gender, age, glass property along with the smile and beard score.

View person content

Let’s upload a new image into the blob container. For the demonstration, I download an image file having different persons with different emotions in a single image. You can download the image from Dreamstime.

Before we rerun the logic app, browse to Azure URL for face cognitive service, and it shows all detected faces in different color rectangles. You get face attributes in the JSON data shown below.

the blob container

Now, let’s rerun the azure logic app, and it inserts data for all persons in the image, as shown below. It inserts all faces attributes in the [imagedata] table.

inserts data for all persons in the image

Similarly, you can configure the logic app to extract other attributes such as hair properties, color, and confidence.

JSON data

Similarly, you can configure logic apps for face grouping, Person identification, finding similar faces and Face grouping using the face API of Azure cognitive services.

Conclusion

This article explored the Azure Automation with Azure Cognitive Service Face AI-based service for face recognition using the Azure Logic App and storing attributes into the Azure SQL database tables. Similarly, configure the image recognition system for your usage such as mask detection, interpreting facial attributes for your research work.

Table of contents

Azure Automation: Export Azure SQL Database to Blob Storage in a BACPAC file
Azure Automation: Create database copies of Azure SQL Database
Azure Automation: Automate Azure SQL Database indexes and statistics maintenance
Azure Automation: Automate Pause and Resume of Azure Analysis Services
Azure Automation: Automate data loading from email attachments using Azure Logic Apps
Azure Automation: Building approval-based automated workflows using Azure Logic Apps
Azure Automation: Auto-scaling Azure SQL database with Azure Logic Apps
Azure Automation: Analyzing Twitter sentiments using Azure Logic Apps
Azure Automation: Use Azure Logic Apps to import data into Azure SQL Database from Azure Blob Storage
Azure Automation: Publish LinkedIn posts and tweets automatically using Azure Logic Apps
Azure Automation: Export Azure SQL database data to CSV files using Azure Logic Apps
Azure Automation: Translate documents into different languages using Cognitive Services with Azure Logic Apps
Azure Automation: Azure Logic Apps for face recognition and insert its data into Azure SQL Database
Azure Automation: Automatic scaling Azure SQL databases based on CPU usage threshold

Rajendra Gupta
Azure, SQL Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

451 Views