JDBC操作Hive
一、准备
1、数据文件下载city_info.txt 提码1111
2、将数据文件上传到虚拟机
3、在HIVE_HOME/bin目录下启动hive
./hive
4、在hive中创建进数据表city_info
①操作default数据库:
use default;
②建表:
CREATE TABLE `city_info`(
`city_id` bigint,
`city_name` string,
`area` string)
row format delimited fields terminated by '\t';
5、将数据加载进city_info表中
load data local inpath '数据文件路径' into table city_info;
我这里是/opt/module/data/city_info.txt
二、启动hadoop集群
hive操作的数据文件存在hdfs上,所以需要启动hadoop
start-dfs.sh
三、在hive的安装目录下的bin/目录下启动hiveserver2
./hive2server2
四、两种使用JDBC的方式
一、在虚拟机中的hive的beeline端
在启动HIVE_HOME/bin/目录下启动beeline
启动命令:
./beeline
然后在beeline下使用JDBC连接mysql:
!connect jdbc:hive2://主机名(安装有mysql的主机)/ip地址:hive的端口号(默认10000)
我这里是
!connect jdbc:hive2://ethan002:10000
操作mysql如下图所示:
二、Windows本地idea集成开发环境
1、创建maven项目并添加依赖到pom.xml文件
依赖:
<dependencies>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j</artifactId>
<version>2.8.2</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>2.7.3</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-client</artifactId>
<version>2.7.3</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-hdfs</artifactId>
<version>2.7.3</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>1.2.1</version>
</dependency>
</dependencies>
2、程序代码编写:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class HiveJdbc {
public static void main(String[] args) throws Exception {
//加载驱动
Class.forName("org.apache.hive.jdbc.HiveDriver");
//创建连接
Connection connection =DriverManager.getConnection("jdbc:hive2://ethan002:10000","ethan","123456");
//准备sql
String sql = "select * from default.city_info";
//预编译sql
PreparedStatement ps = connection.prepareStatement(sql);
//执行sql
ResultSet resultSet = ps.executeQuery();
//迭代表中的内容
while (resultSet.next()){
System.out.println("city_id:"+resultSet.getInt("city_id")+
"\tcity_name:"+resultSet.getString("city_name")+
"\tarea"+resultSet.getString("area"));
}
}
}
3、执行程序
在IDEA中执行JDBC操作hive的程序
结果如下图所示: