Parameterize Synapse Analytics Spark Notebooks Efficiently

Azure Synapse Analytics / December 12, 2021 July 2, 2022 / analytics, integration-pipelines, json, spark, spark-notebooks, synapse-analytics

When creating pipelines in any sort of data flow to move data from an incoming source to a target location, ideally you don’t want to create single-purpose activities. These would only perform one action or set of actions on a specific set of source and target objects. E.g. take the source.sales table, filter out where the date is NULL and import into the target.FactSales table in SQL. This dataflow is only ever useful for that specific sales table. The time it took to develop and test the dataflow cannot be utilized to perform work on other datasets as well.

This makes maintenance and extensibility of the data flow more difficult and time-consuming because there is ultimately “hardcoded” activities running. To integrate any additional datasets, you need to duplicate the code and make small adjustments.

To solve this problem, you would use parameterization of course.

In Synapse Analytics Notebooks, one accomplishes this using a parameters cell.

Tagging a cell in the notebook as the “Parameters Cell” is very straightforward, as shown below. However, it still has a big drawback. If you want to extend the notebook by adding more parameters in future, you would need to alter the Pipeline and the Activity calling the notebook to add the parameter reference as well. This adds extra tasks to the development flow and can easily be overlooked, causing unforeseen errors when testing the notebook.

To solve this, we can use a single JSON Object or JSON Array of Objects as the parameter instead. But first, lets tag the cell as the parameters cell and take it from there.

Tagging a cell in a notebook as the parameters cell

In Synapse Analytics, when calling a Notebook activity via an Integration Pipeline, you can pass values to the Notebook at runtime by tagging a dedicated cell in the Notebook as the Parameters Cell.

There is a small indication at the bottom right of the cell stating this is the parameters cell. There can only be one per notebook.

Within this cell, you define variables that you want to pass the parameter values to at run time. You can provide default values to them for use when building and debugging the notebook in isolation. You reference these variables as any normal variable in the language you are working in.

In the pipeline where you are calling this notebook, add parameters to the notebook activity, using the exact same names as the variables defined in the notebook itself.

At runtime, Synapse will take the values sent to the pipeline, pass them down to the Notebook and override the variables with the runtime values.

Pretty straightforward up to this point. However, here you can see that adding more parameters results in Integration Pipeline changes.

Extensible Parameters using JSON

To solve this issue, we will update the value being passed to the parameter to be a string representation of a JSON Object or JSON Array. The contents of that parameter can be changed to add any number of additional attributes over time, without causing pipeline definition changes. The triple single quotes here are needed in order to escape single or double quotes used in the actual JSON.

In order to use the contents of the variable in the notebook, you capture this string into a string variable first. From there you need to convert it into a JSON Typed Object in order to work with it interactively using the json.loads function from the JSON library in python. Other languages will have other functions – this is note covered in this article.

Now you can extract the specific attributes from the variable to use as parameters as needed. If you are passing in a JSON Array as I am here, you can extract information from a single element in the array using index notation.

Here are some samples showing the types are of the data and elements in this array for reference. This will help inform you on how to interact with this new variable.

If you pass in an array you need to loop over, you can use a regular python for loop to do so – if you are using python in your notebook that is.

To add this notebook to the pipeline, you can use the button top right to add it to an existing or a new integration pipeline.

The parameter config in the activity looks very similar – it is still a string variable, but you will pass in the JSON String instead of a scalar string value.

If you ever need additional parameters to be sent down to the notebook at runtime, alter the JSON String being sent to the single parameter, and update the Notebook code to utilize the additional attribute it will find in the JSON, without ever altering the Pipeline definition at all.

Benefits of this approach

Avoid Integration Pipeline alteration when adding parameters to a notebook – cutting down on development time and reducing risk in introducing bugs.
The complexity of the Notebook can grow larger to perform more advanced tasks dynamically without worrying about the complexity introduced into the Integration Pipeline.
You can make the Integration Pipeline activities even more dynamic to call a different notebook at runtime depending on other runtime variables, not just a dedicated/hardcoded notebook. Since you can make each notebook receive the exact same parameter , you aren’t limited to calling a hardcoded Notebook in an activity. This makes the Integration Pipelines even more dynamic and metadata driven – increasing efficiency in development time.