Query

View as Markdown

QueryInfo object

query_idstring: The query ID.
Example: f996b47c-6672-4763-9668-d491a82099f5

statusstring

Query status with one the following values:

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

Values: QUEUED, STARTED, COMPILING, COMPILED, RUNNING, CANCELED, FAILED, FINISHED

Example: FINISHED

query_textstring: The text of the query.
Example: SELECT * FROM customers;

query_start_time_msint64: The time the query started.
Example: 1595357086200

execution_end_time_msint64: The time execution of the query ended.
Example: 1595357086373

query_end_time_msint64: The time the query ended.
Example: 1595357087200

user_idint64: The ID of the user who ran the query.
Example: 1234567890123456

user_namestring: The email address or username of the user who ran the query.
Example: user@example.com

spark_ui_urlstring: URL to the Spark UI query plan.
Example: https://<databricks-instance>/sparkui/1234-567890-test123/driver-1234567890123456789/SQL/execution/?id=0

endpoint_idstring: Alias for warehouse_id.

rows_producedint64: The number of results returned by the query.
Example: 100

error_messagestring: Message describing why the query could not complete.

lookup_keystring: A key that can be used to look up query details.
Example: CiQ3OGFkYmQ2Zi00ZGUwLTRlNTYtOTkxZC05Y2I5OTNlZTViYjcQ4N6r/dguGhBlM2VlYTVlOTExMjFkMzNjILPbh9OK6uoL

metricsobject

Metrics about query execution.

Show child attributesHide child attributes

total_time_msint64: Total execution time of the query from the client’s point of view, in milliseconds.

read_bytesint64: Total size of data read by the query, in bytes.

rows_produced_countint64: Total number of rows returned by the query.

compilation_time_msint64: Time spent loading metadata and optimizing the query, in milliseconds.

execution_time_msint64: Time spent executing the query, in milliseconds.

read_remote_bytesint64: Size of persistent data read from cloud object storage on your cloud tenant, in bytes.

write_remote_bytesint64: Size pf persistent data written to cloud object storage in your cloud tenant, in bytes.

read_cache_bytesint64: Size of persistent data read from the cache, in bytes.

spill_to_disk_bytesint64: Size of data temporarily written to disk while executing the query, in bytes.

task_total_time_msint64: Sum of execution time for all of the query’s tasks, in milliseconds.

read_files_countint64: Number of files read after pruning

read_partitions_countint64: Number of partitions read after pruning.

photon_total_time_msint64: Total execution time for all individual Photon query engine tasks in the query, in milliseconds.

rows_read_countint64: Total number of rows read by the query.

result_fetch_time_msint64: Time spent fetching the query results after the execution finished, in milliseconds.

network_sent_bytesint64: Total amount of data sent over the network between executor nodes during shuffle, in bytes.

result_from_cacheboolean: true if the query result was fetched from cache, false otherwise.

pruned_bytesint64: Total number of file bytes in all tables not read due to pruning

pruned_files_countint64: Total number of files from all tables not read due to pruning

provisioning_queue_start_timestampint64: Timestamp of when the query was enqueued waiting for a cluster to be provisioned for the warehouse. This field is optional and will not appear if the query skipped the provisioning queue.

overloading_queue_start_timestampint64: Timestamp of when the query was enqueued waiting while the warehouse was at max load. This field is optional and will not appear if the query skipped the overloading queue.

query_compilation_start_timestampint64: Timestamp of when the underlying compute started compilation of the query.

task_time_over_time_rangeobject

sum of task times completed in a range of wall clock time, approximated to a configurable number of points aggregated over all stages and jobs in the query (based on task_total_time_ms)

Show child attributesHide child attributes

entriesarray of object

Show child attributesHide child attributes

task_completed_time_msint64: total task completion time in this time range, aggregated over all stages and jobs in the query

intervalint64: interval length for all entries (difference in start time and end time of an entry range) the same for all entries start time of first interval is query_start_time_ms

work_to_be_doneint64: remaining work to be done across all stages in the query, calculated by autoscaler StatementAnalysis.scala, in milliseconds deprecated: using projected_remaining_task_total_time_ms instead

runnable_tasksint64: number of remaining tasks to complete, calculated by autoscaler StatementAnalysis.scala deprecated: use remaining_task_count instead

projected_remaining_task_total_time_msint64: projected remaining work to be done aggregated across all stages in the query, in milliseconds

remaining_task_countint64: number of remaining tasks to complete this is based on the current status and could be bigger or smaller in the future based on future updates

projected_remaining_wallclock_time_msint64: projected lower bound on remaining total task time based on projected_remaining_task_total_time_ms / maximum concurrency

read_files_bytesint64: Total number of file bytes in all tables read

executed_as_user_idint64: The ID of the user whose credentials were used to run the query.

executed_as_user_namestring: The email address or username of the user whose credentials were used to run the query.

session_idstring: The spark session UUID that query ran on. This is either the Spark Connect, DBSQL, or SDP session ID.
Example: f996b47c-6672-4763-9668-d491a82099f5

is_finalboolean: Whether more updates for the query are expected.
Example: true

channel_usedobject

SQL Warehouse channel information at the time of query execution

Show child attributesHide child attributes

namestring: Name of the channel
Values: CHANNEL_NAME_UNSPECIFIED, CHANNEL_NAME_PREVIEW, CHANNEL_NAME_CURRENT, CHANNEL_NAME_PREVIOUS, CHANNEL_NAME_CUSTOM
Example: CHANNEL_NAME_CURRENT

dbsql_versionstring: DB SQL Version the Channel is mapped to.
Example: 2022.30

plans_statestring: Whether plans exist for the execution, or the reason why they are missing
Values: IGNORED_SMALL_DURATION, IGNORED_LARGE_PLANS_SIZE, EXISTS, UNKNOWN, EMPTY, IGNORED_SPARK_PLAN_TYPE
Example: EXISTS

statement_typestring: Type of statement for this query
Values: OTHER, ALTER, ANALYZE, COPY, CREATE, DELETE, DESCRIBE, DROP, EXPLAIN, GRANT, INSERT, MERGE, OPTIMIZE, REFRESH, REPLACE, REVOKE, SELECT, SET, SHOW, TRUNCATE, UPDATE, USE
Example: SELECT

warehouse_idstring: Warehouse ID.
Example: 098765321fedcba

durationint64: Total time of the statement execution. This value does not include the time taken to retrieve the results, which can result in a discrepancy between this value and the start-to-finish wall-clock time.
Example: 1000

client_applicationstring: Client application that ran the statement. For example: Databricks SQL Editor, Tableau, and Power BI. This field is derived from information provided by client applications. While values are expected to remain static over time, this cannot be guaranteed.
Example: Power BI

query_sourceobject

A struct that contains key-value pairs representing <Databricks> entities that were involved in the execution of this statement, such as jobs, notebooks, or dashboards. This field only records <Databricks> entities.

Show child attributesHide child attributes

dashboard_idstring: The canonical identifier for this Lakeview dashboard
Example: b1efe7f5891c1815b65e21c873fdaf4e

legacy_dashboard_idstring: The canonical identifier for this legacy dashboard
Example: caf1e170-d14e-4bcc-8019-8c3b26ca46e4

alert_idstring: The canonical identifier for this SQL alert
Example: d789836c-56ef-4c89-b951-d7186c4ad3ee

notebook_idstring: The canonical identifier for this notebook
Example: 1335125300829196

sql_query_idstring: The canonical identifier for this SQL query
Example: be6df0a0-c317-44df-9659-b4f206c5d027

job_infoobject

Show child attributesHide child attributes

job_idstring: The canonical identifier for this job.
Example: 445923364221868

job_run_idstring: The canonical identifier of the run. This ID is unique across all runs of all jobs.

job_task_run_idstring: The canonical identifier of the task run.
Example: 870588346649939

genie_space_idstring: The canonical identifier for this Genie space
Example: a1f008dd4daf1340a7d59c66c2bdc5a8

cache_query_idstring: The ID of the cached query if this result retrieved from cache
Example: f996b47c-6672-4763-9668-d491a82099f5

query_tagsarray of object

A query execution can be optionally annotated with query tags

Show child attributesHide child attributes

keystring

valuestring

View as Markdown

The QueryInfo object

{
  "query_id": "f996b47c-6672-4763-9668-d491a82099f5",
  "status": "FINISHED",
  "query_text": "SELECT * FROM customers;",
  "query_start_time_ms": 1595357086200,
  "execution_end_time_ms": 1595357086373,
  "query_end_time_ms": 1595357087200,
  "user_id": 1234567890123456,
  "user_name": "user@example.com",
  "spark_ui_url": "https://\u003cdatabricks-instance\u003e/sparkui/1234-567890-test123/driver-1234567890123456789/SQL/execution/?id=0",
  "endpoint_id": "string",
  "rows_produced": 100,
  "error_message": "Table or view not found: customers; line 1 pos 14;\n'GlobalLimit 1000\n+- 'LocalLimit 1000\n +- 'Project [*]\n +- 'UnresolvedRelation [sales]\n",
  "lookup_key": "CiQ3OGFkYmQ2Zi00ZGUwLTRlNTYtOTkxZC05Y2I5OTNlZTViYjcQ4N6r/dguGhBlM2VlYTVlOTExMjFkMzNjILPbh9OK6uoL",
  "metrics": {
    "total_time_ms": 0,
    "read_bytes": 0,
    "rows_produced_count": 0,
    "compilation_time_ms": 0,
    "execution_time_ms": 0,
    "read_remote_bytes": 0,
    "write_remote_bytes": 0,
    "read_cache_bytes": 0,
    "spill_to_disk_bytes": 0,
    "task_total_time_ms": 0,
    "read_files_count": 0,
    "read_partitions_count": 0,
    "photon_total_time_ms": 0,
    "rows_read_count": 0,
    "result_fetch_time_ms": 0,
    "network_sent_bytes": 0,
    "result_from_cache": true,
    "pruned_bytes": 0,
    "pruned_files_count": 0,
    "provisioning_queue_start_timestamp": 0,
    "overloading_queue_start_timestamp": 0,
    "query_compilation_start_timestamp": 0,
    "task_time_over_time_range": {},
    "work_to_be_done": 0,
    "runnable_tasks": 0,
    "projected_remaining_task_total_time_ms": 0,
    "remaining_task_count": 0,
    "projected_remaining_wallclock_time_ms": 0,
    "read_files_bytes": 0
  },
  "executed_as_user_id": 0,
  "executed_as_user_name": "string",
  "session_id": "f996b47c-6672-4763-9668-d491a82099f5",
  "is_final": true,
  "channel_used": {
    "name": "CHANNEL_NAME_CURRENT",
    "dbsql_version": "2022.30"
  },
  "plans_state": "EXISTS",
  "statement_type": "SELECT",
  "warehouse_id": "098765321fedcba",
  "duration": 1000,
  "client_application": "Power BI",
  "query_source": {
    "dashboard_id": "b1efe7f5891c1815b65e21c873fdaf4e",
    "legacy_dashboard_id": "caf1e170-d14e-4bcc-8019-8c3b26ca46e4",
    "alert_id": "d789836c-56ef-4c89-b951-d7186c4ad3ee",
    "notebook_id": "1335125300829196",
    "sql_query_id": "be6df0a0-c317-44df-9659-b4f206c5d027",
    "job_info": {},
    "genie_space_id": "a1f008dd4daf1340a7d59c66c2bdc5a8"
  },
  "cache_query_id": "f996b47c-6672-4763-9668-d491a82099f5",
  "query_tags": [
    {
      "key": "string",
      "value": "string"
    }
  ]
}

List

GET /api/2.0/sql/history/queries

List the history of queries through SQL warehouses, and serverless compute.

You can filter by user ID, warehouse ID, status, and time range. Most recently started queries are returned first (up to max_results in request). The pagination token returned in response can be used to list subsequent query statuses.

API scopes: query-history

Parameters

filter_byobjectquery

An optional filter object to limit query history results. Accepts parameters such as user IDs, endpoint IDs, and statuses to narrow the returned data. In a URL, the parameters of this filter are specified with dot notation. For example: filter_by.statement_ids.

Show child attributesHide child attributes

query_start_time_rangeobject

A range filter for query submitted time. The time range must be less than or equal to 30 days.

Show child attributesHide child attributes

start_time_msint64: The start time in milliseconds.

end_time_msint64: The end time in milliseconds.

user_idsarray of int64: A list of user IDs who ran the queries.
Example: 1234567890123456

statusesarray of string: A list of statuses (QUEUED, RUNNING, CANCELED, FAILED, FINISHED) to match query results. Corresponds to the status field in the response. Filtering for multiple statuses is not recommended. Instead, opt to filter by a single status multiple times and then combine the results.
Values: QUEUED, STARTED, COMPILING, COMPILED, RUNNING, CANCELED, FAILED, FINISHED
Example: FINISHED

warehouse_idsarray of string: A list of warehouse IDs.
Example: 098765321fedcba

statement_idsarray of string: A list of statement IDs.
Example: 556c0261-147b-4698-9d66-fdb852f3e94e

max_resultsint32query: Limit the number of results returned in one page. Must be less than 1000 and the default is 100.
Default: 100

page_tokenstringquery: A token that can be used to get the next page of results. The token can contains characters that need to be encoded before using it in a URL. For example, the character '+' needs to be replaced by %2B. This field is optional.

include_metricsbooleanquery: Whether to include the query metrics with each query. Only use this for a small subset of queries (max_results). Defaults to false.

Response

Returns a list of QueryInfo objects.

View as Markdown

Response

{
  "next_page_token": "Ci0KJDU4NjEwZjY5LTgzNzUtNDdiMS04YTg1LWYxNTU5ODI5MDYyMhDdobu",
  "has_next_page": true,
  "res": [
    {
      "query_id": "f996b47c-6672-4763-9668-d491a82099f5",
      "status": "FINISHED",
      "query_text": "SELECT * FROM customers;",
      "query_start_time_ms": 1595357086200,
      "execution_end_time_ms": 1595357086373,
      "query_end_time_ms": 1595357087200,
      "user_id": 1234567890123456,
      "user_name": "user@example.com",
      "spark_ui_url": "https://\u003cdatabricks-instance\u003e/sparkui/1234-567890-test123/driver-1234567890123456789/SQL/execution/?id=0",
      "endpoint_id": "string",
      "rows_produced": 100,
      "error_message": "Table or view not found: customers; line 1 pos 14;\n'GlobalLimit 1000\n+- 'LocalLimit 1000\n +- 'Project [*]\n +- 'UnresolvedRelation [sales]\n",
      "lookup_key": "CiQ3OGFkYmQ2Zi00ZGUwLTRlNTYtOTkxZC05Y2I5OTNlZTViYjcQ4N6r/dguGhBlM2VlYTVlOTExMjFkMzNjILPbh9OK6uoL",
      "metrics": {},
      "executed_as_user_id": 0,
      "executed_as_user_name": "string",
      "session_id": "f996b47c-6672-4763-9668-d491a82099f5",
      "is_final": true,
      "channel_used": {},
      "plans_state": "EXISTS",
      "statement_type": "SELECT",
      "warehouse_id": "098765321fedcba",
      "duration": 1000,
      "client_application": "Power BI",
      "query_source": {},
      "cache_query_id": "f996b47c-6672-4763-9668-d491a82099f5",
      "query_tags": [
        {}
      ]
    }
  ]
}

QueryInfo object​

List​

Parameters​

Response​

QueryInfo object

List

Parameters

Response