importorg.junit.After;importorg.junit.Before;importorg.junit.Test;import java.sql.*;/*** JDBC 操作 Hive(注：JDBC 访问 Hive 前需要先启动HiveServer2)*/
public classHiveJDBC {private static String driverName = "org.apache.hive.jdbc.HiveDriver";private static String url = "jdbc:hive2://master:10000/default";private static String user = "root";private static String password = "123456";private static Connection conn = null;private static Statement stmt = null;private static ResultSet rs = null;//加载驱动、创建连接
@Beforepublic void init() throwsException {
Class.forName(driverName);
conn=DriverManager.getConnection(url,user,password);
stmt=conn.createStatement();
}//创建数据库
@Testpublic void createDatabase() throwsException {
String sql= "create database hive_jdbc_test";
System.out.println("Running: " +sql);
stmt.execute(sql);
}//查询所有数据库
@Testpublic void showDatabases() throwsException {
String sql= "show databases";
System.out.println("Running: " +sql);
rs=stmt.executeQuery(sql);while(rs.next()) {
System.out.println(rs.getString(1));
}
}//创建表
@Testpublic void createTable() throwsException {
String sql= "create table emp(\n" +
"empno int,\n" +
"ename string,\n" +
"job string,\n" +
"mgr int,\n" +
"hiredate string,\n" +
"sal double,\n" +
"comm double,\n" +
"deptno int\n" +
")\n" +
"row format delimited fields terminated by '\\t'";
System.out.println("Running: " +sql);
stmt.execute(sql);
}//查询所有表
@Testpublic void showTables() throwsException {
String sql= "show tables";
System.out.println("Running: " +sql);
rs=stmt.executeQuery(sql);while(rs.next()) {
System.out.println(rs.getString(1));
}
}//查看表结构
@Testpublic void descTable() throwsException {
String sql= "desc emp";
System.out.println("Running: " +sql);
rs=stmt.executeQuery(sql);while(rs.next()) {
System.out.println(rs.getString(1) + "\t" + rs.getString(2));
}
}//加载数据
@Testpublic void loadData() throwsException {
String filePath= "/home/hadoop/data/emp.txt";
String sql= "load data local inpath '" + filePath + "' overwrite into table emp";
System.out.println("Running: " +sql);
stmt.execute(sql);
}//查询数据
@Testpublic void selectData() throwsException {
String sql= "select * from emp";
System.out.println("Running: " +sql);
rs=stmt.executeQuery(sql);
System.out.println("员工编号" + "\t" + "员工姓名" + "\t" + "工作岗位");while(rs.next()) {
System.out.println(rs.getString("empno") + "\t\t" + rs.getString("ename") + "\t\t" + rs.getString("job"));
}
}//统计查询(会运行mapreduce作业)
@Testpublic void countData() throwsException {
String sql= "select count(1) from emp";
System.out.println("Running: " +sql);
rs=stmt.executeQuery(sql);while(rs.next()) {
System.out.println(rs.getInt(1) );
}
}//删除数据库
@Testpublic void dropDatabase() throwsException {
String sql= "drop database if exists hive_jdbc_test";
System.out.println("Running: " +sql);
stmt.execute(sql);
}//删除数据库表
@Testpublic void deopTable() throwsException {
String sql= "drop table if exists emp";
System.out.println("Running: " +sql);
stmt.execute(sql);
}//释放资源
@Afterpublic void destory() throwsException {if ( rs != null) {
rs.close();
}if (stmt != null) {
stmt.close();
}if (conn != null) {
conn.close();
}
}
}