How to handle duplicate key errors as REJECTED DATA during COPY

joergschaber · May 2022

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 · May 2022

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 · May 2022

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 · June 2022

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.

We're Moving!

Create My New Community Account Now

How to handle duplicate key errors as REJECTED DATA during COPY

Answers

Leave a Comment