JDBC操作Hive

JDBC操作Hive实现步骤如下：

1.新建maven项目添加依赖

  <properties>
      ...
      <hive.version>3.1.3</hive.version>
  </properties>

  <dependency>
     <groupId>org.apache.hive</groupId>
     <artifactId>hive-jdbc</artifactId>
     <version>${hive.version}</version>
  </dependency>

2.设计HiveJDBC工具类

HiveConst.java

public interface HiveConst {
    /*** hive服务器地址 **/
    String HIVE_DB_URL = "jdbc:hive2://192.168.3.177:10000/myschool";
    /*** hive登录账号 **/
    String USER_NAME = "";
    /*** hive登录密码 **/
    String PASSWORD = "";
}

HiveConnection.java

package com.example.hivedemo.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class HiveConnection {
    private static Connection connection = null;

    /**
     * 获取hive连接单例
     *
     * @return hive连接单例
     */
    public static Connection getConnection() {
        if (null == connection) {
            synchronized (HiveConnection.class) {
                if (null == connection) {
                    try {
                        Class.forName("org.apache.hive.jdbc.HiveDriver");
                        HiveConnection.connection = DriverManager.getConnection(HiveConst.HIVE_DB_URL, HiveConst.USER_NAME, HiveConst.PASSWORD);
                    } catch (SQLException e) {
                        e.printStackTrace();
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return connection;
    }

}

HiveUtil.java

public class HiveUtil {

    //把本地数据文件导入到指定的表中。
    public static void importData(String path, String tableName) throws SQLException{
        String hql = "load data local inpath '"+path+"' into table "+tableName;
        PreparedStatement pstm = HiveConnection.getConnection().prepareStatement(hql);
        boolean flag = pstm.execute(hql);

        pstm.close();
    }

    //执行DML操作
    public static void execute(String hql) throws SQLException {
        PreparedStatement pstm = HiveConnection.getConnection().prepareStatement(hql);
        boolean flag = pstm.execute(hql);

        pstm.close();

    }

    //执行DQL操作
    public static void query(String hql) throws SQLException {
        //String sql = "select name,addr.province,addr.city,addr.street from t_person";
        PreparedStatement pstm = HiveConnection.getConnection().prepareStatement(hql);
        ResultSet rs = pstm.executeQuery(hql);

        ResultSetMetaData rsmd = rs.getMetaData();
        //获取字段的个数
        int filedCount = rsmd.getColumnCount();
        StringBuilder sb = new StringBuilder();
        while (rs.next()) {
           for(int i=1;i<=filedCount;i++){
                sb.append(rs.getString(i)+" ");
            }
            sb.append("\r\n");
        }
        System.out.println(sb);
        pstm.close();
        rs.close();
    }

    public static void main(String[] args) {
        try{
            //HiveUtil.query("select name,addr.province,addr.city,addr.street from t_person");
            HiveUtil.execute("insert into t_person (name,addr) select \"小龙女\",named_struct(\"province\", \"浙江省\",\"city\",\"温州市\",\"street\",\"古墓\")");

        }catch (Exception ex){
            ex.printStackTrace();
        }
    }
}

异常处理：

1).启动服务提示：Command line is too long问题解决

修改文件夹.idea/workspace.xml , 找到name="PropertiesComponent"

//新增"dynamic.classpath":"true"
<component name="PropertiesComponent">
   "dynamic.classpath":"true"
</component>

2).执行DML语句时，hive报错AccessControlException Permission denied错误。

hive报错AccessControlException Permission denied: user=anonymous, access=WRITE, inode=“/user/hive/ware

解决方案：

[root@mihaoyu151 ~]# hadoop fs -chmod -R 777 /