Java通过jdbc连接hive

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

		
		
		
			org.apache.hive
			hive-jdbc
			2.1.0
		

		
		
			org.apache.hive
			hive-metastore
			2.1.0
		

		
		
			org.apache.hive
			hive-service
			2.1.0

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="密码";

    
    public static Connection getConn() throws ClassNotFoundException, SQLException {
        Class.forName(DriverName);
        Connection connection = DriverManager.getConnection(url,user,pass);
        return connection;
    }

    
    public static Statement getStmt(Connection connection) throws SQLException {
        return connection.createStatement();
    }

    
    public static void closeFunc(Connection connection,Statement statement) throws SQLException {
        statement.close();
        connection.close();
    }
}

public class JdbcToHiveUtil {
    
    //获取表字段
    public static Set getFieldByTableName(String dbTableName) throws SQLException, ClassNotFoundException {
        Connection conn = JDBCUtil.getConn();
        System.out.println("创建连接成功");
        Statement stat = JDBCUtil.getStmt(conn);
        System.out.println("创建命令成功");
        Set 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 dataList = new ArrayList<>();
        ResultSet res = stat.executeQuery("select * from " + dbTableName + " LIMIT 10");
        Set 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 fields;
   
    @ApiModelProperty(value = "预览的表数据")
    private List dataList;
}

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

Java通过jdbc连接hive

大数据系统相关栏目本月热门文章