OK I managed to sort this out.
My problem was basic mistake. I copied $HIVE_HOME/conf/hive-core.xml from the template file. I then added the properties of the new metastore including URL, DriverName, UserName, Password at "top" of the file not realising that the hive-core.xml makes reference to derby store further down L So I went back to running hiveserver2 --service metastore & And noticed that it picks up incorrect URL references! java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:oracle:thin:@rhes564:1521:mydb, username = APP. Terminating connection pool (set lazyInit to true if you expect to start your database after your app). Original Exception: ------ java.sql.SQLException: ORA-01017: invalid username/password; logon denied OK so once the redundant properties are removed from hive-core.xml, it worked! Login OS Proc/ID Client Proc/ID SID SER# HOST PROGRAM Logged/Hours --------------- -------------------- -------------------- ----- ----- ---------- ------------------------------ ------------ HIVEUSER oracle/9559 hduser/1234 170 2301 rhes564 JDBC Thin Client 0 HIVEUSER oracle/9562 hduser/1234 23 1857 rhes564 JDBC Thin Client 0 HIVEUSER oracle/9571 hduser/1234 45 929 rhes564 JDBC Thin Client 0 HIVEUSER oracle/9573 hduser/1234 66 285 rhes564 JDBC Thin Client 0 HTH, Mich NOTE: The information in this email is proprietary and confidential. This message is for the designated recipient only, if you are not the intended recipient, you should destroy it immediately. Any information in this message shall not be understood as given or endorsed by Peridale Ltd, its subsidiaries or their employees, unless expressly so stated. It is the responsibility of the recipient to ensure that this email is virus free, therefore neither Peridale Ltd, its subsidiaries nor their employees accept any responsibility. From: Mich Talebzadeh [mailto:m...@peridale.co.uk] Sent: 09 March 2015 09:28 To: user@hive.apache.org Subject: configuring Hive metastore to use Oracle DB ignored Hi all, I am trying to use an Oracle DB as the metastore for Hive. I have create all the users and connections, added Oracle jar file ojdbc6.jar to /usr/lib/hive/lib. I have also renamed the derby jar file derby-10.10.1.1.jar to derby-10.10.1.1.jar_ori so it does not use the embedded Derby database! I have written a simple JDBC connection Java to confirm that I can connect from hive home to Oracle. They both happen to be on the same host rhes564. In my $HIVE_HOME/conf, I have added the following properties to hive-site.xml file <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:oracle:thin:@rhes564:1521:mydb</value> </property> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>oracle.jdbc.OracleDriver</value> </property> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>hiveuser</value> </property> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>hiveuser</value> </property> Now I have also changed thrift port to be 10010 in hive-site.xml just to see if the startup uses this port <name>hive.server2.thrift.port</name> <value>10010</value> <description>Port number of HiveServer2 Thrift interface when hive.server2.transport.mode is 'binary'.</description> </property> Now when I go back and try starting metastore API as follows hiveserver2 --service metastore & [5] 4169 hduser@rhes564::/usr/lib/hive/bin> Starting Hive Metastore Server SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/home/hduser/hadoop/hadoop-2.6.0/share/hadoop/common/lib/slf4j-log 4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/lib/hive/lib/hive-jdbc-0.14.0-standalone.jar!/org/slf4j/impl/ StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory] javax.jdo.JDOFatalInternalException: Error creating transactional connection factory Caused by: org.datanucleus.exceptions.NucleusException: Attempt to invoke the "BONECP" plugin to create a ConnectionPool gave an error : The specified datastore driver ("org.apache.derby.jdbc.EmbeddedDriver") was not found in the CLASSPATH. Please check your CLASSPATH specification, and the name of the driver. OK I have renamed derby jar file deliberately. However, why is still looking for embedded driver. Why does not it connect to Oracle metastore? Am I missing something from hive-site.xml file? Thanks, Mich NOTE: The information in this email is proprietary and confidential. This message is for the designated recipient only, if you are not the intended recipient, you should destroy it immediately. Any information in this message shall not be understood as given or endorsed by Peridale Ltd, its subsidiaries or their employees, unless expressly so stated. It is the responsibility of the recipient to ensure that this email is virus free, therefore neither Peridale Ltd, its subsidiaries nor their employees accept any responsibility.
