STL_LOADERROR_DETAIL - Amazon Redshift

STL_LOADERROR_DETAIL

Displays a log of data parse errors that occurred while using a COPY command to load tables. To conserve disk space, a maximum of 20 errors per node slice are logged for each load operation.

A parse error occurs when Amazon Redshift cannot parse a field in a data row while loading it into a table. For example, if a table column is expecting an integer data type and the data file contains a string of letters in that field, it causes a parse error.

Query STL_LOADERROR_DETAIL for additional details, such as the exact data row and column where a parse error occurred, after you query STL_LOAD_ERRORS to find out general information about the error.

The STL_LOADERROR_DETAIL view contains all data columns including and prior to the column where the parse error occurred. Use the VALUE field to see the data value that was actually parsed in this column, including the columns that parsed correctly up to the error.

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.

Note

STL_LOADERROR_DETAIL 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_LOAD_ERROR_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.
slice integer Slice where the error occurred.
session integer Session ID for the session performing the load.
query integer Query ID. The query column can be used to join other system tables and views.
filename character(256) Complete path to the input file for the load.
line_number bigint Line number in the load file with the error.
field integer Field with the error.
colname character(1024) Column name.
value character(1024) Parsed data value of the field. (May be truncated.) Multibyte characters in the load data are replaced with a period.
is_null integer Whether or not the parsed value is null.
type character(10) Data type of the field.
col_length character(10) Column length, if applicable. This field is populated when the data type has a limit length. For example, for a column with a data type of "character(3)", this column will contain the value "3".

Sample query

The following query joins STL_LOAD_ERRORS to STL_LOADERROR_DETAIL to view the details of a parse error that occurred while loading the EVENT table, which has a table ID of 100133:

select d.query, d.line_number, d.value, le.raw_line, le.err_reason from stl_loaderror_detail d, stl_load_errors le where d.query = le.query and tbl = 100133;

The following sample output shows the columns that loaded successfully, including the column with the error. In this example, two columns successfully loaded before the parse error occurred in the third column, where a character string was incorrectly parsed for a field expecting an integer. Because the field expected an integer, it parsed the string "aaa", which is uninitialized data, as a null and generated a parse error. The output shows the raw value, parsed value, and error reason:

query | line_number | value | raw_line | err_reason -------+-------------+-------+----------+---------------- 4 | 3 | 1201 | 1201 | Invalid digit 4 | 3 | 126 | 126 | Invalid digit 4 | 3 | | aaa | Invalid digit (3 rows)

When a query joins STL_LOAD_ERRORS and STL_LOADERROR_DETAIL, it displays an error reason for each column in the data row, which simply means that an error occurred in that row. The last row in the results is the actual column where the parse error occurred.