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
Databricks Employee
Databricks Employee

@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
Databricks Employee
Databricks Employee

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