QUERY_HISTORY , QUERY_HISTORY_BY_* | Snowflake Documentation (original) (raw)

You can use the QUERY_HISTORY family of table functions to query Snowflake query history along various dimensions:

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

See also:

Syntax

QUERY_HISTORY( [ END_TIME_RANGE_START => ] [, END_TIME_RANGE_END => ] [, RESULT_LIMIT => ] [, INCLUDE_CLIENT_GENERATED_STATEMENT => ] )

QUERY_HISTORY_BY_SESSION( [ SESSION_ID => ] [, END_TIME_RANGE_START => ] [, END_TIME_RANGE_END => ] [, RESULT_LIMIT => ] [, INCLUDE_CLIENT_GENERATED_STATEMENT => ] )

QUERY_HISTORY_BY_USER( [ USER_NAME => '' ] [, END_TIME_RANGE_START => ] [, END_TIME_RANGE_END => ] [, RESULT_LIMIT => ] [, INCLUDE_CLIENT_GENERATED_STATEMENT => ] )

QUERY_HISTORY_BY_WAREHOUSE( [ WAREHOUSE_NAME => '' ] [, END_TIME_RANGE_START => ] [, END_TIME_RANGE_END => ] [, RESULT_LIMIT => ] [, INCLUDE_CLIENT_GENERATED_STATEMENT => ] )

Arguments

All the arguments are optional.

END_TIME_RANGE_START => _constantexpr_ , . END_TIME_RANGE_END => _constantexpr_

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

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 => _constantexpr_

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'). You cannot specify SYSTEM (USER_NAME =>'SYSTEM'), which is a background service rather than a user. However, you can filter on user_name='SYSTEM' when you run queries against QUERY_HISTORY table functions.

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.

INCLUDE_CLIENT_GENERATED_STATEMENT => '_booleanexpr_'

Specifies whether client-generated statements are included in table function queries (given the value of the is_client_generated_statement column).

Default: FALSE.

The ACCOUNT_USAGE QUERY_HISTORY view also contains an is_client_generated_statement column, but queries of this view return all statements, whether or not they are client-generated. If necessary, you can filter the query result.

Usage notes

Query retry columns

A query might need to be retried one or more times in order to successfully complete. There can be multiple causes that result in a query retry. Some of these causes are actionable, that is, a user can make changes to reduce or eliminate query retries for a specific query. For example, if a query is retried due to an out of memory error, modifying warehouse settings might resolve the issue.

Some query retries are caused by a fault that is not actionable. That is, there is no change a user can make to prevent the query retry. For example, a network outage might result in a query retry. In this case, there is no change to the query or to the warehouse that executes it that can prevent the query retry.

The QUERY_RETRY_TIME, QUERY_RETRY_CAUSE, and FAULT_HANDLING_TIME columns can help you optimize queries that are retried and better understand fluctuations in query performance.

Output

The function returns the following columns:

Column Name Data Type Description
query_id VARCHAR The statement’s unique id.
query_text VARCHAR Text of the SQL statement.
database_name VARCHAR Database that was specified in the context of the query at compilation.
schema_name VARCHAR Schema that was specified in the context of the query at compilation.
query_type VARCHAR 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 VARCHAR User who issued the query.
user_type VARCHAR The type of user executing the query. It’s the same as the type column in the USERS view. If a Snowpark Container Services service executes the query, the user type is SNOWFLAKE_SERVICE (see Accessing service query history).
user_database_name VARCHAR When the value in the user_type column is SNOWFLAKE_SERVICE, it specifies the service’s database name; otherwise, it’s NULL.
user_schema_name VARCHAR When the value in the user_type column is SNOWFLAKE_SERVICE, it specifies the service’s schema name; otherwise, it’s NULL.
role_name VARCHAR Role that was active in the session at the time of the query.
warehouse_name VARCHAR Warehouse that the query executed on, if any.
warehouse_size VARCHAR Size of the warehouse when this statement executed.
warehouse_type VARCHAR 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 VARCHAR Query tag set for this statement through the QUERY_TAG session parameter.
execution_status VARCHAR 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 VARCHAR 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 VARCHAR Target cloud provider for statements that unload data to another region and/or cloud.
outbound_data_transfer_region VARCHAR 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 VARCHAR Source cloud provider for statements that load data from another region and/or cloud.
inbound_data_transfer_region VARCHAR Source region for statements that load data from another region and/or cloud.
inbound_data_transfer_bytes NUMBER Number of bytes transferred in a replication operation from another account. The source account could be in the same region or a different region than the current account.
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 VARCHAR Release version in the format of major_release.minor_release.patch_release.
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 VARCHAR The hash value computed based on the canonicalized SQL text.
query_hash_version NUMBER The version of the logic used to compute QUERY_HASH.
query_parameterized_hash VARCHAR The hash value computed based on the parameterized query.
query_parameterized_hash_version NUMBER The version of the logic used to compute QUERY_PARAMETERIZED_HASH.
transaction_id NUMBER ID of the transaction that contains the statement or 0 if the statement is not executed within a transaction.
query_acceleration_bytes_scanned NUMBER Number of bytes scanned by the query acceleration service.
query_acceleration_partitions_scanned NUMBER Number of partitions scanned by the query acceleration service.
query_acceleration_upper_limit_scale_factor NUMBER Upper limit scale factor that a query would have benefited from.
bytes_written_to_result NUMBER Number of bytes written to a result object. For example, SELECT * FROM ... would produce a set of results in tabular format representing each field in the selection. . . In general, the results object represents whatever is produced as a result of the query, and bytes_written_to_result represents the size of the returned result.
rows_written_to_result NUMBER Number of rows written to a result object. For CREATE TABLE AS SELECT (CTAS) and all DML operations, this result is 1.
rows_inserted NUMBER Number of rows inserted by the query.
query_retry_time NUMBER Total execution time (in milliseconds) for query retries caused by actionable errors. For more information, see Query retry columns.
query_retry_cause VARCHAR Error that caused the query to retry. If there is no query retry, the field is NULL. For more information, see Query retry columns.
fault_handling_time NUMBER Total execution time (in milliseconds) for query retries caused by errors that are not actionable. For more information, see Query retry columns.

The potential values for the query_type column include:

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 in 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')));

Retrieve the number of client-generated statements that were run against a warehouse named my_xsmall_wh:

SELECT COUNT(*) FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_WAREHOUSE( WAREHOUSE_NAME => 'my_xsmall_wh', INCLUDE_CLIENT_GENERATED_STATEMENT => TRUE));