topic Re: How to use Databricks Query History API (REST API) in Data Engineering

How to use Databricks Query History API (REST API)

Constantine — Wed, 09 Feb 2022 05:22:13 GMT

I have setup authentication using this page https://docs.databricks.com/sql/api/authentication.html

and run

curl -n -X GET https://<databricks-instance>.cloud.databricks.com/api/2.0/sql/history/queries

To get history of all sql endpoint queries, but I want to get all queries for a particular day - say Jan 1st, 2021.

How do I do this? I don't see any examples in the documentation about this

Re: How to use Databricks Query History API (REST API)

AmanSehgal — Thu, 10 Feb 2022 14:44:02 GMT

Hi @John Constantine

To the above GET request add following raw data.

{
 "filter_by": {
   "endpoint_ids": ["<endpoint_id_1", "<endpoint_id_2", ...... "<endpoint_id_N"],
   "query_start_time_range":{
       "start_time_ms" :1640995200000, 
       "end_time_ms" : 1641081599000
   }
 },
 "max_results": 999
}

Here,

1640995200000 refers to Saturday, January 1, 2022 12:00:00 AM GMT+0

1641081599000 refers to Saturday, January 1, 2022 11:59:59 PM GMT+0

Refer to Query API 2.0 documentation for more filters.

Re: How to use Databricks Query History API (REST API)

jose_gonzalez — Mon, 07 Mar 2022 22:33:32 GMT

Hi @John Constantine ,

Did @Aman Sehgal response helped you with your question? If it does, could you please mark it as best response.

Re: How to use Databricks Query History API (REST API)

MorpheusGoGo — Wed, 06 Dec 2023 01:08:41 GMT

Are you sure this works?

payload = {

"filter_by": {

"max_results": 1

}

Returns 1 result.

payload = {

"filter_by": {

"query_start_time_range":{

"start_time_ms" :1640995200000,

"end_time_ms" : 1641081599000

}

"max_results": 1

}

{'error_code': 'MALFORMED_REQUEST', 'message': "Could not parse request object: Expected 'START_OBJECT' not 'VALUE_STRING'\n at [Source: (ByteArrayInputStream); line: 1, column: 15]\n at [Source: java.io.ByteArrayInputStream@5297a56c; line: 1, column: 15]"}

Re: How to use Databricks Query History API (REST API)

Cheryl — Wed, 27 Mar 2024 18:01:05 GMT

I get the same error message - databricks documentation is not showing a working example

Re: How to use Databricks Query History API (REST API)

yegorski — Tue, 08 Oct 2024 23:51:21 GMT

Here's how to query with databricks-sdk-py (working code). I had a frustrating time doing it with vanilla python + requests/urllib and couldn't figure it out.

import datetime import os from databricks.sdk import WorkspaceClient from databricks.sdk.service import sql w = WorkspaceClient( host=os.getenv("DATABRICKS_HOST"), token=os.getenv("DATABRICKS_TOKEN"), ) NOW = datetime.datetime.now() ONE_MINUTE = NOW - datetime.timedelta(minutes=1) NOW_MS = int(NOW.timestamp() * 1000) ONE_MINUTE_MS = int(ONE_MINUTE.timestamp() * 1000) recent_queries = w.query_history.list( filter_by=sql.QueryFilter( query_start_time_range=sql.TimeRange( start_time_ms=ONE_MINUTE_MS, end_time_ms=NOW_MS ), statuses=[sql.QueryStatus.FINISHED], ), include_metrics=False, ) print(list(recent_queries))