Copy Rejected Data

Steve_1

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.

Prasanta_Pal

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.

Steve_1

That gives me the error "Could not open rejected data file for writing: Is not a directory"

Prasanta_Pal

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

Steve_1

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.

Prasanta_Pal

So, it means you are facing difficulty in loading the data in a particular table?

Steve_1

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.

Steve_1

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. 

Prasanta_Pal

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.

Steve_1

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?