Best way to upload the oracle table data into Vertica

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.

Export the data to csv, the copy from csv

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