Hi everyone, I'm currently working on capturing logging information about query executions and data downloads within a Databricks workspace. Here's a summary of my current setup and the issue I'm facing: Diagnostic Settings in Azure Databricks : I have configured the diagnostic setting for DatabricksSQL events to forward logs to a Log Analytics workspace. This setup successfully captures various useful details, including the 'downloadQueryResult' operation. While these logs provide user information, they unfortunately do not include the text of the queries being downloaded. They do, however, contain the queryId . Retrieving Query Details via REST API : Using the queryId from the logs, I attempted to retrieve detailed query information using the Databricks REST API. Strangely, the API returns a 404 error when I use the queryId associated with the 'downloadQueryResult' operation, although it works fine with other queryIds Does anyone know how I can obtain the text of the queries associated with the 'downloadQueryResult' operations? Any insights or suggestions would be greatly appreciated. Thanks in advance for your help!

DatabricksSQL Logs and correlate with query history

Julio Avellaneda 0

Hi everyone,

I'm currently working on capturing logging information about query executions and data downloads within a Databricks workspace. Here's a summary of my current setup and the issue I'm facing:

Diagnostic Settings in Azure Databricks: I have configured the diagnostic setting for DatabricksSQL events to forward logs to a Log Analytics workspace. This setup successfully captures various useful details, including the 'downloadQueryResult' operation. While these logs provide user information, they unfortunately do not include the text of the queries being downloaded. They do, however, contain the queryId.
Retrieving Query Details via REST API: Using the queryId from the logs, I attempted to retrieve detailed query information using the Databricks REST API. Strangely, the API returns a 404 error when I use the queryId associated with the 'downloadQueryResult' operation, although it works fine with other queryIds

Does anyone know how I can obtain the text of the queries associated with the 'downloadQueryResult' operations? Any insights or suggestions would be greatly appreciated.

Thanks in advance for your help!

Julio Avellaneda 0 Reputation points

2024-04-22T12:48:58.6533333+00:00

@PRADEEPCHEEKATLA-MSFT hello, I've seen you are answering some question about databricks, maybe could you help with my question, thanks
Julio Avellaneda 0 Reputation points

2024-04-22T14:13:30.0333333+00:00

I was seeing this https://learn.microsoft.com/en-us/answers/questions/1181136/databricks-audit-logs-where-the-log-files-are-stor?source=docs but no luck
PRADEEPCHEEKATLA-MSFT 77,676 Reputation points Microsoft Employee

2024-04-23T05:18:47.6+00:00
@Julio Avellaneda - Thanks for the question and using MS Q&A platform.

Based on the information you provided, it seems that you are trying to capture logging information about query executions and data downloads within a Databricks workspace. You have configured the diagnostic setting for DatabricksSQL events to forward logs to a Log Analytics workspace, which successfully captures various useful details, including the 'downloadQueryResult' operation. However, the logs do not include the text of the queries being downloaded, but they contain the queryId. You have also attempted to retrieve detailed query information using the Databricks REST API, but it returns a 404 error when you use the queryId associated with the 'downloadQueryResult' operation.

To obtain the text of the queries associated with the 'downloadQueryResult' operations, you can try using the Databricks Query History API. This API provides a list of queries that have been executed in a Databricks workspace, including the query text and other details such as the user who executed the query, the time the query was executed, and the query status. You can filter the list of queries by queryId to retrieve the text of the queries associated with the 'downloadQueryResult' operations.

Here is an example of how to use the Databricks Query History API to retrieve the text of a query:

GET /api/2.0/sql/queries/{query_id}

Replace {query_id} with the queryId associated with the 'downloadQueryResult' operation. This API call will return a JSON object that includes the query text.

For more details, refer to https://docs.databricks.com/api/azure/workspace/queryhistory/list

I hope this helps! Let me know if you have any further questions.
Julio Avellaneda 0 Reputation points

2024-04-23T14:10:26.3433333+00:00

@PRADEEPCHEEKATLA-MSFT hello and thank you for the answer, the process that you just describe is the process that I'm running now, after took the event information from the azure log, I did a call to the query history sending the queryId check the image pls that I posted when i show the queryId in the azure log info) value from the azure log info, however, that call returns me a 404.
PRADEEPCHEEKATLA-MSFT 77,676 Reputation points Microsoft Employee

2024-04-24T02:51:51.83+00:00

@Julio Avellaneda - Thank you for clarifying. It's strange that you are receiving a 404 error when using the queryId associated with the 'downloadQueryResult' operation.

One possible reason for this error could be that the queryId is no longer available in the query history. Query history in Databricks has a retention period, and if the query was executed outside of this retention period, it may not be available for retrieval.

Another possibility is that there may be an issue with the REST API call itself. You may want to double-check the syntax of the API call and ensure that you are using the correct queryId.

If neither of these solutions work, you may want to open a support ticket for further assistance. They may be able to provide more insight into why you are receiving a 404 error and help you troubleshoot the issue.
PRADEEPCHEEKATLA-MSFT 77,676 Reputation points Microsoft Employee

2024-04-29T03:02:07.2+00:00

@Julio Avellaneda - We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Julio Avellaneda 0 Reputation points

2024-05-02T01:25:09.7266667+00:00

@PRADEEPCHEEKATLA-MSFT sorry for my delay, i was out some days. I've been testing again and noticed that while the downloadQueryResult appears in the Azure Log Monitor, it doesn't show up in the Databricks query history, either through the API or the web interface. This makes it difficult to gather more details about the downloaded query. However, I did conduct a test with more recent information and confirmed that the query API is operational since it successfully worked with a different query ID. I'm currently struggling to resolve this issue.
PRADEEPCHEEKATLA-MSFT 77,676 Reputation points Microsoft Employee

2024-05-02T03:56:38.2633333+00:00

@Julio Avellaneda - I agree that this issue looks strange and I wasn't able to reproduce this issue. If you have a support plan could you please file a support ticket for deeper investigation and do share the SR# with us?.