Monitor/Clean Up the CopyErrorLogs Directory

Posted March 15, 2019 by Jim Knicely, Big Data Solutions Architect, Vertica

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!