Calling Synapse REST API to automate tasks using Powershell
Published Mar 11 2021 08:53 AM 8,096 Views
Microsoft

Sometimes we need to do some automations on Synapse and if using Powershell by default use the Az Powershell Module to simplify this interaction

https://docs.microsoft.com/en-us/powershell/module/az.synapse/

 

But sometimes we need call the REST APIs directly and this get the connectivity part a little more complicated depending on the BEARER TOKEN (This bearer token is a lightweight security token that grants the “bearer” access to a protected resource) and you need to request the token to the correct provider

 

$token = (Get-AzAccessToken -Resource "https://management.azure.com").Token
$token = (Get-AzAccessToken -Resource "https://dev.azuresynapse.net").Token
 
If you request the wrong token you will get error like below
 
Invoke-RestMethod: {"code":"InvalidTokenAuthenticationAudience","message":"Token Authentication failed with SecurityTokenInvalidAudienceException - IDX10214: Audience validation failed. Audiences: '[PII is hidden]'. Did not match: validationParameters.ValidAudience: '[PII is hidden]' or validationParameters.ValidAudiences: '[PII is hidden]'."}
 
You can check it in the API documentation
FonsecaSergio_0-1615481441962.png
 
FonsecaSergio_1-1615481518841.png

 

 
Find below two samples
 - Sample 1 - Get Synapse workspace (Azure Management Endpoint)
 - Sample 2 - Get Synapse SQL Pools (Synapse DEV Endpoint)
 - Sample 3 - Resume Synapse SQL Pools
 
 
 
 
 
 

Sample 1 - Get Synapse workspace (Azure Management Endpoint)

 
$ResourceGroup = "ResGroup"
$workspaceName = "Synapse"
$SubscriptionId = "de41dc76-xxxxxxx"

# ------------------------------------------
# these Az modules required
# https://docs.microsoft.com/powershell/azure/install-az-ps
Import-Module Az.Accounts 

#########################################################################################CONNECT TO AZURE

$Context = Get-AzContext

if ($Context -eq $null) {
    Write-Information "Need to login"
    Connect-AzAccount -Subscription $SubscriptionId
}
else
{
    Write-Host "Context exists"
    Write-Host "Current credential is $($Context.Account.Id)"
    if ($Context.Subscription.Id -ne $SubscriptionId) {
        $result = Select-AzSubscription -Subscription $SubscriptionId
        Write-Host "Current subscription is $($result.Subscription.Name)"
    }
    else {
        Write-Host "Current subscription is $($Context.Subscription.Name)"    
    }
}
########################################################################################

# ------------------------------------------
# get Bearer token for current user for Synapse Workspace API
$token = (Get-AzAccessToken -Resource "https://management.azure.com").Token
$headers = @{ Authorization = "Bearer $token" }
# ------------------------------------------

#https://docs.microsoft.com/en-us/rest/api/synapse/workspaces/get

$uri = "https://management.azure.com/subscriptions/$SubscriptionID/"
$uri += "resourceGroups/$ResourceGroup/providers/Microsoft.Synapse/"
$uri += "workspaces/$workspaceName/?api-version=2019-06-01-preview"

$result = Invoke-RestMethod -Method Get -ContentType "application/json" -Uri $uri -Headers $headers

Write-Host ($result | ConvertTo-Json)
 
It will return JSON with data
FonsecaSergio_0-1615479391491.png

 

But you can explore it like a Powershell object

$result.properties.connectivityEndpoints.sql
 
$workspaceName = "Synapse"
$SubscriptionId = "de41dc76-xxxxxx"

# ------------------------------------------
# these Az modules required
# https://docs.microsoft.com/powershell/azure/install-az-ps
Import-Module Az.Accounts 

########################################################################################
#CONNECT TO AZURE

$Context = Get-AzContext

if ($Context -eq $null) {
    Write-Information "Need to login"
    Connect-AzAccount -Subscription $SubscriptionId
}
else
{
    Write-Host "Context exists"
    Write-Host "Current credential is $($Context.Account.Id)"
    if ($Context.Subscription.Id -ne $SubscriptionId) {
        $result = Select-AzSubscription -Subscription $SubscriptionId
        Write-Host "Current subscription is $($result.Subscription.Name)"
    }
    else {
        Write-Host "Current subscription is $($Context.Subscription.Name)"    
    }
}
########################################################################################

# ------------------------------------------
# get Bearer token for current user for Synapse Workspace API
$token = (Get-AzAccessToken -Resource "https://dev.azuresynapse.net").Token
$headers = @{ Authorization = "Bearer $token" }
# ------------------------------------------

# https://docs.microsoft.com/en-us/rest/api/synapse/data-plane/sqlpools/list
# GET {endpoint}/sqlPools?api-version=2019-06-01-preview

$uri = "https://$workspaceName.dev.azuresynapse.net/"
$uri += "sqlPools?api-version=2019-06-01-preview"

$result = Invoke-RestMethod -Method Get -ContentType "application/json" -Uri $uri -Headers $headers

Write-Host ($result | ConvertTo-Json)


 
{
  "value": [
    {
      "properties": "@{status=Online; maxSizeBytes=263882790666240; collation=SQL_Latin1_General_CP1_CI_AS; restorePointInTime=01/01/0001 00:00:00; creationDate=10/22/2020 11:20:08; provisioningState=Succeeded}",
      "sku": "@{name=DW100c; capacity=0}",
      "id": "/subscriptions/de41dc76-...",
      "name": "pool01",
      "type": "Microsoft.Synapse/workspaces/sqlPools",
      "location": "westeurope",
      "tags": "@{test=true}"
    },
    {
      "properties": "@{status=Paused; maxSizeBytes=263882790666240; collation=SQL_Latin1_General_CP1_CI_AS; restorePointInTime=01/01/0001 00:00:00; creationDate=03/04/2021 20:14:32; provisioningState=Failed}",
      "sku": "@{name=DW100c; capacity=0}",
      "id": "/subscriptions/de41dc76-....",
      "name": "lab01",
      "type": "Microsoft.Synapse/workspaces/sqlPools",
      "location": "westeurope",
      "tags": ""
    }
  ]
}
 

Sample 3 - Resume Synapse SQL Pools

 
FonsecaSergio_0-1615482556060.png

 

In this case its a little bit more trick to monitor as the resume operation is async

 

 

 

 

Co-Authors
Version history
Last update:
‎Mar 11 2021 09:11 AM
Updated by: