1.30 COPY: Capturing Load Rejections and Exceptions

COPY: Capturing Load Rejections and Exceptions

• Loading data with COPY has two main phases, parsing and loading.

Rejected data is created whenever COPY cannot parse a row of data.

• Following are some parser errors that can cause a rejected row: 
• Unsupported parser options
• Incorrect data types for the table into which data is being loaded
• Malformed context for the parser in use
• Missing delimiters

Other problems can occur during the load phase, but such problems are not rejected data from parser errors.

• 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, HP Vertica saves both files in a database catalog subdirectory, called CopyErrorLogs, as listed in this example:

v_mart_node003_catalog\CopyErrorLogs\trans-STDIN-copy-from-rejected-data.1
v_mart_node003_catalog\CopyErrorLogs\trans-STDIN-copy-from-exceptions.1

• There are two other ways of saving the COPY rejections and Exceptions.

1. Use the REJECTED DATA reject_file and EXCEPTIONS except_file parameters to save both files to a location of your choice. 

2. Use the REJECTED DATA AS TABLE reject_table clause. Saving rejected rows to a reject_table also retains the exception descriptions.

You can save rejected data and exceptions to a table, or to non-default file locations, but not to both.

If you save COPY rejected data to a table, the table files are stored in the data subdirectory(RejectionTableData directory ).

Example:

 

=> cd v_mart_node003_data\RejectionTableData\
=> ls
TABLE_REJECTED_RECORDS_"bg"_mytest01.verticacorp.-25441:0x6361_45035996273805099_1.1
TABLE_REJECTED_RECORDS_"bg"_mytest01.verticacorp.-25441:0x6361_45035996273805113_2.2
.
.
.
TABLE_REJECTED_RECORDS_"delimr"_mytest01.verticacorp.-5958:0x3d47_45035996273815749_1.1
TABLE_REJECTED_RECORDS_"delimr"_mytest01.verticacorp.-5958:0x3d47_45035996273815749_1.2

• For COPY LOCAL the reject_file and except_file paths must reside on the client. If path resolves to a storage location and the user invoking COPY is not a superuser, the following permissions are required:
   
• The storage location must have been created with the USER usage type
• The user must already have been granted access to the storage location where the files exist