Hive从入门到精通7：Hive JDBC 客户端

本节来介绍Hive的JDBC客户端的操作。

1.准备工作

（1）下载Hive安装目录$HIVE_HOME/lib下的所有Jar包到本地目录如E:/hivelibs；

（2）打开Eclipse，新建工程HiveJDBCTest，将下载好的Hive Jar包加入到工程中Path中。

（3）修改服务器上的$HADOOP_HOME/etc/hadoop/core-site.xml文件，加入下面的配置：

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

（4）启动Hadoop：

[root@bigdata ~]# start-all.sh

（5）后台方式启动Hive远程服务：

[root@bigdata ~]# hive --service metastore &
[root@bigdata ~]# hive --service hiveserver &
[root@bigdata ~]# hive --service hiveserver2 & (建议使用这个)

注意：
（1）hiveserver和hiveserver2启动一个即可，建议启动hiveserver2。
（2）如果使用的是hiveserver，程序中驱动的名字写：org.apache.hadoop.hive.jdbc.HiveDriver，
数据库链接地址写：jdbc:hive://192.168.126.110:10000/default；
（3）如果使用的是hiveserver2，程序中驱动的名字写：org.apache.hive.jdbc.HiveDriver，
数据库链接地址写：jdbc:hive2://192.168.126.110:10000/default；
（4）如果不对应的话，执行程序时会报错：

Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class

2.Hive实战案例：使用JDBC方式操作Hive

（1）创建Hive JDBC客户端类：HiveJDBCClient.java

package demo.jdbc;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveJDBCClient {

private String driverName = null;
private String url = null;
private String user = null;
private String password = null;
private static Connection connection = null;
private static Statement statement = null;
private static ResultSet resultSet = null;

public boolean initClient(String driverName,String url,String user,String password) {
this.driverName = driverName;
try {
Class.forName(this.driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
return false;
}
this.url = url;
this.user = user;
this.password = password;
return true;
}

public boolean getConnection() {
try {
connection = DriverManager.getConnection(url, user, password);
if(connection == null) {
return false;
}
} catch (SQLException e) {
e.printStackTrace();
return false;
}
try {
statement = connection.createStatement();
if(statement == null) {
return false;
}
} catch (SQLException e) {
e.printStackTrace();
return false;
}
return true;
}

public void releaseConnection() {
if(resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
resultSet = null;
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

public void showTables() {
String sql = "show tables";
try {
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println(resultSet.getString(1));
}
} catch (SQLException e) {
e.printStackTrace();
}
}

public boolean isExist(String tableName) {
try {
resultSet = connection.getMetaData().getTables(null, null, tableName, null);
if (resultSet.next()) {
return true;
} else {
return false;
}
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}

public void createTable(String tableName, String sql) {
if (!isExist(tableName)) {
try {
statement.execute(sql);
System.out.println("Create table " + tableName + " successfully");
} catch (SQLException e) {
e.printStackTrace();
}
} else {
System.out.println(tableName + " has existed");
}
}

public void insert(String tableName, String sql) {

if (isExist(tableName)) {
try {
statement.execute(sql);
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("Insert data successfully");
} else {
System.out.println(tableName + " does not exist");
}
}

public void truncateTable(String tableName) {

if (isExist(tableName)) {
String sql = "truncate table " + tableName;
try {
statement.execute(sql);
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("Truncate table successfully");
} else {
System.out.println(tableName + " does not exist");
}
}

public void dropTable(String tableName) {

if (isExist(tableName)) {
String sql = "drop table " + tableName;
try {
statement.execute(sql);
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("Drop table " + tableName + " successfully");
} else {
System.out.println(tableName + " does not exist");
}
}

public void descTable(String tableName) {
if (isExist(tableName)) {
String sql = "describe " + tableName;
try {
resultSet = statement.executeQuery(sql);
System.out.println("col_name\tdata_type");
while (resultSet.next()) {
System.out.println(resultSet.getString(1) + "\t" + resultSet.getString(2));
}
} catch (SQLException e) {
e.printStackTrace();
}
} else {
System.out.println(tableName + " does not exist");
}
}
public void select(String tableName, String sql, int columnCount) {
if (isExist(tableName)) {
try {
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String res = "";
for(int i=1; i<=columnCount; i++) {
res += resultSet.getString(i) + "\t";
}
System.out.println(res);
}
} catch (SQLException e) {
e.printStackTrace();
}
} else {
System.out.println(tableName + " does not exist");
}
}
}

（2）创建测试类：TestMain.java

package demo.jdbc;
public class TestMain {
public static void main(String[] args) {
//定义Hive连接信息
String driverName = "org.apache.hive.jdbc.HiveDriver";
String url = "jdbc:hive2://192.168.126.110:10000/default";
String user = "root";
String password = "123456";

//创建HiveJDBC客户端 
HiveJDBCClient client = new HiveJDBCClient();
if(!client.initClient(driverName, url, user, password)) {
System.err.println("Failed to initialize Hive JDBC Client !!");
}

//将HiveJDBC客户端连接到Hive服务器
if(!client.getConnection()) {
System.err.println("Cannot connect to Hive Server !!");
}

//定义HQL语句
String table1 = "emp";
String table2 = "emp_in";
String createSQL = "create table emp(empno int, ename string, job string, mgr int, "+ 
"hiredate string, sal int, comm int, deptno int)";
String selectSQL = "select * from emp_in where deptno != 30";
String insertSQL = "insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) "
+ "select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp_in "
+ "where deptno != 20";

//test_1：查看Hive中有哪些表
client.showTables();

//test_2：判断某张表是否存在
//if(!client.isExist(table1)) {
// System.out.println("Table " + table1 + " does not exist !!");
//}else {
// System.out.println("Table " + table1 + " exists !");
//}

//test_3：创建一张表
//client.createTable(table1, createSQL);

//test_4：查看表结构
//client.descTable(table1);
//client.descTable(table2);

//test_5：查看表数据
//client.select(table2, selectSQL, 8);

//test_6：将emp_in中的数据插入到emp中
//client.insert(table1, insertSQL);
//client.select(table1, "select * from emp", 8);

//test_7：清空表
//client.truncateTable(table1);

//test_8：删除表
//client.dropTable(table1);

//释放连接
client.releaseConnection();
}
}