Export data from BD Oracle to Vertica in less time?

Hello guys,


Please I need you to guide me in replication environments from Oracle to Vertica.

I have a production DB in Oracle environment, this oracle DB has about 900 tables, all tables need to transfer the Vertica DB.


For this I built an ETL takes approximately 6 hours to transfer the information.


I have the following questions:


Is there any method to transfer the tables in less time?

You may only update tables that have been changed in your records?




  • Options

     1 - You need ot create the tables in Vertica and mapp the data type to apply to Vertica specific data types, most of the cases are the same.

     2 - ETL 

           a - do you have fillters on your E ? 

           b - is this the initial load ? 


     - loading the data can be done using a tool like Informatica or any ETL tool or by hand. 


     By hand : 

       1 -  export your Oracle tables to csv files zip them and move to Vertica Server -> import the zipped data using the copy direct command for faster load. 


    Spool out of Oracle 

    set colsep '|'
    set echo off
    set feedback off
    set linesize 1000
    set pagesize 0
    set sqlprompt ''
    set trimspool on
    set headsep off

    spool output.dat

    select '|', <table>.*, '|'
    from <table>
    where <conditions>

    spool off

    Import into Vertica

    copy <tbl> from '<file>' delimiter '|' direct;


      2  - Stream the data from Oracle to Vertica.

    -you need ot install the vsql client on you Oracle db host


    echo "set colsep '|'"
    echo "set echo off"
    echo "set feedback off"
    echo "set linesize 1000"
    echo "set pagesize 0"
    echo "set sqlprompt ''"
    echo "set trimspool on"
    echo "set headsep off"
    echo "SELECT col1||','||col2||','||col3 FROM tbl;"
    } | sqlplus -s $username/$password@$database | vsql -h $target_host -d $dbname -U $dbuser -w $passwd -c "COPY <tbl name> FROM LOCAL STDIN SKIP 1 DELIMITER '|' NULL as 'null' DIRECT STREAM NAME 'Loading some stuff' EXCEPTIONS 'exception.txt' REJECTED DATA 'rejected.txt';"

    - you can build this script dynamically and then run it.

    - give this a try ! :)


  • Options

    Hi Pedro,

    have you found the solution ?
    I am facing the same issue : need to replicate lots of oracle tables to vertica
    (big size average 77 GB for 1 Billion records) .

    How to duplicate those big oracle tables over to vertica with minimal transfer time ?

    thank you

  • Options

    What we have done in another similar situation is export the data to files and then import it!
    Do a csv export if you can and the copy from file in vertica

Leave a Comment

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