STL_FILE_SCAN

Returns the files that Amazon Redshift read while loading data via the COPY command.

Querying this table can help troubleshoot data load errors. STL_FILE_SCAN can be particularly helpful with pinpointing issues in parallel data loads because parallel data loads typically load many files with a single COPY command.

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.
query	integer	Query ID. The query column can be used to join other system tables and views.
slice	integer	Number that identifies the slice where the query was running.
name	character(90)	Full path and name of the file that was loaded.
lines	bigint	Number of lines read from the file.
bytes	bigint	Number of bytes read from the file.
loadtime	bigint	Amount of time spent loading the file (in microseconds).
curtime	Timestamp	Timestamp representing the time that Amazon Redshift started processing the file.

Sample queries

The following query retrieves the names and load times of any files that took over 1000000 microseconds for Amazon Redshift to read:

select trim(name)as name, loadtime from stl_file_scan
where loadtime > 1000000;

This query returns the following example output:

           name            | loadtime
---------------------------+----------
 listings_pipe.txt         |  9458354
 allusers_pipe.txt         |  2963761
 allevents_pipe.txt        |  1409135
 tickit/listings_pipe.txt  |  7071087
 tickit/allevents_pipe.txt |  1237364
 tickit/allusers_pipe.txt  |  2535138
 listings_pipe.txt         |  6706370
 allusers_pipe.txt         |  3579461
 allevents_pipe.txt        |  1313195
 tickit/allusers_pipe.txt  |  3236060
 tickit/listings_pipe.txt  |  4980108
(11 rows)