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

Regard's
Nirmesh

Comments

  • [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


    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

Leave a Comment

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