通过JDBC连接HiveServer2

时间:2021-09-22 22:48:57
如果通过JDBC连接HiveServer2时提示:User: hive is not allowed to impersonate hive,需要在core-site.xml中新增如下配置:
  hadoop.proxyuser.hive.hosts=*   hadoop.proxyuser.hive.groups=* 红色部分表示可通过代理用户hive操作的主机和用户组,蓝色部分表示所有的hadoop主机及该主机上的hadoop用户组。 连接代码如下:
package com.mengyao.bigdata.utils;

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

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

/**
 * HiveJdbcUtil
 * core-site.xml配置hive连接用户通过代理操作任意hadoop的用户组及主机
 * 		hadoop.proxyuser.hive.hosts=*
 * 		hadoop.proxyuser.hive.groups=*
 * @author mengyao
 *
 */
@Component
public class HiveJdbcUtil {

	private static HiveJdbcUtil hiveJdbcUtil;
	@Value("${hive.driverClassName}")
	private String driverClassName = "org.apache.hive.jdbc.HiveDriver";
	@Value("${hive.url}")
	private String url = "jdbc:hive2://h3:10000/default?mapred.job.queue.name=default;hive.mapred.mode=nonstrict";
	@Value("${hive.username}")
	private String username = "hive";
	@Value("${hive.password}")
	private String password = "hive";
	private Connection connection;
	
	
	/**
	 * 私有构造函数声明
	 */
	private HiveJdbcUtil() {
		try {
			Class.forName(driverClassName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取HiveJdbcUtil单例对象
	 * @return
	 */
	public static HiveJdbcUtil getInstance() {
		if (null == hiveJdbcUtil) {
			hiveJdbcUtil = new HiveJdbcUtil();
		}
		return hiveJdbcUtil;
	}
	
	/**
	 * 获取HiveJdbc连接
	 * @return
	 */
	public Connection getConnection() {
		try {
			connection = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}
	
	/**
	 * 关闭连接
	 * @param rs
	 * @param ps
	 * @param st
	 * @param connection
	 */
	public void closeAll(ResultSet rs, PreparedStatement ps, Statement st, Connection connection) {
		try {
			if (null != rs) {
				rs.close();
			}
			if (null != ps) {
				ps.close();
			}
			if (null != st) {
				st.close();
			}
			if (null != connection) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		HiveJdbcUtil instance = HiveJdbcUtil.getInstance();
		Connection connection_ = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			connection_ = instance.getConnection();
			ps = connection_.prepareStatement("show databases");
			rs = ps.executeQuery();
			while(rs.next()) {
				System.out.println("==== "+rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			instance.closeAll(rs, ps, null, connection_);
		}
	}

}
 

User: hive is not allowed to impersonate hive