springboot连接hive数据库

springboot后台开发连接hive数据库

确保集群上hivesever2的服务已启动

hive数据库一般在10000端口

springboot开发

• pox.xml 文件

<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.2.0.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.education</groupId> <artifactId>edu_server</artifactId> <version>0.0.1-SNAPSHOT</version> <name>edu_server</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> <version>2.1.2.RELEASE</version> <exclusions> <exclusion> <groupId>org.apache.logging.log4j</groupId> <artifactId>log4j-slf4j-impl</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> <version>2.1.2.RELEASE</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.10</version> </dependency> <dependency> <groupId>org.springframework.data</groupId> <artifactId>spring-data-hadoop</artifactId> <version>2.5.0.RELEASE</version> <exclusions> <exclusion> <groupId>jdk.tools</groupId> <artifactId>jdk.tools</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.apache.hive</groupId> <artifactId>hive-jdbc</artifactId> <version>1.1.0</version> <exclusions> <exclusion> <groupId>org.eclipse.jetty.aggregate</groupId> <artifactId>*</artifactId> </exclusion> <exclusion> <groupId>jdk.tools</groupId> <artifactId>jdk.tools</artifactId> </exclusion> <exclusion> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> </exclusion> <exclusion> <groupId>org.apache.hive</groupId> <artifactId>hive-shims</artifactId> </exclusion> <exclusion> <artifactId>jasper-compiler</artifactId> <groupId>tomcat</groupId> </exclusion> <exclusion> <artifactId>jasper-runtime</artifactId> <groupId>tomcat</groupId> </exclusion> <exclusion> <artifactId>servlet-api</artifactId> <groupId>javax.servlet</groupId> </exclusion> <exclusion> <artifactId>log4j-slf4j-impl</artifactId> <groupId>org.apache.logging.log4j</groupId> </exclusion> <exclusion> <artifactId>slf4j-log4j12</artifactId> <groupId>org.slf4j</groupId> </exclusion> <exclusion> <groupId>tomcat</groupId> <artifactId>*</artifactId> </exclusion> <exclusion> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> </exclusion> <exclusion> <groupId>org.eclipse.jetty.orbit</groupId> <artifactId>*</artifactId> </exclusion> <exclusion> <groupId>javax.servlet</groupId> <artifactId>servlet-api</artifactId> </exclusion> <exclusion> <groupId>org.mortbay.jetty</groupId> <artifactId>*</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.apache.tomcat</groupId> <artifactId>tomcat-jdbc</artifactId> <version>9.0.11</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.4</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <optional>true</optional> <scope>runtime</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project> 

• application.yml文件

server: port: 18891 hive: url: jdbc:hive2://10.100.XX.XXX:10000/default #hive默认数据库 driver-class-name: org.apache.hive.jdbc.HiveDriver type: com.alibaba.druid.pool.DruidDataSource user: a password: b initialSize: 1 minIdle: 3 maxActive: 20 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 30000 validationQuery: select 1 testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true maxPoolPreparedStatementPerConnectionSize: 20 

• hive配置

package com.education.edu_server.config; import org.apache.tomcat.jdbc.pool.DataSource; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.jdbc.core.JdbcTemplate; @Configuration public class HiveConfig { 
    @Value("${hive.url}") private String url; @Value("${hive.driver-class-name}") private String driver; @Value("${hive.user}") private String user; @Value("${hive.password}") private String password; @Bean public DataSource dataSource(){ 
    DataSource dataSource = new DataSource(); dataSource.setUrl(url); dataSource.setDriverClassName(driver); dataSource.setUsername(user); dataSource.setPassword(password); return dataSource; } @Bean public JdbcTemplate jdbcTemplate(DataSource dataSource){ 
    return new JdbcTemplate(dataSource); } } 

• controller

package com.education.edu_server.controller; import com.alibaba.fastjson.JSON; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; import java.util.Map; @RestController @RequestMapping("/index") public class IndexController { 
    @Autowired @Qualifier("jdbcTemplate") private JdbcTemplate jdbcTemplate; @GetMapping("/list") public String list() { 
    String sql = "select app,pref from game_pref order by pref desc"; List<Map<String, Object>> list = jdbcTemplate.queryForList(sql); return JSON.toJSONString(list); } } 

注意事项（坑）

1. Error: Could not open client transport with JDBC Uri:
   hive地址不对，询问管理hive集群的人解决
   
   
2. Required field ‘client_protocol’ is unset!
   jdbc-hive依赖的版本（2.3.3）与hive的版本（1.1.0）不匹配，将jdbc-hive依赖版本改为1.1.0