Azure Synapse Serverless SQL - Get SQL requests through REST API or Sys Views

Question

Hello Everyone,

We are using Azure Synapse and Serverless SQL to query data from Azure Data Lake gen 2 storage. I want to get the information of SQL requests made by pipelines. I can see those in Synapse Studio under Monitor > Activities > SQL requests.

I want to get this data pragmatically so we can calculate the total processed data and costs related to it. I am comfortable with using REST API or any System view to get this data. Searched a lot but couldn't find the exact API or table to get this data.

The closest I got was by running this query SELECT * FROM sys.dm_external_data_processed
But it doesn't give the amount of data processed by each pipeline run.

Thanks in advance!

Answer 1

I connected with Microsoft support team and got a query working. This can be used to get the required data:
SELECT [Status], Transaction_id as [Request ID], 'SQL On-demand' as [SQL Resource], login_name as [Submitter], 'N/A' as [Session ID], start_time as [Submit time], start_time as [Start time], end_time as [End time], command as [Request Type], query_text as [Query Text], total_elapsed_time_ms as [Duration], 'N/A' as [Queued Duration], data_processed_mb as [Data processed in MB], 'N/A' as [Workload group], 'N/A' as [Source], 'N/A' as [Pipeline], 'N/A' as [Importance], 'N/A' as [Classifier], [Error] FROM sys.dm_exec_requests_history;