Copy Rejected Data

Moderator: NorbertKrupa

ClevelandSteve
Newbie
Newbie
Posts: 16
Joined: Fri Aug 09, 2013 7:10 pm

Copy Rejected Data

Post by ClevelandSteve » 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:

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
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?

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Copy Rejected Data

Post by NorbertKrupa » 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)
Checkout vertica.tips for more Vertica resources.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Copy Rejected Data

Post by JimKnicely » 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
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

ClevelandSteve
Newbie
Newbie
Posts: 16
Joined: Fri Aug 09, 2013 7:10 pm

Re: Copy Rejected Data

Post by ClevelandSteve » 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.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Copy Rejected Data

Post by NorbertKrupa » Fri Jun 20, 2014 8:31 pm

Of those 10%, are you tracking the actual output that your shell script is writing?
Checkout vertica.tips for more Vertica resources.

ClevelandSteve
Newbie
Newbie
Posts: 16
Joined: Fri Aug 09, 2013 7:10 pm

Re: Copy Rejected Data

Post by ClevelandSteve » 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.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Copy Rejected Data

Post by JimKnicely » 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?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Data Load”