java通過jdbc連接hive

Hive系列文章

  1. Hive表的基本操作
  2. Hive中的集合數(shù)據(jù)類型
  3. Hive動(dòng)態(tài)分區(qū)詳解
  4. hive中orc格式表的數(shù)據(jù)導(dǎo)入
  5. Java通過jdbc連接hive
  6. 通過HiveServer2訪問Hive
  7. SpringBoot連接Hive實(shí)現(xiàn)自助取數(shù)
  8. hive關(guān)聯(lián)hbase表
  9. Hive udf 使用方法
  10. Hive基于UDF進(jìn)行文本分詞
  11. Hive窗口函數(shù)row number的用法
  12. 數(shù)據(jù)倉庫之拉鏈表

java連接hive進(jìn)行一些統(tǒng)計(jì)分析，在工作中再正常不過了。這里介紹通過java直連和mybatis整合的方式。

1. java直連的方式

java直連的方式跟連接mysql手法一樣，只是driverName不一樣而已。

public class HiveJdbcConnect implements Closeable{

    private static String driverName = "org.apache.hive.jdbc.HiveDriver";

    //    jdbc:hive2://
    private String url ;
    private String user ;
    private String password ;

    private  Connection conn = null;

    public HiveJdbcConnect(String url, String user, String password) {
        this.url = url;
        this.user = user;
        this.password = password;
        try {
            init();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void init() throws Exception {
        Class.forName(driverName);
        conn = DriverManager.getConnection(url, user, password);
    }

}
Java

測試用例

@Test
    public void test(){
        HiveConfigModel hiveConfigModel = ConfigureContext.getInstance("hive-config.properties")
                .addClass(HiveConfigModel.class)
                .getModelProperties(HiveConfigModel.class);

        try {
            Connection conn = DriverManager.getConnection(hiveConfigModel.getUrl(),
                    hiveConfigModel.getUsername(), hiveConfigModel.getPassword());

            String sql = "show tables";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            ResultSet rs = preparedStatement.executeQuery();
            List<String> tables = new ArrayList<>();
            while (rs.next()){
                tables.add(rs.getString(1));
            }

            System.out.println(tables);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
Java

2. 整合mybatis

需要的maven依賴，為了節(jié)省篇幅，給出了主要的依賴：

<dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.2.7</version>
</dependency>
<!-- hadoop依賴 -->
<dependency>
    <groupId>org.apache.hadoop</groupId>
    <artifactId>hadoop-common</artifactId>
    <version>2.6.5</version>
</dependency>

<!-- hive-jdbc -->
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-jdbc</artifactId>
    <exclusions>
        <exclusion>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
        </exclusion>
        <exclusion>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
        </exclusion>
        <exclusion>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
        </exclusion>
    </exclusions>
    <version>1.2.1</version>
</dependency>
XML

mybatis配置文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="production">
        <environment id="production">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="org.apache.hive.jdbc.HiveDriver"/>
                <property name="url" value="jdbc:hive2://localhost:10000/db"/>
                <property name="username" value="hive"/>
                <property name="password" value="hive"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/hive/hm2/ActiveHelperMapper.xml"/>
    </mappers>
</configuration>
XML

mapper文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="***">
    <select  id="***" resultType="Integer" useCache="false" parameterType="String">
        select count(1) from tableName where dt = #{dateTime}
</select>

</mapper>
XML

sqlSessionFactory部分：

public class HiveSqlSessionFactory {

    private static Object o = new Object();

    private static HiveSqlSessionFactory hiveSqlSessionFactory;
    private SqlSessionFactory sqlSessionFactory;

    public static HiveSqlSessionFactory getInstance (){
        synchronized (o){
            if (hiveSqlSessionFactory==null){
                hiveSqlSessionFactory = new HiveSqlSessionFactory();
            }
            return hiveSqlSessionFactory;
        }
    }

    private HiveSqlSessionFactory(){
        try {
            init();
        }catch (Exception e){
            log.error("init sqlSessionError->"+e.getMessage());
        }
    }

    public void init () throws IOException {
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        InputStream inputStream= Resources.getResourceAsStream("sqlConfig.xml");
        SqlSessionFactory sqlSessionFactory = builder.build(inputStream);
        log.info("sqlSession init->sqlConfig.xml");
        this.sqlSessionFactory=sqlSessionFactory;

    }

    public SqlSessionFactory getSqlSessionFactory(){
        synchronized (o) {
            return sqlSessionFactory;
        }
    }
}
Java

mapper層：

public interface ActiveHelperMapper {
    int getHelperCount(@Param("dateTime") String dateTime);
}
Java

dao層：

public class ActiveHelperMapperImpl implements ActiveHelperMapper {

    private static SqlSessionFactory sqlSessionFactory = HiveSqlSessionFactory.getInstance().getSqlSessionFactory();

    @Override
    public int getHelperCount(String dateTime) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ActiveHelperMapper activeHelperMapper = sqlSession.getMapper(ActiveHelperMapper.class);

        int count = activeHelperMapper.getHelperCount(dateTime);

        sqlSession.close();

        return count;
    }
}
Java

測試用例：

@Test
public void getHelperCountTest(){
    String dateTime = DateUtils.getDayDelayFormat(new Date(), -1, "yyyy-MM-dd");
    ActiveHelperMapperImpl activeHelperMapper = new ActiveHelperMapperImpl();
    System.out.println(activeHelperMapper.getHelperCount(dateTime));
}


作者：柯廣的網(wǎng)絡(luò)日志

微信公眾號：Java大數(shù)據(jù)與數(shù)據(jù)倉庫