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