STL_UNLOAD_LOG - Amazon Redshift

STL_UNLOAD_LOG

Records the details for an unload operation.

STL_UNLOAD_LOG records one row for each file created by an UNLOAD statement. For example, if an UNLOAD creates 12 files, STL_UNLOAD_LOG will contain 12 corresponding rows.

STL_UNLOAD_LOG 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.

Note

STL_UNLOAD_LOG only contains queries run on main clusters. It doesn't contain queries run on concurrency scaling clusters. To access queries run on both main and concurrency scaling clusters, we recommend that you use the SYS monitoring view SYS_UNLOAD_HISTORY and SYS_UNLOAD_DETAIL . The data in the SYS monitoring view is formatted to be easier to use and understand.

Table columns

Column name Data type Description
userid integer ID of the user who generated the entry.
query integer The query ID.
slice integer Number that identifies the slice where the query was running.
pid integer Process ID associated with the query statement.
path character(1280) The complete Amazon S3 object path for the file.
start_time timestamp Start time for the transaction.
end_time timestamp End time for the transaction.
line_count bigint Number of lines (rows) unloaded to the file.
transfer_size bigint Number of bytes transferred.
file_format character(10) Format of unloaded file.

Sample query

To get a list of the files that were written to Amazon S3 by an UNLOAD command, you can call an Amazon S3 list operation after the UNLOAD completes. You can also query STL_UNLOAD_LOG.

The following query returns the pathname for files that were created by an UNLOAD for the last query completed:

select query, substring(path,0,40) as path from stl_unload_log where query = pg_last_query_id() order by path;

This command returns the following sample output:

query | path -------+-------------------------------------- 2320 | s3://my-bucket/venue0000_part_00 2320 | s3://my-bucket/venue0001_part_00 2320 | s3://my-bucket/venue0002_part_00 2320 | s3://my-bucket/venue0003_part_00 (4 rows)