cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Report on SQL queries that are being executed

AlexDavies
Contributor

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?

1 ACCEPTED SOLUTION

Accepted Solutions

Kaniz_Fatma
Community Manager
Community Manager
  • 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

View solution in original post

12 REPLIES 12

Anonymous
Not applicable

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
Contributor III

Were you able to find a solution for this?

Anonymous
Not applicable

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

Kaniz_Fatma
Community Manager
Community Manager
  • 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
Esteemed Contributor

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

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

AlexDavies
Contributor

@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_Fatma
Community Manager
Community Manager

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.

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

Hi @Alex Daviesโ€‹ , Would you like to raise a feature request ?

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

Anonymous
Not applicable

@Alex Daviesโ€‹ - Would you be willing to share your solution when you've got it? ๐Ÿ™‚

Anonymous
Not applicable

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!

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group