Amazon Redshift: Check 'stl_load_errors' system table for details

[HenrietteH]

Amazon Redshift: Check 'stl_load_errors' system table for error details

When writing to Amazon Redshift, the following error appears:  

Data Stream In (x): The COPY failed with error: [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState XX000] ERROR: Load into table 'ayx1XXX' failed. Check 'stl_load_errors' system table for details.

Environment

• Alteryx Designer
• In-DB connection to Amazon Redshift

Cause

Amazon Redshift uses the stl_load_errors table to track errors that happen when loading data to a Redshift table. This table contains error messages that will provide necessary detail to determine the cause for an error. 

For more information on the stl_load_errors table, see Amazon's STL LOAD_ERROR documentation. 

Solution

Users with appropriate permissions can access the table themselves to investigate errors:

1. Open a new Alteryx workflow.
2. Drag an Input Tool on the canvas.
3. Connect to the Redshift database.
4. Go to the SQL Editor Window and enter the following query: 

Select * From stl_load_errors

• Run the workflow.
• The table will return an error code and an error reason. Use the column "starttime" to find the right error.
• 
• See Amazon's STL_LOAD_ERROR documentation for a detailed explanation of all fields on the table and their load error reference for a list of errors. 

Solution B 

If the user doesn't have access to the table themselves, their DBA should be able to provide the same information. 

Error Message examples

String length exceeds DDL length

• Input data exceeded the acceptable range for the data type, try increasing the field size in Alteryx to resolve the error. 
• This often happens with multi-byte characters. In Alteryx, field size relates to characters, i.e.the value 'Góðan dag' only needs a field length of 9. In Redshift, field size is in bytes, to write out 'Góðan dag', the field size has to be at least 11. 
• See Amazon's document on Redshift character types for more information.

Multibyte character not supported for CHAR (Hint: try using VARCHAR)

• The CHAR datatype in Redshift only accepts single-byte UTF-8 characters. The VARCHAR datatype accepts multi-byte characters, to a maximum of four bytes. The WString datatype in Alteryx is translated into a CHAR datatype in Redshift. To load multi-byte characters into Redshift, use the V_WString datatype in Alteryx. 

Additional Resources

• STL_LOAD_ERRORS Documentation from Amazon
• Database Issues – Working with Alteryx Customer Support Engineers (CSEs)