大数据篇--HiveServer2使用心得

CDH4中HiveServer2的配置

• CDH4版与新版Hive中部分参数有区别
• 使用cm界面对需要开启HiveServer2的机器中的hive-site.xml添加以下

<property>
  <name>hive.server2.authentication</name>
  <value>CUSTOM</value>
</property>
<property>
  <name>HIVE_SERVER2_CUSTOM_AUTHENTICATION_CLASS</name>
  <value>top.fresh00air.hive.CustomHiveServer2Auth</value>
  <description>自定义的hiveserver2权限校验类</description>
</property>
<property>
  <name>hive.server2.custom.authentication.file</name>
  <value>/app/bi/hive/hive.server2.users.conf</value>
  <description>权限校验的用户名,密码(MD5)</description>
</property>

CDH5+中HiveServer2的配置

<property>
  <name>hive.server2.authentication</name>
  <value>CUSTOM</value>
</property>
<property>
  <name>hive.server2.custom.authentication.class</name>
   <value>top.fresh00air.hive.CustomHiveServer2Auth</value>
  <description>自定义的hiveserver2权限校验类</description>
</property>
<property>
  <name>hive.server2.custom.authentication.file</name>
  <value>/app/bi/hive/hive.server2.users.conf</value>
  <description>权限校验的用户名,密码(MD5)</description>
</property>

Java 验证类代码(CustomHiveServer2Auth)

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.security.sasl.AuthenticationException;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hive.conf.HiveConf;
import org.apache.hive.service.auth.PasswdAuthenticationProvider;
public class CustomHiveServer2Auth implements PasswdAuthenticationProvider {
    protected static String filePath;

    static {
        HiveConf hiveConf = new HiveConf();
        Configuration conf = new Configuration(hiveConf);
        filePath = conf.get("hive.server2.custom.authentication.file");
        System.out.println(getTime() + ": hive.server2.custom.authentication.file ["
        + filePath + "] ..");
    }
    @Override
    public void Authenticate(String username, String password)
    throws AuthenticationException {
        String passMd5 = new MD5().md5(password);
        boolean ok = false;
        File file = new File(filePath);
        try (BufferedReader reader = new BufferedReader(new FileReader(file))) {
    String tempString;
    while ((tempString = reader.readLine()) != null) {
        String[] datas = tempString.split(",", -1);
        if (datas.length != 2)
            continue;
        // ok
        if (datas[0].equals(username) && datas[1].equals(passMd5)) {
            ok = true;
            break;
        }
    }
        } catch (Exception e) {
    e.printStackTrace();
    throw new AuthenticationException("read auth config file error, ["
            + filePath + "] ..", e);
        }
        if (ok) {
    System.out.println(getTime() + ": user [" + username
            + "] auth check ok .. ");
        } else {
    System.out.println(getTime() + ": user [" + username
            + "] auth check fail .. ");
    throw new AuthenticationException("user [" + username
            + "] auth check fail .. ");
        }
    }
    // MD5加密
    class MD5 {
        private MessageDigest digest;
        private char hexDigits[] = { '0', '1', '2', '3', '4', '5', '6', '7',
        '8', '9', 'a', 'b', 'c', 'd', 'e', 'f' };
        MD5() {
    try {
        digest = MessageDigest.getInstance("MD5");
    } catch (NoSuchAlgorithmException e) {
        throw new RuntimeException(e);
    }
        }
        String md5(String str) {
    byte[] btInput = str.getBytes();
    digest.reset();
    digest.update(btInput);
    byte[] md = digest.digest();
    // 把密文转换成十六进制的字符串形式
    int j = md.length;
    char strChar[] = new char[j * 2];
    int k = 0;
    for (byte byte0 : md) {
        strChar[k++] = hexDigits[byte0 >>> 4 & 0xf];
        strChar[k++] = hexDigits[byte0 & 0xf];
    }
    return new String(strChar);
        }
    }
    public static String getTime() {
        SimpleDateFormat dateFormat = new SimpleDateFormat(
        "YYYY-MM-dd HH:mm:ss:SSS");
        return dateFormat.format(new Date());
    }
}

使用方法

法一(Java调用)

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class HiveUtil {
    private static final String driverName;
    private String url;
    private String userName;
    private String password;
    static {
        driverName = "org.apache.hive.jdbc.HiveDriver";
        try {
            Class.forName(driverName);
            System.err.println("Hive-JDBC参数初始化成功");
        } catch (ClassNotFoundException e) {
            System.err.println(String.format("无法载入%s", driverName));
            e.printStackTrace();
        }
    }
    private Connection conn = null;
    public HiveUtil(String url, String userName, String password) throws SQLException {
        this.url = url;
        this.userName = userName;
        this.password = password;
        init();
    }
    public List<Map<String, String>> executeQuery(String sql) throws SQLException {
        System.err.println("[hive]-查询hql: {" + sql + "}");
        List<Map<String, String>> result = new ArrayList<>();
        PreparedStatement pstsm = null;
        ResultSet resultSet = null;
        try {
            pstsm = conn.prepareStatement(sql);
            resultSet = pstsm.executeQuery();
            ResultSetMetaData md = resultSet.getMetaData(); // 获得结果集结构信息,元数据
            int columnCount = md.getColumnCount(); // 获得列数
            while (resultSet.next()) {
                Map<String, String> rowData = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i),  resultSet.getString(i));
                }
                result.add(rowData);
            }
        } catch (Exception e) {
            System.err.println("[hive].executeQuery异常");
            e.printStackTrace();
        } finally {
            if (pstsm != null) {
                pstsm.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        }
        System.err.println("[hive]-结果条数: {" + result.size() + "}");
        return result;
    }
    public List<List<String>> executeQueryList(String sql) throws SQLException {
        System.err.println("[hive]-查询hql: {" + sql + "}");
        List<List<String>> result = new ArrayList<>();
        PreparedStatement pstsm = null;
        ResultSet resultSet = null;
        try {
            pstsm = conn.prepareStatement(sql);
            resultSet = pstsm.executeQuery();
            ResultSetMetaData md = resultSet.getMetaData(); // 获得结果集结构信息,元数据
            int columnCount = md.getColumnCount(); // 获得列数
            List<String> headData = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                headData.add(md.getColumnName(i));
            }
            result.add(headData);
            while (resultSet.next()) {
                List<String> rowData = new ArrayList<>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.add(resultSet.getString(i));
                }
                result.add(rowData);
            }
        } catch (Exception e) {
            System.err.println("[hive].executeQuery异常");
            e.printStackTrace();
        } finally {
            if (pstsm != null) {
                pstsm.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        }
        System.err.println("[hive]-结果条数: {" + (result.size() - 1) + "}");
        return result;
    }
    public void setDatabase(String sql) throws SQLException {
        System.err.println("指定数据库{" + sql + "}");
        PreparedStatement pstsm = null;
        try {
            pstsm = conn.prepareStatement(sql);
            pstsm.execute();
        } catch (Exception e) {
        } finally {
            if (pstsm != null) {
                pstsm.close();
            }
        }
    }
    public void close() throws SQLException {
        if (conn != null) {
            conn.close();
        }
    }
    private void init() throws SQLException {
        conn = DriverManager.getConnection(url, userName, password);
    }

法二(Beeline调用)

beeline -u jdbc:hive2://IP<HOST>:10000 -n root