This page explains how to download queries in a Jupyter Notebook. The queries can then be analyzed using the data-lineage library.
Amazon Redshift has many system tables and views that contain information about how the system is functioning.
STL_QUERYTEXT captures the query text of SQL commands.
STL_QUERY returns execution information about a database query.
The following SQL queries are captured in these two tables.
- SELECT, SELECT INTO
- INSERT, UPDATE, DELETE
- VACUUM, ANALYZE
- CREATE TABLE AS (CTAS)
STL_QUERY stores a part of the query text. To reconstruct the SQL stored in the text column of STL_QUERYTEXT, run a SELECT statement to create SQL from 1 or more parts in the text column. Before running the reconstructed SQL, replace any (\n) special characters with a new line. The result of the following SELECT statement is rows of reconstructed SQL in the query_statement field.
Use the following query to capture queries for a specific time range.
Store query results in a CSV file
Store the results of the above query using psql. Run the command below where query.sql contains the query in the previous section.
Pro-tip: Persist System Tables in AWS Redshift
System tables in AWS Redshift store data only for a few days. Persist the these tables to normal tables in the same instance or in another database. AWSLabs provides sample scripts to persist system tables in AWS Redshift
Snowflake stores queries in the information schema in QUERY_HISTORY table.
The following query returns queries run in the last 7 days
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.
- Install Snowflake Python Connector
- Connect to Snowflake using one of the authentication methods.
- Run query and store results in an array.