springboot使用presto查询hive数据

引包

<dependency> <groupId>com.facebook.presto</groupId> <artifactId>presto-jdbc</artifactId> <version>0.234.1</version> </dependency>

1、配置

设置presto的配置信息

presto: ip: 10.36.29.37 port: 7788 userName: elco driver-class-name: com.facebook.presto.jdbc.PrestoDriver databasename: hive

2、编写config类

package com.elco.entity;  import lombok.Data; import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Component;  @Component @Data public class PrestoConfig {      private String ip;     private String port;     private String userName;     private String driverClassName;     private String databasename;      public String getDatabasename() {         return databasename;     }     @Value("${presto.databasename}")     public void setDatabasename(String databasename) {         this.databasename = databasename;     }      public String getDriverClassName() {         return driverClassName;     }     @Value ("${presto.driver-class-name}")     public void setDriverClassName(String driverClassName) {         this.driverClassName = driverClassName;     }     public String getIp() {         return ip;     }     @Value("${presto.ip}")     public void setIp(String ip) {         this.ip = ip;     }      public String getPort() {         return port;     }     @Value ( "${presto.port}" )     public void setPort(String port) {         this.port = port;     }      public String getUserName() {         return userName;     }     @Value ( "${presto.userName}" )     public void setUserName(String userName) {         this.userName = userName;     }  } 

3.编写连接工具

package com.elco.utils;  import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import com.elco.entity.PrestoConfig; import org.springframework.beans.factory.annotation.Value;  import java.sql.*;  public class PrestoJDBCUtil {     /**      * 连接presto      * @param prestoConfig      * @return      */     public static synchronized Connection getPrestoConn(PrestoConfig prestoConfig) {         String url = "jdbc:presto://"   prestoConfig.getIp()   ":"   prestoConfig.getPort()   "/"   prestoConfig.getDatabasename();         Connection connection = null;         String username = prestoConfig.getUserName();         try {             Class.forName(prestoConfig.getDriverClassName()); //执行驱动             connection = DriverManager.getConnection(url, username, null);         } catch (ClassNotFoundException e) {             e.printStackTrace();         } catch (SQLException e) {             e.printStackTrace();         }          return connection;     }      /**      * 通过presto连接hive查询数据      * @param sql      * @param prestoConfig      * @return      */     public static JSONArray selectPresto(String sql, PrestoConfig prestoConfig) {          Connection conn1 = getPrestoConn(prestoConfig);         //连接         PreparedStatement pstmt = null;    //使用预编译语句         ResultSet rs = null;    //获取的结果集         JSONArray jsonArray = new JSONArray();         try {              pstmt = conn1.prepareStatement(sql);             rs = pstmt.executeQuery();             ResultSetMetaData metaData = rs.getMetaData();             int in = metaData.getColumnCount();             JSONObject jsonObject;             while (rs.next()) {                 jsonObject = new JSONObject();                 for (int x = 1; x <= in; x  ) {                     jsonObject.put(metaData.getColumnName(x), rs.getString(metaData.getColumnName(x)));                 }                 jsonArray.add(jsonObject);             }         } catch (Exception e) {             throw new RuntimeException(e);         } finally {             //关闭资源，倒关             try {                 if (rs != null) rs.close();                 if (pstmt != null) rs.close();                 if (pstmt != null) pstmt.close();                 if (conn1 != null) conn1.close();  ///必须关闭             } catch (Exception e) {                 e.printStackTrace();             }         }         return jsonArray;     } }

4、接口调用

 /**      * 某一时间段的查询hive数据      *      * @param exportTBoxMessageDto      * @param equipmentCode      * @return      */      public Map<String, List<MqttDiVo>> exportTBoxMessageQuery(ExportTBoxMessageDto exportTBoxMessageDto, String equipmentCode, String productModel) {         Map<String, List<MqttDiV>> resultMap = new HashMap<>();
        //字段集合
        Map<String, String> tBoxEfileMap =null;
        //获取日期
        String day = exportTBoxMessageDto.getStartTime().substring(0, 10);
        //起始小时
        int startHour =Integer.valueOf( exportTBoxMessageDto.getStartTime().substring(11, 13));
        //结束时间
        int  endHour = Integer.valueOf(exportTBoxMessageDto.getEndTime().substring(11, 13));
        //根据设备编码及时间段查询数据
        StringBuffer sql = new StringBuffer("");
        //调用presto查询方法执行查询语句
        JSONArray dataArray = PrestoJDBCUtil.selectPresto(sql.toString(),prestoConfig);
        //遍历数据
        for (int i = 0; i < dataArray.size(); i++) {
            JSONObject data = dataArray.getJSONObject(i);
            String dataTmie = data.getString("data_time").replace("\t", "");
            String filed = tBoxEfileMap.get(data.getString("data_item_code"));
            if(filed != null){
                if (resultMap.get(dataTmie) != null) {
                    MqttDiVo mqttDiVo = new MqttDiVo();
                    mqttDiVo.setMessageCode(data.getString("message_code"));
                    mqttDiVo.setCollectTime(data.getLong("collect_time"));
                    mqttDiVo.setDataItemCode(filed);
                    mqttDiVo.setDataItemValue(data.getString("data_item_value"));
                    mqttDiVo.setDataTime(dataTmie);
                    mqttDiVo.setDatatype(data.getString("datatype"));
                    mqttDiVo.setGwdeviceCode(data.getString("gwdevice_code"));
                    resultMap.get(dataTmie).add(mqttDiVo);
                } else {
                    List<MqttDiVo> mqttDiVoList = new ArrayList<>();
                    MqttDiVo mqttDiVo = new MqttDiVo();
                    mqttDiVo.setMessageCode(data.getString("message_code"));
                    mqttDiVo.setCollectTime(data.getLong("collect_time"));
                    mqttDiVo.setDataItemCode(filed);
                    mqttDiVo.setDataItemValue(data.getString("data_item_value"));
                    mqttDiVo.setDataTime(dataTmie);
                    mqttDiVo.setDatatype(data.getString("datatype"));
                    mqttDiVo.setGwdeviceCode(data.getString("gwdevice_code"));
                    mqttDiVoList.add(mqttDiVo);
                    resultMap.put(dataTmie, mqttDiVoList);
                }
            }

        }

        return resultMap;
    }