ODI and Hive configuration

0
votes

I have a "Cloudera CDH 4.4.0" running on Oracle VirtualBox VM. I have a few tables in Hive that I want to load into ODI. I have installed ODI 11.1.1.7 on the home cloudera directory. I have copied all the necessary jar files into the "userlib" directory. The steps followed for creating a connection to Hive: - Imported "Hive" technology, setup a dataserver with the necessary JDBC driver. Mentioned the jdbc url and the hive metastore URI. The connection successfully connects to Hive. - Created a physical and logical schema for the source(Hive) and target(Oracle DB). - Import the "RKM" for reverse engineering the hive table. - Created a data model, and tried to reverse engineer the tables present in Hive. But the model shows no tables that are reverse engineered. In the terminal it shows that " GETTING TABLE lists" and then the reverse engineering ends with any table being accessed.

(P.S.: www.toadworld.com/platforms/oracle/w/wiki/10955.integrating-apache-hive-table-data-with-oracle-database-11g-in-oracle-data-integrator-11g.aspx, the link I followed for the installation and setup of ODI, I didn't change the environment variables. ) I have two questions: 1. Do I need to edit the .bashsrc, and change the environment variables? 2. What could be the reason that Hive tables are not getting reverse engineered? Any help would be appreciated.

1
oraclehadoophiveclouderaoracle-data-integrator

1 Answers

0
votes

Hive JDBC drivers are still quite immature. I need to reconnect few times per hour, regardless if I work from SQuirrel SQL or SQL Workbench/J. This happens especially when I let the tool to query the metadata.

I assume you're using dedicated RKM for Hive? Maybe the quick win would be if you try use generic one (java call)?

What version of ODI you're on? Check latest patches or borrow RKM code from higher version (12 to 11)

If that doesn't work I would go and analyze queries sent by Hive RKM (have a look at operator's log) You may want to submit the same from the tools I mention above to see which query goes wrong.

Last resort would be to manually run Show create table [tablename] statement and add tables to ODI model manually.