Expressions and functions in Azure Data Factory and Azure Synapse Analytics

APPLIES TO: Azure Data Factory Azure Synapse Analytics

This article provides details about expressions and functions supported by Azure Data Factory and Azure Synapse Analytics.

Expressions

JSON values in the definition can be literal or expressions that are evaluated at runtime. For example:

"name": "value"

or

"name": "@pipeline().parameters.password"

Expressions can appear anywhere in a JSON string value and always result in another JSON value. If a JSON value is an expression, the body of the expression is extracted by removing the at-sign (@). If a literal string is needed that starts with @, it must be escaped by using @@. The following examples show how expressions are evaluated.

Expressions can also appear inside strings, using a feature called string interpolation where expressions are wrapped in @{ ... }. For example: "name" : "First Name: @{pipeline().parameters.firstName} Last Name: @{pipeline().parameters.lastName}"

Using string interpolation, the result is always a string. Say I have defined myNumber as 42 and myString as foo:

In the control flow activities like ForEach activity, you can provide an array to be iterated over for the property items and use @item() to iterate over a single enumeration in ForEach activity. For example, if items is an array: [1, 2, 3], @item() returns 1 in the first iteration, 2 in the second iteration, and 3 in the third iteration. You can also use @range(0,10) like expression to iterate ten times starting with 0 ending with 9.

You can use @activity('activity name') to capture output of activity and make decisions. Consider a web activity called Web1. For placing the output of the first activity in the body of the second, the expression generally looks like: @activity('Web1').output or @activity('Web1').output.data or something similar depending upon what the output of the first activity looks like.

Examples

Complex expression example

The below example shows a complex example that references a deep sub-field of activity output. To reference a pipeline parameter that evaluates to a sub-field, use [] syntax instead of dot(.) operator (as in case of subfield1 and subfield2), as part of an activity output.

@activity('*activityName*').output.*subfield1*.*subfield2*[pipeline().parameters.*subfield3*].*subfield4*

Creating files dynamically and naming them is common pattern. Let us explore few dynamic file naming examples.

1. Append Date to a filename: @concat('Test_', formatDateTime(utcnow(), 'yyyy-dd-MM'))

2. Append DateTime in customer timezone : @concat('Test_', convertFromUtc(utcnow(), 'Pacific Standard Time'))

3. Append Trigger Time : @concat('Test_', pipeline().TriggerTime)

4. Output a custom filename in a Mapping Data Flow when outputting to a single file with date : 'Test_' + toString(currentDate()) + '.csv'

In above cases, 4 dynamic filenames are created starting with Test_.

Dynamic content editor

Dynamic content editor automatically escapes characters in your content when you finish editing. For example, the following content in content editor is a string interpolation with two expression functions.

{ 
  "type": "@{if(equals(1, 2), 'Blob', 'Table' )}",
  "name": "@{toUpper('myData')}"
}

Dynamic content editor converts above content to expression "{ \n \"type\": \"@{if(equals(1, 2), 'Blob', 'Table' )}\",\n \"name\": \"@{toUpper('myData')}\"\n}". The result of this expression is a JSON format string showed below.

{
  "type": "Table",
  "name": "MYDATA"
}

A dataset with a parameter

In the following example, the BlobDataset takes a parameter named path. Its value is used to set a value for the folderPath property by using the expression: dataset().path.

{
    "name": "BlobDataset",
    "properties": {
        "type": "AzureBlob",
        "typeProperties": {
            "folderPath": "@dataset().path"
        },
        "linkedServiceName": {
            "referenceName": "AzureStorageLinkedService",
            "type": "LinkedServiceReference"
        },
        "parameters": {
            "path": {
                "type": "String"
            }
        }
    }
}

A pipeline with a parameter

In the following example, the pipeline takes inputPath and outputPath parameters. The path for the parameterized blob dataset is set by using values of these parameters. The syntax used here is: pipeline().parameters.parametername.

{
    "name": "Adfv2QuickStartPipeline",
    "properties": {
        "activities": [
            {
                "name": "CopyFromBlobToBlob",
                "type": "Copy",
                "inputs": [
                    {
                        "referenceName": "BlobDataset",
                        "parameters": {
                            "path": "@pipeline().parameters.inputPath"
                        },
                        "type": "DatasetReference"
                    }
                ],
                "outputs": [
                    {
                        "referenceName": "BlobDataset",
                        "parameters": {
                            "path": "@pipeline().parameters.outputPath"
                        },
                        "type": "DatasetReference"
                    }
                ],
                "typeProperties": {
                    "source": {
                        "type": "BlobSource"
                    },
                    "sink": {
                        "type": "BlobSink"
                    }
                }
            }
        ],
        "parameters": {
            "inputPath": {
                "type": "String"
            },
            "outputPath": {
                "type": "String"
            }
        }
    }
}

Replacing special characters

Dynamic content editor automatically escapes characters like double quote, backslash in your content when you finish editing. This causes trouble if you want to replace line feed or tab by using \n, \t in replace() function. You can edit your dynamic content in code view to remove the extra \ in the expression, or you can follow below steps to replace special characters using expression language:

1. URL encoding against the original string value
2. Replace URL encoded string, for example, line feed (%0A), carriage return(%0D), horizontal tab(%09).
3. URL decoding

For example, variable companyName with a newline character in its value, expression @uriComponentToString(replace(uriComponent(variables('companyName')), '%0A', '')) can remove the newline character.

Contoso-
Corporation

Escaping single quote character

Expression functions in pipelines use the single quote (') to surround string value parameters. Use two consecutive single quote characters within a pipeline string expression to include a single quote. Here's an example: expression @concat('Here is a double quote character: ". ', 'And here is a single quote character all within the same string: ''.') will return the following result:

Here is a double quote character: ". And here is a single quote character all within the same string: '.

However, in data flow expressions, this syntax isn't supported. Instead, data flow expressions can be surrounded by either single or double quotes. Enclose text requiring single quotes within double quotes, and text requiring double quotes within single quotes, within string functions. If you require a string containing both single and double quotes, you can use concat() to merge two substrings that each contain either single quotes or double quotes. The data flow equivalent of the previous pipeline expression example would be concat('Here is a double quote character: ". ', "And here is a single quote character all within the same string: '."). In a data flow, that expression will return the same result as the previous example for pipeline expressions.

Tutorial

This tutorial walks you through how to pass parameters between a pipeline and activity as well as between the activities. The tutorial specifically demonstrates steps for an Azure Data Factory although steps for a Synapse workspace are nearly equivalent but with a slightly different user interface.

Functions

You can call functions within expressions. The following sections provide information about the functions that can be used in an expression.

Date functions

String functions

To work with strings, you can use these string functions and also some collection functions. String functions work only on strings.

Collection functions

To work with collections, generally arrays, strings, and sometimes, dictionaries, you can use these collection functions.

Logical functions

These functions are useful inside conditions, they can be used to evaluate any type of logic.

Conversion functions

These functions are used to convert between each of the native types in the language:

• string
• integer
• float
• boolean
• arrays
• dictionaries

Math functions

These functions can be used for either types of numbers: integers and floats.

Function reference

This section lists all the available functions in alphabetical order.

add

Return the result from adding two numbers.

add(<summand_1>, <summand_2>)

Example

This example adds the specified numbers:

add(1, 1.5)

And returns this result: 2.5

addDays

Add a number of days to a timestamp.

addDays('<timestamp>', <days>, '<format>'?)

Example 1

This example adds 10 days to the specified timestamp:

addDays('2018-03-15T13:00:00Z', 10)

And returns this result: "2018-03-25T00:00:0000000Z"

Example 2

This example subtracts five days from the specified timestamp:

addDays('2018-03-15T00:00:00Z', -5)

And returns this result: "2018-03-10T00:00:0000000Z"

addHours

Add a number of hours to a timestamp.

addHours('<timestamp>', <hours>, '<format>'?)

Example 1

This example adds 10 hours to the specified timestamp:

addHours('2018-03-15T00:00:00Z', 10)

And returns this result: "2018-03-15T10:00:0000000Z"

Example 2

This example subtracts five hours from the specified timestamp:

addHours('2018-03-15T15:00:00Z', -5)

And returns this result: "2018-03-15T10:00:0000000Z"

addMinutes

Add a number of minutes to a timestamp.

addMinutes('<timestamp>', <minutes>, '<format>'?)

Example 1

This example adds 10 minutes to the specified timestamp:

addMinutes('2018-03-15T00:10:00Z', 10)

And returns this result: "2018-03-15T00:20:00.0000000Z"

Example 2

This example subtracts five minutes from the specified timestamp:

addMinutes('2018-03-15T00:20:00Z', -5)

And returns this result: "2018-03-15T00:15:00.0000000Z"

addSeconds

Add a number of seconds to a timestamp.

addSeconds('<timestamp>', <seconds>, '<format>'?)