How to load multiple files with same structure but different names into a table using COPY vertica

sri_vtca

Hi,

We have a scenario where we get multiple files with same structure but different names from different source systems and we need to load these files into same table in Vertica.

Can some one help with loading multiple files with same structure,different names into same table using COPY vertica
eg:

File1: //abcdefg_YYYYMMDD.dat
File2: //pqrstuvx_YYYYMMDD.dat
File Format: Pipe(|) delimited
Structre: Both files have same structure( # of fields, filed position)
Vertica table to load into: table_pqrst

Tried with different options below but not succeeded:

-->Method1 - By specifying the two files in one quotes seperated by space
-->ERROR 5783: Client error: Could not open file [.....] for reading (in function statFiles() at Bulkload.cpp:320)
COPY table_xyz
(....)
FROM
LOCAL
'//abcdefg_YYYYMMDD.dat //pqrstuvx_YYYYMMDD.dat'
DELIMITER E'|'
REJECTED DATA '//table_xyz_yyyymmdd.rej'
EXCEPTIONS '//table_xyz_yyyymmdd.rej'
DIRECT;

-->Method2 - By specifying the two files in two different quotes seperated by space
-->ERROR 4856: Syntax error at or near "'//pqrstuvx_YYYYMMDD.dat'" at character 438
COPY table_xyz
(
....
)
FROM
LOCAL
'//abcdefg_YYYYMMDD.dat' '//pqrstuvx_YYYYMMDD.dat'
DELIMITER E'|'
REJECTED DATA '//table_xyz_yyyymmdd.rej'
EXCEPTIONS '//table_xyz_yyyymmdd.rej'
DIRECT;

-->Method3 - By specifying the two files in two different quotes without space
-->ERROR 5783: Client error: Could not open file [//abcdefg_YYYYMMDD.dat'//pqrstuvx_YYYYMMDD.dat] for reading (in function statFiles() at Bulkload.cpp:320)
COPY table_xyz
(
....
)
FROM
LOCAL
'//abcdefg_YYYYMMDD.dat''//pqrstuvx_YYYYMMDD.dat'
DELIMITER E'|'
REJECTED DATA '//table_xyz_yyyymmdd.rej'
EXCEPTIONS '//table_xyz_yyyymmdd.rej'
DIRECT;

Thanks

Ariel_Cary

Hi,

You can include many files in the copy-from-file clause, just separate them by a comma.

$ cat /tmp/a
1
2

$ cat /tmp/b
3
4

$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

create table t(i int);
CREATE TABLE

copy t from local '/tmp/a', '/tmp/b';

Rows Loaded

       4

(1 row)

select * from t;

i

1
2
3
4
(4 rows)

sri_vtca

Thank you @Ariel_Cary...It worked... ..I tried with space but not comma...