Hive的API操作

基于hadoop的Hive数据仓库JavaAPI简单调用的实例，关于Hive的简介在此不赘述。hive提供了三种用户接口：CLI，JDBC/ODBC和 WebUI

1. CLI，即Shell命令行
2. JDBC/ODBC 是 Hive 的Java，与使用传统数据库JDBC的方式类似
3. WebGUI是通过浏览器访问 Hive

本文主要介绍的就是第二种用户接口，直接进入正题。

1、Hive 安装：

1）hive的安装请参考网上的相关文章，测试时只在hadoop一个节点上安装hive即可。

2）测试数据data文件'\t'分隔：

1 zhangsan

2 lisi

3 wangwu

3）将测试数据data上传到linux目录下，我放置在：/home/hadoop01/data

2、在使用 JDBC 开发 Hive 程序时, 必须首先开启 Hive 的远程服务接口。使用下面命令进行开启:

Java代码 

收藏代码

1. hive --service hiveserver >/dev/null 2>/dev/null &

3、测试代码：

Java代码 

收藏代码

1. import java.sql.Connection;
2. import java.sql.DriverManager;
3. import java.sql.ResultSet;
4. import java.sql.SQLException;
5. import java.sql.Statement;
7. import org.apache.log4j.Logger;
9. 
   
10. public class HiveJdbcCli {
12. private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";
13. private static String url = "jdbc:hive://hadoop3:10000/default";
14. private static String user = "hive";
15. private static String password = "mysql";
16. private static String sql = "";
17. private static ResultSet res;
18. private static final Logger log = Logger.getLogger(HiveJdbcCli.class);
20. public static void main(String[] args) {
21. Connection conn = null;
22. Statement stmt = null;
23. try {
24. conn = getConn();
25. stmt = conn.createStatement();
27. // 第一步:存在就先删除
28. String tableName = dropTable(stmt);
30. // 第二步:不存在就创建
31. createTable(stmt, tableName);
33. // 第三步:查看创建的表
34. showTables(stmt, tableName);
36. // 执行describe table操作
37. describeTables(stmt, tableName);
39. // 执行load data into table操作
40. loadData(stmt, tableName);
42. // 执行 select * query 操作
43. selectData(stmt, tableName);
45. // 执行 regular hive query 统计操作
46. countData(stmt, tableName);
48. } catch (ClassNotFoundException e) {
49. e.printStackTrace();
50. log.error(driverName + " not found!", e);
51. System.exit(1);
52. } catch (SQLException e) {
53. e.printStackTrace();
54. log.error("Connection error!", e);
55. System.exit(1);
56. } finally {
57. try {
58. if (conn != null) {
59. conn.close();
60. conn = null;
61. }
62. if (stmt != null) {
63. stmt.close();
64. stmt = null;
65. }
66. } catch (SQLException e) {
67. e.printStackTrace();
68. }
69. }
70. }
72. private static void countData(Statement stmt, String tableName)
73. throws SQLException {
74. sql = "select count(1) from " + tableName;
75. System.out.println("Running:" + sql);
76. res = stmt.executeQuery(sql);
77. System.out.println("执行“regular hive query”运行结果:");
78. while (res.next()) {
79. System.out.println("count ------>" + res.getString(1));
80. }
81. }
83. private static void selectData(Statement stmt, String tableName)
84. throws SQLException {
85. sql = "select * from " + tableName;
86. System.out.println("Running:" + sql);
87. res = stmt.executeQuery(sql);
88. System.out.println("执行 select * query 运行结果:");
89. while (res.next()) {
90. System.out.println(res.getInt(1) + "\t" + res.getString(2));
91. }
92. }
94. private static void loadData(Statement stmt, String tableName)
95. throws SQLException {
96. String filepath = "/home/hadoop01/data";
97. sql = "load data local inpath '" + filepath + "' into table "
98. + tableName;
99. System.out.println("Running:" + sql);
100. res = stmt.executeQuery(sql);
101. }
103. private static void describeTables(Statement stmt, String tableName)
104. throws SQLException {
105. sql = "describe " + tableName;
106. System.out.println("Running:" + sql);
107. res = stmt.executeQuery(sql);
108. System.out.println("执行 describe table 运行结果:");
109. while (res.next()) {
110. System.out.println(res.getString(1) + "\t" + res.getString(2));
111. }
112. }
114. private static void showTables(Statement stmt, String tableName)
115. throws SQLException {
116. sql = "show tables '" + tableName + "'";
117. System.out.println("Running:" + sql);
118. res = stmt.executeQuery(sql);
119. System.out.println("执行 show tables 运行结果:");
120. if (res.next()) {
121. System.out.println(res.getString(1));
122. }
123. }
125. private static void createTable(Statement stmt, String tableName)
126. throws SQLException {
127. sql = "create table "
128. + tableName
129. + " (key int, value string) row format delimited fields terminated by '\t'";
130. stmt.executeQuery(sql);
131. }
133. private static String dropTable(Statement stmt) throws SQLException {
134. // 创建的表名
135. String tableName = "testHive";
136. sql = "drop table " + tableName;
137. stmt.executeQuery(sql);
138. return tableName;
139. }
141. private static Connection getConn() throws ClassNotFoundException,
142. SQLException {
143. Class.forName(driverName);
144. Connection conn = DriverManager.getConnection(url, user, password);
145. return conn;
146. }
148. }

4、测试结果

5、终端查询结果：

hive> select * from testHive;

OK

1 zhangsan

2 lisi

3 wangwu

Time taken: 11.232 seconds

hive>