Synapse Create Or Update Sql Script using API endpoint with pyspark

Question

I am able to perform a get request to retrieve SQL scripts within our workspace as mentioned in the docs: Sql Script - Get Sql Scripts By Workspace

%%pyspark
import requests
token = mssparkutils.credentials.getToken('Synapse')

# api-endpoint voor get requests
url = "https://xxxx-xx-xx-xxx-xx-xxx-xx.dev.azuresynapse.net/sqlScripts?api-version=2020-12-01"
headers = {'Authorization': f"Bearer {token}"}
response = requests.get(url,  headers=headers)

However, I also want to be able to create new sql script using the same method, but instead performing a PUT request as mentioned here: Sql Script - Create Or Update Sql Script

#instead of request.get now using put
requests.put(url2, data=data, headers=headers2)

I am getting a '202 Accepted' response, meaning the response was accepted. Unfortunately no new scripts are created in our workspace. The response state does say "state":"Creating","created"...

What am I doing wrong?

This is an example of the response output:

b'{"id":"/subscriptions/xxxx-xx-xxx-xxx-xxxx/resourceGroups/xxx-xx-xxx-xxx-xx/providers/Microsoft.Synapse/workspaces/xxx-xx-xx-xxx-xx-xxxx-xxx/sqlScripts/test","recordId":xxxx,"state":"Creating","created":"2023-10-12T15:40:03.6666667Z","changed":"2023-10-12T15:40:03.6666667Z","type":"SqlScript","name":"test","operationId":"xxxxxx-xxx-xxx-xxxx-xxxxxx","artifactId":"xxxxx-xxxxx-xxxx-xxx-xxxxxxxx"}' <Response [202]>

Jaya Shankar G S · Accepted Answer · 2023-10-13 15:35:20Z

1

You need to pass the json body with name and properties with following details.

request_body = {
  "name": "user",
  "properties":
    {'content':{'query': "SELECT TOP 100 * FROM user;",
                'metadata': {'language': 'sql'},
                'currentConnection': {'databaseName': 'master', 'poolName': 'Built-in'},
                'resultLimit': 5000},
      'type': 'SqlQuery'}
  }

Here, i am creating the script called user.

u = "<endpoint>t/sqlScripts/user?api-version=2020-12-01"
token = mssparkutils.credentials.getToken('Synapse')
headers = {
    'Authorization': f"Bearer {token}",
    "Content-Type": "application/json"
}
request_body = {
  "name": "user",
  "properties":
    {'content':{'query': "SELECT TOP 100 * FROM user;",
                'metadata': {'language': 'sql'},
                'currentConnection': {'databaseName': 'master', 'poolName': 'Built-in'},
                'resultLimit': 5000},
      'type': 'SqlQuery'}
  }

r = requests.put(u,headers=headers,json=request_body)
print(r.status_code)
print(r.content)

enter image description here

After hitting on refresh button, the script is loaded.

enter image description here

edited Oct 13, 2023 at 15:35

answered Oct 13, 2023 at 5:43

Jaya Shankar G S

8,6282 gold badges6 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ByronSchuurman Over a year ago

Thanks, I've passed the json body with the exact same details and I get the exact same response. Unfortunately the created scripts do not appear within my workspace, also not after a refresh. I have no clue where or how I can still access or view them. Very strange.. it is as if I do not have the right credentials, or that the scripts are created on a different workspace....

Jaya Shankar G S Over a year ago

what is the state you got? is it creating? If yes then script is created. make sure you are using correct endpoint.

ByronSchuurman Over a year ago

Yes, I get "state":"Creating". So the script is created somewhere... I am using the same endpoint by which I am able to get request the sql scripts. So it is extremely confusing that I am unable to find the newly created scripts.

Jaya Shankar G S Over a year ago

Sry my mistake. I've uploaded the code. Need to give request body to json parameter.

Jaya Shankar G S Over a year ago

Give json=request_body in request.put and do refresh

Collectives™ on Stack Overflow

Synapse Create Or Update Sql Script using API endpoint with pyspark

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related