Export data from BD Oracle to Vertica in less time?

 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. 

Eg:

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

 Eg: 

{
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 !

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

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