Azure Synapse Analytics pipeline expression to create dynamic URL

A URL can be broken up into multiple parts. 1 If you break up the URL into its parts, how do you put all the pieces together to form the full URL in Azure Synapse Analytics (#AzureSynapse) pipelines or Azure Data Factory? This post gives an example of how to create a valid URL from parameters representing parts of the URL.

When connecting to a web resource in Azure Synapse Analytics pipeline, you need to create two objects: HTTP linked service and an HTTP dataset. The linked service (which you can access using Azure Synapse Studio, clicking the Manage tab) has the basic information for connecting to the web page. An HTTP dataset (called “Integration dataset” in Azure Synapse Analytics) provides the detailed path for connecting to web resource.

The URL to the specific resource that you are trying to access is set up in the HTTP dataset. You can find the HTTP dataset in Azure Synapse Studio by clicking the Data icon and then select the Linked tab. The datasets used in Azure Synapse Analytics pipelines are listed under the “Integration Datasets” section.

You select the HTTP linked service that will connect to your web resource. Then you add the Relative URL. When using a dynamic Relative URL, you will need to do the following steps:

1. Add parameters to the dataset
2. Use the parameters in the expression for your properties
3. In the pipeline, set the parameter values for the dataset

These are the parameters that I used:

Note the QueryString has no Default value. The word “Value” uses a faded font, and clicking in the Default value box puts the cursor at the beginning.

I separate the Path parameter from File. The query string is a basically a name value parameter list following the pattern “Name=Value” with each parameter separated by ampersand (&).

I need a formula to created the URL. The Path needs to be separated from the File by a forward slash (/). The query string needs to be separated from the rest of the URL by a question mark. I don’t want to add a forward slash if either the Path or File is empty. I don’t want to add a question mark if there is no query string.

My first try was to just use the “concat” function.2 The following expression simply concatenates the Path, File and QueryString parameters with forward slash and question mark separating them.

@concat(dataset().Path
      , '/'
      , dataset().File
      , '?'
      , dataset().QueryString
      )

The problem with this expression is that if Path or File are empty, the forward slash still shows. And if the QueryString parameter is empty, the question mark still shows. This doesn’t work.

I then used an if statement between Path and File and before QueryString. If either Path or File are empty then don’t put a forward slash, otherwise put a forward slash. If the QueryString is empty, don’t add a question mark otherwise do. My first attempt was to simply test the length of each of the parameters.3

@concat(dataset().Path
      , if(or(
           equals(length(dataset().Path),0)
          , equals(length(dataset().File),0)
          ),'','/')
      , dataset().File
      , if(
              equals(length(dataset().QueryString),0)
              , '','?')
     , dataset().QueryString
      )

When I ran this I got an error. The value in QueryString is null, not empty string. The length function errors when null is the argument.

Solution

I made a slight modification to the expression. If you call the concat function with an empty string and a null value, the result is empty string. The length function is perfectly happy evaluating an empty string and returns a length of 0.

@concat(dataset().Path
      , if(or(
           equals(length(concat('',dataset().Path)),0)
          , equals(length(concat('',dataset().File)),0)
          ),'','/')
      , dataset().File
      , if(
              equals(length(concat('',dataset().QueryString)),0)
              , '','?')
     , dataset().QueryString
      )

This expression gives me a flexible tool for creating a full URL from its parts.

1. I found different ways to break up the URL. The components of a URL – IBM Documentation suggests 3 or 4. The 5 Basic Parts of a URL: A Short Guide (hubspot.com) suggests 5 (they leave out the query string). 9 Parts of a URL You Should Know. if you plan on building a website | by Joseph Pyram | Medium suggests 9.

2. See Expression and functions – Azure Data Factory & Azure Synapse | Microsoft Docs for information about the functions in Azure Synapse Pipeline and Azure Data Factory.

3. The expression language takes some getting used to if you come from a .Net, Python, Java, SQL background. What those languages would express using operator symbols like = + < >, this language expresses using functions. I think that the intention is clear.