Monitor/Clean Up the CopyErrorLogs Directory

Posted March 15, 2019 by Jim Knicely, Vertica Principal Solution Architect

Three 3D arrows, different colors pointing in different directions
The COPY statement automatically saves a copy of each rejected row in a rejected-data file. COPY also saves a corresponding explanation of what caused the rejection in an exceptions file. By default, Vertica saves both files in a database catalog subdirectory called CopyErrorLogs.

After you’ve reviewed and resolved any issues with the load, it’s a good idea to monitor/clean up the files that are generated in this directory so that they don’t waste your available storage.

Example: dbadmin=> SELECT catalog_path FROM nodes; catalog_path ---------------------------------------------------------- /home/dbadmin/test_db/v_test_db_node0001_catalog/Catalog (1 row) dbadmin=> CREATE TABLE test (c1 INT); CREATE TABLE dbadmin=> \! ls -lrt /home/dbadmin/test_db/v_test_db_node0001_catalog/CopyErrorLogs total 0 dbadmin=> \! cat /home/dbadmin/test.txt 1 2 A 3 dbadmin=> COPY test FROM '/home/dbadmin/test.txt'; Rows Loaded ------------- 3 (1 row) dbadmin=> \! ls -lrt /home/dbadmin/test_db/v_test_db_node0001_catalog/CopyErrorLogs total 8 -rw------- 1 dbadmin verticadba 2 Mar 14 20:15 test-test.txt-copy-from-rejected-data -rw------- 1 dbadmin verticadba 273 Mar 14 20:15 test-test.txt-copy-from-exceptions Helpful Link:

https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/CapturingLoadExceptionsAndRejections.htm

Have fun!