1、前题
(1)Hadoop2.2,Hive能正常运行
(2)开启Hive客户端监听服务:hiveserver
./bin/hive --service hiveserver
2、依赖的jar包
antlr-runtime-3.4.jar
commons-logging-1.1.3.jar
hadoop-common-2.2.0.jar
hive-exec-0.13.1.jar
hive-jdbc-0.13.1.jar
hive-metastore-0.13.1.jar
hive-service-0.13.1.jar
jdo-api-3.0.1.jar
libfb303-0.9.0.jar
log4j-1.2.17.jar
mysql-connector-java-5.1.7-bin.jar
slf4j-api-1.7.5.jar
slf4j-log4j12-1.7.5.jar
3、Java程序实现测试
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Test {
public static void main(String[] args) throws Exception {
Class.forName("org.apache.hadoop.hive.jdbc.HiveDriver");// DriverName 注册Hive驱动
//建立与Hive数据库的连接, 默认端口10000,使用数据库:hive,用户名密码:hive
// URL,USER,PASSWORD
Connection conn = DriverManager.getConnection("jdbc:hive://ip:10000/hive", "root", "password");
Statement stmt = conn.createStatement();
String tableName = "test1";
// 如果已经存在就删除
String sql = "DROP TABLE " + tableName;
stmt.executeQuery(sql);
sql = "CREATE TABLE " + tableName + " (userid int,itemid int,perference int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','";
stmt.executeQuery(sql);
// 执行“LOAD DATA INTO TABLE”操作
String filepath = "/root/develop/hivejdbc.data";
sql = "LOAD DATA LOCAL INPATH '" + filepath + "' OVERWRITE INTO TABLE "+ tableName;
stmt.executeQuery(sql);
System.out.println(sql);
// 执行“SELCET”操作
sql = "SELECT * FROM " + tableName;
System.out.println(sql);
ResultSet res = stmt.executeQuery(sql);
System.out.println("SELCET运行结果:");
while (res.next()) {
System.out.println(res.getInt(1) + "\t" + res.getString(2)+ "\t" + res.getString(3));
}
conn.close();
conn = null;
}
}
4、输出结果
log4j:WARN No appenders could be found for logger (org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
LOAD DATA LOCAL INPATH '/root/develop/hivejdbc.data' OVERWRITE INTO TABLE test1
SELECT * FROM test1
SELCET运行结果:
1 1 3
1 2 4
1 3 5