COPY Statement Issue

Moderator: NorbertKrupa

Post Reply
apsc
Newbie
Newbie
Posts: 2
Joined: Mon Jul 22, 2013 10:28 am

COPY Statement Issue

Post by apsc » Tue Sep 24, 2013 10:43 am

I observed some weird behaviour while loading multiple files
in a single Copy statement. While trying to load these 3 files,
i had some corrupt data in File 3 and my job failed.

Upon looking into the exception File it says record read from File1, though
error record was present in File3. So it becomes pretty cumbersome when i
load some 40-50 files in a single copy statement as per my project requirement.


create table USER_30_DAY_TABLES.foo (empid integer, empname char(10));

copy USER_30_DAY_TABLES.foo
(
empid
,empname

) FROM local

'/home/ac927c/cdr_v/srcfiles/File1',
'/home/ac927c/cdr_v/srcfiles/File2',
'/home/ac927c/cdr_v/srcfiles/File3'

delimiter '|' TRAILING NULLCOLS DIRECT
REJECTED DATA '/home/ac927c/cdr_v/reject/foo.rej.dat'
EXCEPTIONS '/home/ac927c/cdr_v/reject/foo.excep.dat'
NO COMMIT;

COMMIT;


COPY: Input record 12 has been rejected (Invalid integer format 'sddd' for column 1 (empid)).
Please see /home/ac927c/cdr_v/reject/foo.rej.dat, record 1 for the rejected record. This record was read from File1.



File1
1|amit
2|sumit
3|varun
4|kunal
5|rahul

File2
11|akash
12|sahil
13|rakesh
14|harish
15|kumar


File3
21|dsswd
asaa|sub

Appreciate any comments or suggestions:

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: COPY Statement Issue.

Post by id10t » Tue Sep 24, 2013 11:36 am

Hi!

Vertica concatenates all files to one stream with name of first file from the list.
From point of view of Vertica - it's a single file with name "File1".

Use in rows number if you want determine a file: row number 12 can be only in "File3" (File1 = 5 rows, File2 = 5 rows, File3 = 2 rows, order of files preserved)

Code: Select all

daniel@synapse:/tmp$ cat exceptions
COPY: Input record 12 has been rejected (Invalid integer format 'asaa' for column 1 (empid)).  Please see /tmp/rej, record 1 for the rejected record. This record was read from File1
daniel@synapse:/tmp$ wc -l File?
  5 File1
  5 File2
  2 File3
 12 total
daniel@synapse:/tmp$ 

apsc
Newbie
Newbie
Posts: 2
Joined: Mon Jul 22, 2013 10:28 am

Re: COPY Statement Issue.

Post by apsc » Tue Sep 24, 2013 12:33 pm

Thanks for the prompt response.

Appreciate the reply. :)

Post Reply

Return to “New to Vertica SQL”