ieda 远程访问hive_idea 连接hiveI进行一些简单操作

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();

}

}

}