Export data from BD Oracle to Vertica in less time?

Pedro_x

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?

 

regards!

Adrian_Oprea_1

 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 !

 

nobita

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

ckotsidimos

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