Best way to upload the oracle table data into Vertica

nirmeshvats

I have one Oracle table which data I have to used on Vertica database. so I am planning to upload that table data into Vertica database every business day.
What will be the best way to do that? do I have to use create the CSV and upload that or there is something like db link that I can use to upload the data.

Please suggest?
thanks in Advance

Regard's
Nirmesh

[Deleted User]

Vertica does not support dblink between Vertica and Oracle . You have to use Copy statement to load data into vertica and source of this copy could be csv file.

ckotsidimos

Export the data to csv, the copy from csv

vgarg

Hi Nirmesh, why don't you use ETL tool. There should be one in which you should be allowed to schedule the job.

RaghavA

Hi Nirmesh,

This is one of major problem many peope faces. The best method to solve such problem is to manually generate CSV spool of data from Oracle and use COPY command to load the data into the database.

You may use following example to accomplish it:

For eg, i have a table in Oracle as below:

SQL> desc tbl_ora
Name Null? Type

---

START_TIME DATE
NAME VARCHAR2(50)
IP_ADD VARCHAR2(19)
S_NO NUMBER

Now, you can create spool of all the rows.

NOTE: remeber the order of columns you add.

SQL> SPOOL
SQL> SELECT START_TIME||','||NAME||','IP_ADD||','||S_NO from tbl_ora;
SQL> SPOOL OFF

Now, just FTP this file to your Vertica hosting server. and push the data into database.

COPY tbl_vertica FROM '' DELIMITER ',' DIRECT EXCEPTIONS '<file_name.exception>';

NOTE: if the order of COLUMNS in vertica is same as of oracle then you can directly load into it else also mention the COLUMN names in COPY command.
And if you are trying to load more than 100MB of data then it is recommended for you to use DIRECT keyword, else you may ignore this parameter as well.

Regards,
Raghav Agrawal