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/source*' 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:
Copy myTable From '/myDirectory/source*' 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_1, '/exceptions_directory/exceptions.txt' on node_2
I get:
Error 2016: Copy: Could not open rejected data file for writing: Is not a directory
Are you kidding me? I specified a directory in the first query! 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? Additional info, I'm using version 6.1.3. The rejected and exception directories are a common mount point on each node. Each source file being loaded has a unique name, therefore the reject files will have a unique name.
Copy Rejected Data
select * from nodes;
Take those node names and use in them in the COPY as below.
dbadmin=> Copy boston.table_restore From '/home/dbadmin/table_restore*' On Any Node
dbadmin-> Rejected Data '/home/dbadmin/rejects.txt' On v_proj_test_node0001, '/home/dbadmin/rejects.txt' on v_proj_test_node0002
dbadmin-> Exceptions '/home/dbadmin/exceptions.txt' On v_proj_test_node0001, '/home/dbadmin/exceptions.txt' on v_proj_test_node0002;
Rows Loaded
-------------
4
(1 row)
-rw-rw-r-- 1 dbadmin dbadmin 65 Sep 24 15:34 table_restore.txt
-rw-rw-r-- 1 dbadmin dbadmin 12 Sep 24 15:46 rejects.txt
-rw-rw-r-- 1 dbadmin dbadmin 207 Sep 24 15:46 exceptions.txt
ppal1:/home/dbadmin $ vi exceptions.txt
COPY: Input record 5 has been rejected (Invalid integer format 'sadhgjsad?' for column 1 (id)). Please see /home/dbadmin/rejects.txt, record 1 for the rejected record.
COPY: Loaded 4 rows, rejected 1 rows.
Take those node names and use in them in the COPY as below.
dbadmin=> Copy boston.table_restore From '/home/dbadmin/table_restore*' On Any Node
dbadmin-> Rejected Data '/home/dbadmin/rejects.txt' On v_proj_test_node0001, '/home/dbadmin/rejects.txt' on v_proj_test_node0002
dbadmin-> Exceptions '/home/dbadmin/exceptions.txt' On v_proj_test_node0001, '/home/dbadmin/exceptions.txt' on v_proj_test_node0002;
Rows Loaded
-------------
4
(1 row)
-rw-rw-r-- 1 dbadmin dbadmin 65 Sep 24 15:34 table_restore.txt
-rw-rw-r-- 1 dbadmin dbadmin 12 Sep 24 15:46 rejects.txt
-rw-rw-r-- 1 dbadmin dbadmin 207 Sep 24 15:46 exceptions.txt
ppal1:/home/dbadmin $ vi exceptions.txt
COPY: Input record 5 has been rejected (Invalid integer format 'sadhgjsad?' for column 1 (id)). Please see /home/dbadmin/rejects.txt, record 1 for the rejected record.
COPY: Loaded 4 rows, rejected 1 rows.
That gives me the error "Could not open rejected data file for writing: Is not a directory"
Please paste the exact SQL you used, did you use proper node name?
See also if the dbadmin has write access to those directories ( try touch) ? Try use the directory where you have database directory, eg. /home/dbadmin OR /tmp
See also if the dbadmin has write access to those directories ( try touch) ? Try use the directory where you have database directory, eg. /home/dbadmin OR /tmp
I cannot post my exact code because my company will not allow it. Like I said in my first post, it is not a syntax error because the query will work when the parameter passed is a different table.
So, it means you are facing difficulty in loading the data in a particular table?
Well, the schema is also a parameter and the same data files will load into the same table using the same sql syntax in one schema, but not the other.
Additionally, if I comment out the rejected data and exception lines the copy statement works fine and there are no rejections in any of the default directories.
It works for me, I create another schema and created same table structure and projections, it works, untill we get the details about the schamas, design of table/projections and some sample data, it would hard be find the cause of the failure.
Unfortunately that will not happen. The data I'm working with is protected by HIPA laws. Can you tell me more about what the "Rejected Data '/directory/' on node" statement does. Will it move the rejection file from the default CopyErrorLogs directory to the directory I specify, or does it create the file directly in the specified directory?
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
