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.

[Kaniz · Accepted solution]

• Hi @John Constantine​ and @Alex Davies​  , Improvements have been made to how you can view, share, and import a query’s profile. See Query profile.

Source

[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

[Kaniz]

Hi @Alex Davies​ ,

You can use a query profile to visualize the details of query execution. The query profile helps you troubleshoot performance bottlenecks during the query’s execution. For example:

• You can visualize each query task and its related metrics, such as the time spent, the number of rows processed, rows processed, and memory consumption.
• You can identify the slowest part of a query execution at a glance and assess the impacts of modifications to the query.
• You can discover and fix common mistakes in SQL statements, such as exploding joins or full table scans.

Requirements

To view a query profile, you must either be the owner of the query or you must have the Can Manage permission on the SQL endpoint that executed the query.

Source

• To analyse the query history as an aggregate, or If you want a new feature to be added here, you can request the feature here at this link.

[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

[Kaniz]

Hi @Alex Davies​ , Would you like to raise a feature request ?

[Kaniz]

Hi @Alex Davies​ , How are you? Is everything okay? Would you like to raise a feature request?

[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!