Java调用hiveServer2,用Java代码通过JDBC连接Hiveserver2

1.在终端启动hiveserver2

#hiveserver2

2.使用beeline连接hive

另外打开一个终端，输入如下命令(xavierdb必须是已经存在的数据库)

#beeline -u jdbc:hive2://localhost:10000/xavierdb -n hive -p hive

3.添加maven依赖

org.apache.hive

hive-jdbc

1.1.0

junit

junit

4.9

org.apache.hadoop

hadoop-common

2.6.0

org.apache.hadoop

hadoop-client

2.6.0

org.apache.hive

hive-metastore

1.1.0

org.apache.hive

hive-exec

1.1.0

maven依赖

出现过的错误： Error: Could not open client transport with JDBC Uri: jdbc:hive2://localhost:10000/default

解决办法：检查发现运行beeline时Driver版本Driver: Hive JDBC (version 1.1.0-cdh5.16.1)比maven依赖中的Driver版本低，将maven版本调至1.1.0问题解决

Java API测试：

注意：这里的url必须是beeline值中使用的url

packageTestOption;importorg.junit.Test;importorg.junit.After;importorg.junit.Before;import java.sql.*;/*** @Author:Xavier

* @Data:2019-02-18 11:43

**/

public classHiveOption {private static String driverName = "org.apache.hive.jdbc.HiveDriver";private static String url = "jdbc:hive2://172.19.224.213:10000/xavierdb";private static Connection con = null;private static Statement state = null;private static ResultSet res = null;//加载驱动,创建连接

@Beforepublic void init() throwsClassNotFoundException, SQLException {

Class.forName(driverName);

con= DriverManager.getConnection(url, "hive", "hive");

state=con.createStatement();

}//创建数据库

@Testpublic void CreateDb() throwsSQLException {

state.execute("create database xavierdb1");

}//查询所有数据库

@Testpublic void selectDb() throwsSQLException {

res= state.executeQuery("show databases");while(res.next()) {

System.out.println(res.getString(1));

}

}//删除数据库

@Testpublic void dropDb() throwsSQLException {

state.execute("drop database if exists xavierdb1");

}//创建表

@Testpublic void createTab() throwsSQLException {

state.execute("create table if not exists student ( " +

"name string , " +

"age int , " +

"agent string ," +

"adress struct) " +

"row format delimited " +

"fields terminated by ‘,‘ " +//字段与字段之间的分隔符

"collection items terminated by ‘:‘"+//一个字段各个item的分隔符

"lines terminated by ‘

‘ ");//行分隔符

}//查询所有表

@Testpublic void selectTab() throwsSQLException {

res=state.executeQuery("show tables");while(res.next()){

System.out.println(res.getString(1));

}

}//查看表结构

@Testpublic void descTab() throwsSQLException {

res=state.executeQuery("desc student");while(res.next()){

System.out.println(res.getString(1)+""+res.getString(2));

}

}//加载数据(本地加载)

@Testpublic void loadData() throwsSQLException {

String infile=" ‘/root/studentData‘ ";

state.execute("load data local inpath "+infile+"overwrite into table student");

}//查询数据

@Testpublic void selectTab() throwsSQLException {

res=state.executeQuery("select * from student");while(res.next()){

System.out.println(

res.getString(1)+"-"+res.getString(2)+"-"+res.getString(3)+"-"+res.getString(4));

}

}//统计查询(会运行mapreduce作业,资源开销较大)

@Testpublic void countData() throwsSQLException {

res=state.executeQuery("select count(1) from student");while(res.next()){

System.out.println(res.getInt(1));

}

}//删除表

@Testpublic void dropTab() throwsSQLException {

state.execute("drop table student1");

}

@Afterpublic void destory() throwsSQLException {if (res != null) state.close();if (state != null) state.close();if (con != null) con.close();

}

}