packagecom.zhi.test.hive;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;importorg.apache.logging.log4j.LogManager;importorg.apache.logging.log4j.Logger;importorg.junit.jupiter.api.AfterAll;importorg.junit.jupiter.api.BeforeAll;importorg.junit.jupiter.api.MethodOrderer;importorg.junit.jupiter.api.Order;importorg.junit.jupiter.api.Test;importorg.junit.jupiter.api.TestInstance;importorg.junit.jupiter.api.TestMethodOrder;importorg.junit.jupiter.api.TestInstance.Lifecycle;/*** 使用JDBC进行Hive操作
*
*@authorzhi
*@since2019年9月11日11:55:00
**/@TestInstance(Lifecycle.PER_CLASS)
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)public classHiveTest {private final Logger logger = LogManager.getLogger(this.getClass());private static Connection connection = null;
@BeforeAllpublic void init() throwsException {try{
Class.forName("org.apache.hive.jdbc.HiveDriver");
connection= DriverManager.getConnection("jdbc:hive2://192.168.107.143:10000/test_db", "root", "abc123");
}catch (SQLException |ClassNotFoundException e) {
logger.error("创建Hive连接失败", e);throwe;
}
}
@AfterAllpublic void destory() throwsException {if (connection != null) {
connection.close();
}
}/*** 创建数据库*/@Order(1)
@Testpublic voidcreateDatabase() {
String sql= "create database test_db";
logger.info("创建数据库,脚本:{}", sql);try (Statement statement =connection.createStatement()) {
statement.execute(sql);
logger.info("创建数据库成功");
}catch(SQLException e) {
logger.error("创建数据库出错", e);
}
}/*** 查询数据库*/@Order(2)
@Testpublic voidshowDatabases() {
String sql= "show databases";
logger.info("查询数据库,脚本:{}", sql);try (Statement statement = connection.createStatement(); ResultSet rs =statement.executeQuery(sql)) {while(rs.next()) {
logger.info("查询到数据库:{}", rs.getString(1));
}
}catch(SQLException e) {
logger.error("创建数据库出错", e);
}
}/*** 创建表*/@Order(3)
@Testpublic voidcreateTable() {
String sql= "create table user_tb(id int, name string) row format delimited fields terminated by ','";
logger.info("创建表,脚本:{}", sql);try (Statement statement =connection.createStatement()) {
statement.execute(sql);
logger.info("创建表成功");
}catch(SQLException e) {
logger.error("创建表出错", e);
}
}/*** 查询所有表*/@Order(3)
@Testpublic voidshowTables() {
String sql= "show tables";
logger.info("查询所有表,脚本:{}", sql);try (Statement statement = connection.createStatement(); ResultSet rs =statement.executeQuery(sql)) {while(rs.next()) {
logger.info("查询到表:{}", rs.getString(1));
}
}catch(SQLException e) {
logger.error("查询所有表出错", e);
}
}/*** 查看表结构*/@Order(4)
@Testpublic voiddescTable() {
String sql= "desc user_tb";
logger.info("查看表结构,脚本:{}", sql);try (Statement statement = connection.createStatement(); ResultSet rs =statement.executeQuery(sql)) {while(rs.next()) {
logger.info("字段名:{},类型:{}", rs.getString(1), rs.getString(2));
}
}catch(SQLException e) {
logger.error("查看表结构出错", e);
}
}/*** 导入数据,data.txt中的数据为格式为:
* 1,张三
* 2,李四*/@Order(5)
@Testpublic voidloadData() {
String sql= "load data local inpath '/home/data.txt' overwrite into table user_tb";
logger.info("导入数据,脚本:{}", sql);try (Statement statement =connection.createStatement()) {
statement.execute(sql);
logger.info("导入数据成功");
}catch(SQLException e) {
logger.error("导入数据出错", e);
}
}/*** 查询数据*/@Order(6)
@Testpublic voidselectData() {
String sql= "select * from user_tb";
logger.info("查询数据,脚本:{}", sql);try (Statement statement = connection.createStatement(); ResultSet rs =statement.executeQuery(sql)) {while(rs.next()) {
logger.info("id={},name={}", rs.getInt("id"), rs.getString("name"));
}
}catch(SQLException e) {
logger.error("查询数据出错", e);
}
}/*** 查数量*/@Order(7)
@Testpublic voidcount() {
String sql= "select count(1) from user_tb";
logger.info("查数量,脚本:{}", sql);try (Statement statement = connection.createStatement(); ResultSet rs =statement.executeQuery(sql)) {while(rs.next()) {
logger.info("数量={}", rs.getInt(1));
}
}catch(SQLException e) {
logger.error("查数量出错", e);
}
}/*** 删除表*/@Order(8)
@Testpublic voiddeopTable() {
String sql= "drop table if exists user_tb";
logger.info("删除表,脚本:{}", sql);try (Statement statement =connection.createStatement()) {
statement.execute(sql);
logger.info("删除表成功");
}catch(SQLException e) {
logger.error("删除表出错", e);
}
}/*** 删除数据库*/@Order(9)
@Testpublic voiddropDatabase() {
String sql= "drop database if exists test_db";
logger.info("删除数据库,脚本:{}", sql);try (Statement statement =connection.createStatement()) {
statement.execute(sql);
logger.info("删除数据库成功");
}catch(SQLException e) {
logger.error("删除数据库出错", e);
}
}
}