Support Questions
Find answers, ask questions, and share your expertise

Remote Oracle backed services seem to hang

Highlighted

Remote Oracle backed services seem to hang

Explorer

Oracle is hosted at a remote datacenter from the cluster.

Hive Metastore doesn't have errors, but doesn't seem to respond when queried.

When installing multiple services at once via Ambari each insert of service config data would take longer than the last. Eventually the install would hang and not recover. Installing less than ~3 services completes.

There does not appear to be any timeout errors in the logs for either Ambari or Hive. Oracle remains responsive when queried via SQLPlus from the server running Ambari and Hive. Without errors I am not sure where to look next. Running with logging set to DEBUG has yet to provide any additional insight.

3 REPLIES 3
Highlighted

Re: Remote Oracle backed services seem to hang

Mentor

@Erik Nor

Option 1

Look for the entry like the below in your hadoop-env.sh in the $HADOOP_HOME/conf,make sure you have the jarFiles in your path

#Add libraries required by oracle  connector 
for jarFile in `ls /usr/share/java/*oracle* 2>/dev/null` 
do   
JAVA_JDBC_LIBS=${JAVA_JDBC_LIBS}:$jarFile 
done 
export HADOOP_CLASSPATH=${HADOOP_CLASSPATH}:${JAVA_JDBC_LIBS} 

Option 2

How to control the number of mappers required for a Hive query

Option 3

Configure the Metastore Service to Communicate with the Oracle Database

You need to set in hive-site.xmlto configure the metastore service to communicate with the Oracle database, and provides sample settings. Though you can use the same hive-site.xml on all hosts (client, metastore, HiveServer), hive.metastore.uris is the only property that must be configured on all of them; the others are used only on the metastore host.

Install the Oracle JDBC drive

$ sudo mv ojdbc6.jar /usr/lib/hive/lib/

Example

Given an Oracle database running on texas and the user account hiveuser with the password passwd, set the configuration as follows (overwriting any existing values): hive-site.xml

<property> 
<name>javax.jdo.option.ConnectionURL</name>  
<value>jdbc:oracle:thin:@//texas/xe</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>passwd</value>
</property>
<property> 
<name>datanucleus.autoCreateSchema</name>  
<value>false</value>
</property>
<property> 
<name>datanucleus.fixedDatastore</name>  
<value>true</value>
</property>
<property>  
<name>hive.metastore.uris</name> 
<value>thrift://<n.n.n.n>:9083</value><description>IP
address (or fully-qualified domain name) and port of the metastore
host</description>
</property>
Highlighted

Re: Remote Oracle backed services seem to hang

@Erik Nor

Please collect Oracle AWR for the period when the services are being added. If you find Ambari related sql's to be in top for CPU / Parse, it could be a JDBC issue which might have to be fixed.

Highlighted

Re: Remote Oracle backed services seem to hang

Explorer

The connection works, it is just very slow. For example, in testing via hive cli creating a single column test table takes 4-6 minutes to complete.

I will have the AWR collected, but I feel now that this is a network problem.