STL_FILE_SCAN - Amazon Redshift

STL_FILE_SCAN

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

Querying this view 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 view 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.
is_partial integer Value that if true (1) indicates the input file is split into ranges during a COPY operation. If this value is false (0), the input file isn't split.
start_offset bigint Value that, if the input file is split during a COPY operation, indicates the offset value of the split (in bytes). If the file isn't split, this value is 0.

Sample queries

The following query retrieves the names and load times of any files that took over 1,000,000 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)