How to query in synapse pipeline activity runs using api

Here’s how you can query activity runs in Synapse using the API:

Steps:

1. Get Authentication Token: You’ll first need to authenticate against Azure AD to get an access token for authorization. Use the following endpoint to get an access token:

   
      POST https://login.microsoftonline.com/{tenantId}/oauth2/token
   
   

   Replace {tenantId} with your Azure AD tenant ID. The request body should contain the following parameters:
   • grant_type: client_credentials
   • client_id: (your application client ID)
   • client_secret: (your application secret)
   • resource: https://management.azure.com/
2. Query Pipeline Runs: After getting the access token, you can query pipeline runs using the Pipeline Runs - Query By Factory API. The API allows you to filter pipeline runs by a specific time range, such as daily.

   
      POST https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Synapse/workspaces/{workspaceName}/pipelines/{pipelineName}/queryActivityRuns?api-version=2020-12-01
   
   

   Replace:
   • {subscriptionId}: Your Azure subscription ID
   • {resourceGroupName}: The resource group containing your Synapse workspace
   • {workspaceName}: The name of your Synapse workspace
   • {pipelineName}: The name of the pipeline you want to query
   Request Body:

   
      {
   
        "startTime": "YYYY-MM-DDTHH:MM:SS",
   
        "endTime": "YYYY-MM-DDTHH:MM:SS",
   
        "filters": []
   
      }
   
   

   Set the startTime and endTime for your desired date range.
3. Retrieve Activity Runs: Once you get the pipeline run information, you can query for activity runs within that pipeline. Use the following endpoint:

   
      GET https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Synapse/workspaces/{workspaceName}/pipelines/{pipelineName}/runs/{runId}/activityruns?api-version=2020-12-01
   
   

   Replace {runId} with the ID of the pipeline run obtained in step 2.

Example:

Here’s an example of querying activity runs within a Synapse pipeline for the last day:

1. Get a list of pipeline runs filtered by the previous day.
2. For each pipeline run, retrieve the associated activity runs.

Sample Python Code:

import requests

# Set your credentials

tenant_id = "your-tenant-id"

client_id = "your-client-id"

client_secret = "your-client-secret"

subscription_id = "your-subscription-id"

resource_group = "your-resource-group"

workspace_name = "your-workspace-name"

pipeline_name = "your-pipeline-name"

# Get the OAuth2 token

auth_url = f"https://login.microsoftonline.com/{tenant_id}/oauth2/token"

auth_payload = {

    "grant_type": "client_credentials",

    "client_id": client_id,

    "client_secret": client_secret,

    "resource": "https://management.azure.com/"

}

auth_response = requests.post(auth_url, data=auth_payload)

access_token = auth_response.json()["access_token"]

# Set headers for the request

headers = {

    "Authorization": f"Bearer {access_token}",

    "Content-Type": "application/json"

}

# Query pipeline runs

pipeline_runs_url = f"https://management.azure.com/subscriptions/{subscription_id}/resourceGroups/{resource_group}/providers/Microsoft.Synapse/workspaces/{workspace_name}/pipelines/{pipeline_name}/queryActivityRuns?api-version=2020-12-01"

pipeline_runs_payload = {

    "startTime": "2024-09-11T00:00:00",

    "endTime": "2024-09-12T00:00:00",

    "filters": []

}

pipeline_runs_response = requests.post(pipeline_runs_url, headers=headers, json=pipeline_runs_payload)

# Parse the response

print(pipeline_runs_response.json())