Query example for databricks Query History API

[Cheryl]

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

[shan_chandra]

@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]

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]

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)