Query History: Information Schema Cloudyard

Read Time:2 Minute, 11 Second

Information Schema: The Snowflake Information Schema (aka “Data Dictionary”) consists of a set of system-defined views and table functions that provide extensive metadata information about the objects created in your account.

The Snowflake Information Schema is based on the SQL-92 ANSI Information Schema, But with the addition of views and functions that are specific to Snowflake.

Each database created in your account automatically includes a built-in, read-only schema named INFORMATION_SCHEMA. The schema contains the following objects:

Views for all the objects contained in the database, as well as views for account-level objects.
- non-database objects such as roles, warehouses, and databases)
Table functions for historical and usage data across your account.

General Usage Notes:

Each INFORMATION_SCHEMA schema is read-only i.e. cannot be modified or dropped.
Queries on INFORMATION_SCHEMA views do not guarantee consistency with respect to concurrent DDL.
- For example, if tables created while a long-running INFORMATION_SCHEMA query executes. The result of the query may include some, none, or all of the tables created.
The output of a view or table function depend on the privileges granted to the user’s current role. Only objects for which the current role has been granted access privileges are returned.
To prevent performance issues, the following error is returned if the filters specified in an INFORMATION_SCHEMA query are not sufficiently selective:

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;

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

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

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

Fetch up to the last 100 queries run in the past hour by the current user.

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

Extract 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 15 days:

select *
  from table(information_schema.query_history(
  end_time_range_start=>to_timestamp_ltz('2020-09-11 12:00:00.000 -0700'),
    end_time_range_end=>to_timestamp_ltz('2020-09-24 12:30:00.000 -0700')));