Springboot+druid+hive-jdbc 整合实例
2018-12-19 09:18 RingWu 阅读(7751) 评论(4) 编辑 收藏 举报简单的hive-jdbc整合实例,整合完成后,可以通过sql 对hive仓库的表、数据、角色等进行操作。
完整代码已上传至github https://github.com/RingWu2012/HiveJdbcDemo
一、添加依赖
pom文件加入以下依赖:
<properties> <java.version>1.8</java.version> <hive.version>1.1.0</hive.version> <hadoop.version>2.6.0</hadoop.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-tomcat</artifactId> <scope>provided</scope> </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.1.10</version> </dependency> <dependency> <groupId>org.apache.hive</groupId> <artifactId>hive-jdbc</artifactId> <version>${hive.version}</version> </dependency> <dependency> <groupId>org.apache.hadoop</groupId> <artifactId>hadoop-common</artifactId> <version>${hadoop.version}</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> </dependencies>
二、修改yml配置文件
在yml配置文件中加入以下配置
hive:
url: #hiveserver2的url
driver-class-name: org.apache.hive.jdbc.HiveDriver
type: com.alibaba.druid.pool.DruidDataSource
user: #用户名
password: #密码
initialSize: 1
minIdle: 3
maxActive: 20
maxWait: 6000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 30000
validationQuery: select 1
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
connectionErrorRetryAttempts: 0
breakAfterAcquireFailure: true
三、通过bean对druid、hive-jdbc进行配置
新建一个HiveDruidConfig类
package com.example.demo.config; import com.alibaba.druid.pool.DruidDataSource; 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 javax.sql.DataSource; @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; private int connectionErrorRetryAttempts; private boolean breakAfterAcquireFailure; @Bean(name = "hiveDruidDataSource") @Qualifier("hiveDruidDataSource") public DataSource dataSource() { DruidDataSource datasource = new DruidDataSource(); datasource.setUrl(url); datasource.setUsername(user); datasource.setPassword(password); datasource.setDriverClassName(driverClassName); 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); datasource.setConnectionErrorRetryAttempts(connectionErrorRetryAttempts); datasource.setBreakAfterAcquireFailure(breakAfterAcquireFailure); return datasource; } @Bean(name = "hiveDruidTemplate") public JdbcTemplate hiveDruidTemplate(@Qualifier("hiveDruidDataSource") DataSource dataSource) { return new JdbcTemplate(dataSource); } //省略getter、setter }
四、使用DruidDataSource或JdbcTemplate对hive仓库进行操作
在相关的bean里注入datasource、jdbcTemplate进行使用。
package com.example.demo.controller; 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.jdbc.core.JdbcTemplate; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.sql.DataSource; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; @RestController public class IndexController { public static final Logger logger = LoggerFactory.getLogger(IndexController.class); @Autowired @Qualifier("hiveDruidDataSource") private DataSource druidDataSource; @Autowired @Qualifier("hiveDruidTemplate") private JdbcTemplate jdbcTemplate; @RequestMapping("/table/show") public List<String> showtables() { List<String> list = new ArrayList<String>(); Statement statement = null; try { 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)); } } catch (SQLException e) { e.printStackTrace(); } return list; } }
如不能运行或有疑问请留言。