The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Best way to upload the oracle table data into Vertica

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



  • [Deleted User][Deleted User] Administrator

    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, why don't you use ETL tool. There should be one in which you should be allowed to schedule the job.
  • 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


    Now, you can create spool of all the rows.

    NOTE: remeber the order of columns you add.

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

    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.

    Raghav Agrawal

Leave a Comment

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