Integration Oracle GoldenGate tool to Vertica database

Hi. Not sure if this will help or not. About 4 years ago we did a technical exploration of Vertica and Oracle GoldenGate for Big Data, It was just after they added the generic Java connectivity support. I've attached the exploration doc that was the result of the testing. Note that it was from back in 2018 using Vertica 9 and GoldenGate 12.3, our source was MySQL not Oracle, and there was no Vertica connector just the generic Java one. There were some limitations found that exposed it as non performant and those are hightlighted. A lot has changed in Vertica since then, and presumably a lot has changed in GoldenGate. So the steps in this doc may not match current software. There are other migration tools that do have Vertica connectors and work well. Qlik Replicate and Pentaho Data Integration are a couple. Info on them can be found on our Tech Partner page https://www.vertica.com/documentation/vertica/technology-partner-integrations

I hope it helps.

I found the link to the original post on the GoldenGate testing. https://forum.vertica.com/discussion/240140/vertica-and-oracle-goldengate-for-big-data-jdbc-handler-a-technical-exploration

Thank you so much for you help. I'll try and reponse.

I was try and it's is sucessfull. Thank you so much.

Yes, as noted in tech exploration guide the JDBC handler processes records row by row, so it's more adept at OLTP type environments. At the time it didn't support the batchsql feature. A quick check in the Oracle GoldenGate 19.1 release docs indicates nothing has changed. This was the major reason we noted it didn't seem to be a good fit with Vertica.
"17.2.1 Single Operation Mode
The JDBC Handler performs SQL operations on every single trail record (row operation) when the trail record is processed by the handler. The JDBC Handler does not use the BATCHSQL feature of the JDBC API to batch operations."

For some reason the OGG 21.1 docs link for JDBC Handler points to 19.1 https://docs.oracle.com/en/middleware/goldengate/big-data/21.1/gadbd/using-jdbc-handler.html

There is only database and session level control over isolation levels. There is no node specific control. The only theoretical solution would be to make sure native load balance is not enabled at the client end (OGG Vertica properties file gg.handler.jdbcwriter.connectionURL). This means the node in the connection string would be the only one connected to by OGG. Transactions would still be distributed. Then setting the session level isolation level in the Connection string.

Unfortunately everywhere in the docs It shows this must be some sort of compatability mode because
READ UNCOMMITTED is converted automatically converted to READ COMMITTED.

And this particular note seems to be the root reason for the conversions.
Doc page url http://doc-build1.verticacorp.com/12.0.2-hotfix/HTML/Content/Authoring/ConnectingToVertica/ClientJDBC/ChangingTheTransactionIsolationLevel.htm

Note: The Connection interface also defines several other transaction isolation constants (READ_UNCOMMITTED and REPEATABLE_READ). Since Vertica does not support these isolation levels, they are converted to READ_COMMITTED and SERIALIZABLE, respectively.

So unforutnately setting the read_uncommitted isn't actually possible and would have no impact on the session or performance.

Any chance OGG supports exporting data to a csv file? If so it might be faster to export to csv and then copy into Vertica. Then it's all done in a single transaction. Just a thought.

Regards,