How to run SQL script resources in Synapse Pipelines
Why can't I just run my sql script resource in a pipeline just like a dataflow or notebook?
I been asking myself that question many times while copy-pasting scripts into pipeline activities and stepping away from one source of truth. I was getting my hopes up with the release of the script activity. Unfortunately, sql scripts and Synapse pipelines are still disconnected resources within the Synapse workspace - or are they?
In this post I will show how you can use the Synapse REST API to tie these resources together and unleash the magic of this union, and the REST API for that matter. Let's get started with connecting to the REST API.
Establish connection to Synapse REST API
In order to connect to the REST API we need to find our Synapse development endpoint and a way to authenticate to the endpoint. The Synapse development endpoint is located in the Synapse workspace overview and have the following naming pattern:
https://<synapse workspace>.dev.azuresynapse.net
As for authentication we can use the System Assigned Managed Identity. Since the workspace have access to itself, we only need to provide the following resource reference in the web activity settings:1
https://dev.azuresynapse.net/
The Synapse development endpoint will only provide us the base URL. To test the connection we need to request something. For instance we can get a list of SQL scripts in the workspace. Extend the base URL with the additional request details and run it.
Voila! That simple. Now let's advance to the next level.
Run extracted SQL script in Script Activity
As you see in the response body from our request we get a lot of details for each SQL script resource type. Our point of interest is the content, the query and the connection details. To extract these details we need to locate their path in the activity output from the request. With this path you can use as an input into a script activity that would, hopefully, execute your query!
For this step we are going to use a different request in order to get just one SQL script back. To make the pipeline more modular I've added parameters to decouple the REST API request and combined them again with a pipeline expression.
In order to reference the activity output we need to examine the structure of the output. Following the JSON structure we see that the query is nested inside content, which is nested inside properties.
We need to add this path inside the expression builder. Do the same for database reference.2
Hit debug and success! The SQL script resource were returned by the REST API and executed with the SQL script activity.
Hopefully this gave you some ideas on how you can use the Synapse REST API. I will explore more patterns in later posts. Stay tuned.
Limitations
The REST API only return published resources. This means you can only successfully test and debug your solution after publish.
The SQL script activity do not seem to like GO signals inside the scripts. The script could be running perfectly fine from the SQL script editor. More testing needed.
References:
If you need to query a different Synapse workspace you will need to give the requesting Synapse workspace sufficient rights to the other workspace.
You need to add database parameter in your linked service properties in order to get this option. And yes, I'm using the SQL serverless endpoint to run the query.