Java通过kerberos权限认证集成hive

java通过kerberos权限认证集成hive，并操作hive实现hive库和表、分区表的增删查等功能

1、pom文件中引入hive包

		<dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-client</artifactId>
            <version>2.7.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>2.7.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-hdfs</artifactId>
            <version>2.7.3</version>
        </dependency>
		<dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>2.1.0</version>
        </dependency>

2、从集群中下载认证过可以登录的keytab文件，以及krb5.conf文件还有core-site.xml、hdfs-site.xml、hive-site.xml、yarn-site.xml放到项目的resources目录下
（xml文件按照自己项目需要下载）

3、代码实现

package com.example.demo.hive;

import com.beust.jcommander.internal.Lists;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.security.UserGroupInformation;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;

public class HiveUtil {
    private final static Logger logger = LogManager.getLogger(HiveUtil.class);

    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
    private static Connection connection = null;

    public static void main(String[] args) {

        connection = getConnection();
        //createDatabase();//创建数据库
        //dropDatabase();//删除数据库
        //createTable();//创建表
        //dropTable();//删除表
        //insertTableData();//插入数据
        descTable();//查看表结构
        //getTableData();//查询表数据
        //loadData();//导入数据

/*        List<LinkedHashMap<String, Object>> tables = querySql("show databases");
        for (LinkedHashMap<String, Object> table : tables) {
            String s = JSONObject.toJSONString(table);
            System.out.println(s);
        }*/

    }

    public static Connection getConnection() {
        System.setProperty("java.security.krb5.conf", "src/main/resources/krb5.conf");
        Configuration config = new Configuration();
        UserGroupInformation.setConfiguration(config);
        try {
            UserGroupInformation.loginUserFromKeytab("hive", "src/main/resources/hive.keytab");
            String driver = "org.apache.hive.jdbc.HiveDriver";
            String jdbc = "jdbc:hive2://192.168.1.110:10000/default;principal=hive/hive@HADOOP.COM";
            String user = "hive";
            String pass = "123456";
            try {
                Class.forName(driver);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            try {
                connection = DriverManager.getConnection(jdbc, user, pass);
                System.out.println("连接成功");
            } catch (SQLException e) {
                e.printStackTrace();
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static List<LinkedHashMap<String, Object>> querySql(String sql) {
        // 执行sql
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            return buildListMap(resultSet);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭
            close(resultSet, statement);
        }
        return null;
    }

    /**
     * @Description 功能描述：将resultSet构造为List<Map>
     **/
    public static List<LinkedHashMap<String, Object>> buildListMap(ResultSet resultSet) throws SQLException {
        if (resultSet == null) {
            return Lists.newArrayList();
        }
        List<LinkedHashMap<String, Object>> resultList = new ArrayList<>();
        // 获取元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        while (resultSet.next()) {
            // 获取列数
            int columnCount = metaData.getColumnCount();
            LinkedHashMap<String, Object> map = new LinkedHashMap<>();
            for (int i = 0; i < columnCount; i++) {
                String columnName = metaData.getColumnName(i + 1);
                // 过滤掉查询的结果包含序号的
                if ("mm.row_num_01".equalsIgnoreCase(columnName)
                        || "row_num_01".equalsIgnoreCase(columnName)) {
                    continue;
                }
                // 去除hive查询结果的mm.别名前缀
                if (columnName.startsWith("mm.")) {
                    columnName = columnName.substring(columnName.indexOf(".") + 1);
                }
                Object object = resultSet.getObject(columnName);
                map.put(columnName, object);
            }
            resultList.add(map);
        }
        return resultList;
    }


    /**
     * 创建hive数据库
     */
    public static void createDatabase() {
        //创建hive库时指定location
       /* create database if not exists hive_user_location
        location "/user/hive/hive_location_db"
        with dbproperties ('createby'='linda');*/

        String sql = "create database if not exists test_db";
        System.out.println("创建数据库，脚本："+sql);
        try (Statement statement = connection.createStatement()) {
            statement.execute(sql);
            System.out.println("创建数据库成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建表
     */
    public static void createTable() {
        //创建分区表
        //String sql = "create table user_partition(deptno int, dname string) partitioned by (month string) row format delimited fields terminated by '\t'";
        String sql = "create table user_tb(id int, name string) row format delimited fields terminated by ','";
        logger.info("创建表，脚本：{}", sql);
        try (Statement statement = connection.createStatement()) {
            statement.execute(sql);
            logger.info("创建表成功");
        } catch (SQLException e) {
            logger.error("创建表出错", e);
        }
    }

    /**
     * 查看表结构
     */
    public static void descTable() {
        /***
         * 查看表已有分区信息
         *  show partitions 表名;
         */
        String sql = "desc  formatted  user_partition";// 查看分区表结构：desc  formatted  user_tb;
        System.out.println("查看表结构，脚本："+sql);
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                logger.info("字段名：{}，类型：{}",rs.getString(1),rs.getString(2));
            }
        } catch (SQLException e) {
            logger.error("查看表结构出错", e);
        }
    }

    /**
     * 查询表数据
     */
    public static void getTableData() {
        Date date = new Date();
        String month = sdf.format(date);
        //查看分区表数据
        //String sql = "select * from user_partition where month='"+month+"'";
        String sql = "select * from user_tb where id=1";
        System.out.println("查看表数据，脚本："+sql);
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                logger.info("字段名：{}，数据：{}",rs.getString(1),rs.getString(2));
            }
        } catch (SQLException e) {
            logger.error("查看表数据出错", e);
        }
    }

    /**
     * 删除表
     */
    public static void dropTable() {
        String sql = "drop table if exists user_tb";
        logger.info("删除表，脚本：{}", sql);
        try (Statement statement = connection.createStatement()) {
            statement.execute(sql);
            logger.info("删除表成功");
        } catch (SQLException e) {
            logger.error("删除表出错", e);
        }
    }

    /**
     * 删除数据库
     */
    public static void dropDatabase() {
        String sql = "drop database if exists test_db";
        logger.info("删除数据库，脚本：{}", sql);
        try (Statement statement = connection.createStatement()) {
            statement.execute(sql);
            logger.info("删除数据库成功");
        } catch (SQLException e) {
            logger.error("删除数据库出错", e);
        }
    }

    /**
     * 插入数据
     */
    public static void insertTableData() {
        Date date = new Date();
        String month = sdf.format(date);
        //插入分区表数据
        //String sql = "insert into table  user_partition partition(month='"+month+"') values (1,'分区数据')";
        String sql = "insert into table user_tb values (1,'张三')";
        logger.info("插入数据，脚本：{}", sql);
        try (Statement statement = connection.createStatement()) {
            statement.execute(sql);
            logger.info("插入数据成功");
        } catch (SQLException e) {
            logger.error("插入数据出错", e);
        }
    }
    /**
     * 导入数据，data.txt中的数据为格式为：<br>
     * 1,张三<br>
     * 2,李四
     */
    public static void loadData() {
        String sql = "load data local inpath '/home/data.txt' overwrite into table user_tb";
        logger.info("导入数据，脚本：{}", sql);
        try (Statement statement = connection.createStatement()) {
            statement.execute(sql);
            logger.info("导入数据成功");
        } catch (SQLException e) {
            logger.error("导入数据出错", e);
        }
    }

    /**
     * 关闭对象 传入多个时注意顺序， 需要先关闭哪个就传在参数前面
     * @param objs 对象动态数组
     */
    public static void close(Object... objs) {
        if (objs == null || objs.length == 0) {
            return;
        }
        for (Object obj : objs) {
            if (obj instanceof Statement) {
                try {
                    ((Statement) obj).close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (obj instanceof ResultSet) {
                try {
                    ((ResultSet) obj).close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (obj instanceof Connection) {
                try {
                    ((Connection) obj).close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

}