Search code examples
sqlazureazure-synapse

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?

Solution

  • 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.