Configuring a Remote MySQL Database for the Hive Metastore
About this task
After installing MySQL, perform the following steps to configure Hive Metastore on MySQL
Procedure
-
Install the MySQL connector. To install:
- MySQL connector on a RHEL 6+ system
- On the Hive Metastore server host, install
mysql-connector-java
and symbolically link the file to the/opt/mapr/hive/hive-<version>/lib/
directory.$ sudo yum install mysql-connector-java $ ln -s /usr/share/java/mysql-connector-java.jar /opt/mapr/hive/hive-<version>/lib/mysql-connector-java.jar
- MySQL connector on a SLES system
- On the Hive Metastore server host, install
mysql-connector-java
and symbolically link the file to the/opt/mapr/hive/hive-<version>/lib/
directory.$ sudo zypper install mysql-connector-java $ ln -s /usr/share/java/mysql-connector-java.jar /opt/mapr/hive/hive-<version>/lib/mysql-connector-java.jar
- MySQL connector on a Debian/Ubuntu system
- On the Hive Metastore server host, install
mysql-connector-java
and symbolically link the file into the/opt/mapr/hive/hive-<version>/lib/
directory.$ sudo apt-get install libmysql-java $ ln -s /usr/share/java/libmysql-java.jar /opt/mapr/hive/hive-<version>/lib/mysql-connector-java.jar
-
Create the database and an associated user. The following commands are for a
Hive Metastore with hostname
metastorehost
to create a MySQL user with namehive
and passwordmypassword
:$ mysql -u root -p mysql> CREATE DATABASE metastore; mysql> CREATE USER 'hive'@'metastorehost' IDENTIFIED BY 'mypassword'; ... mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'hive'@'metastorehost'; mysql> GRANT ALL PRIVILEGES ON metastore.* TO 'hive'@'metastorehost'; mysql> FLUSH PRIVILEGES; mysql> quit;
-
Configure the Metastore service to communicate with the MySQL database by
setting the necessary properties (shown below) in the
/opt/mapr/hive//hive-<version>/conf/hive-site.xml
file.Suppose a MySQL database running onmyhost
and the user accounthive
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:mysql://myhost/metastore</value> <description>the URL of the MySQL database</description> </property> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.jdbc.Driver</value> </property> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>hive</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 mysql -initSchema