package com.hqgf.hive;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
public class HiveJDBC {
private static String driverName = "org.apache.hive.jdbc.HiveDriver";
private static String url = "jdbc:hive2://172.69.1.221:10000/hqgf";
private static String user = "hive";
private static String password = "";
private static Connection conn = null;
private static Statement stmt = null;
private static ResultSet rs = null;
public static void main(String[] args) throws Exception {
init();
showDatabases();
// createDatabase();
createTable();
showTables();
// selectData();
// Thread.sleep(1000*60);
// loadData();
// descTable();
// deopTable();
// dropDatabase();
destory();
}
// 加载驱动、创建连接
public static void init() throws Exception {
Class.forName(driverName);
conn = DriverManager.getConnection(url,user,password);
stmt = conn.createStatement();
}
// 创建数据库
public static void createDatabase() throws Exception {
String sql = "create database hqgf";
System.out.println("Running: " + sql);
stmt.execute(sql);
}
// 查询所有数据库
public static void showDatabases() throws Exception {
String sql = "show databases";
System.out.println("Running: " + sql);
rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1));
}
}
// 创建表
public static void createTable() throws Exception {
String sql = "create table bill(BILL_ID string,TEMPLATE_NAME string,RECORD_XML string) row format delimited fields terminated by ';'";
System.out.println("Running: " + sql);
stmt.execute(sql);
}
// 查询所有表
public static void showTables() throws Exception {
String sql = "show tables";
System.out.println("Running: " + sql);
rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1));
}
}
// 查看表结构
public static void descTable() throws Exception {
String sql = "desc bill";
System.out.println("Running: " + sql);
rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" + rs.getString(2));
}
}
// 加载数据
public static void loadData() throws Exception {
SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
String filename = format.format(new Date()).concat(".txt");
String filePath = "/usr/tools/".concat(filename);
String sql = "load data local inpath '" + filePath + "' overwrite into table bill";
System.out.println("Running: " + sql);
stmt.execute(sql);
}
// 查询数据
public static void selectData() throws Exception {
String sql = "select * from bill";
System.out.println("Running: " + sql);
rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("RECORD_XML"));
}
}
// 统计查询(会运行mapreduce作业)
public static void countData() throws Exception {
String sql = "select count(1) from bill";
System.out.println("Running: " + sql);
rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getInt(1) );
}
}
// 删除数据库
public static void dropDatabase() throws Exception {
String sql = "drop database if exists hqgf";
System.out.println("Running: " + sql);
stmt.execute(sql);
}
// 删除数据库表
public static void deopTable() throws Exception {
String sql = "drop table if exists bill";
System.out.println("Running: " + sql);
stmt.execute(sql);
}
// 释放资源
public static void destory() throws Exception {
if ( rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
}