大数据（3c）HIVE开启网络服务并使用IDEA连接

本文续：https://yellow520.blog.youkuaiyun.com/article/details/111563044

1、额外配置

1.1、Hadoop

• proxy意思是代理人
• 这个配置大概就是，提交给HiveServer2服务端的操作都交由代理人来做
  此处配置这个代理人名字为root，也可以是别的名字
  比如a用户经此HiveServer2上传了文件，该文件权限不是a而是代理人名字

vi $HADOOP_HOME/etc/hadoop/core-site.xml

<!-- HIVE Server2 代理配置 -->
<property>
    <name>hadoop.proxyuser.root.hosts</name>
    <value>*</value>
    <description>哪些主机节点允许通过本代理</description>
</property>
<property>
    <name>hadoop.proxyuser.root.groups</name>
    <value>*</value>
    <description>哪些组允许通过本代理</description>
</property>
<property>
    <name>hadoop.proxyuser.root.users</name>
    <value>*</value>
    <description>哪些用户允许通过本代理</description>
</property>

记得分发噢~

1.2、HIVE

vi $HIVE_HOME/conf/hive-site.xml

<!-- 这俩配置server2的地址和端口 -->
<property>
    <name>hive.server2.thrift.port</name>
    <value>10000</value>
    <description>
    Port number of HiveServer2 Thrift interface when hive.server2.transport.mode is 'binary'.
    </description>
</property>
<property>
    <name>hive.server2.thrift.bind.host</name>
    <value>hadoop100</value>
    <description>Bind host on which to run the HiveServer2 Thrift service.</description>
</property>
<!-- 用于 metastore的客户端 远程连接 metastore 的URI -->
<property>
    <name>hive.metastore.uris</name>
    <value>thrift://hadoop100:9083</value>
    <description>
    Thrift URI for the remote metastore.
    Used by metastore client to connect to remote metastore.
    </description>
</property>
<!-- -->
<property>
    <name>hive.metastore.schema.verification</name>
    <value>false</value>
    <description>
Enforce metastore schema version consistency.
True:
Verify that version information stored in is compatible with one from Hive jars.
Also disable automatic schema migration attempt.
Users are required to manually migrate schema after Hive upgrade
 which ensures proper metastore schema migration. (Default)
False:
Warn if the version information stored in metastore doesn't match with one from in Hive jars.
    </description>
</property>
<!-- -->
<property>
    <name>hive.metastore.event.db.notification.api.auth</name>
    <value>false</value>
    <description>
Should metastore do authorization against database notification related APIs such as get_next_notification.
If set to true, then only the superusers in proxy settings have the permission
    </description>
</property>
<!-- -->
<property>
    <name>hive.server2.active.passive.ha.enable</name>
    <value>true</value>
    <description>
Whether HiveServer2 Active/Passive High Availability be enabled
 when Hive Interactive sessions are enabled.
This will also require hive.server2.support.dynamic.service.discovery to be enabled.
    </description>
</property>

2、服务启动命令

Xshell两个窗口，分别执行下面两条命令

hive --service metastore

hive --service hiveserver2

3、启动脚本

touch $HIVE_HOME/bin/hiveServices.sh
chmod 777 $HIVE_HOME/bin/hiveServices.sh
vi $HIVE_HOME/bin/hiveServices.sh

下面9083和10000这两个端口号是依据HIVE的配置

#!/bin/bash
HIVE_LOG_DIR=$HIVE_HOME/logs

mkdir -p $HIVE_LOG_DIR

#检查进程是否运行正常，参数1为进程名，参数2为进程端口
function check_process()
{
    pid=$(ps -ef 2>/dev/null | grep -v grep | grep -i $1 | awk '{print $2}')
    ppid=$(netstat -nltp 2>/dev/null | grep $2 | awk '{print $7}' | cut -d '/' -f 1)
    echo $pid
    [[ "$pid" =~ "$ppid" ]] && [ "$ppid" ] && return 0 || return 1
}

function hive_start()
{
    metapid=$(check_process HiveMetastore 9083)
    cmd="nohup hive --service metastore >$HIVE_LOG_DIR/metastore.log 2>&1 &"
    cmd=$cmd" sleep 4; hdfs dfsadmin -safemode wait >/dev/null 2>&1"
    [ -z "$metapid" ] && eval $cmd
    server2pid=$(check_process HiveServer2 10000)
    cmd="nohup hive --service hiveserver2 >$HIVE_LOG_DIR/hiveServer2.log 2>&1 &"
    [ -z "$server2pid" ] && eval $cmd
}

function hive_stop()
{
    metapid=$(check_process HiveMetastore 9083)
    [ "$metapid" ] && kill $metapid
    server2pid=$(check_process HiveServer2 10000)
    [ "$server2pid" ] && kill $server2pid
}

case $1 in
"start")
    hive_start
    ;;
"stop")
    hive_stop
    ;;
"restart")
    hive_stop
    sleep 2
    hive_start
    ;;
"status")
    check_process HiveMetastore 9083 >/dev/null && echo "Metastore在运" || echo "Metastore停运"
    check_process HiveServer2 10000 >/dev/null && echo "HiveServer2在运" || echo "HiveServer2停运"
    ;;
*)
    echo Invalid Args!
    echo 'Usage: '$(basename $0)' start|stop|restart|status'
    ;;
esac

用法

$HIVE_HOME/bin/hiveServices.sh start

启动后jps可以看到多了两个RunJar

4、使用客户端连接到HIVE

4.1、beeline客户端

进入交互界面

beeline -u jdbc:hive2://hadoop100:10000 -n root

SHOW DATABASES;
!quit

执行一次query

beeline -u jdbc:hive2://HOST:10000/库名 -n root -e 'SHOW TABLES'

选项	全称	参数值
-u	database URL	jdbc:hive2://主机:端口/库名
-n	username	用户名
-p	password	密码
-e	query	执行一次SQL

4.1.1、beeline脚本

touch $HIVE_HOME/bin/beeline.py
chmod 777 $HIVE_HOME/bin/beeline.py
vim $HIVE_HOME/bin/beeline.py

#!/usr/bin/python2
# coding:utf-8
from os import system
from subprocess import check_output
from re import findall

t = check_output('cat $HIVE_HOME/conf/hive-site.xml', shell=True)
host = findall(r'<name>hive.server2.thrift.bind.host</name>\s*<value>(.+?)</value>', t)[0]
port = findall(r'<name>hive.server2.thrift.port</name>\s*<value>([0-9]+?)</value>', t)[0]
user = check_output('whoami', shell=True).strip()
system('beeline -u jdbc:hive2://{}:{} -n {}'.format(host, port, user))

4.2、IDEA连接HIVE（重点！有截图！版本≥2019）

根据提示，下载JDBC

5、配置hiveServer2密码验证

5.1、创建Maven工程，写个Java代码，打包
pom.xml

<dependencies>
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-jdbc</artifactId>
        <version>3.1.2</version>
    </dependency>
</dependencies>

org.custom.MyPwdAuthenticator

package org.custom;

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hive.conf.HiveConf;
import org.slf4j.Logger;
import javax.security.sasl.AuthenticationException;

public class MyPwdAuthenticator implements org.apache.hive.service.auth.PasswdAuthenticationProvider{

    private Logger LOG = org.slf4j.LoggerFactory.getLogger(MyPwdAuthenticator.class);
    private static final String HIVE_JDBC_PASSWD_AUTH_PREFIX="hive.jdbc_passwd.auth.%s";
    private Configuration conf=null;

    @Override
    public void Authenticate(String userName, String passwd) throws AuthenticationException {
        LOG.info("Hive2 login info... user: "+ userName +" is trying login.");
        String passwdConf = getConf().get(String.format(HIVE_JDBC_PASSWD_AUTH_PREFIX, userName));
        if(passwdConf==null){
            String message = "Hive2 login error! Configuration null. user:"+userName;
            LOG.info(message);
            throw new AuthenticationException(message);
        }
        if(!passwd.equals(passwdConf)){
            String message = "Hive2 login error! userName or password is error. user:"+userName;
            throw new AuthenticationException(message);
        }
    }

    public Configuration getConf() {
        if(conf==null){
            this.conf=new Configuration(new HiveConf());
        }
        return conf;
    }

    public void setConf(Configuration conf) {
        this.conf=conf;
    }
}

5.2、将jar上传到$HIVE_HOME/lib
5.3、编辑$HIVE_HOME/conf/hive-site.xml

<!-- hiveServer2密码验证 -->
<property>
    <name>hive.server2.authentication</name>
    <value>CUSTOM</value>
    <description>认证模式设定为CUSTOM，默认NONE</description>
</property>
<property>
    <name>hive.server2.custom.authentication.class</name>
    <value>org.custom.MyPwdAuthenticator</value>
    <description>自定义认证的全类名</description>
</property>
<property>
    <name>hive.jdbc_passwd.auth.用户名</name>
    <value>密码</value>
</property>

5.4、重启

hiveServices.sh restart

5.5、beeline客户端测试

beeline -u jdbc:hive2://主机名:10000 -n 用户名 -p 密码

---

Appendix

en	🔉	cn
hive	haɪv	n. 忙碌的场所；蜂房；蜂群；蜂窝状物体
bee	biː	n. 蜜蜂；<美>聚会，联谊赛；工作忙碌的人
thrift	θrɪft	n. 节约，节俭；海石竹；<美>储蓄机构
compatible	kəmˈpætəbl	adj. 兼容的；可共存的；可和睦相处的；与……一致的；n. 兼容性计算机
consistency	kənˈsɪstənsi	n. 一致性，连贯性；黏稠度，平滑度
migrate	ˈmaɪɡreɪt	v. 迁徙；移居；转移；把（程序，硬件）从一系统转移到另一系统
migration	migration	n. 移民，迁徙；（计算机系统的）改变，（程序或硬件的）迁移，转移
interactive	ˌɪntərˈæktɪv	adj. 交互式的，人机对话的；相互交流的，互动的
passive	ˈpæsɪv	adj. 消极的，被动的；被动语态的；（抵抗）非暴力的；（电路，装置）无源的
notification	ˌnoʊtɪfɪˈkeɪʃn	n. 通知；通知单
related	rɪˈleɪtɪd	adj. 相关的
authentication	ɔːˌθentɪˈkeɪʃn	n. 证明真实性，鉴定；身份验证，认证