I. installation

1. Hive installation: brew install hive
2. mysql installation: brew install mysql
3. Start MySQL: bash mysql.server start

2. Metabase configuration

Hive uses derby as the metabase by default. Here we use mysql to store metadata, and do some initialization configuration below

1. Log in to mysql: mysql -u root
2. create database metastore;
3. Create a new user: create user 'hive' @ 'localhost' identified by '123456';
4. Modify user permissions: grant select,insert,update,delete,alter,create,index,references on metastore.* to 'hive' @ 'localhost';
5. Refresh permissions: flush privileges;

III. configure Hive

1. Enter Hive's installation directory and create hive-site.xml file

   • cd /usr/local/Cellar/hive/2.1.1/libexec/conf
   • cp hive-default.xml.template hive-site.xml

2. Modify the hive-site.xml file, find the following corresponding property and modify its value

<property>
         <name>hive.metastore.uris</name>
         <value>thrift://127.0.0.1:9083</value>
         <description>Thrift URI for the remote metastore. Used by metastore client to connect to remote metastore.</description>
</property>
<property>
       <name>javax.jdo.option.ConnectionURL</name>
       <value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true&amp;useUnicode=true&amp;characterEncoding=latin1&amp;useSSL=true</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>123456</value>
        <description>password to use against metastore database</description>
</property>
<property>
        <name>hive.exec.scratchdir</name>
        <value>hdfs://localhost:9000/user/hive/tmp</value>
        <description>HDFS root scratch dir for Hive jobs which gets created with write all (733) permission. For each connecting user, an HDFS scratch dir: ${hive.exec.scratchdir}/&lt;username&gt; is created, with ${hive.scratch.dir.permission}.</description>
  </property>
<property>
    <name>hive.exec.local.scratchdir</name>
    <value>/usr/local/hive/tmp</value>
    <description>Local scratch space for Hive jobs</description>
  </property>
<property>
       <name>hive.metastore.warehouse.dir</name>
       <value>hdfs://localhost:9000/user/hive/warehouse</value>
       <description>location of default database for the warehouse</description>
</property>
<property>
        <name>hive.downloaded.resources.dir</name>
        <value>/usr/local/hive/resources</value>
        <description>Temporary local directory for added resources in the remote file system.</description>
</property>
<property>
         <name>hive.querylog.location</name>
         <value>/usr/local/hive/log</value>
         <description>Location of Hive run time structured log file</description>
</property>
<property>
         <name>hive.server2.logging.operation.log.location</name>
         <value>/usr/local/hive/operation_logs</value>
         <description>Top level directory where operation logs are stored if logging functionality is enabled</description>
</property>

1. Add the following configuration to Hadoop core site to authorize the current user to access hdfs

<property>
        <name>hadoop.proxyuser.zj-db0972.groups</name>
        <value>*</value>
 </property>
 <property>
        <name>hadoop.proxyuser.zj-db0972.hosts</name>
        <value>*</value>
 </property>

1. Configure hive log directory

   • cp hive-log4j.properties.template hive-log4j.properties
   • vi hive-log4j.properties
     hive.log.dir=/usr/local/hive/log / / configure the directory of hive log

2. Copy MySQL connector to hive
   Copy a MySQL connector to Hive's lib directory

   • curl -L 'http://www.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.42.tar.gz/from/http://mysql.he.net/'; | tar xz
   • cp mysql-connector-java-5.1.42/mysql-connector-java-5.1.42-bin.jar /usr/local/Cellar/hive/2.1.1/libexec/lib/

IV. initialize metabase

1. Initialize metastore Library: schematool -initSchema -dbType mysql
2. Log in to mysql: mysql -u hive -p123456
3. Use metastore database: use metastore
4. View tables: show tables

V. HDFS creation directory

1. Establish / tmp and / usr/hive/warehouse directories on HDFS, give group users write permission, and configure Hive's default data file storage directory
   • hadoop dfs -mkdir -p hdfs://localhost:9000/user/hive/warehouse
   • hadoop dfs -mkdir -p hdfs://localhost:9000/user/hive/tmp
   • hadoop dfs -chmod 777 hdfs://localhost:9000/user/hive/warehouse
   • hadoop dfs -chmod 777 hdfs://localhost:9000/user/hive/tmp

Vi. start up service

1. Start the metastore service
   hive —service metastore &
2. Start the hiverserver2 service
   hive --service hiveserver2
3. You can start to use jdbc mode to connect hivesever for read and write operations

7. Client connection code

package com.test.hive.client;

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

public class HiveJdbcClient {
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    /**
     * @param args
     * @throws SQLException
     */
    public static void main(String[] args) throws SQLException {
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.exit(1);
        }
        // 1. connect to hive2
        Connection con = DriverManager.getConnection("jdbc:hive2://localhost:10000/default", "", "");
        Statement stmt = con.createStatement();
        // 2. create table
        String tableName = "testHiveDriverTable";
        stmt.execute("drop table if exists " + tableName);
        stmt.execute("create table " + tableName + " (key int, value string)");
        // 3. show tables
        String sql = "show tables '" + tableName + "'";
        System.out.println("Running: " + sql);
        ResultSet res = stmt.executeQuery(sql);
        if (res.next()) {
            System.out.println(res.getString(1));
        }
        // 4. describe table
        sql = "describe " + tableName;
        System.out.println("Running: " + sql);
        res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(res.getString(1) + "\t" + res.getString(2));
        }
        // 5. select * query
        sql = "select * from " + tableName;
        System.out.println("Running: " + sql);
        res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(String.valueOf(res.getInt(1)) + "\t" + res.getString(2));
        }
        // 6. regular hive query
        sql = "select count(1) from " + tableName;
        System.out.println("Running: " + sql);
        res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(res.getString(1));
        }
    }
}