Query History API

Important

To access Databricks REST APIs, you must authenticate.

List

Endpoint HTTP Method
2.0/sql/history/queries GET

List the history of queries through SQL endpoints. You can filter by user ID, endpoint ID, status, and time range.

Request

Field Name Type Description
filter_by QueryFilter A filter to limit query history results. This field is optional.
max_results INT32 Limit the number of results returned in one page. The default is 100.
page_token STRING Opaque token used to get the next page of results. This field is optional.
include_metrics BOOLEAN Whether to include metrics about query execution.

Response

Field Name Type Description
next_page_token STRING Opaque token used to get the next page.
has_next_page BOOLEAN Whether there is another page of results.
res Array of QueryInfo Query results.

Example request

{
  "filter_by": {
    "statuses": ["RUNNING"],
    "user_ids": [12345],
    "endpoint_ids": ["1234567890abcdef"]
  },
  "max_results": 100
}

Example response

{
  "next_page_token": "Ci0KJDU4NjEwZjY5LTgzNzUtNDdiMS04YTg1LWYxNTU5ODI5MDYyMhDdobu YuS4SABhk",
  "has_next_page": true,
  "res": [
    {
      "query_id": "26b5c452-1dff-429e-9b55-7c16131c89ee",
      "status": "FINISHED",
      "query_text": "select 1 + 1",
      "query_start_time_ms": 1595357086200,
      "execution_end_time_ms": 1595357086373,
      "query_end_time_ms": 1595357087200,
      "user_id": [12345],
      "user_name": "user@example.com",
      "spark_ui_url":"https://<databricks-instance>/sparkui/0710-201419-test887/driver-8401376710892156045/SQL/execution/?id=0",
      "endpoint_id": "1234567890abcdef",
      "rows_produced": 100
    },
    {
      "query_id": "26b5c452-1dff-429e-9b55-7c16131c89ee",
      "status": "FAILED",
      "query_text": "select 1 + 1",
      "query_start_time_ms": 1595357196200,
      "user_id": [12345],
      "user_name": "user@example.com",
      "endpoint_id": "1234567890abcdef",
      "error_message": "Query failed because ..."
    }
  ]
}

Data structures

QueryFilter

Field Name Type Description
statuses Array of QueryStatus Statuses of queries.
user_ids Array of INT64 IDs of the users that ran the queries.
endpoint_ids Array of STRING IDs of endpoints on which the queries ran.
query_start_time_range TimeRange Time range of when the queries started.

QueryInfo

Field Name Type Description
query_id INT64 Query ID.
status QueryStatus Query status.
query_text STRING The text of the query.
query_start_time_ms INT64 The time the query started.
execution_end_time_ms INT64 The time execution of the query ended.
query_end_time_ms INT64 The time the query ended.
user_id INT64 ID of the user that ran the query.
user_name STRING Email address of the user that ran the query.
spark_ui_url STRING URL to the query plan.
endpoint_id STRING Endpoint ID.
error_message STRING Message describing why the query could not complete.
rows_produced INT32 The number of results returned by the query.
metrics QueryMetrics Metrics about query execution.

QueryStatus

Status Description
QUEUED Query has been received and queued.
RUNNING Query execution has started.
CANCELED Query has been cancelled by the user.
FAILED Query execution has failed.
FINISHED Query execution has completed.

TimeRange

Field Name Type Description
start_time_ms INT64 Limit results to queries that started after this time.
end_time_ms INT64 Limit results to queries that started before this time.

QueryMetrics

Field Name Type Description
total_time_ms INT64 Total execution time of the query from the client’s point of view, expressed in milliseconds.
read_bytes INT64 Total size of data read by the query, expressed in bytes.
rows_produced_count INT64 Total number of rows returned by the query.
compilation_time_ms INT64 Time spent loading metadata and optimizing the query, expressed in milliseconds.
execution_time_ms INT64 Time spent executing the query, expressed in milliseconds.
queued_provisioning_time_ms INT64 Time waiting for compute resources to be provisioned for the SQL endpoint, expressed in milliseconds.
queued_overload_time_ms INT64 Time spent waiting to execute the query because the SQL endpoint is already running the maximum number of concurrent queries.
read_remote_bytes INT64 Size of persistent data read from cloud object storage on your cloud tenant, expressed in bytes .
write_remote_bytes INT64 Size pf persistent data written to cloud object storage in your cloud tenant, expressed in bytes.
read_cache_bytes INT64 Size of persistent data read from the cache, expressed in bytes.
spill_to_disk_bytes INT64 Size of data temporarily written to disk while executing the query, expressed in bytes.
task_total_time_ms INT64 Sum of execution time for all of the query’s tasks, expressed in milliseconds.
read_files_count INT64 Number of files read after pruning.
total_files_count INT64 Number of files that would have been read without pruning.
read_partitions_count INT64 Number of partitions read after pruning.
total_partitions_count INT64 Number of partitions that would have been read without pruning.
photon_total_time_ms INT64 Total execution time for all individual Photon query engine tasks in the query, expressed in milliseconds.
rows_read_count INT64 Total number of rows read by the query.
result_fetch_time_ms INT64 Time spent fetching the query results after the execution finished, expressed in milliseconds.
network_sent_bytes INT64 Total amount of data sent over the network, expressed in bytes.
result_from_cache BOOLEAN true if the query result was fetched from cache, false otherwise.