Java通过JDBC操作Hive

使用JDBC连接Hive之前必须先启动HiveServer2

HiveServer2是服务器接口，能使客户端执行Hive查询的服务。 是HiveServer1的改进版，支持多客户端并发和身份认证。旨在为开放API客户端（如JDBC和ODBC）提供更好的支持。

1.添加jar

<dependency>
	<groupId>org.apache.hive</groupId>
	<artifactId>hive-jdbc</artifactId>
	<version>1.1.0</version>
		<exclusions>
		<exclusion>
			<groupId>org.eclipse.jetty.aggregate</groupId>
			<artifactId>jetty-all</artifactId>
		</exclusion>
		<exclusion>
			<groupId>org.apache.hive</groupId>
				<artifactId>hive-shims</artifactId>
			</exclusion>
		</exclusions>
</dependency>

2.连接驱动

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class HiveJDBCUtils {
	
	private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    private static String url = "jdbc:hive2://ip:10000/default";//端口默认10000
    private static String user = "name";
    private static String password = "password";

    private static Connection conn = null;
    private static Statement stmt = null;
    
    // 加载驱动、创建连接
    public Statement Connection() throws Exception {
        Class.forName(driverName);
        conn = DriverManager.getConnection(url,user,password);
        stmt = conn.createStatement();
        return stmt;
    }
}

3. demo代码

public class HiveJDBC {

    private static Connection conn = null;
    private static Statement stmt = null;
    private static ResultSet rs = null;
    
    public static void main(String[] args) throws Exception {
    	HiveJDBCUtils h = new HiveJDBCUtils();
    	stmt = h.Connection();
    	loadData();
    	destory();
	}

    // 创建数据库
    public static void createDatabase() throws Exception {
        String sql = "create database databaseName";
        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 tableName(name string,sex 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 formatted tableName";
        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 {
    	String filePath = "file路径";
        String sql = "load data inpath '" + filePath + "' into table tableName";
        System.out.println("Running: " + sql);
        stmt.execute(sql);
    	}  	
    }
    
    // 查询数据
    public static void selectData(String str) throws Exception {
    	String sql = "select * from tableName";
        System.out.println("Running: " + sql);
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
			System.out.println(rs.getString(1));
        }
    }
   
    // 删除数据库
    public static void dropDatabase() throws Exception {
        String sql = "drop database if exists tableName";
        System.out.println("Running: " + sql);
        stmt.execute(sql);
    }

    // 删除数据库表
    public static void deopTable() throws Exception {
        String sql = "drop table if exists tableName";
        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();
        }
    }
}