- Categories:
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
to10000
Default:
100
.
Usage Notes¶
Returns queries run by the current user. Also returns queries run by any user when the executing role, or a higher role in a hierarchy, has the MONITOR or OPERATE privilege on the warehouse.
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 Snowflake Information Schema.
The values for the columns EXTERNAL_FUNCTION_TOTAL_INVOCATIONS, EXTERNAL_FUNCTION_TOTAL_SENT_ROWS, EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS, EXTERNAL_FUNCTION_TOTAL_SENT_BYTES, and EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES are affected by many factors, including:
The number of external functions in the SQL statement.
The number of rows per batch sent to each remote service.
The number of retries due to transient errors (e.g. because a response was not received within the expected time).
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 specified in the context of the query at compilation. |
SCHEMA_NAME |
TEXT |
Schema that was specified in the context of the query at compilation. |
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 compute resources to provision, due to warehouse creation, resume, or resize. |
QUEUED_REPAIR_TIME |
NUMBER |
Time (in milliseconds) spent in the warehouse queue, waiting for compute resources 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_FILE_TIME |
NUMBER |
Time (in milliseconds) spent listing external files. |
CREDITS_USED_CLOUD_SERVICES |
NUMBER |
Number of credits used for cloud services. |
RELEASE_VERSION |
TEXT |
Release version in the format of |
EXTERNAL_FUNCTION_TOTAL_INVOCATIONS |
NUMBER |
The aggregate number of times that this query called remote services. For important details, see the Usage Notes. |
EXTERNAL_FUNCTION_TOTAL_SENT_ROWS |
NUMBER |
The total number of rows that this query sent in all calls to all remote services. |
EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS |
NUMBER |
The total number of rows that this query received from all calls to all remote services. |
EXTERNAL_FUNCTION_TOTAL_SENT_BYTES |
NUMBER |
The total number of bytes that this query sent in all calls to all remote services. |
EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES |
NUMBER |
The total number of bytes that this query received from all calls to all remote services. |
IS_CLIENT_GENERATED_STATEMENT |
BOOLEAN |
Indicates whether the query was client-generated. |
QUERY_HASH |
TEXT |
The hash value computed based on the canonicalized SQL text. 1 |
QUERY_HASH_VERSION |
NUMBER |
The version of the logic used to compute |
QUERY_PARAMETERIZED_HASH |
TEXT |
The hash value computed based on the parameterized query. 1 |
QUERY_PARAMETERIZED_HASH_VERSION |
NUMBER |
The version of the logic used to compute |
- 1(1,2,3,4)
This column is present only when the 2023_06 behavior change bundle is enabled. This column is part of the query hash feature.
The potential values for the QUERY_TYPE
column include:
CREATE_USER
CREATE_ROLE
CREATE_NETWORK_POLICY
ALTER_ROLE
ALTER_NETWORK_POLICY
ALTER_ACCOUNT
DROP_SEQUENCE
DROP_USER
DROP_ROLE
DROP_NETWORK_POLICY
RENAME_NETWORK_POLICY
REVOKE
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')));