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
-
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
-
Download JDBC Driver from here, untar the file, and follow
instructions in the
install.txt
file to install the driver. -
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/
- For Java 7
-
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 onmyhost
and the user accounthiveuser
with the passwordmypassword
, set the following properties (overwriting any existing values) in thehive-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>
NOTEThough you can set the samehive-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. -
Run
schemaTool
to create the initial DB structure./opt/mapr/hive/hive-<version>/bin/schematool -dbType mssql -initSchema