Hive Java API操作

前提条件

Linux下安装好Hadoop2.7.3、Hive2.3.6

步骤

(1) 修改hadoop的core-site.xml添加如下配置

1.  
   <property>
2.  
   <name>hadoop.proxyuser.hadoop.hosts</name>
3.  
   <value>*</value>
4.  
   </property>
5.  
   <property>
6.  
   <name>hadoop.proxyuser.hadoop.groups</name>
7.  
   <value>*</value>
8.  
   </property>

注意：

配置中hadoop.proxyuser.<用户名>.hosts 和 hadoop.proxyuser.<用户名>.groups 的用户名要改为Linux的用户名，例如Linux的用户名为hadoop就把<用户名>改为hadoop

(2) 启动hadoop

start-all.sh

(3) 启动hiveserver2服务

hiveserver2

此时当前终端进入阻塞状态，不要关闭它。可以另外启动一个终端，jps查看进程，看到RunJar进程。

(4) 在新的终端下，使用beeline通过jdbc连接hiveserver2

beeline -u jdbc:hive2://node1:10000/defaul -n hadoop -p

-u 为hive的url地址

-n 为hive所在Linux机器当前登录的用户名

-p 为Linux机器的登录密码

 运行命令，根据提示输入密码后，看到如下箭头所示，为连接成功

(5) 编码

新建maven工程，工程名例如：hive-api

添加pom.xml依赖

1.  
   <dependencies>
2.  
   <dependency>
3.  
   <groupId>org.apache.hadoop</groupId>
4.  
   <artifactId>hadoop-common</artifactId>
5.  
   <version>2.7.3</version>
6.  
   </dependency>
7.  
   <dependency>
8.  
   <groupId>org.apache.hive</groupId>
9.  
   <artifactId>hive-jdbc</artifactId>
10.  
    <version>2.3.6</version>
11.  
    </dependency>
12.  
    <dependency>
13.  
    <groupId>junit</groupId>
14.  
    <artifactId>junit</artifactId>
15.  
    <version>4.13.2</version>
16.  
    <scope>test</scope>
17.  
    </dependency>
18.  
    </dependencies>

编码，在src\test\java\org.example包下创建

HiveJDBCTest.java

 代码如下：

1.  
   package org.example;
2.  
    
3.  
   import org.example.api.JDBCUtils;
4.  
   import org.junit.After;
5.  
   import org.junit.Before;
6.  
   import org.junit.Test;
7.  
    
8.  
   import java.sql.*;
9.  
    
10.  
    public class HiveJDBCTest {
11.  
    private static String driverName="org.apache.hive.jdbc.HiveDriver";//驱动名称
12.  
    private static String url = "jdbc:hive2://node1:10000/default";//连接hive的default数据库
13.  
    private static String user = "hadoop";//hive所在机器的用户名
14.  
    private static String password = "hadoop";//hive所在机器的密码
15.  
     
16.  
    private static Connection conn = null;//连接
17.  
    private static Statement stmt = null;//声明
18.  
    private static ResultSet rs = null;//结果集
19.  
     
20.  
    @Before
21.  
    public void init() throws ClassNotFoundException, SQLException {
22.  
    Class.forName(driverName);
23.  
    conn = DriverManager.getConnection(url, user, password);
24.  
    stmt = conn.createStatement();
25.  
    }
26.  
     
27.  
    /**
28.  
    * 创建数据库
29.  
    * @throws SQLException
30.  
    */
31.  
    @Test
32.  
    public void createDatabase() throws SQLException {
33.  
    String sql = "create database testdb1912";
34.  
    System.out.println("Running: " sql);
35.  
    stmt.execute(sql);
36.  
    }
37.  
     
38.  
    /**
39.  
    * 查询所有数据库
40.  
    * @throws SQLException
41.  
    */
42.  
    @Test
43.  
    public void showDatabases() throws SQLException {
44.  
    String sql = "show databases";
45.  
    System.out.println("Running: " sql);
46.  
    rs = stmt.executeQuery(sql);
47.  
    while (rs.next()){
48.  
    System.out.println(rs.getString(1));//从1开始，查看第一列
49.  
    // System.out.println("========");
50.  
    // System.out.println(rs.getString(2));//没有第二列会报错Invalid columnIndex: 2
51.  
    }
52.  
    }
53.  
     
54.  
    /**
55.  
    * 创建表
56.  
    * @throws SQLException
57.  
    */
58.  
    @Test
59.  
    public void createTable() throws SQLException {
60.  
    String sql = "create table dept_api1(deptno int, dname string, loc string)"
61.  
    "row format delimited fields terminated by ','";
62.  
    System.out.println("Running:" sql);
63.  
    stmt.execute(sql);
64.  
    }
65.  
     
66.  
    @Test
67.  
    public void showTables() throws SQLException {
68.  
    String sql = "show tables";
69.  
    System.out.println("Running:" sql);
70.  
    rs = stmt.executeQuery(sql);
71.  
    while (rs.next()){
72.  
    System.out.println(rs.getString(1));
73.  
    }
74.  
    }
75.  
     
76.  
    /**
77.  
    * 查看表结构
78.  
    * @throws SQLException
79.  
    */
80.  
    @Test
81.  
    public void descTable() throws SQLException {
82.  
    String sql = "desc dept_api";
83.  
    System.out.println("Running:" sql);
84.  
    rs = stmt.executeQuery(sql);
85.  
    while (rs.next()){
86.  
    // 表结构： 列名 列类型
87.  
    System.out.println(rs.getString(1) "\t" rs.getString(2));
88.  
    }
89.  
    }
90.  
     
91.  
    /**
92.  
    * 加载数据
93.  
    * @throws SQLException
94.  
    */
95.  
    @Test
96.  
    public void loadData() throws SQLException {
97.  
    // String filePath = "D:/test/inputemp/dept.csv";
98.  
    // String sql = "load data local inpath 'D:/test/inputemp/dept.csv' overwrite into table dept_api";
99.  
    // String sql = "LOAD DATA LOCAL INPATH 'D:\\test\\inputemp\\dept.csv' " "OVERWRITE INTO TABLE dept_api";
100.  
     // 注意：这里相当于连接了hiveserver2的客户端，hiverserver2在linux上
101.  
     // 路径为Linux的本地文件，用windows路径会报错
102.  
     String sql = "LOAD DATA LOCAL INPATH '/home/hadoop/dept.csv' " "OVERWRITE INTO TABLE dept_api1";
103.  
     System.out.println("Running:" sql);
104.  
     stmt.execute(sql);
105.  
     }
106.  
      
107.  
     /**
108.  
     * 统计记录数
109.  
     * @throws SQLException
110.  
     */
111.  
     @Test
112.  
     public void countData() throws SQLException {
113.  
     String sql = "select count(1) from dept_api1";
114.  
     System.out.println("Running:" sql);
115.  
     rs = stmt.executeQuery(sql);
116.  
     while (rs.next()){
117.  
     System.out.println(rs.getInt(1));
118.  
     }
119.  
     }
120.  
      
121.  
     /**
122.  
     * 删除数据库
123.  
     * @throws SQLException
124.  
     */
125.  
     @Test
126.  
     public void dropDB() throws SQLException {
127.  
     String sql = "drop database if exists testdb1";
128.  
     System.out.println("Running:" sql);
129.  
     stmt.execute(sql);
130.  
     }
131.  
      
132.  
     /**
133.  
     * 删除表
134.  
     * @throws SQLException
135.  
     */
136.  
     @Test
137.  
     public void deleteTable() throws SQLException {
138.  
     String sql = "drop table if exists dept_api1";
139.  
     System.out.println("Running:" sql);
140.  
     stmt.execute(sql);
141.  
     }
142.  
      
143.  
      
144.  
     @After
145.  
     public void destory() throws SQLException {
146.  
     if(rs !=null){
147.  
     rs.close();
148.  
     }
149.  
     if(stmt != null){
150.  
     stmt.close();
151.  
     }
152.  
     if(conn !=null){
153.  
     conn.close();
154.  
     }
155.  
     }
156.  
      
157.  
     }

分别进行单元测试。

更多代码：

JDBCUtils.java

1.  
   package org.example.api;
2.  
    
3.  
   import java.sql.*;
4.  
    
5.  
   public class JDBCUtils {
6.  
   // Hive的驱动
7.  
   private static String driver = "org.apache.hive.jdbc.HiveDriver";
8.  
    
9.  
   // Hive的URL地址
10.  
    // private static String url = "jdbc:hive2://192.168.193.140:10000/default";
11.  
    private static String url = "jdbc:hive2://node1:10000/default";
12.  
     
13.  
    // 注册数据库的驱动
14.  
    static{
15.  
    try {
16.  
    Class.forName(driver);
17.  
    } catch (ClassNotFoundException e) {
18.  
    e.printStackTrace();
19.  
    }
20.  
    }
21.  
     
22.  
    // 获取数据库Hive的链接
23.  
    public static Connection getConnection(){
24.  
    try {
25.  
    return DriverManager.getConnection(url,"hadoop","hadoop");
26.  
    // return DriverManager.getConnection(url);
27.  
    } catch (SQLException throwables) {
28.  
    throwables.printStackTrace();
29.  
    }
30.  
    return null;
31.  
    }
32.  
     
33.  
    // 释放资源
34.  
    public static void release(Connection conn, Statement st, ResultSet rs){
35.  
    if(rs != null){
36.  
    try {
37.  
    rs.close();
38.  
    } catch (SQLException throwables) {
39.  
    throwables.printStackTrace();
40.  
    } finally {
41.  
    rs = null;
42.  
    }
43.  
    }
44.  
     
45.  
    if(st != null){
46.  
    try {
47.  
    st.close();
48.  
    } catch (SQLException throwables) {
49.  
    throwables.printStackTrace();
50.  
    } finally {
51.  
    st = null;
52.  
    }
53.  
    }
54.  
     
55.  
    if(conn != null){
56.  
    try {
57.  
    conn.close();
58.  
    } catch (SQLException throwables) {
59.  
    throwables.printStackTrace();
60.  
    } finally {
61.  
    conn = null;
62.  
    }
63.  
    }
64.  
    }
65.  
    }

HiveDemo.java

1.  
   package org.example.api;
2.  
    
3.  
   import java.sql.Connection;
4.  
   import java.sql.ResultSet;
5.  
   import java.sql.SQLException;
6.  
   import java.sql.Statement;
7.  
    
8.  
   public class HiveDemo {
9.  
   public static void main(String[] args) {
10.  
    // String sql = "select * from emp1";
11.  
    String sql = "create database testdb1";
12.  
     
13.  
    Connection conn = null;
14.  
    Statement st = null;
15.  
    ResultSet rs = null;
16.  
     
17.  
    try {
18.  
    conn = JDBCUtils.getConnection();
19.  
    st = conn.createStatement();
20.  
    rs = st.executeQuery(sql);
21.  
     
22.  
    while(rs.next()){
23.  
    String ename = rs.getString("ename");
24.  
    double sal = rs.getDouble("sal");
25.  
    System.out.println(ename "\t" sal);
26.  
    }
27.  
    } catch (SQLException throwables) {
28.  
    throwables.printStackTrace();
29.  
    } finally {
30.  
    JDBCUtils.release(conn,st,rs);
31.  
    }
32.  
    }
33.  
    }

完成！enjoy it!

这篇好文章是转载于：学新通技术网