community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More

Amazon Redshift: Check 'stl_load_errors' system table for details

Alteryx
Alteryx
Created on

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.
  • 2019-11-08_12-33-45.jpg
  • 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