Handling COPY FROM errors when loading with Wildcards

Tibor_Mozes

Hi,

When using wildcards with COPY FROM, I am having difficulty with handling copy errors.

When I run this command:

COPY myTable FROM '/mydirectory/*.dat' ON ANY NODE
EXCEPTIONS '/copy_errors/exceptions/'
REJECTED DATA '/copy_errors/rejected/';

This works if /mydirectory/ has multiple files in them.

But if it is a single file, I get an error:

ERROR: COPY: Could not open rejected data file [/copy_errors/rejected/] for writing: Is a directory.

If I change things around and specify a file instead, the copy picking up multiple files from the directory would break.

This is a huge pain. Is there any workaround other than checking the number files to be picked up before issuing a copy command?

Thank you,
Tibor

PS.: The preview functionality of this message board is keep timing out!!! It took me 5 minutes to post this message

Prasanta_Pal

You can write a shell script ( .sh file ) and use for example
vsql -w<password> -c "COPY test from '/home/dbadmin/export.dat' DELIMITER E'\001' RECORD TERMINATOR E'\003' DIRECT" in the script)  for two choices i) if the number of files is one and 2 ) if the number of files >1

rrusso2007

FYI an alternative here is to pass some dummy file as an additional argument. Vertica doesn't mind that its empty and it forces vertica to always use the multiple files logic.