topic Query example for databricks Query History API in Data Engineering

Query example for databricks Query History API

Cheryl — Wed, 27 Mar 2024 18:06:27 GMT

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

Re: Query example for databricks Query History API

shan_chandra — Wed, 27 Mar 2024 20:02:49 GMT

@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

Re: Query example for databricks Query History API

Cheryl — Wed, 27 Mar 2024 21:37:44 GMT

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
},
}
}

Re: Query example for databricks Query History API

shan_chandra — Thu, 28 Mar 2024 15:32:34 GMT

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)