Download Queries from AWS Redshift
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.
WITH query_sql AS ( SELECT query, LISTAGG(text) WITHIN GROUP (ORDER BY sequence) AS sql FROM stl_querytext GROUP BY 1 ) SELECT q.query, userid, xid, pid, starttime, endtime, DATEDIFF(milliseconds, starttime, endtime)/1000.0 AS duration, TRIM(database) AS database, (CASE aborted WHEN 1 THEN TRUE ELSE FALSE END) AS aborted, sql FROM stl_query q JOIN query_sql qs ON (q.query = qs.query) WHERE endtime between '2018-12-01 00:00:00' and '2018-12-14 00:00:00' ORDER BY starttime;
Store the results of the above query using psql. Run the command below where query.sql contains the query in the previous section.
PGPASSWORD=<password> psql -h <host> -U <user> -p 5439 \ -f query.sql -F ',' -o queries.csv
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