CODEWITHSUNDEEP
sqlazureazure-synapse
javadev
javadev

Reputation: 287

How to Read Synapse Analytics SQL Script (JSON Format) as a SQL Script in an IDE?

I have a Synapse Git Project that has SQL Scripts created in the Azure Portal like so Microsoft Docs SQL Scriptand the challenge is that in GIT they appear as this kinda bulky JSON file and I would love to read it as SQL File DBEAVER or IntelliJ …

Any way to do this without having to manually select the Query Section of the file and kinda clean it?

Upvotes: 2

Views: 984

Answers (2)

R0106
R0106

Reputation: 43

We can copy .sql file to git location but yes it is manual process. enter image description here

Atleast it helps to push .sql file to Azure Devops git repo.

Upvotes: 0

Joel Cochran
Joel Cochran

Reputation: 7748

First Some Background

Synapse stores all artifacts in JSON format. In the Git repo, they are in the following folder structure:

Synapse Git Folders

Inside each folder are the JSON files that define the artifacts. Folder sqlscript contains the JSON for SQL Scripts in the following format:

enter image description here

NOTE: the Synapse folder of the script is just a property - this is why all SQL Script names have to be unique across the entire workspace.

Extracting the script

The workspace does allow you to Export SQL to a .sql file: enter image description here

There are drawbacks: you have to do it manually, 1 file at a time, and you cannot control the output location or SQL file name.

To pull the SQL back out of the JSON, you have to access the properties.content.query property value and save it as a .sql file. As far as I know, there is no built in feature to automatically save a Script as SQL. Simple Copy/Paste doesn't really work because of the \ns.

I think you could automate at least part of this with an Azure DevOps Pipeline (or a GitHub Action). You might need to copy the JSON file out to another location, and then have a process (Data Factory, Azure Function, Logic App, etc.) read the file and extract the query.

Upvotes: 1

Related Questions