Query example for databricks Query History API
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-27-2024 11:06 AM
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]"}
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-27-2024 01:02 PM
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-27-2024 02:36 PM - edited 03-27-2024 02:37 PM
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
},
}
}
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-28-2024 08:32 AM
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)

