使用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();
}
}
}