Setting up RDBMS for use with Hive Metastore

Hive supports multiple databases. This section uses Oracle as an example. To use an Oracle database as the Hive Metastore database, you must have already installed HDP and Hive.

Important

	Important
When Hive is configured to use an Oracle database and transactions are enabled in Hive, queries might fail with the error `org.apache.hadoop.hive.ql.lockmgr.LockException: No record of lock could be found, might have timed out`. This can be caused by a bug in the BoneCP connection pooling library. In this case, Hortonworks recommends that you set the `datanucleus.connectionPoolingType` property to `dbcp` so the DBCP component is used.

When Hive is configured to use an Oracle database and transactions are enabled in Hive, queries might fail with the error org.apache.hadoop.hive.ql.lockmgr.LockException: No record of lock could be found, might have timed out. This can be caused by a bug in the BoneCP connection pooling library. In this case, Hortonworks recommends that you set the datanucleus.connectionPoolingType property to dbcp so the DBCP component is used.

To set up Oracle for use with Hive:

On the Hive Metastore host, install the appropriate JDBC .jar file.
- Download the Oracle JDBC (OJDBC) driver.
- Select "Oracle Database 11g Release 2 - ojdbc6.jar"
- Copy the .jar file to the Java share directory:
  cp ojdbc6.jar /usr/share/java/
  Note
  Make sure the .jar file has the appropriate permissions - 644.
Create a user for Hive and grant it permissions using SQL*Plus, the Oracle database administration utility:
```
# sqlplus sys/root as sysdba
CREATE USER $HIVEUSER IDENTIFIED BY $HIVEPASSWORD;
GRANT SELECT_CATALOG_ROLE TO $HIVEUSER;
GRANT CONNECT, RESOURCE TO $HIVEUSER;
QUIT;
```
Where $HIVEUSER is the Hive user name and $HIVEPASSWORD is the Hive user password.

	Note
	Make sure the .jar file has the appropriate permissions - 644.

​Setting up RDBMS for use with Hive Metastore

Setting up RDBMS for use with Hive Metastore