COPY COMMAND REJECT DATA

@Jim_Knicely Any inputs that you could provide?

Seems like your front end asked the rejected count, which you get
Try adding the REJECTED DATA and the EXCEPTION clauses - like at the bottom of the better formatted COPY command I pasted below. You just have to specify good directories - /tmp exists in any UNIX system - and you'll find the rejected data and why they were rejected there ...

COPY ETL_LOAD_TAB ( 
   ID_V FILLER VARCHAR(50)
, ID As CASE WHEN LENGTH(ID_V) > 0 THEN ID_V ELSE NULL END
,  FIELD_V FILLER VARCHAR(100)
,  FIELD As CASE WHEN LENGTH(FIELD_V) > 0 THEN FIELD_V ELSE NULL END
,  VALUE_V FILLER VARCHAR(100)
,  VALUE As CASE WHEN LENGTH(VALUE_V) > 0 THEN VALUE_V ELSE NULL END
,  ERROR_MESSAGE_V FILLER VARCHAR(250)
,  ERROR_MESSAGE As CASE WHEN LENGTH(ERROR_MESSAGE_V) > 0 THEN ERROR_MESSAGE_V ELSE NULL END
,  ACTION_TAKEN_V FILLER VARCHAR(100)
,  ACTION_TAKEN As CASE WHEN LENGTH(ACTION_TAKEN_V) > 0 THEN ACTION_TAKEN_V ELSE NULL END
,  LOAD_ID
,  SEVERITY
,  CREATED_BY_ID_V FILLER VARCHAR(12)
,  CREATED_BY_ID As CASE WHEN LENGTH(CREATED_BY_ID_V) > 0 THEN CREATED_BY_ID_V ELSE NULL END
,  CREATION_TS_V FILLER VARCHAR(50)
,  PROCESS_STAGE_V FILLER VARCHAR(10)
,  PROCESS_STAGE As CASE WHEN LENGTH(PROCESS_STAGE_V) > 0 THEN PROCESS_STAGE_V ELSE NULL END
,  RECORD_NUM
) 
FROM local STDIN RECORD TERMINATOR E'\r\n' 
DELIMITER E'|' ENCLOSED BY '' ENFORCELENGTH 
DIRECT NULL AS 'null' returnrejected 
REJECTED DATA '/tmp/etl_load.bad'
EXCEPTIONS '/tmp/etl_load.log'
NO COMMIT;

Thanks @marcothesane . As per the link https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/CapturingLoadExceptionsAndRejections.htm " Since the statement does not include either the REJECTED DATA or EXCEPTIONS parameters, rejected data and exceptions files are written to the default location, the database catalog subdirectory, CopyErrorLogs, on each node:" Based on vertica documentation I was trying to check if the rejections/exceptions were written to the catalog directory but i didnt find any, So as per you suggestion we need to specify the rejections and exceptions path?

@sreeblr - I think the issue might be that you are trying to do a COPY LOCAL... The rejected and exception files do not show up on the server for these.

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/COPYLOCALRejectionAndExceptionFiles.htm

It seems to work as documented on my 9.x single node setup using a csv file as the source and copy issued in vsql with and without specifying the "reject data" and "exceptions" args (first two tests below). Seems to work with STDIN as well (last test below).

FROM FILE DEFAULT
dbadmin=> ! ls /vertica/data/VMart/v_vmart_node0001_catalog/CopyErrorLogs
badtext-badtext.txt-copy-from-exceptions foo-foo.csv-copy-from-rejected-data
badtext-badtext.txt-copy-from-rejected-data jira66017-badrow.csv-copy-from-exceptions
foo-foo.csv-copy-from-exceptions jira66017-badrow.csv-copy-from-rejected-data
dbadmin=> ! cat /vertica/data/VMart/v_vmart_node0001_catalog/CopyErrorLogs/foo-foo.csv-copy-from-exceptions
COPY: Input record 3 has been rejected (Invalid integer format 'z' for column 1 (c1)). Please see /vertica/data/VMart/v_vmart_node0001_catalog/CopyErrorLogs/foo-foo.csv-copy-from-rejected-data, record 1 for the rejected record.
COPY: Loaded 2 rows, rejected 1 rows.
dbadmin=> ! cat /vertica/data/VMart/v_vmart_node0001_catalog/CopyErrorLogs/foo-foo.csv-copy-from-rejected-data
z,z,z,z
FROM FILE SPECIFYING REJECT DATA AND EXCEPTIONS
dbadmin=> copy foo from '/home/dbadmin/foo.csv' delimiter ',' rejected data '/tmp/foorejects.txt' exceptions '/tmp/fooexcepts.txt';
NOTICE 7850: In a multi-threaded load, rejected record data may be written to additional files
HINT: Exceptions may be written to files [/tmp/fooexcepts.txt], [/tmp/fooexcepts.txt.1], etc

Rows Loaded

       2

(1 row)
dbadmin=> ! cat /tmp/fooexcepts.txt
COPY: Input record 3 has been rejected (Invalid integer format 'z' for column 1 (c1)). Please see /tmp/foorejects.txt, record 1 for the rejected record.
COPY: Loaded 2 rows, rejected 1 rows.
dbadmin=> ! cat /tmp/foorejects.txt
z,z,z,z
FROM STDIN DEFAULT
dbadmin=> copy foo from stdin delimiter ',';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

w,w,w,w
.

dbadmin=> ! ls -l /vertica/data/VMart/v_vmart_node0001_catalog/CopyErrorLogs
total 48
-rw-------. 1 dbadmin verticadba 468 Feb 11 2019 badtext-badtext.txt-copy-from-exceptions
-rw-------. 1 dbadmin verticadba 83 Feb 11 2019 badtext-badtext.txt-copy-from-rejected-data
-rw-------. 1 dbadmin verticadba 267 Mar 11 08:46 foo-foo.csv-copy-from-exceptions
-rw-------. 1 dbadmin verticadba 8 Mar 11 08:46 foo-foo.csv-copy-from-rejected-data
-rw-------. 1 dbadmin verticadba 267 Mar 11 09:00 foo-STDIN-copy-from-exceptions.1
-rw-------. 1 dbadmin verticadba 8 Mar 11 09:00 foo-STDIN-copy-from-rejected-data.1
-rw-------. 1 dbadmin verticadba 13348 Feb 11 2019 jira66017-badrow.csv-copy-from-exceptions
-rw-------. 1 dbadmin verticadba 5340 Feb 11 2019 jira66017-badrow.csv-copy-from-rejected-data
dbadmin=> ! cat /vertica/data/VMart/v_vmart_node0001_catalog/CopyErrorLogs/foo-STDIN-copy-from-exceptions.1
COPY: Input record 1 has been rejected (Invalid integer format 'w' for column 1 (c1)). Please see /vertica/data/VMart/v_vmart_node0001_catalog/CopyErrorLogs/foo-STDIN-copy-from-rejected-data.1, record 1 for the rejected record.
COPY: Loaded 0 rows, rejected 1 rows.
dbadmin=> ! cat /vertica/data/VMart/v_vmart_node0001_catalog/CopyErrorLogs/foo-STDIN-copy-from-rejected-data.1
w,w,w,w

Good catch Jim, I missed the local. This is correct, on the local client there is no catalog dir so you have to use the rejected data and exceptions keywords and specify valid paths on the local machine.