Report on SQL queries that are being executed

[AlexDavies]

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?

[Anonymous]

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.

[Constantine]

Were you able to find a solution for this?

[Anonymous]

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

[Atanu]

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

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

[AlexDavies]

@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

[AlexDavies]

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

[slakshmanan]

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 

[Anonymous]

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

[Anonymous]

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!