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:
COPY Statement Issue
Moderator: NorbertKrupa
Re: COPY Statement Issue.
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)
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$
Re: COPY Statement Issue.
Thanks for the prompt response.
Appreciate the reply.
Appreciate the reply.