Clever Geek Handbook

How to clear stl_load_errors table in Redshift?

Is there a way to clear the contents of a table stl_load_errors

in Amazon Redshift?

I am running batch processes in COPY

Redshift, and it would be handy if I could view all stl_load_errors in one go without filtering out for the time range.

When I try to execute DELETE FROM stl_load_errors

I get the message "ERROR: Unable to delete from system table"

When I try to execute TRUNCATE stl_load_errors

, I get the message "ERROR: allowed: stl_load_errors" is the system directory "

+3


source to share


3 answers


No, you cannot delete from this table.



It's worth noting that Redshift will automatically delete this table over time, i.e. all download errors will not be saved forever.

+7


source


You cannot remove from stl_load_errors, but if you are using a COPY request from S3, you can filter the SELECT from stl_load_errors using the filename. eg: select * from stl_load_errors where filename like 's3://BUCKET/PREFIX_OF_PATH%'



stl_load_errors will delete old data (usually a week old), so you don't have to worry about disk storage.

+2


source


You can use below query to get all errors for your copy command.

SELECT err.userid,
       err.process,
       err.recordtime,
       err.pid,
       err.errcode,
       err.file,
       err.linenum,
       err.context,
       err.error
FROM stl_error err,
     stv_recents rec
WHERE rec.pid=err.pid
  AND rec.status='running'
  AND rec.query LIKE 'COPY%';

Please edit copy%

in the above part of the request according to your team.

+1


source






More articles:


All Articles