How to handle duplicate key errors as REJECTED DATA during COPY

joergschaber

Hi,

is there a way to handle duplicate key errors during COPY the same way as parsing errors, i.e. load them into the REJECTED DATA TABLE?
Currently, I catch the exception, create a tmp table without constraints re-run the COPY statement and then INSERT the distinct rows into the original table. However, that is not possible when I want to automatically run COPY using a scheduler.

best,

Jörg

SruthiA

if you are loading json data, then there is one option. please use --parser-parameters and pass reject_on_duplicate to true and it will reject the rows with duplicate key errors.. please find the below links for the same.

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/KafkaIntegrationGuide/UtilityOptions/LoadSpecUtilityOptions.htm?

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/KafkaIntegrationGuide/KafkaFunctions/KafkaJSONParser.htm?Highlight=reject_on_duplicate

joergschaber

Thanks for the hint, but my COPY comand still throws an duplicate key error. The command I use is

COPY <TABLE> SOURCE
KAFKASOURCE(stream='<TOPIC>|0|-2,<TOPIC>|1|-2',
brokers = '<SERVER>:<PORT>',
group_id = '<GROUP>', executionparallelism = 2, stop_on_eof=true)
PARSER KafkaJSONParser(reject_on_duplicate=TRUE) 
REJECTED DATA AS TABLE rejection_table DIRECT 
NO COMMIT;

Bryan_H

Could you share the Vertica server version, exact text of the COPY error, and if possible a sample JSON that triggers the error? Please open a support case if you'd rather not share details on public forum.