Using Hive with Oracle

Before setting up Hive with an existing Oracle database, obtain the appropriate drivers and .jar files, create an hive user with sufficient permissions, and load the hive database schema.

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

Steps:

1. Install the appropriate JDBC .jar file on the Oracle host.

   1. Download the Oracle JDBC (OJDBC) driver from JDBC and UCP Downloads page .

      1. For Oracle Database 19C: select Oracle Database 23ai (23.8.0.25.04) JDBC Driver > ojdbc11.jar.

   2. Copy the .jar file to the Java share directory.

cp ojdbc11.jar /usr/share/java/

Make sure the .jar file has the appropriate permissions. For example:

chmod 644 /usr/share/java/ojdbc11.jar

2. Create the Hive databases using the Oracle database administrator. You can use the following series of commands to create a hive user and grant permissions using SQL*Plus.

# sqlplus sys/root as sysdba

sqlplus / as sysdba

​

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 9 04:27:05 2025

Version 19.3.0.0.0

​

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

​

SQL> show con_name

​

CON_NAME

------------------------------

CDB$ROOT

​

SQL> show pdbs;

​

    CON_ID CON_NAME       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

   2 PDB$SEED       READ ONLY  NO

   3 ORCLPDB1       READ WRITE NO

​

SQL> alter session set container=ORCLPDB1;

Session altered.

​

SQL> CREATE USER <hive> IDENTIFIED BY <hive_password> default tablespace "USERS" temporary tablespace "TEMP";

User created.

​

SQL> GRANT unlimited tablespace to <hive>;

Grant succeeded.

​

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE TO <hive>;  

2    3    4    5

Grant succeeded.

3. Set the jdbc/driver/path based on the location of the Oracle JDBC driver .jar file. This command must be run on the server where Ambari server is installed: ambari-server setup --jdbc-db={database-type} --jdbc-driver={/jdbc/driver/path}.

ambari-server setup --jdbc-db=oracle --jdbc-driver=/usr/share/java/ojdbc11.jar

4. Start the Hive installation.