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) 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<MqttDiVo>> 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;
    }