QUERY_HISTORY , QUERY_HISTORY_BY_*¶

Syntax¶

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

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

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

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

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'). 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 => 'boolean_expr'

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¶

• 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 either of the following privileges:

  • The MONITOR or OPERATE privilege on the user-managed warehouses where the queries were run.

  • The MONITOR or OPERATE privilege on the task. Exception: If the task executes an owner’s right stored procedure or UDF, the role requires at least MONITOR privilege on the warehouse on which the task executed to view the stored procedure query and the UDF query.

  • The MONITOR EXECUTION privilege on the account in which the task resides.

  • Exceptions: Neither stored procedures nor user-defined functions (UDFs) can run this query.

  For more information, see Virtual warehouse privileges.

• When you call an Information Schema table function, your session must use the INFORMATION_SCHEMA, or the function name must be fully-qualified. For more information, 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).

• Canceled queries are identified by their error_message text (SQL execution canceled), not by their execution_status value.

Output¶

The function returns the following columns:

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