Copy Rejected Data

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.

Comments

  • Prasanta_PalPrasanta_Pal - Select Field - Employee
    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.




  • That gives me the error "Could not open rejected data file for writing: Is not a directory"
  • Prasanta_PalPrasanta_Pal - Select Field - Employee
    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
  • 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_PalPrasanta_Pal - Select Field - Employee
    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. 
  • Prasanta_PalPrasanta_Pal - Select Field - Employee
    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?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file