Configuring a Remote MS SQL SERVER Database for the Hive Metastore

About this task

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

Procedure

  1. Create hiveuser and Metastore schema. 
    1>CREATE DATABASE metastore;
2>GO
1>CREATE LOGIN <hiveuser> with password='<mypassword>;
2>CREATE USER <hiveuser> for login <hiveuser>;
3>GRANT <PRIVILEGES> to <hiveuser>;
4>GO
  2. Download JDBC Driver from here, untar the file, and follow instructions in the install.txt file to install the driver.
  3. Copy the JAR file to /opt/mapr/hive/hive-version>/lib/ directory.
    • For Java 7 
      cp ~/sqljdbc_6.0/enu/jre7/sqljdbc41.jar /opt/mapr/hive/hive-<version>/lib/
    • For Java 8 
      cp ~/sqljdbc_6.0/enu/jre8/sqljdbc42.jar /opt/mapr/hive/hive-<version>/lib/
  4. Configure the Metastore service to communicate with the MS SQL database by setting the necessary properties (shown below) in the /opt/mapr/hive//hive-<version>/conf/hive-site.xml file.
    Suppose an MS SQL 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:sqlserver://<SERVER_NAME>:1433;DatabaseName=metastore;</value>
</property>
 
<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.microsoft.sqlserver.jdbc.SQLServerDriver</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.
  5. Run schemaTool to create the initial DB structure. 
    /opt/mapr/hive/hive-<version>/bin/schematool -dbType mssql -initSchema