STL_QUERY and STL_QUERYTEXT
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