前提条件
Linux下安装好Hadoop2.7.3、Hive2.3.6
步骤
(1) 修改hadoop的core-site.xml添加如下配置
hadoop.proxyuser.hadoop.hosts * hadoop.proxyuser.hadoop.groups *
注意：
配置中hadoop.proxyuser.<用户名>.hosts 和 hadoop.proxyuser.<用户名>.groups 的用户名要改为Linux的用户名，例如Linux的用户名为hadoop就把<用户名>改为hadoop
(2) 启动hadoop
start-all.sh
(3) 启动hiveserver2服务
hiveserver2
此时当前终端进入阻塞状态，不要关闭它。可以另外启动一个终端，jps查看进程，看到RunJar进程。
(4) 在新的终端下，使用beeline通过jdbc连接hiveserver2
beeline -u jdbc:hive2://node1:10000/defaul -n hadoop -p
-u 为hive的url地址
-n 为hive所在Linux机器当前登录的用户名
-p 为Linux机器的登录密码
运行命令，根据提示输入密码后，看到如下箭头所示，为连接成功
(5) 编码
新建maven工程，工程名例如：hive-api
添加pom.xml依赖
org.apache.hadoop hadoop-common2.7.3 org.apache.hive hive-jdbc2.3.6 junit junit4.13.2 test
编码，在srctestjavaorg.example包下创建
HiveJDBCTest.java
代码如下：
package org.example;
import org.example.api.JDBCUtils;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.sql.*;
public class HiveJDBCTest {
private static String driverName="org.apache.hive.jdbc.HiveDriver";//驱动名称
private static String url = "jdbc:hive2://node1:10000/default";//连接hive的default数据库
private static String user = "hadoop";//hive所在机器的用户名
private static String password = "hadoop";//hive所在机器的密码
private static Connection conn = null;//连接
private static Statement stmt = null;//声明
private static ResultSet rs = null;//结果集
@Before
public void init() throws ClassNotFoundException, SQLException {
Class.forName(driverName);
conn = DriverManager.getConnection(url, user, password);
stmt = conn.createStatement();
}
@Test
public void createDatabase() throws SQLException {
String sql = "create database testdb1912";
System.out.println("Running: " + sql);
stmt.execute(sql);
}
@Test
public void showDatabases() throws SQLException {
String sql = "show databases";
System.out.println("Running: " + sql);
rs = stmt.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getString(1));//从1开始，查看第一列
// System.out.println("========");
// System.out.println(rs.getString(2));//没有第二列会报错Invalid columnIndex: 2
}
}
@Test
public void createTable() throws SQLException {
String sql = "create table dept_api1(deptno int, dname string, loc string)" +
"row format delimited fields terminated by ','";
System.out.println("Running:"+sql);
stmt.execute(sql);
}
@Test
public void showTables() throws SQLException {
String sql = "show tables";
System.out.println("Running:"+sql);
rs = stmt.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getString(1));
}
}
@Test
public void descTable() throws SQLException {
String sql = "desc dept_api";
System.out.println("Running:"+sql);
rs = stmt.executeQuery(sql);
while (rs.next()){
// 表结构： 列名 列类型
System.out.println(rs.getString(1)+"t"+rs.getString(2));
}
}
@Test
public void loadData() throws SQLException {
// String filePath = "D:/test/inputemp/dept.csv";
// String sql = "load data local inpath 'D:/test/inputemp/dept.csv' overwrite into table dept_api";
// String sql = "LOAD DATA LOCAL INPATH 'D:\test\inputemp\dept.csv' " + "OVERWRITE INTO TABLE dept_api";
// 注意：这里相当于连接了hiveserver2的客户端，hiverserver2在linux上
// 路径为Linux的本地文件，用windows路径会报错
String sql = "LOAD DATA LOCAL INPATH '/home/hadoop/dept.csv' " + "OVERWRITE INTO TABLE dept_api1";
System.out.println("Running:"+sql);
stmt.execute(sql);
}
@Test
public void countData() throws SQLException {
String sql = "select count(1) from dept_api1";
System.out.println("Running:"+sql);
rs = stmt.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getInt(1));
}
}
@Test
public void dropDB() throws SQLException {
String sql = "drop database if exists testdb1";
System.out.println("Running:"+sql);
stmt.execute(sql);
}
@Test
public void deleteTable() throws SQLException {
String sql = "drop table if exists dept_api1";
System.out.println("Running:"+sql);
stmt.execute(sql);
}
@After
public void destory() throws SQLException {
if(rs !=null){
rs.close();
}
if(stmt != null){
stmt.close();
}
if(conn !=null){
conn.close();
}
}
}
分别进行单元测试。
更多代码：
JDBCUtils.java
package org.example.api;
import java.sql.*;
public class JDBCUtils {
// Hive的驱动
private static String driver = "org.apache.hive.jdbc.HiveDriver";
// Hive的URL地址
// private static String url = "jdbc:hive2://192.168.193.140:10000/default";
private static String url = "jdbc:hive2://node1:10000/default";
// 注册数据库的驱动
static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 获取数据库Hive的链接
public static Connection getConnection(){
try {
return DriverManager.getConnection(url,"hadoop","hadoop");
// return DriverManager.getConnection(url);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
// 释放资源
public static void release(Connection conn, Statement st, ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
rs = null;
}
}
if(st != null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
st = null;
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
conn = null;
}
}
}
}
HiveDemo.java
package org.example.api;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class HiveDemo {
public static void main(String[] args) {
// String sql = "select * from emp1";
String sql = "create database testdb1";
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
st = conn.createStatement();
rs = st.executeQuery(sql);
while(rs.next()){
String ename = rs.getString("ename");
double sal = rs.getDouble("sal");
System.out.println(ename + "t" + sal);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.release(conn,st,rs);
}
}
}
完成！enjoy it!
