How to use Databricks Query History API (REST API)

[Constantine]

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

[AmanSehgal · Accepted solution]

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.

[jose_gonzalez]

Hi @John Constantine​ ,

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

[MorpheusGoGo]

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]"}

[Cheryl]

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

[yegorski]

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))