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

sri_vtcasri_vtca Registered User

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

Comments

  • Ariel_CaryAriel_Cary Employee, Registered User, VerticaExpert

    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_vtcasri_vtca Registered User

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

Leave a Comment

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