Move Data From Oracle to Vertica

Moderator: NorbertKrupa

Post Reply
ishaqbaig
Newbie
Newbie
Posts: 2
Joined: Thu Jun 14, 2012 10:45 pm

Move Data From Oracle to Vertica

Post by ishaqbaig » Thu Jun 14, 2012 10:49 pm

Dear Vertica Gurus,

I am newbie to vertica .
I would like to Move Data From Oracle to Vertica. Please suggest some tools / procedure to do so.
Also I would like to know is there any Database browser tool for vertica like Toad.

Regards

Ishaq

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1758
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Move Data From Oracle to Vertica

Post by JimKnicely » Fri Jun 15, 2012 2:45 am

Hi! Welcome to Vertica forums!

Check out this link for the best free tools for querying Vertica:

viewtopic.php?f=7&t=212

There is also a version of Toad you can use:

viewforum.php?f=19

How many tables and how much data do you have to move into Vertica from Oracle? I moved data from a MySQL and SQL Server database by simply extracting the data from them into flat files and loaded them with the bulk copy command in vsql. It's really, really efficient.

Here's another post that may help you out:

viewtopic.php?f=5&t=200&p=443&hilit=oracle#p443
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

ishaqbaig
Newbie
Newbie
Posts: 2
Joined: Thu Jun 14, 2012 10:45 pm

Re: Move Data From Oracle to Vertica

Post by ishaqbaig » Fri Jun 15, 2012 10:16 pm

Hello,

Thanks for the quick reply. The links were helpful.

Regarding the amount of data to be transferred. I do an incremental load of the facts which are currently in oracle. I could create flat file for the updated/inserted rows and then move them using the bulk copy command what you suggested. Please let me know more info of the bulk copy command or point me to the link.

I am evaluating vertica against Oracle using Business Objects as our query tool. Do you have more information regarding the combination of Business Objects and vertica.

Thanks again for your valuable help.

Regards

Ishaq
Business Objects Developer
Saudi Arabia

stazer
Newbie
Newbie
Posts: 2
Joined: Fri Nov 16, 2012 10:20 am

Re: Move Data From Oracle to Vertica

Post by stazer » Tue Dec 18, 2012 11:24 pm

Hello Ishaq,

Do you have any feedback to share on the BO-Vertica integration?

Thanks for your inpu.

Stazer

fasterfaster
Newbie
Newbie
Posts: 1
Joined: Tue Mar 26, 2013 6:26 am

Re: Move Data From Oracle to Vertica

Post by fasterfaster » Tue Mar 26, 2013 6:30 am

hi, we've recently moved from oracle to vertica and have BO XiR3 and R4 connected using Data Direct drivers and seems to work fine.

we do have problems with Web Intelligence not recognising the vertica supports database ranking, that icon in webi is disabled. It is active when connecting to Oracle

does anyone have any ideas what that is so?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1758
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Move Data From Oracle to Vertica

Post by JimKnicely » Thu Mar 16, 2017 4:51 pm

Below are several options for copying data from Oracle to Vertica that I have used:

  • odbc_loader_package: This package contains a pair of User-Defined Load functions, ODBCSource() and ODBCLoader(), that can be used to load data directly from a remote database. These Load functions connect to a remote database and load data directly from that database into Vertica. Data is not converted into an intermediate file format; the data is fetched over the network via ODBC and each record is copied directly into Vertica as it is received.

    Get it here: https://github.com/vertica/Vertica-Exte ... er_package
  • SQL*Plus: Install Oracle SQL*Plus on a Vertica node. Use SQL*Plus on that node to select data from Oracle and pipe the output to a vsql COPY command

    Example:
    sqlplus64 -s oracle_user@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=oracle_port)))(CONNECT_DATA=(SID=oracle_sid)(SRVR=DEDICATED)))"/oracle_user_password @extract.sql | vsql -Cc "copy table_name from stdin direct;"
  • ODB tool: odb is a platform independent, multi-threaded, ODBC command line tool you can use as a query driver, data loader/extractor and SQL interpreter.
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Durso
Newbie
Newbie
Posts: 1
Joined: Tue Jun 20, 2017 11:22 am

Re: Move Data From Oracle to Vertica

Post by Durso » Mon Jun 26, 2017 10:08 am

Thanks for the advice, I'm another one moving over from Oracle too.

Post Reply

Return to “New to Vertica”