Hive uses DerbyDB to store its metadata by default, if you have ever tried having more than one person, or multiple services, such as the Hive Web UI or Hive Service connect, it doesn’t work really well and can cause problems. To improve this I recommend use MySQL as the metastore_db.
Install a MySQL server and client either on the same server as Hive or a remote server.
Log into MySQL and create the metastore and user permission.
shell> mysql -u root -p
mysql > create database hive_metastore;
mysql > CREATE USER ‘hive’@'[localhost|remotehost]’ IDENTIFIED BY ‘metastore’;
mysql > GRANT ALL PRIVILEGES ON hive_metastore.* TO ‘hive’@'[localhost|remotehost]”;
Now download the MySQL JDBC driver from http://www.mysql.com/downloads/connector/j/an install it into you hive library path “install_path/hive/lib”
Now configure your hive-site.xml file which should be in “install_path/hive/conf/hive-site.xml”.
<?xml version=”1.0″?>
<?xml-stylesheet type=”text/xsl” href=”configuration.xsl”?>
<configuration>
<property>
<name>javax generic xenical.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://[localhost|remotehost]/metastore</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hive</value>
<description>username to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>metastore</value>
<description>password to use against metastore database</description>
</property>
<property>
<name>datanucleus.autoCreateSchema</name>
<value>false</value>
<description>creates necessary schema on a startup if one doesn’t exist. Set this to false, after creating it once</description>
</property>
<property>
More information at:https://cwiki.apache.org/confluence/display/Hive/AdminManual+MetastoreAdmin