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)
