Categories:

Information Schema , Table Functions

QUERY_HISTORY , QUERY_HISTORY_BY_*

The QUERY_HISTORY family of table functions can be used to query Snowflake query history along various dimensions:

  • QUERY_HISTORY returns queries within a specified time range.

  • QUERY_HISTORY_BY_SESSION returns queries within a specified session and time range.

  • QUERY_HISTORY_BY_USER returns queries submitted by a specified user within a specified time range.

  • QUERY_HISTORY_BY_WAREHOUSE returns queries executed by a specified warehouse within a specified time range.

Each function is optimized for querying along the specified dimension. The results can be further filtered using SQL predicates.

Note

These functions return query activity within the last 7 days.

See also:

QUERY_HISTORY View (Account Usage)

Syntax

QUERY_HISTORY(
      [ END_TIME_RANGE_START => <constant_expr> ]
      [, END_TIME_RANGE_END => <constant_expr> ]
      [, RESULT_LIMIT => <num> ] )

QUERY_HISTORY_BY_SESSION(
      [ SESSION_ID => <constant_expr> ]
      [, END_TIME_RANGE_START => <constant_expr> ]
      [, END_TIME_RANGE_END => <constant_expr> ]
      [, RESULT_LIMIT => <num> ] )

QUERY_HISTORY_BY_USER(
      [ USER_NAME => '<string>' ]
      [, END_TIME_RANGE_START => <constant_expr> ]
      [, END_TIME_RANGE_END => <constant_expr> ]
      [, RESULT_LIMIT => <num> ] )

QUERY_HISTORY_BY_WAREHOUSE(
      [ WAREHOUSE_NAME => '<string>' ]
      [, END_TIME_RANGE_START => <constant_expr> ]
      [, END_TIME_RANGE_END => <constant_expr> ]
      [, RESULT_LIMIT => <num> ] )

Arguments

All the arguments are optional.

END_TIME_RANGE_START => constant_expr , . END_TIME_RANGE_END => constant_expr

Time range (in TIMESTAMP_LTZ format), within the last 7 days, in which the query completed running:

  • If END_TIME_RANGE_END is not specified, the function returns all queries, including those that are still running.

  • If END_TIME_RANGE_END is CURRENT_TIMESTAMP, the function returns only those queries that have completed.

If the time range does not fall within the last 7 days, an error is returned.

Note

If no start or end time is specified, the most recent queries are returned, up to the specified limit.

SESSION_ID => constant_expr

Applies only to QUERY_HISTORY_BY_SESSION

The numeric identifier for a session or CURRENT_SESSION. Only queries from the specified session are returned.

Default: CURRENT_SESSION

USER_NAME => 'string'

Applies only to QUERY_HISTORY_BY_USER

A string specifying a user login name or CURRENT_USER. Only queries run by the specified user are returned. Note that the login name must be enclosed in single quotes. Also, if the login name contains any spaces, mixed-case characters, or special characters, the name must be double-quoted within the single quotes (e.g. '"User 1"' vs 'user1').

Default: CURRENT_USER

WAREHOUSE_NAME => 'string'

Applies only to QUERY_HISTORY_BY_WAREHOUSE

A string specifying a warehouse name or CURRENT_WAREHOUSE. Only queries executed by that warehouse are returned. Note that the warehouse name must be enclosed in single quotes. Also, if the warehouse name contains any spaces, mixed-case characters, or special characters, the name must be double-quoted within the single quotes (e.g. '"My Warehouse"' vs 'mywarehouse').

Default: CURRENT_WAREHOUSE

RESULT_LIMIT => num

A number specifying the maximum number of rows returned by the function:

If the number of matching rows is greater than this limit, the queries with the most recent end time (or those that are still executing) are returned, up to the specified limit.

Range: 1 to 10000

Default: 100.

Usage Notes

  • Returns queries run by the current user. Also returns queries run by any user on any warehouse on which the current user has the MONITOR privilege.

    A system administrator (user with the SYSADMIN role) that owns all warehouses can see all queries.

  • When calling an Information Schema table function, the session must have an INFORMATION_SCHEMA schema in use or the function name must be fully-qualified. For more details, see Information Schema.

Output

The function returns the following columns:

Column Name

Data Type

Description

QUERY_ID

TEXT

The statement’s unique id.

QUERY_TEXT

TEXT

Text of the SQL statement.

DATABASE_NAME

TEXT

Database that was in use at the time of the query.

SCHEMA_NAME

TEXT

Schema that was in use at the time of the query.

QUERY_TYPE

TEXT

DML, query, etc. If the query is currently running, or the query failed, then the query type may be UNKNOWN.

SESSION_ID

NUMBER

Session that executed the statement.

USER_NAME

TEXT

User who issued the query.

ROLE_NAME

TEXT

Role that was active in the session at the time of the query.

WAREHOUSE_NAME

TEXT

Warehouse that the query executed on, if any.

WAREHOUSE_SIZE

TEXT

Size of the warehouse when this statement executed.

WAREHOUSE_TYPE

TEXT

Type of the warehouse when this statement executed.

CLUSTER_NUMBER

NUMBER

The cluster (in a multi-cluster warehouse) that this statement executed on.

QUERY_TAG

TEXT

Query tag set for this statement through the QUERY_TAG session parameter.

EXECUTION_STATUS

TEXT

Execution status for the query: resuming_warehouse, running, queued, blocked, success, failed_with_error, or failed_with_incident.

ERROR_CODE

NUMBER

Error code, if the query returned an error

ERROR_MESSAGE

TEXT

Error message, if the query returned an error

START_TIME

TIMESTAMP_LTZ

Statement start time

END_TIME

TIMESTAMP_LTZ

Statement end time. If the query is still running, the END_TIME is the UNIX epoch timestamp (“1970-01-01 00:00:00”), adjusted for the local time zone. E.g. for Pacific Standard Time, this would be “1969-12-31 16:00:00.000 -0800”.

TOTAL_ELAPSED_TIME

NUMBER

Elapsed time (in milliseconds)

BYTES_SCANNED

NUMBER

Number of bytes scanned by this statement.

ROWS_PRODUCED

NUMBER

Number of rows produced by this statement.

COMPILATION_TIME

NUMBER

Compilation time (in milliseconds)

EXECUTION_TIME

NUMBER

Execution time (in milliseconds)

QUEUED_PROVISIONING_TIME

NUMBER

Time (in milliseconds) spent in the warehouse queue, waiting for the warehouse servers to provision, due to warehouse creation, resume, or resize.

QUEUED_REPAIR_TIME

NUMBER

Time (in milliseconds) spent in the warehouse queue, waiting for servers in the warehouse to be repaired.

QUEUED_OVERLOAD_TIME

NUMBER

Time (in milliseconds) spent in the warehouse queue, due to the warehouse being overloaded by the current query workload.

TRANSACTION_BLOCKED_TIME

NUMBER

Time (in milliseconds) spent blocked by a concurrent DML.

OUTBOUND_DATA_TRANSFER_CLOUD

TEXT

Target cloud provider for statements that unload data to another region and/or cloud.

OUTBOUND_DATA_TRANSFER_REGION

TEXT

Target region for statements that unload data to another region and/or cloud.

OUTBOUND_DATA_TRANSFER_BYTES

NUMBER

Number of bytes transferred in statements that unload data to another region and/or cloud.

INBOUND_DATA_TRANSFER_CLOUD

TEXT

Source cloud provider for statements that load data from another region and/or cloud.

INBOUND_DATA_TRANSFER_REGION

TEXT

Source region for statements that load data from another region and/or cloud.

INBOUND_DATA_TRANSFER_BYTES

NUMBER

Number of bytes transferred in statements that load data from another region and/or cloud.

LIST_EXTERNAL_FILES_TIME

NUMBER

Time (in milliseconds) spent listing external files.

CREDITS_USED_CLOUD_SERVICES

NUMBER

Number of credits used for cloud services in the hour.

Examples

Retrieve up to the last 100 queries run in the current session:

select *
from table(information_schema.query_history_by_session())
order by start_time;

Retrieve up to the last 100 queries run by the current user (or run by any user on any warehouse on which the current user has the MONITOR privilege):

select *
from table(information_schema.query_history())
order by start_time;

Retrieve up to the last 100 queries run in the past hour by the current user (or run by any user on any warehouse on which the current user has the MONITOR privilege):

select *
from table(information_schema.query_history(dateadd('hours',-1,current_timestamp()),current_timestamp()))
order by start_time;

Retrieve all queries run by the current user (or run by any user on any warehouse on which the current user has the MONITOR privilege) within a specified 30 minute block of time within the past 7 days:

select *
  from table(information_schema.query_history(
    END_TIME_RANGE_START=>to_timestamp_ltz('2017-12-4 12:00:00.000 -0700'),
    END_TIME_RANGE_END=>to_timestamp_ltz('2017-12-4 12:30:00.000 -0700')));