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: 

Query example for databricks Query History API

Cheryl
New Contributor II

Hi 

I am trying to get query history data from my SQL warehouse. 

Following previous examples is not working. 

databricks_workspace_url = "xxx"
token = "xxx"
start_time = 1707091200
end_time = 1707174000

api_endpoint = f"{databricks_workspace_url}/api/2.0/sql/history/queries"

headers = {
"Authorization": f"Bearer {token}",
"Content-Type": "application/json"
}

params={
"include_metrics": "true",
"warehouse_ids" : ['xxx'],
"filter_by": {
"query_start_time_range": {
"end_time_ms": end_time,
"start_time_ms": start_time
},
}
}

response = requests.get(api_endpoint, headers=headers, params=params)

I get the following error -

Error: 400 - {"error_code":"MALFORMED_REQUEST","message":"Could not parse request object: Expected 'START_OBJECT' not 'VALUE_STRING'\n at [Source: (ByteArrayInputStream); line: 1, column: 75]\n at [Source: java.io.ByteArrayInputStream@6efba358; line: 1, column: 75]"}

 

 

3 REPLIES 3

shan_chandra
Esteemed Contributor

@Cheryl - you can use query_start_time=2023-01-01T00:00:00Z  as a parameter to filter for the time frame. available filter criteria are given below - https://docs.databricks.com/api/workspace/queryhistory/list#filter_by-query_start_time_range

 

 

Cheryl
New Contributor II

do you mean like this? if so, I'm getting the same error unfortunately.

query_start_time='2024-03-01T00:00:00Z'
query_end_time='2024-03-02T00:00:00Z'

params={
"include_metrics": "true",
"warehouse_ids" : ['xxx'],
"filter_by": {
"query_start_time_range": {
"end_time_ms": query_end_time,
"start_time_ms": query_start_time
},
}
}

shan_chandra
Esteemed Contributor

Cheryl - Please find the below script to retrieve the issue. 

 

import requests
import json

workspace_url = 'XXXX'
uri = f"https://{workspace_url}/api/2.0/sql/history/queries"
print(uri)
headers_auth = {"Authorization": "Bearer XXXX"}


start_ts_ms = 1711577103226
end_ts_ms = 1711577179912

request_string = {
    "filter_by": {
        "query_start_time_range": {
            "end_time_ms": end_ts_ms,
            "start_time_ms": start_ts_ms
        },
        "statuses": [
            "FINISHED", "CANCELED"
        ],
        "warehouse_ids": "XXXX"
    },
    "include_metrics": "true",
    "max_results": "1000"
}

v = json.dumps(request_string)

endp_resp = requests.get(uri, data=v, headers=headers_auth).json()
beautified_resp = json.dumps(endp_resp, indent=4)
print(beautified_resp)

 

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