Error loading data using PloadDelimitedSource, especially with REJECTED DATA and EXCEPTIONS

David_Chen

We managed to install and use the pload library.  However, we are having trouble getting the REJECTED DATA and EXCEPTIONS clauses to work.  They seem to only work with COPY LOCAL.

For example:

COPY some_schema.some_table with source PloadDelimitedSource(file='/tmp/some_file.txt') DELIMITER E'\t' REJECTED DATA '/tmp/failed.rejected_lines' EXCEPTIONS '/tmp/failed.why_they_failed' DIRECT

I get the following error:

vsql:example.sql:10: ERROR 4368:  Permission denied for storage location [/tmp/failed.why_they_failed]

But locally, all permissions are ok (especially since it is /tmp)

David_Chen

In https://my.vertica.com/docs/6.1.x/HTML/index.htm#20146.htm it says:

Exceptions and Rejected Data Files

Because Pload splits large files across multiple cores on the server, exceptions and rejected data files can exist for multiple process threads that COPY creates. The files are not correlated automatically but are available in the standard location, unless you specify another path with one or both of the COPY EXCEPTIONS and REJECTED DATA parameters. For more information, see Controlling Load Exceptions and Rejections.

This implies that we can use EXCEPTIONS and REJECTED DATA parameters to specify where the rejected data goes, but so far, no success. 

id10t

Hi!

PLoader is deprecated(depends on Vertica version).
Review: Retired Functionality

Raul_1

The error message points to the storage location -
Please try creating the storage location and granting READ/WRITE permissions to the user -
Something like the following -
dbadmin=> create location '/tmp' NODE 'v_DBNAME_node0001' usage 'user';
CREATE LOCATION
dbadmin=> grant all on location '/tmp' to NAME-OF-USER;
GRANT PRIVILEGE