Using Hive with Oracle

Before using Hive with a new or existing Oracle database; obtain the appropriate driver and .jar files, and create a Hive user with sufficient permissions.

Determine the appropriate Oracle database version and obtain the release drivers and .jar file.

Table 1. Oracle Database Version Information
Oracle Database Version	Drivers	File
Oracle Database 11g	Oracle Database 11g Release 2 drivers	ojdbc6.jar
Oracle Database 12c	Oracle Database 12c Release 1 drivers	ojdbc6.jar

	Note
	Although Oracle recommends ojbdc version 7 for use with Oracle12, Hive 1.x, 2.x, and 3 currently work best with ojdbc6.

On the Ambari Server host, stage the appropriate JDBC driver file for later deployment.
1. Download the Oracle JDBC (OJDBC) driver from https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html.
2. Make sure the .jar file has the appropriate permissions.
  chmod 644 ojdbc6.jar
3. Add the path to the downloaded .jar file.
  ambari-server setup --jdbc-db=oracle --jdbc-driver=/path/to/downloaded/ojdbc6.jar
Create a user for Hive and grant it permissions.
using the Oracle database admin utility:
```
# sqlplus sys/root as sysdba
CREATE USER [HIVE_USER] IDENTIFIED BY [HIVE_PASSWORD];
GRANT SELECT_CATALOG_ROLE TO [HIVE_USER];
GRANT CONNECT, RESOURCE TO [HIVE_USER];
QUIT;
```
Where [HIVE_USER] is the Hive user name and [HIVE_PASSWORD] is the Hive user password.