How to Join Oracle Table with Vertica Table
In one scenario we want to Join Source Oracle Table with Target Vertica Table.
Example:
We have a table in Oracle DB called TableA with columns col1 and col2
We want to extract records from TableA using Join with TableB which is in Vertica DB.
TableB has col2 and col3 . Join is based on col2
Sample Query:
select TableA.col1,TableA.col2 from TableA join TableB on TableA.col2=TableB.col2
Is there a way we can use the above query and load in TableB ?
Example:
We have a table in Oracle DB called TableA with columns col1 and col2
We want to extract records from TableA using Join with TableB which is in Vertica DB.
TableB has col2 and col3 . Join is based on col2
Sample Query:
select TableA.col1,TableA.col2 from TableA join TableB on TableA.col2=TableB.col2
Is there a way we can use the above query and load in TableB ?
0
Comments
These is no such thing like linked servers / database link / federated databases supported by Vertica. So you cannot perform a join between a table in Oracle and a table in Vertica.
However, one possible solution to this could be:
1. Get the source Table into Vertica from Oracle
2. Perform the join
3. Send the result set back to Oracle.
Pulling and pushing data between Oracle and Vertica is possible using ODBC loader package provided by Vertica
Vertica ODBC loader
Hope this helps.
NC