Java通过jdbc连接hive

好久没有写博客了，今天开始会陆续将自己在工作中用到的工具及碰到的问题更新到博客中以做记录，期待和大家交流探讨。
废话不多说，直接上代码，有问题可以评论区交流，如下为demo示例：

		<!-- jdbc连接hive所需依赖 -->
		<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
		<dependency>
			<groupId>org.apache.hive</groupId>
			<artifactId>hive-jdbc</artifactId>
			<version>2.1.0</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-metastore -->
		<dependency>
			<groupId>org.apache.hive</groupId>
			<artifactId>hive-metastore</artifactId>
			<version>2.1.0</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-service -->
		<dependency>
			<groupId>org.apache.hive</groupId>
			<artifactId>hive-service</artifactId>
			<version>2.1.0</version>
		</dependency>

public class JDBCUtil {
    static final String DriverName="org.apache.hive.jdbc.HiveDriver";
    static final String url="hive连接";
    static final String user=“账号";
    static final String pass="密码";

    /**
     * 创建连接
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConn() throws ClassNotFoundException, SQLException {
        Class.forName(DriverName);
        Connection connection = DriverManager.getConnection(url,user,pass);
        return connection;
    }

    /**
     * 创建命令
     * @param connection
     * @return
     * @throws SQLException
     */
    public static Statement getStmt(Connection connection) throws SQLException {
        return connection.createStatement();
    }

    /**
     * 关闭连接
     * @param connection
     * @param statement
     * @throws SQLException
     */
    public static void closeFunc(Connection connection,Statement statement) throws SQLException {
        statement.close();
        connection.close();
    }
}

public class JdbcToHiveUtil {
    
    //获取表字段
    public static Set<String> getFieldByTableName(String dbTableName) throws SQLException, ClassNotFoundException {
        Connection conn = JDBCUtil.getConn();
        System.out.println("创建连接成功");
        Statement stat = JDBCUtil.getStmt(conn);
        System.out.println("创建命令成功");
        Set<String> fields = new HashSet<>();
        ResultSet res = stat.executeQuery("desc " + dbTableName);
        while (res.next()) {
            String col_name = res.getString("col_name");
            if (StringUtils.isNotBlank(col_name) && !col_name.contains("#")) {
                fields.add(col_name);
            }
        }
        JDBCUtil.closeFunc(conn,stat);
        return fields;
    }

    //获取表字段及字段对应的10条数据
    public static GetTableByHiveDto listDataByTableName(String dbTableName) throws SQLException, ClassNotFoundException {
        Connection conn = JDBCUtil.getConn();
        System.out.println("创建连接成功");
        Statement stat = JDBCUtil.getStmt(conn);
        System.out.println("创建命令成功");
        List<Map> dataList = new ArrayList<>();
        ResultSet res = stat.executeQuery("select * from " + dbTableName + " LIMIT 10");
        Set<String> fields =  GetTableByHiveUtil.getFieldByTableName(dbTableName);
        while (res.next()) {
            Map map = new HashMap();
            for (String fieldName : fields) {
                map.put(fieldName,res.getString(fieldName));
            }
            dataList.add(map);
        }
        GetTableByHiveDto dto = new GetTableByHiveDto();
        dto.setDataList(dataList);
        dto.setFields(fields);
        JDBCUtil.closeFunc(conn,stat);
        return dto;
    }

    //创建表
    public static void carryOUtDdl(String sql) throws SQLException, ClassNotFoundException {
        Connection conn = JDBCUtil.getConn();
        Statement stat = JDBCUtil.getStmt(conn);
        stat.executeUpdate(sql);
        JDBCUtil.closeFunc(conn,stat);
    }
    
}

测试：

@Test
public void hiveTest() throws SQLException, ClassNotFoundException {
    String sql = "CREATE  TABLE st_student_test2(  \n" +
            "      id BIGINT COMMENT '学生id',   \n" +
            "      studentName string COMMENT '学生姓名',   \n" +
            "      age INT COMMENT '年龄') COMMENT '学生表建表测试'\n" +
            "    partitioned by(dt string ) \n" +
            "    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','";
    GetTableByHiveUtil.carryOUtDdl(sql);
}

@Data
@ApiModel(value="数据预览字段", description="数据预览字段")
public class GetTableByHiveDto implements Serializable {
   /**预览的表字段*/
    @ApiModelProperty(value = "预览的表字段")
    private Set<String> fields;
   /**预览的表数据*/
    @ApiModelProperty(value = "预览的表数据")
    private List<Map> dataList;
}

hive建表语句可参考：
待更新