Try it now and let us know what you think. Switch to the new look >>
You can return to the original look by selecting English in the language selector above.
STL_QUERYTEXT
Captures the query text for SQL commands.
Query the STL_QUERYTEXT table to capture the SQL that was logged for the following statements:
-
SELECT, SELECT INTO
-
INSERT, UPDATE, DELETE
-
COPY
-
UNLOAD
-
VACUUM, ANALYZE
-
CREATE TABLE AS (CTAS)
To query activity for these statements over a given time period, join the STL_QUERYTEXT and STL_QUERY tables.
Note
The STL_QUERY and STL_QUERYTEXT tables only contain information about queries, not other utility and DDL commands. For a listing and information on all statements executed by Amazon Redshift, you can also query the STL_DDLTEXT and STL_UTILITYTEXT tables. For a complete listing of all statements executed by Amazon Redshift, you can query the SVL_STATEMENTTEXT view.
See also STL_DDLTEXT, STL_UTILITYTEXT, and SVL_STATEMENTTEXT.
This table is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see Visibility of Data in System Tables and Views.
Table Columns
| Column Name | Data Type | Description |
|---|---|---|
| userid | integer | ID of the user who generated the entry. |
| xid | bigint | Transaction ID. |
| pid | integer | Process ID. Normally, all of the queries in a session are run in the same process, so this value usually remains constant if you run a series of queries in the same session. Following certain internal events, Amazon Redshift might restart an active session and assign a new PID. For more information, see STL_RESTARTED_SESSIONS. You can use this column to join to the STL_ERROR table. |
| query | integer | Query ID. The query column can be used to join other system tables and views. |
| sequence | integer | When a single statement contains more than 200 characters, additional rows are logged for that statement. Sequence 0 is the first row, 1 is the second, and so on. |
| text | character(200) | SQL text, in 200-character increments. This field might contain special characters
such as backslash (\\) and newline (\n).
|
Sample Queries
You can use the PG_BACKEND_PID() function to retrieve information for the current session. For example, the following query returns the query ID and a portion of the query text for queries executed in the current session.
select query, substring(text,1,60) from stl_querytext where pid = pg_backend_pid() order by query desc; query | substring -------+-------------------------------------------------------------- 28262 | select query, substring(text,1,80) from stl_querytext where 28252 | select query, substring(path,0,80) as path from stl_unload_l 28248 | copy category from 's3://dw-tickit/manifest/category/1030_ma 28247 | Count rows in target table 28245 | unload ('select * from category') to 's3://dw-tickit/manifes 28240 | select query, substring(text,1,40) from stl_querytext where (6 rows)
Reconstructing Stored SQL
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.
SELECT query, LISTAGG(CASE WHEN LEN(RTRIM(text)) = 0 THEN text ELSE RTRIM(text) END) WITHIN GROUP (ORDER BY sequence) as query_statement, COUNT(*) as row_count FROM stl_querytext GROUP BY query ORDER BY query desc;
For example, the following query selects 3 columns. The query itself is longer than 200 characters and is stored in parts in STL_QUERYTEXT.
select 1 AS a0123456789012345678901234567890123456789012345678901234567890, 2 AS b0123456789012345678901234567890123456789012345678901234567890, 3 AS b012345678901234567890123456789012345678901234 FROM stl_querytext;
In this example, the query is stored in 2 parts (rows) in the text column of STL_QUERYTEXT.
select query, sequence, text from stl_querytext where query=pg_last_query_id() order by query desc, sequence limit 10;
query | sequence | text -------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 45 | 0 | select\n1 AS a0123456789012345678901234567890123456789012345678901234567890,\n2 AS b0123456789012345678901234567890123456789012345678901234567890,\n3 AS b012345678901234567890123456789012345678901234 45 | 1 | \nFROM stl_querytext;
To reconstruct the SQL stored in STL_QUERYTEXT, run the following SQL.
select LISTAGG(CASE WHEN LEN(RTRIM(text)) = 0 THEN text ELSE RTRIM(text) END, '') within group (order by sequence) AS text from stl_querytext where query=pg_last_query_id();
To use the resulting reconstructed SQL in your client, replace any (\n) special characters with a
new line.
text ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ select\n1 AS a0123456789012345678901234567890123456789012345678901234567890,\n2 AS b0123456789012345678901234567890123456789012345678901234567890,\n3 AS b012345678901234567890123456789012345678901234\nFROM stl_querytext;
