Configuring a Remote Oracle Database for the Hive Metastore

After installing Oracle, perform the following steps to configure Hive Metastore on Oracle.

Note: To configure Hive Metastore on Oracle, you can:
  • Perform steps 1 through 5 and skip step 6.
  • Skip steps 3 and 4 and perform steps 1, 2, 5, and 6.
  1. Install the Oracle JDBC Driver.
    1. Download the Oracle JDBC Driver (ojdbc6.jar) from the Oracle website.
    2. Move the ojdbc6.jar file to /opt/mapr/hive/hive-<version>/lib/ directory
  2. Create the Metastore database and user account.
    Connect to your Oracle database as administrator, create the user that will use the Hive Metastore, and create the Metastore schema. For example:
    $ sqlplus "sys as sysdba"
    SQL> create user hiveuser identified by mypassword;
    SQL> grant connect to hiveuser;
    SQL> grant all privileges to hiveuser;
  3. Connect as the newly created hiveuser user and load the initial schema (as in the example below).
    Use the appropriate script for the current release (for example, hive-schema-2.1.0.oracle.sql) in /opt/mapr/hive/hive-2.1/scripts/metastore/upgrade/oracle/:
    $ sqlplus hiveuser
    SQL> @/opt/mapr/hive/hive-2.1/scripts/metastore/upgrade/oracle/hive-schema-<n.n.n>.oracle.sql
  4. Connect back as an administrator, remove the power privileges from user hiveuser, and then grant limited access to all the tables:
    $ sqlplus "sys as sysdba" 
    SQL> revoke all privileges from hiveuser;
    SQL> BEGIN
      2     FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='HIVEUSER') LOOP
      3        EXECUTE IMMEDIATE 'grant  SELECT,INSERT,UPDATE,DELETE on '||R.owner||'.'||R.table_name||' to hiveuser';
      4     END LOOP;
      5  END;
      6  
      7  /
  5. Configure the Metastore service to communicate with the Oracle database by setting the necessary properties (shown below) in the /opt/mapr/hive//hive-<version>/conf/hive-site.xml file.
    Suppose an Oracle database running on myhost and the user account hiveuser with the password mypassword, set the following properties (overwriting any existing values) in the hive-site.xml file:
    <property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:oracle:thin:@//myhost/xe</value>
    </property>
     
    <property>
      <name>javax.jdo.option.ConnectionDriverName</name>
      <value>oracle.jdbc.OracleDriver</value>
    </property>
     
    <property>
      <name>javax.jdo.option.ConnectionUserName</name>
      <value>hiveuser</value>
    </property>
     
    <property>
      <name>javax.jdo.option.ConnectionPassword</name>
      <value>mypassword</value>
    </property>
     
    <property>
      <name>hive.metastore.uris</name>
      <value>thrift://<n.n.n.n>:9083</value>
      <description>IP address (or fully-qualified domain name) and port of the metastore host</description>
    </property>
    Note: Though you can set the same hive-site.xml properties on all the hosts (client, Metastore, HiveServer), hive.metastore.uris is the only property that must be configured on all the hosts; the other properties are only needed on the Metastore host.
  6. Run schemaTool to create the initial DB structure.
    /opt/mapr/hive/hive-<version>/bin/schematool -dbType oracle -initSchema