Menu
Is there a way to clear out the contents of the stl_load_errors table in Amazon's Redshift?
I am running batch processes to COPY into Redshift and it would be convenient if I could view the entire stl_load_errors in one go without having to filter by a time range.
When I attempt to DELETE FROM stl_load_errors, I get "ERROR: cannot delete from a system table"
When I attempt to TRUNCATE stl_load_errors, I get "ERROR: permission denied: "stl_load_errors" is a system catalog"
308
If you are trying to empty a table of rows, without removing the table, use the DELETE or TRUNCATE command. DROP TABLE removes constraints that exist on the target table. Multiple tables can be removed with a single DROP TABLE command.
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.
Drag an Input Tool on the canvas. Connect to the Redshift database. 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.
The following Amazon Redshift system tables can be helpful in troubleshooting data load issues: Query STL_LOAD_ERRORS to discover the errors that occurred during specific loads.
Nope, you can't delete from that table. It's worth noting that Redshift will automatically clear down that table over time, i.e., it doesn't hold all load errors forever. Show activity on this post.
Query STL_LOAD_ERRORS to discover the errors that occurred during specific loads. Query STL_FILE_SCAN to view load times for specific files or to see if a specific file was even read. Query STL_S3CLIENT_ERROR to find details for errors encountered while transferring data from Amazon S3.
Nope, you can't delete from that table.
It's worth noting that Redshift will automatically clear down that table over time, i.e., it doesn't hold all load errors forever.
128
You can't delete from stl_load_errors but if you use COPY query from S3 you can filter the SELECT from stl_load_errors using filename.
for example:
select * from stl_load_errors where filename like 's3://BUCKET/PREFIX_OF_PATH%'
The stl_load_errors will delete old data (usually a week old), so you don't need to worry about disk space.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
