Azure Synapse Serverless SQL Pool - CI/CD

Question

Azure Synapse Serverless SQL Pool - CI/CD

Graeme Cash 76

Hello

I have been exploring building a Data Lakehouse using a SQL serverless pool starting with data from Dataverse. I have created various views and even used CETAS statements to store some parquet files. I have set up separate environments and use Azure DevOps to promote the changes up from Dev. Although the SQL scripts move to the different environments I can't think of a way of automating the the creation of the SQL objects.

Is there any way of doing this? I know it is possible with a dedicated pool using a dacpac file.

Thanks

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2021-12-10T08:05:52.287+00:00

Hi @Graeme Cash ,

Just checking in to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.

Accepted answer

0 additional answers

Your answer

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2021-12-10T08:05:52.287+00:00

Hi @Graeme Cash ,

Just checking in to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.

Answer 1

ShaikMaheer-MSFT 38,546 Microsoft Employee Moderator

Hi @Graeme Cash ,

Thank you for posting query in Microsoft Q&A Platform.

CI/CD process in Synapse will deploy script files to different environments. But If I understands your query better, you are looking for running those scripts on serverless SQL Pool.

There is a activity called "SQL Pool stored procedure", this will only support dedicated SQL pool at this moment.

So, below is one possible way for deploying SQL scripts on serverless SQL Pool.

Below steps we can implement in Synapse Pipeline and run same to deploy SQL scripts.

Make a REST API call using web activity to get SQL script connect. Click here to know about REST API for same.
Take SQL Script from step1 Response and use Lookup activity to run script.

Hope this will help. Please let us know how it goes and any further queries.

Graeme Cash 76 Reputation points

2021-12-07T19:38:14.463+00:00

Hello, thanks for coming back to me, what component would you use to parse the json that comes back to get the queries? I could do it with SQL or Spark but I guess that would mean storing the data and I am back to my initial problem.
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2021-12-08T06:12:52.32+00:00

Hi @Graeme Cash ,

Thank you for follow-up query.

Below is the response what you get from web activity when you make REST API call to get SQL script.

You need to write expression to read your script from response. Below is expression to write.
@activity(<web activity name>).output.value[0].properties.query

Hope this will help. Thank you.

------------

Please consider hitting Accept Answer. Accepted Answers helps community as well.
Graeme Cash 76 Reputation points

2021-12-10T11:30:35.813+00:00

Thanks, I managed to get it to work passing the queries to a for each activity

Share via

Azure Synapse Serverless SQL Pool - CI/CD

0 additional answers

Your answer