spring boot集成hive

---

前言

spring boot集成hive，实现在网页查询hive数据。
非完全原创，参考链接放在下面
原帖的依赖会冲突导致无法运行，本帖修改了一些依赖，并加入了lombok，其余同原帖。

https://blog.csdn.net/pengjunlee/article/details/81838480

---

本例使用了德鲁伊连接池，原帖还有不使用德鲁伊连接池的例子。
spring boot 2.4.3
hive 3.1.2

一、创建spring boot工程

starters选择web、jdbc、thymeleaf、lombok。

1. 添加hive-jdbc依赖（无需hadoop依赖）

org.eclipse.jetty会冲突，所以排除掉
hive-shims和javax-el如果冲突也可以排除掉，不影响使用

		<dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>3.1.2</version>
            <exclusions>
                <exclusion>
                    <groupId>org.eclipse.jetty</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.hive</groupId>
                    <artifactId>hive-shims</artifactId>
                </exclusion>
                <exclusion>
          		 	 <groupId>org.glassfish</groupId>
           			 <artifactId>javax-el</artifactId>
      		  </exclusion>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-log4j12</artifactId>
                </exclusion>
                 <exclusion>
                    <groupId>org.apache.logging.log4j</groupId>
                    <artifactId>log4j-slf4j-impl</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

2. 添加德鲁伊连接池依赖

 		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </dependency>

3. 完整依赖如下

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        
		<!-- 德鲁伊连接池依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>

        <!-- 添加hive依赖 -->
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>3.1.2</version>
            <exclusions>
                <exclusion>
                    <groupId>org.eclipse.jetty</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.hive</groupId>
                    <artifactId>hive-shims</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.glassfish</groupId>
                    <artifactId>javax-el</artifactId>
                </exclusion>
                <!--<exclusion>-->
                    <!--<groupId>org.slf4j</groupId>-->
                    <!--<artifactId>slf4j-log4j12</artifactId>-->
                <!--</exclusion>-->
                 <exclusion>
                    <groupId>org.apache.logging.log4j</groupId>
                    <artifactId>log4j-slf4j-impl</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

二、使用步骤

1.配置Druid连接池

import javax.sql.DataSource;

import lombok.Data;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import com.alibaba.druid.pool.DruidDataSource;

@Data
@Configuration
@ConfigurationProperties(prefix = "hive")
public class HiveDruidConfig {

    private String url;
    private String user;
    private String password;
    private String driverClassName;
    private int initialSize;
    private int minIdle;
    private int maxActive;
    private int maxWait;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;

    @Bean(name = "hiveDruidDataSource")
    @Qualifier("hiveDruidDataSource")
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(url);
        datasource.setUsername(user);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);

        // pool configuration
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        datasource.setPoolPreparedStatements(poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        return datasource;
    }

    @Bean(name = "hiveDruidTemplate")
    public JdbcTemplate hiveDruidTemplate(@Qualifier("hiveDruidDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
    
}

2. 配置application.yml

url、用户名需要改成自己的

hive:
  url: jdbc:hive2://hadoop:10000/default
  driver-class-name: org.apache.hive.jdbc.HiveDriver
  type: com.alibaba.druid.pool.DruidDataSource
  user: abc
  password:
  
  # 下面为连接池的补充设置，应用到上面所有数据源中
  # 初始化大小，最小，最大
  initialSize: 1
  minIdle: 3
  maxActive: 20
  # 配置获取连接等待超时的时间
  maxWait: 60000
  # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
  timeBetweenEvictionRunsMillis: 60000
  # 配置一个连接在池中最小生存的时间，单位是毫秒
  minEvictableIdleTimeMillis: 30000
  validationQuery: select 1
  testWhileIdle: true
  testOnBorrow: false
  testOnReturn: false
  # 打开PSCache，并且指定每个连接上PSCache的大小
  poolPreparedStatements: true
  maxPoolPreparedStatementPerConnectionSize: 20

3. 编写controller进行测试

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * 使用 DataSource 操作 Hive
 */
@RestController
public class HiveDataSourceController {

    private static final Logger logger = LoggerFactory.getLogger(HiveDataSourceController.class);

    @Autowired
    @Qualifier("hiveDruidDataSource")
    DataSource druidDataSource;

 	/**
     * 测试spring boot是否正常启动
     */
    @RequestMapping("/")
    public String hello(){
        return "hello world";
    }
    
    /**
     * 列举当前Hive库中的所有数据表
     */
    @RequestMapping("/table/list")
    public List<String> listAllTables() throws SQLException {
        List<String> list = new ArrayList<String>();
        Statement statement = druidDataSource.getConnection().createStatement();
        String sql = "show tables";
        logger.info("Running: " + sql);
        ResultSet res = statement.executeQuery(sql);
        while (res.next()) {
            list.add(res.getString(1));
        }
        return list;
    }

    /**
     * 查询指定tableName表中的数据
     */
    @RequestMapping("/table/select")
    public List<String> selectFromTable(String tableName) throws SQLException {
	    List<String> list = new ArrayList<String>();
        Statement statement = druidDataSource.getConnection().createStatement();
        String sql = "select * from " + tableName;
        logger.info("Running: " + sql);
        ResultSet res = statement.executeQuery(sql);
        int count = res.getMetaData().getColumnCount();
        String str = null;
        while (res.next()) {
            str = "";
            for (int i = 1; i < count; i++) {
                str += res.getString(i) + " ";
            }
            str += res.getString(count);
            logger.info(str);
            list.add(str);
        }
        return list;
    }

}

使用table/list实测可以连上

三、网页显示

接收提交的表单数据，并做一些处理后作为查询条件，将查询的结果放在model中，并在网页中显示

1. controller修改

想在网页上显示查询出来的数据的话，还需要修改一下上面的controller
将@RequestMapping("/table/select")改为下面的代码
封装一个Record类用来接收数据。

 /**
     * 查询指定tableName表中的数据，这是一个普通的controller
     */
  @PostMapping("/table/select")
    public String selectFromTable(@RequestParam(required = false) String name,
                                  Model model) throws SQLException {

        String tableName = "sale_statistics";
        List<Record> list = new ArrayList<>();
        Statement statement = druidDataSource.getConnection().createStatement();
        String sql = "";
       /*
       此处添加需查询的sql
       */
        logger.info("Running: " + sql);
        ResultSet res = statement.executeQuery(sql);
        int count = res.getMetaData().getColumnCount();
        while (res.next()) {
            Record record = new Record();
            /*
            此处将查询到的数据封装到javabean中，如
            record.setName(res.getString(1));
            下标从1开始
			*/
            logger.info(record.toString());
            list.add(record);
        }
        model.addAttribute("records",list);	//将结果放在model中
        return "result";					//返回result页面
    }

2. 网页编写

需要注意的就是使用thymeleaf的each标签，可以对数据进行遍历
（thymeleaf不支持高并发）

	<tr>
        <th>名字</th>
	</tr>
	<tr th:each="record : ${records}">
        <td th:text="${record.name}"></td>
	</tr>

四、一些问题

1. jar包冲突问题

表现为

Failed to start component [StandardEngine[Tomcat].StandardHost[localhost].TomcatEmbeddedContext[]]

或日志下面明确给你说了哪两个jar包冲突。
解决方法看上方依赖引入处

2. thymeleaf检查报错

表现为网页能正常显示查询的数据，但是下面代码的name处有红色波浪线。

<td th:text="${record.name}"></td>

并提示Validates unresolved references and invalid expressions
解决方法：网上都说是误报，关闭thymeleaf的自动检查就行了。我一开始报红，没管他，后面跑着跑着就没了，不知道为什么。

3. 日志jar包冲突

在idea中不影响使用，据说打成jar包可能会报错。
表现为

SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/C:/Users/.m2/repository/ch/qos/logback/logback-classic/1.2.3/logback-classic-1.2.3.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/C:/Users/.m2/repository/org/apache/logging/log4j/log4j-slf4j-impl/2.14.1/log4j-slf4j-impl-2.14.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/C:/Users/.m2/repository/org/slf4j/slf4j-log4j12/1.7.30/slf4j-log4j12-1.7.30.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [ch.qos.logback.classic.util.ContextSelectorStaticBinder]

实际上使用的是logback
其中一个是hive-jdbc里面zookeeper中的，上面依赖处已经去除
另一个疑似hive-jdbc里面元数据中的，上面依赖处已经去除

完整代码已上传码云

https://gitee.com/mao_com/spring-boot---hive.git