The Vertica Database Forums

Posted: **Fri Jun 20, 2014 2:53 pm**

I’m having a problem using a copy command with the exceptions and rejected data clause. When I run this type of query:

Copy myTable From ‘/myDirectory/*’ On Any Node
Rejected Data ‘/rejects_directory/’ On node_1, ‘/rejects_directory/’ on node_2
Exceptions ‘/exceptions_directory/’ On node_1, ‘/exceptions_directory/’ on node_2

I get

Error 2016: Copy: Could not open rejected data file for writing: Is a directory

If I change the query to specify a rejection and exception file like so:

Code: Select all

Copy myTable From ‘/myDirectory/*’ On Any Node
Rejected Data ‘/rejects_directory/rejects.txt’ On node_1, ‘/rejects_directory/rejects.txt’ on node_2
Exceptions ‘/exceptions_directory/exceptions.txt’ On node_2, ‘/exceptions_directory/exceptions.txt’ on node_2

I get

Error 2016: Copy: Could not open rejected data file for writing: Is not a directory

If I just comment out the rejected data and exceptions lines, the query works fine, but then I’m stuck searching through each node of my cluster for my reject files. What makes this even stranger is that the table and source directory are passed through as parameter values in a shell script and the query works fine in most cases, but throws the error in others. Any ideas why this is happening?

Posted: **Fri Jun 20, 2014 3:16 pm**

Have you read COPY Rejected Data and Exception Files, Capturing Load Rejections and Exceptions and Saving Load Rejections?

If path resolves to a storage location, and the user invoking COPY is not a superuser, these are the required permissions:

- The storage location must have been created (or altered) with the USER option (see ADD_LOCATION and ALTER_LOCATION_USE)
- The user must already have been granted READ access to the storage location where the file(s) exist, as described in GRANT (Storage Location)

Posted: **Fri Jun 20, 2014 3:42 pm**

ClevelandSteve,

Make sure to use single quotes and not back ticks (backward leaning apostrophes) to surround your file names:

Code: Select all

Copy myTable From '/myDirectory/*' On Any Node
Rejected Data '/rejects_directory/' On node_1, '/rejects_directory/' on node_2
Exceptions '/exceptions_directory/' On node_1, '/exceptions_directory/' on node_2

Posted: **Fri Jun 20, 2014 5:13 pm**

Yes I have read the documentation many times. They are definitely single quotes and not back ticks. Like I said, there is a shell script building the sql syntax for me and the shell loops replacing the target table and source directory. 90% of the time it works fine, but on some of the tables I get the weird error.

Posted: **Fri Jun 20, 2014 8:31 pm**

Of those 10%, are you tracking the actual output that your shell script is writing?

Posted: **Mon Jun 23, 2014 1:35 pm**

Yes, I am using echo and qecho to watch what the variables are set to as the script runs. I also have the -s option turned on in the call to the vsql command line to see the sql that is being executed. There is nothing out of the ordinary anywhere.

Posted: **Thu Jun 26, 2014 2:34 pm**

Is the issue that in some cases you are specifying just a directory and not a file in the the REJECTED DATA and EXCEPTIONS clauses?

The Vertica Database Forums

Copy Rejected Data

Copy Rejected Data

Re: Copy Rejected Data

Re: Copy Rejected Data

Re: Copy Rejected Data

Re: Copy Rejected Data

Re: Copy Rejected Data

Re: Copy Rejected Data