How to implement support for regular expressions in Azure Data Factory ?

In this article, we will look at how to add regular expression support in Azure Data Factory, going beyond its standard string functions. The solution ? In short, with some extra effort to set up an Azure Function, we will show how to achieve a refined and flexible approach.

What is Azure Data Factory ?

Azure Data Factory (ADF) is a cloud-based data integration service provided by Microsoft Azure. It allows users to create, schedule, and orchestrate data workflows, enabling the extraction, transformation, and loading (ETL) of data from various sources into destinations such as databases, data lakes, and data warehouses.

Azure Data Factory is indeed a robust platform that simplifies the development of workflows involving datastores. Our focus though is not to extensively explore this platform but rather to explore ways to extend its capabilities and harness its full potential.

How can string matching be performed in Azure Data Factory ?

Despite its capabilities, Azure Data Factory occasionally encounters limitations in string manipulation, making it challenging to execute certain scenarios accurately. For instance, envision a use case where specific files need to be transferred from one blob storage to another—say, files that begin with "IT", include the term "BANK", and end with "TXT".

In the subsequent discussion, we will assume that the file name is provided, which can be obtained through means such as a storage event trigger. In the context of Azure Data Factory (ADF), this implies that the file name is treated as a parameter within the pipeline.

Hence, we can utilize a Filter activity and attempt to apply the aforementioned specific rules using built-in string functions.

In such a scenario, the existing approach may suffice if the requirement remains relatively straightforward. However, consider a scenario where we also need files ending with "csv" or "CSV" in a case-insensitive manner.

What are regular expressions ?

Regular expressions are sequences of characters that form a search pattern. They are used to match strings or substrings within text, enabling flexible search, replace, and validation operations. Regular expressions allow us to specify complex text patterns using special characters and symbols. They are powerful tools commonly used in programming, data processing, validation, and text editing. For instance, regex patterns can verify email formats, locate specific patterns in logs, or extract phone numbers from text files.

As an example, implementing the previous conditions using regular expressions would be relatively straightforward. Here's an example written in C# code.

1var r = new Regex("IT(.*)BANK(.*).txt");
2var res = r.IsMatch("IT_123f_BANK_abcd.txt"); // returns true

As demonstrated, the process is straightforward (even though the provided example is overly simplistic), and this feature is commonly offered by most programming languages for convenient string processing. However, Azure Data Factory does not support the use of regular expressions, necessitating the exploration of alternative strategies to achieve the desired outcome.

Using Azure Functions to implement regular expressions

In the scenario above, we utilized C# code to illustrate the ease of handling strings with regular expressions. Consequently, a natural question arises: could we incorporate C# code (and use regular expressions) into Azure Data Factory to leverage it for string manipulation ? To put it another way, is it possible to have the best of both worlds ?

The answer is affirmative, achieved through Azure Functions.

As shown in the image below, Azure Data Factory natively supports the integration of Azure Functions within a pipeline.

Conclusion: albeit with the added complexity of incorporating an Azure Function into the infrastructure, we could effectively manage regular expressions. But how can we utilize concretely this functionality to accomplish our objective ?

Creating an Azure Function

The initial step is to add an Azure Function. While we won't delve into the specifics here, this can be accomplished either manually or, preferably, through the use of tools such as Bicep, Terraform, or another form of infrastructure as code. For the purpose of this discussion, we will assume that the function has been created.

Adding the MatchNameWithPattern function

We'll incorporate the following code into the Azure Function. This code performs a simple task: it verifies whether a string conforms to a specified regular expression pattern. Additionally, it's important to mention that this function is invoked by an HTTP request using the POST method.

 1[FunctionName("MatchNameWithPattern")]
 2public async Task<IActionResult> MatchNameWithPattern([HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = null) HttpRequest req, ILogger log)
 3{
 4    var data = await new StreamReader(req.Body).ReadToEndAsync();
 5    var regexModel = JsonConvert.DeserializeObject<RegexModel>(data);
 6	
 7    var r = new Regex(regexModel.Pattern);
 8    var res = r.IsMatch(regexModel.Name);
 9	
10    return new OkObjectResult(res);
11}
12
13public class RegexModel
14{
15    public string Pattern { get; set; }
16	
17    public string Name { get; set; }
18}

Using the Azure Function in the pipeline

Now, we can integrate this Azure Function into the pipeline.

• Add a linked service and choose Azure Function

  

• Configure the linked service

  

• Add an Azure Function activity in the pipeline

  

  In this context, we make a POST request to our Azure Function, providing the necessary parameters. We ensure that the request aligns with the signature of the function we previously created.

• Add an If Condition activity

The If Condition activity will allow us to proceed with the pipeline or halt its execution based on the result obtained from the Azure Function.

Final thoughts

In this post, we outlined the process of configuring regular expressions in Azure Data Factory using an Azure Function. Our solution effectively addresses the issue, though it requires a more intricate infrastructure setup. However, it's well worth the effort, especially since an Azure Function is relatively easy to implement. This development can be coded once and then reused by other teams.