大数据小试牛刀---------（一）配置Hiveserver2，并编写Spring boot集成hive-jdbc程序（结尾附源码）

本文链接：https://blog.csdn.net/qq_32969281/article/details/102880173

hiveserver2的配置和启动

在之前的讲解中，我们已经安装好啦Hive，那么这次我们来配置好hiveserver2进行实际项目中操作Hive吧。
Hive安装地址：Hive 安装教程

配置hive-site.xml
进入hive安装目录下的conf文件夹,更改hive-site.xml配置文件，现对其进行配置修改。

        <property>
            <name>javax.jdo.option.ConnectionURL</name>
            <value>jdbc:mysql://192.168.30.101:3306/hive_new</value>
        </property>
        <property>
                <name>javax.jdo.option.ConnectionDriverName</name>
                <value>com.mysql.jdbc.Driver</value>
        </property>
        <property>
                <name>javax.jdo.option.ConnectionUserName</name>
                <value>root</value>
        </property>
        <property>
                <name>javax.jdo.option.ConnectionPassword</name>
                <value>root</value>
        </property>

        <property>
                <name>hive.metastore.schema.verification</name>
                <value>false</value>
        </property>
		<property> 
   		 		<name>hive.cli.print.current.db</name>
		 		<value>true</value>
		</property>
		<property> 
	         <name>hive.cli.print.header</name>
	         <value>true</value>
		</property>
	<!-- 这是hiveserver2 -->
	<property>
       		 <name>hive.server2.thrift.port</name>
     		 <value>10000</value>
	</property>
    <property>
       		<name>hive.server2.thrift.bind.host</name>
       		<value>192.168.30.101</value>
    </property>

配置hadoop中的core-site.xml文件
进入你安装的hadoop路径下找到我们之前配置的core-site.xml文件。对其配置进行修改。这里特别说明hadoop.proxyuser.root.hosts，这个属性中的root是操作你Hadoop的用户，如果你不是root，请改成你的用户，这个我配置的时候坑了我好久😭

<property>     
	<name>hadoop.proxyuser.root.hosts</name>     
	<value>*</value>
 </property> 
<property>     
	<name>hadoop.proxyuser.root.groups</name>    
    <value>*</value> 
</property>
<property>     
	<name>hadoop.proxyuser.root.hosts</name>     
	<value>*</value> 
</property> 
<property>     
	<name>hadoop.proxyuser.root.groups</name>     
	<value>*</value> 
</property>

测试
在配置好上面两个文件后，我们就可以启动hiveserver2了。如果你配置了环境变量，启动命令：

[root@master ~]# hiveserver2
#或者
[root@master ~]# hive --service hiveserver2

Spring boot集成hive-jdbc程序

SpringBoot是伴随着Spring 4.0而生的，boot是引导的意思，也就是它的作用其实就是在于帮助开发者快速的搭建Spring框架，因此SpringBoot继承了Spring优秀的基因，在Spring中开发更为方便快捷。所以我们就快速搭建一个Spring boot和Hive的程序吧。

我们使用Maven来创建项目，首先添加依赖，修改pom.xml。

<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<java.version>1.8</java.version>
		<zookeeper.version>3.4.8</zookeeper.version>
		<curator.version>2.11.1</curator.version>
		<spring-data-hadoop.version>2.4.0.RELEASE</spring-data-hadoop.version>
		<hive.version>1.2.1</hive.version>
		<druid.version>1.0.28</druid.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>cn.myframe</groupId>
			<artifactId>open-bigdata-hadoop</artifactId>
			<version>1.0.0</version>
		</dependency>
		<!--hive-->
		<dependency>
			<groupId>org.apache.hive</groupId>
			<artifactId>hive-jdbc</artifactId>
			<version>${hive.version}</version>
			<exclusions>
				<exclusion>
					<groupId>org.eclipse.jetty.aggregate</groupId>
					<artifactId>*</artifactId>
				</exclusion>
				<exclusion>
					<groupId>org.slf4j</groupId>
					<artifactId>*</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>org.springframework.data</groupId>
			<artifactId>spring-data-hadoop</artifactId>
			<version>${spring-data-hadoop.version}</version>
			<exclusions>
				<exclusion>
					<groupId>javax.servlet</groupId>
					<artifactId>*</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.1.1</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.apache.tomcat</groupId>
			<artifactId>tomcat-jdbc</artifactId>
		</dependency>
	</dependencies>

修改application.yml配置文件

server:
  port: 8001
hive:
  url: jdbc:hive2://192.168.30.101:10000
  driver-class-name: org.apache.hive.jdbc.HiveDriver
  type: com.alibaba.druid.pool.DruidDataSource
  user: root
  password: root
  # 下面为连接池的补充设置，应用到上面所有数据源中
  # 初始化大小，最小，最大
  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
hadoop.name-node: hdfs://192.168.30.101:8020
hadoop.namespace: /JPFee

配置Druid数据源，主要配置内容如下。

@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;
	}
	// 此处省略各个属性的get和set方法
	@Bean(name = "hiveDruidTemplate")
	public JdbcTemplate hiveDruidTemplate(@Qualifier("hiveDruidDataSource") DataSource dataSource) {
		return new JdbcTemplate(dataSource);
	}
}

Hive下增删改查操作

/**
	 * 示例：创建新表
	 */
	@RequestMapping("/table/create")
	public String createTable() {
		StringBuffer sql = new StringBuffer("CREATE TABLE IF NOT EXISTS ");
		sql.append("user_sample");
		sql.append("(user_num BIGINT, user_name STRING, user_gender STRING, user_age INT)");
		sql.append("ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' "); // 定义分隔符
		sql.append("STORED AS TEXTFILE"); // 作为文本存储
 
		logger.info("Running: " + sql);
		String result = "Create table successfully...";
		try {
			// hiveJdbcTemplate.execute(sql.toString());
			hiveDruidTemplate.execute(sql.toString());
		} catch (DataAccessException dae) {
			result = "Create table encounter an error: " + dae.getMessage();
			logger.error(result);
		}
		return result;
 
	}
 
	/**
	 * 示例：将Hive服务器本地文档中的数据加载到Hive表中
	 */
	@RequestMapping("/table/load")
	public String loadIntoTable() {
		String filepath = "/home/hadoop/user_sample.txt";
		String sql = "load data local inpath '" + filepath + "' into table user_sample";
		String result = "Load data into table successfully...";
		try {
			// hiveJdbcTemplate.execute(sql);
			hiveDruidTemplate.execute(sql);
		} catch (DataAccessException dae) {
			result = "Load data into table encounter an error: " + dae.getMessage();
			logger.error(result);
		}
		return result;
	}
 
	/**
	 * 示例：向Hive表中添加数据
	 */
	@RequestMapping("/table/insert")
	public String insertIntoTable() {
		String sql = "INSERT INTO TABLE  user_sample(user_num,user_name,user_gender,user_age) VALUES(888,'Plum','M',32)";
		String result = "Insert into table successfully...";
		try {
			// hiveJdbcTemplate.execute(sql);
			hiveDruidTemplate.execute(sql);
		} catch (DataAccessException dae) {
			result = "Insert into table encounter an error: " + dae.getMessage();
			logger.error(result);
		}
		return result;
	}
 
	/**
	 * 示例：删除表
	 */
	@RequestMapping("/table/delete")
	public String delete(String tableName) {
		String sql = "DROP TABLE IF EXISTS "+tableName;
		String result = "Drop table successfully...";
		logger.info("Running: " + sql);
		try {
			// hiveJdbcTemplate.execute(sql);
			hiveDruidTemplate.execute(sql);
		} catch (DataAccessException dae) {
			result = "Drop table encounter an error: " + dae.getMessage();
			logger.error(result);
		}
		return result;
	}

在这里给大家推荐一个大牛整理的Spring boot的合集，里面非常的全。集成了rabbitmq,zookeeper,dubbo,activemq,mongodb,springcloud,netty4,elasticsearch,solr，fastdfs,hadoop,hive,hbase,spark等等。
地址：代码地址
喜欢的请给个关注哦！
在这里插入图片描述