01-24-2022 07:33 AM
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?
02-11-2022 02:11 PM
01-25-2022 07:01 AM
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.
02-08-2022 09:25 PM
Were you able to find a solution for this?
02-09-2022 07:53 AM
@John Constantine - I'm sorry it is taking so long. I will escalate this to the team.
02-11-2022 02:11 PM
02-12-2022 07:57 AM
https://docs.microsoft.com/en-us/azure/databricks/sql/admin/query-history
Is this help @Alex Davies or. the one Kaniz has shared .
02-14-2022 12:56 AM
@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
02-16-2022 08:56 AM
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:
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.
02-17-2022 01:30 AM
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
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
03-16-2022 03:07 AM
Hi @Alex Davies , Would you like to raise a feature request ?
04-07-2022 12:53 PM
Hi @Alex Davies , How are you? Is everything okay? Would you like to raise a feature request?
03-06-2022 04:29 PM
@Alex Davies - Would you be willing to share your solution when you've got it? 🙂
05-02-2022 08:21 AM
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!
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