Error in running Hive with Postgresql as metastore DB

[Saurabh Bajaj <Sa...@mu-sigma.com>]

Hi Everyone,

I am getting an error while installing and running hive using postgresql db as a metastore DB.
The error is as follows which occurs while running any query in Hive from the terminal


FAILED: Error in metadata: javax.jdo.JDOFatalInternalException: Error creating transactional connection factory
NestedThrowables:
java.lang.reflect.InvocationTargetException
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask


I installed CDH3U2 Hadoop and Hive version and the only changes I have made in Hive configuration are as follows:

1. export HIVE_AUX_JARS_PATH=/usr/local/hadoop

2. There is no Hive-site.xml so i made changes in the hive-default.xml as follows:

<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:postgresql://localhost:5432/hadoop</value>
  <description>JDBC connect string for a JDBC metastore</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>org.postgresql.Driver</value>
  <description>Driver class name for a JDBC metastore</description>
</property>

<property>
  <name>javax.jdo.PersistenceManagerFactoryClass</name>
  <value>org.datanucleus.jdo.JDOPersistenceManagerFactory</value>
  <description>class implementing the jdo persistence</description>
</property>


<property>
  <name>javax.jdo.option.DetachAllOnCommit</name>
  <value>true</value>
  <description>detaches all objects from session so that they can be used after transaction is committed</description>
</property>

<property>
  <name>javax.jdo.option.NonTransactionalRead</name>
  <value>true</value>
  <description>reads outside of transactions</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>hadoop</value>
  <description>username to use against metastore database</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>hadoop</value>
  <description>password to use against metastore database</description>
</property>


3. I have placed postgresql-9.1-901.jdbc4.jar in the /usr/local/hadoop path
4. Installed Postgresql and created username/pass hadoop/hadoop
5. postgresql.conf - uncommented the listen_addresses = 'localhost','*' while adding the '*' part to it.


Please help me understand why this error would be occuring.

Thanks in advance!


Saurabh Bajaj
+91 9986588089





________________________________
This email message may contain proprietary, private and confidential information. The information transmitted is intended only for the person(s) or entities to which it is addressed. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited and may be illegal. If you received this in error, please contact the sender and delete the message from your system.

Mu Sigma takes all reasonable steps to ensure that its electronic communications are free from viruses. However, given Internet accessibility, the Company cannot accept liability for any virus introduced by this e-mail or any attachment and you are advised to use up-to-date virus checking software.