How to set configuration in Hive-Site.xml file for hive metastore connection?

Question

I want to connect MetaStore using the java code. I have no idea how to set configuration setting in Hive-Site.xml file and where I'll post the Hive-Site.xml file. Please help.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.hadoop.fs.Path;
import org.apache.hadoop.hive.conf.HiveConf;
import org.apache.hadoop.hive.conf.HiveConf.ConfVars;

public class HiveMetastoreJDBCTest {

    public static void main(String[] args) throws Exception {

        Connection conn = null;
        try {
            HiveConf conf = new HiveConf();
            conf.addResource(new Path("file:///path/to/hive-site.xml"));
            Class.forName(conf.getVar(ConfVars.METASTORE_CONNECTION_DRIVER));
            conn = DriverManager.getConnection(
                    conf.getVar(ConfVars.METASTORECONNECTURLKEY),
                    conf.getVar(ConfVars.METASTORE_CONNECTION_USER_NAME),
                    conf.getVar(ConfVars.METASTOREPWD));

            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(
                "select t.tbl_name, s.location from tbls t " +
                "join sds s on t.sd_id = s.sd_id");
            while (rs.next()) {
                System.out.println(rs.getString(1) + " : " + rs.getString(2));
            }
        }        

    }
}

Answer 1

Add these lines in your hive-site.xml:

<property>
  <name>hive.metastore.local</name>
  <value>true</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://localhost:3306/hive</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>hiveuser</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>hivepass</value>
</property>

In jdbc:mysql://localhost:3306/hive, 3306 is your default mysql port; hive is our mysql database name for hive metastore. Change hiveuser to your mysql hive username and hivepass to your mysql hive password.

Do this step in terminal, if you haven't created a database for hive metastore in mysql:

mysql -u root -p

Enter your mysql root password.

mysql> create database hive;

mysql> create user 'hiveuser'@'%' IDENTIFIED BY 'hivepass';

mysql> GRANT all on *.* to 'hiveuser'@localhost identified by 'hivepass';

mysql> flush privileges;

Here, hiveuser and hivepass are whatever username and password you give for hive metastore respectively.

NOTE: You need to have mysql-jdbc-connector.jar in $HIVE_HOME/lib and $HADOOP_HOME/lib