ADF Tutorial - Expression Builder

Azure Data Factory Tutorial – Add Dynamic Content (Expression Builder) – Lesson 6

by

In this lesson 6 of our Azure Data Factory Tutorial for beginners series I will take you through how to add the dynamic content in the ADF. Add Dynamic Content using the expression builder helps to provide the dynamic values to the properties of the various components of the Azure Data Factory. I will also take you through step by step processes of using the expression builder along with using multiple functions like, concat, split, equals and many more. We will also see how you can access the pipeline parameters’, variables using the dynamic content. You can also pass the output of one activity as input to the next activity using the expression builder. Besides these I also share my own experience of using the expression builder in the most efficient way. In case you haven’t gone through my first Lesson 1 of Azure Data Factory tutorial,  I would highly recommend going to lesson 1 to understand the Azure Data factory from scratch because we have covered quick concepts about ADF and how to create your first ADF account. Let’s dive into the tutorial now.

Contents

How to access the Add Dynamic Content or Expression Builder in Azure Data Factory?

Go to any text box or text area property under any activity details. Moment you select the field you will get ‘Add Dynamic Content’ link below it. Just click on it, this will open up the expression builder

For example in the copy activity go to the source tab. Now in the source tab let’s say I select any SQL dataset. Moment I click on the query timeout textbox, just below the textbox you will see the link ‘Add Dynamic Content’. You can click on the link and it will open up the expression builder blade for you. See the screenshot below:

Add Dynamic Content Function
Figure 1: Add Dynamic Content Function

What is the Add Dynamic Content in Azure Data Factory

‘Add Dynamic Content’ is the way to provide the information in the expression format. Sometimes you don’t want to hardcode certain information within the pipeline in ADF hence dynamic content is the way to pass the dynamic value.

It can be consist of these:

  1. System Variable
  2. Functions
  3. Expression

What is the System Variable in the Azure Data Factory?

System variable are the way to hold the certain specific properties of the items like pipeline, triggers within the ADF. For example every pipeline could have pipeline run id, pipeline name etc. When you want to access these information within the pipeline you can use the system variable to access such properties.

Types of the System variable:

  1. Pipeline scope
  2. Schedule trigger scope
  3. Tumbling window trigger scope
  4. Storage event trigger scope
  5. Custom event trigger scope

How to get the pipeline name in the Azure Data Factory

You can get the pipeline name using the system variable within the dynamic content builder.

@pipeline().Pipeline

How to get the Data factory name in the Azure Data Factory

You can get the data factory name using the system variable within the dynamic content builder.

@pipeline().DataFactory

How to get the Run ID of specific pipeline execution run in the Azure Data Factory

You can get the run id of the specific pipeline execution run using the system variable within the dynamic content builder.

@pipeline().RunId

How to get the type of the trigger that invoke the pipeline in the Azure Data Factory

You can get the type of the trigger that invoke the pipeline using the system variable within the dynamic content builder. 

@pipeline().TriggerType

How to get the ID of the trigger that invoke the pipeline in the Azure Data Factory

You can get the ID of the trigger that invoke the pipeline using the system variable within the dynamic content builder. 

@pipeline().TriggerId

How to get the Name of the trigger that invoke the pipeline in the Azure Data Factory

You can get the name of the trigger that invoke the pipeline using the system variable within the dynamic content builder. 

@pipeline().TriggerName

How to get the time of the trigger run that invoke the pipeline in the Azure Data Factory

You can get the time of the trigger run that invoke the pipeline using the system variable within the dynamic content builder. 

@pipeline().TriggerTime

Understand the Functions in the expression builder of ADF

There are many useful function given by the Azure Data Factory to create or build the expression. These functions ranging from various string functions like concat, replace, split to logical functions like and or not.

There are basically six different types of the function categories we have in ADF

  1. String Functions
  2. Conversion Functions
  3. Date Functions
  4. Logical Functions
  5. Math Functions
  6. Collection Functions

How to concat two string or more strings within the Azure Data Factory dynamic content

You can use the concat function of the expression function to concatenate two or more strings in the ADF. For example

@concat('Hello', 'Hi')

-- Result HelloHi

How to concat single quote within the Azure Data Factory dynamic content

You can use the concat function of the expression function to concatenate two or more strings in the ADF. For concatenating the single quote use the one more single quote as the skip character. For example

@concat('Hello from ', 'Azurelib', ''s', 'website')

-- Result Hello from Azurelib's website

How to Check whether a string ends with a specific substring in the Azure Data Factory dynamic content

You can use the endsWith function of the expression function to check whether a string ends with a specific substring in the ADF. It takes two parameter first the string itself and second the substring. For example

- It returns result as true or false in the Boolean format
@endsWith('Hello from',  'from')

-- Result true

How to generate GUID within the Azure Data Factory dynamic content

You can use the guid function of the expression function to Generate a globally unique identifier (GUID) in the ADF. For example

@guid()

-- Result (c2eadasdd-434sdd-409dsa6-912c-ddasd38ce)

How to check indexOf substring within the Azure Data Factory dynamic content

You can use the indexOf function of the string expression function to get the index of the first occurrence of substring in the ADF. It take two parameters first is the actual string and second substring. For example

@indexOf('azurelib.com', 'com')

-- Result 9

How to check last indexOf substring within the Azure Data Factory dynamic content

You can use the lastIndexOf function of the string expression function to get the index of the last occurrence of substring in the ADF. It take two parameters first is the actual string and second substring. For example 

@indexOf('azurelib.com', 'com')

-- Result 9

How to Replace a substring within the Azure Data Factory dynamic content

You can use the replace function of the expression function to Replace a substring with the specified string in the ADF. It takes three parameters. First the actual string, second old string and third is the new string. For example

@replace('My old website', 'old', 'new')

-- Result My new website

How to split string into multiple token within the Azure Data Factory dynamic content

You can use the split function of the expression function to split the string into multiple substring token and return it as the array. For example

@split('st1|st2|st3|st4', '|')

-- Result ['st1,st2,st3,st4']

How to check string start with specific substring within the Azure Data Factory dynamic content

You can use the startsWith function of the expression function to check whether string starts with specific substring in the ADF. It will return the boolean true or false. For example

@startsWith('hello from Azurelib', 'hello')

-- Result true

How to get the substring within the Azure Data Factory dynamic content

You can use the substring function of the expression function to concatenate two or more strings in the ADF. It contains following 3 parameter:

  1. Actual string
  2. Start Index (it starts with 0)
  3. Length

For example:

@substring('Hello from Azurelib', 7.,4)

-- Result: from

How to convert string to upper case within the Azure Data Factory dynamic content

You can use the toUpper function of the expression function to convert string into uppercase string in the ADF. For example

@toUpper('hello from Azurelib')

-- Result HELLO FROM AZURELIB

How to convert string to lower case within the Azure Data Factory dynamic content

You can use the toLower function of the expression function to convert string into lowercase string in the ADF. For example

@toLower('hello from Azurelib')

-- Result hello from azurelib

How to trim or remove whitespace of string within the Azure Data Factory dynamic content

You can use the trim function of the expression function to Remove leading and trailing whitespace from a string in the ADF. It will return the new string. For example

@trim('        hello from Azurelib            ')

-- Result hello from Azurelib

How to use contains function within the Azure Data Factory dynamic content

You can use the contains function of the expression function to check whether string contains specific substring within in the ADF. It will return true or false. For example

@contains('hello from Azurelib', 'from')

-- Result true

How to convert string into int within the Azure Data Factory dynamic content

You can use the int function of the expression function to convert string into integer in the ADF. It takes inout as string an return int as output. For example

@int('101')

-- Result 101

How to convert string into float within the Azure Data Factory dynamic content

You can use the float function of the expression function to convert string into float in the ADF. It takes input as string and return float as output. For example

@float('101.000')

-- Result 101.000

How to convert string into boolean within the Azure Data Factory dynamic content

You can use the bool function of the expression function to convert string into Boolean (true, false ) in the ADF. It takes input as string an return Boolean as output. For example

@bool('true')

-- Result true

How to convert string into JSON object within the Azure Data Factory dynamic content

You can use the json function of the expression function to convert string into json in the ADF. It takes input as string an return json object as output. For example

@json('{"name": "Deepka Goyal"}')

-- Result {
  "name": "Deepak Goyal"
}

How to convert string into array object within the Azure Data Factory dynamic content

You can use the array function of the expression function to convert string into array in the ADF. It takes input as string an return array as output. For example

@array('101')

-- Result [101]

How to use coalesce function within the Azure Data Factory dynamic content

You can pass multiple values within the coalesce function and it will return first not null value out of it. For example

@coalesce(null, true, false)

-- Result true

How to use if condition function within the Azure Data Factory dynamic content

You can Check whether an expression is true or false using the if function and it return the value correspondingly. For example

@if (equals(1,2), 'They are equal', 'They are not equal')

-- Result They are not equal

Microsoft Azure Data Factory official Link

Final Thoughts

By this we have reached the last section of our Lesson 6 of Azure data factory tutorial for beginners. In this lesson we have seen how to use the add dynamic content or the expression builder function of the Azure data factory. I have also explained and show you about all the various frequently used string function in the ADF. We have also gone through the various system variables as well.  

In the next lesson we will go deeper into the Azure Data factory and learn new concepts with some exciting practical.

Please share your feedback and your comments. In case you have any questions or query please drop them in the comment box below and I will try to answer them as early as possible.

Join Telegram Group
Download Free Azure Videos
Connect on LinkedIn

DeepakGoyal
Deepak Goyal

Deepak Goyal is certified Azure Cloud Solution Architect. He is having around decade and half experience in designing, developing and managing enterprise cloud solutions. He is also Big data certified professional and passionate cloud advocate.