QUERY_HISTORY , QUERY_HISTORY_BY_*¶

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 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:

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

Copy

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;

Copy

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;

Copy

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

Copy