java hive查询_Java操作Hive

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

}

}

}