You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Mich Talebzadeh <mi...@peridale.co.uk> on 2015/03/09 10:27:44 UTC

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.

RE: configuring Hive metastore to use Oracle DB ignored

Posted by Mich Talebzadeh <mi...@peridale.co.uk>.
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:mich@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.