topic Re: Report on SQL queries that are being executed in Data Engineering

Report on SQL queries that are being executed

AlexDavies — Mon, 24 Jan 2022 15:33:22 GMT

We have a SQL workspace with a cluster running that services a number of self service reports against a range of datasets. We want to be able to analyse and report on the queries our self service users are executing so we can get better visibility of who is using the data platform, and what/how the tables are being used. Ideally this would be using databricks SQL workspace to do this reporting rather than using another tool.

All this information is available in the UI in the Query history, but this is not in a form we can easily analyse or create graphs against

We know there is an API to pull the query history from the UI, however it does seem convoluted to query the API to fetch data about our cluster so we can ingest into our cluster so we can query it

What is the best way to get query history information information into a hive table so we can query, analyse and graph it?

Re: Report on SQL queries that are being executed

Anonymous — Tue, 25 Jan 2022 15:01:30 GMT

Hello, @Alex Davies! My name is Piper, and I'm one of the moderators here in the community. Welcome to the community; it's great to meet you!

We'll give the community some time to respond then we will come back to this if we need to. Thanks for your patience.

Re: Report on SQL queries that are being executed

Constantine — Wed, 09 Feb 2022 05:25:47 GMT

Were you able to find a solution for this?

Re: Report on SQL queries that are being executed

Anonymous — Wed, 09 Feb 2022 15:53:49 GMT

@John Constantine - I'm sorry it is taking so long. I will escalate this to the team.

Re: Report on SQL queries that are being executed

Atanu — Sat, 12 Feb 2022 15:57:55 GMT

https://docs.microsoft.com/en-us/azure/databricks/sql/admin/query-history

Is this help @Alex Davies or. the one Kaniz has shared .

Re: Report on SQL queries that are being executed

AlexDavies — Mon, 14 Feb 2022 08:56:53 GMT

@Kaniz Fatma , @Atanu Sarkar thanks for your repsonse, and for investigating an individual query the UI is great and import could be a useful feature

But what we were after was a way to analyse the queyr history as an aggregate. For example graphing the number of queries over time, or who has been making the most queries, or the 95th percentile of query times. This sort of analysis is not possible in the UI and the data is not exposed in a way we can just utilize the already existing data analytics tools on the platform

We have started to make progress on working around this by calling the API and downloading the Query History into a table. Then we can write SQL queries and create dashboards from the downloaded data. Considering this a platform to processing data it is a bit surprising that its own internal data is so hard extract for processing

We did also notice Overwatch (https://github.com/databrickslabs/overwatch) but it does not seem to import any of the SQL workspace entities so does not solve this issue, but may well solve other related issues of getting visibility of internal data

Re: Report on SQL queries that are being executed

AlexDavies — Thu, 17 Feb 2022 09:30:07 GMT

These features are amazing and we do use these to optimize individual queries

But I was looking for a way where we can calculate statistics over all the queries running on the platform. Answer questions like

Who is running the most queries
What is the average query execution time
What tables are being used the most/least

The information required to service these queries is available in the query history tab, but in its current form its impossible to run aggregation queries over of top of that data. I was looking to see if that data could easily be exposed as a table in databricks

But, as it doesn't appear possible, we are writing something custom to export query history from the API and import it into a table

Re: Report on SQL queries that are being executed

Anonymous — Mon, 07 Mar 2022 00:29:44 GMT

@Alex Davies - Would you be willing to share your solution when you've got it? 🙂

Re: Report on SQL queries that are being executed

Anonymous — Mon, 02 May 2022 15:21:26 GMT

Hey there @Alex Davies

Hope you are doing great. Just checking in if you were able to resolve your issue or do you need more help? We'd love to hear from you.

Thanks!

Re: Report on SQL queries that are being executed

slakshmanan — Tue, 08 Oct 2024 17:12:24 GMT

I would like to see if there are any queries running with run time more than 30 minutes or queries pulling more than 1 million