2.1. Using Hive with Oracle

To set up Oracle for use with Hive:

On the Hive Metastore host, install the appropriate JDBC .jar file.
1. Download the Oracle JDBC (OJDBC) driver from http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html.
2. Select Oracle Database 11g Release 2 - ojdbc6.jar.
3. Copy the .jar file to the Java share directory.
```
cp ojdbc6.jar /usr/share/java
```
4. Make sure the .jar file has the appropriate permissions - 644.
Create a user for Hive and grant it permissions.
- Using the Oracle database admin 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.
Load the Hive database schema.
- You must pre-load the Hive database schema into your Oracle database using the schema script.
- When using HDP 2.1 Stack:
```
sqlplus $HIVEUSER/$HIVEPASSWORD < hive-schema-0.13.0.oracle.sql
```
  Find the hive-schema-0.13.0.oracle.sql file in the /var/lib/ambari-server/resources/stacks/HDP/2.1/services/HIVE/etc/ directory of the Ambari Server host after you have installed Ambari Server.
- When using HDP 2.0 Stack:
```
sqlplus $HIVEUSER/$HIVEPASSWORD < hive-schema-0.12.0.oracle.sql
```
  Find the hive-schema-0.12.0.oracle.sql file in the /var/lib/ambari-server/resources/stacks/HDP/2.0.6/services/HIVE/etc/ directory of the Ambari Server host after you have installed Ambari Server.
- When using HDP 1.3 Stack:
```
sqlplus $HIVEUSER/$HIVEPASSWORD < hive-schema-0.10.0.oracle.sql
```
  Find the hive-schema-0.10.0.oracle.sql file in the /var/lib/ambari-server/resources/stacks/HDP/1.3.2/services/HIVE/etc/ directory of the Ambari Server host after you have installed Ambari Server.

Legal notices