SYS_EXTERNAL_QUERY_DETAIL - Amazon Redshift
Table columnsSample queries
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

SYS_EXTERNAL_QUERY_DETAIL

Use SYS_EXTERNAL_QUERY_DETAIL to view details for queries at a segment level. Each row represents a segment from a particular WLM query with details like the number of rows processed, number of bytes processed, and partition info of external tables in Amazon S3. Each row in this view will also have a corresponding entry in the SYS_QUERY_DETAIL view, except this view has more detail information related to external query processing.

SYS_EXTERNAL_QUERY_DETAIL 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
user_id integer The identifier of the user who submitted the query.
query_id bigint The query identifier of the external query.
transaction_id bigint The transaction identifier.
child_query_sequence integer The sequence of the rewritten user query. Starts with 0, similar to segment_id.
segment_id integer The segment identifier of the query segment.
source_type character(32) The data source type of the query, it could be S3 for Redshift Spectrum, PG for federated query.
start_time timestamp The time when the query began.
end_time timestamp The time when the query completed.
duration bigint The amount of time (microseconds) spent on the query.
total_partitions integer The number of partitions an Amazon S3 query required.
qualified_partitions integer The number of partitions an Amazon S3 query scanned.
scanned_files bigint The number of Amazon S3 files scanned.
returned_rows bigint The number of scanned rows for an Amazon S3 query, or the number of returned rows for a federated query.
returned_bytes bigint The number of scanned bytes for an Amazon S3 query, or the number of returned bytes for a federated query.
file_format text The file format of Amazon S3 files.
file_location text The Amazon S3 location of external table.
external_query_text text The segment level query text for a federated query.
warning_message character(4000) The warning message displayed when the query runs.
table_name character(136) The table name of the step that is being operated.
is_recursive character(1) Indicates whether there is recursive scan for subfolders.
is_nested character(1) Indicates whether the nested column data type is accessed.
s3list_time bigint The duration of file listing in milliseconds.
get_paritition_time long Time spent to list and qualify partitions for a given external object from the Amazon Glue Data Catalog and Apache Hive.

Sample queries

The following query shows the external query details.

SELECT query_id,
       segment_id,
       start_time,
       end_time,
       total_partitions,
       qualified_partitions,
       scanned_files,
       returned_rows,
       returned_bytes,
       trim(external_query_text) query_text,
       trim(file_location) file_location
FROM sys_external_query_detail
ORDER BY query_id, start_time DESC
LIMIT 2;

Sample output.

 query_id | segment_id |         start_time         |          end_time          | total_partitions | qualified_partitions | scanned_files | returned_rows | returned_bytes | query_text | file_location
----------+------------+----------------------------+----------------------------+------------------+----------------------+---------------+---------------+----------------+------------+---------------
   763251 |          0 | 2022-02-15 22:32:23.312448 | 2022-02-15 22:32:24.036023 |                3 |                    3 |             3 |         38203 |        2683414 |            |
   763254 |          0 | 2022-02-15 22:32:40.17103  | 2022-02-15 22:32:40.839313 |                3 |                    3 |             3 |         38203 |        2683414 |            |