pom.xml连接hive的jar
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>hive-test</groupId>
<artifactId>hive-test</artifactId>
<version>0.0.1-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>2.1.1</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.9</version>
</dependency>
<dependency>
<groupId>jdk.tools</groupId>
<artifactId>jdk.tools</artifactId>
<version>1.8</version>
<scope>system</scope>
<systemPath>${JAVA_HOME}/lib/tools.jar</systemPath>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.5.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
</project>
java操作hive的代码
package com.bigdata.hadoop.hive;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.sql.*;
/**
* JDBC 操作 Hive(注:JDBC 访问 Hive 前需要先启动HiveServer2)
*/
public class HiveJDBC2 {
private static String driverName = "org.apache.hive.jdbc.HiveDriver";
private static String url = "jdbc:hive2://172.16.20.115:10000/default";
private static String user = "hiveuser";
private static String password = "Whdh166188";
private static Connection conn = null;
private static Statement stmt = null;
private static ResultSet rs = null;
// 加载驱动、创建连接
@Before
public void init() throws Exception {
Class.forName(driverName);
conn = DriverManager.getConnection(url,user,password);
stmt = conn.createStatement();
}
// 创建数据库
@Test
public void createDatabase() throws Exception {
String sql = "create database hive_jdbc_test";
System.out.println("Running: " + sql);
stmt.execute(sql);
}
// 查询所有数据库
@Test
public 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));
}
}
// 创建表
@Test
public void createTable() throws Exception {
String sql = "CREATE TABLE poi_amap(\n" +
"id string,\n" +
"lng string,\n" +
"lat string,\n" +
"name string,\n" +
"address string,\n" +
"telephone string,\n" +
"type string,\n" +
"areaid string,\n" +
"wgslng string,\n" +
"wgslat string,\n" +
"bdlng string,\n" +
"bdlat string,\n" +
"updatetime string\n" +
")\n" +
"row format delimited fields terminated by '\\t'";
System.out.println("Running: " + sql);
stmt.execute(sql);
}
// 查询所有表
@Test
public 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));
}
}
// 查看表结构
@Test
public void descTable() throws Exception {
String sql = "desc poi_amap";
System.out.println("Running: " + sql);
rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" + rs.getString(2));
}
}
// 加载数据
@Test
public void loadData() throws Exception {
String filePath = "/input/poi_amap.txt";
String sql = "load data inpath '" + filePath + "' overwrite into table poi_amap";
System.out.println("Running: " + sql);
stmt.execute(sql);
}
// 查询数据
@Test
public void selectData() throws Exception {
String sql = "select * from poi_amap";
System.out.println("Running: " + sql);
rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t\t" + rs.getDouble("lng") + "\t\t" + rs.getDouble("lat")
+ "\t\t" + rs.getString("name")+ "\t\t" + rs.getString("address")+ "\t\t" + rs.getString("telephone")
+"\t\t" + rs.getString("type")
+ "\t\t" + rs.getInt("areaid")+ "\t\t" + rs.getDouble("wgslng")+ "\t\t" + rs.getDouble("wgslat")
+ "\t\t" + rs.getDouble("bdlng")+ "\t\t" + rs.getDouble("bdlat")+ "\t\t" + rs.getString("updatetime"));
}
}
// 统计查询(会运行mapreduce作业)
@Test
public void countData() throws Exception {
String sql = "select count(1) from emp";
System.out.println("Running: " + sql);
rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getInt(1) );
}
}
// 删除数据库
@Test
public void dropDatabase() throws Exception {
String sql = "drop database if exists poi_amap";
System.out.println("Running: " + sql);
stmt.execute(sql);
}
// 删除数据库表
@Test
public void deopTable() throws Exception {
String sql = "drop table if exists poi_amap";
System.out.println("Running: " + sql);
stmt.execute(sql);
}
// 释放资源
@After
public void destory() throws Exception {
if ( rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
}