Analyze Queries from Snowflake

QUERY_HISTORY

Snowflake stores queries in the information schema in QUERY_HISTORY table.

The following query returns queries run in the last 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')));

Python Connector

In a Jupyter Notebook, use the snowflake-python-connector to connect to snowflake and store the queries in an array. Then use the data-lineage library to visualize or process the lineage.

Steps

  1. Install Snowflake Python Connector
  2. Connect to Snowflake using one of the authentication methods.
  3. Run query and store results in an array.
import snowflake.connector
query = """
select query_text
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')));
"""
connection = snowflake.connector.connect(...)
queries = []
with connection.get_cursor() as cursor:
cursor.execute(query)
row = cursor.fetchone()
while row is not None:
queries.append(row[0])