异步处理hive-jdbc执行请求

  最近需要使用hive服务进行标签计算任务,故需要使用hive-jdbc在后端搭建服务,以执行前端发送的hql语句.
  由于hive的计算常需要运行MR/SPARK任务,所以一个hql语句(如count语句)往往要等待很长时间才能完成,若采用同步等待的方式,前端的http连接需要等待若干分钟才能返回,又由于标签计算任务的数量不可预计,即使扩大连接池也无法保证全部处理前端发来的计算请求.针对这种情况,适合采用生产者-消费者的设计模式去处理计算请求,这样请求来临时只需放进执行队列然后立即返回,服务端的消费者会定时去获取计算任务,从而实现异步的处理.

以下是具体的实现:

1. 项目依赖

maven依赖:

    <dependencies>
        <!-- hive-jdbc依赖 -->
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>2.1.0</version>
        </dependency>

    </dependencies>

2. Hive客户端实现

主要是通过jdbc为程序提供执行入口对象,此处提供关键实现,关键方法为executeQuery

import com.google.common.base.Predicate;
import com.google.common.collect.Lists;
import com.google.common.collect.Sets;

import org.apache.hive.jdbc.HivePreparedStatement;
import org.apache.hive.jdbc.HiveStatement;
import org.apache.parquet.Log;
import org.apache.parquet.Strings;
import org.mortbay.util.ajax.JSON;

import javax.annotation.Nullable;
import java.io.Closeable;
import java.io.IOException;
import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.ConcurrentHashMap;


/**
 * Created by chenyuzhi on 18-4-29.
 */
public class HiveClient implements Closeable{
    private static final Log log = Log.getLog(HiveClient.class);
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    private static String url = "";
    private static String user = "";
    private static String password = "";
    private static Map<SQLBean,HivePreparedStatement> runningSQLs = new ConcurrentHashMap<>();
    private Connection conn;

    public HiveClient() {
    }


    private void mapParams(HivePreparedStatement stmt, List params) throws SQLException {
        if(params == null || params.isEmpty()) return;
        int length = params.size();
        int sqlParameterIndex;
        for (int i = 0; i < length; i++){

            Object param = params.get(i);
            sqlParameterIndex = i+1;
            if(param instanceof Number) {
                stmt.setInt(sqlParameterIndex,((Number)param).intValue());
            }else if(param instanceof String){
                stmt.setString(sqlParameterIndex,(String)param);
            }else if (param instanceof Boolean){
                stmt.setBoolean(sqlParameterIndex,(Boolean)param);
            }else{
                StringBuilder errStrBuilder = new StringBuilder();
                errStrBuilder.append("No mapping available for param type: ")
                        .append(param.getClass().getSimpleName())
                        .append(" value: ")
                        .append(param);
                throw new RuntimeException(errStrBuilder.toString());
            }
        }
    }


    public  String executeQuery(SQLBean sqlBean)
            throws ClassNotFoundException, SQLException {

        String resultStr = null;
        if(conn == null){
            conn = getConn();
        }

        String queryId = sqlBean.getQueryId();
        String sql = sqlBean.getSql();
        List params= sqlBean.getParams();

        HivePreparedStatement stmt;
        synchronized (runningSQLs){
            //检查该queryId对应的hql是否正在执行
            this.checkRunningQueue(queryId);
            stmt = (HivePreparedStatement)conn.prepareStatement(sql);
            if(!Strings.isNullOrEmpty(queryId)){
                //对于提供了queryId的hql,缓存其hivePrepareStatement对象,以提供cancel的功能
                runningSQLs.put(sqlBean,stmt);
            }
        }

        try{
            //动态匹配PrepareStatement的参数
            mapParams(stmt,params);
            if(stmt.execute()) resultStr = parseResultSet(stmt.getResultSet());
            return resultStr;
        }finally {
            if(stmt !=null ) { stmt.close();}
            if(!Strings.isNullOrEmpty(queryId) && runningSQLs.containsKey(sqlBean)) {runningSQLs.remove(sqlBean);}
        }
    }


    private static Set<SQLBean> getSQLBeansInRunningQueueByQueyId(String queryId){
        return Sets.filter(runningSQLs.keySet(), new Predicate<SQLBean>() {
            @Override
            public boolean apply(@Nullable SQLBean sqlBean) {
                assert sqlBean != null;
                return sqlBean.getQueryId().equals(queryId);
            }
        });
    }

    private void checkRunningQueue(String queryId){
        if(Strings.isNullOrEmpty(queryId) ) return;
        if(getSQLBeansInRunningQueueByQueyId(queryId).isEmpty()) return;

        throw new RuntimeException(String.format("sql [%s] has in the running queue,please wait!",queryId));
    }

    private String parseResultSet(ResultSet resultSet) throws SQLException {
        String resultStr;
        ResultSetMetaData metaData = resultSet.getMetaData();
        int colCount = metaData.getColumnCount();
        List res = Lists.newArrayList();
        while (resultSet.next()) {
            List row = Lists.newArrayList();
            for (int i = 1; i <= colCount; i++) {
                row.add(resultSet.getObject(i));
            }
            res.addAll(row);
        }
        resultStr = JSON.toString(res);

        log.info("resultStr:"+resultStr);
        return  resultStr;
    }


    private Connection getConn() throws ClassNotFoundException, SQLException {
        if(conn == null){
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, user, password);
        }
        return conn;
    }

    public void close() throws IOException {
        if(null != conn) try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

3. Hive管理类实现

hive管理类主要是用生产者-消费者模式处理计算请求,此处提供关键实现,关键方法为start.
注意,在这个类中,对于计算请求消费者才会使用专门的连接去执行,而对于非计算请求(如DDL的create语句),由于执行的耗时很短,不需要也不可以走生产者-消费者模式(因为不可能建个表也让前端等半天,体验不好),所以会同步执行,在缓存中取出连接直接执行返回.

import hive.client.SystemConfig;
import hive.client.api.HiveClient;
import hive.client.api.HiveClientFactory;
import hive.client.bean.SQLBean;
import hive.client.cache.Cache;

import org.apache.commons.lang.StringUtils;
import org.apache.parquet.Log;
import org.apache.parquet.Strings;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

import java.sql.SQLException;
import java.util.Map;
import java.util.concurrent.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * Created by chenyuzhi on 18-5-10.
 */
public class SQLManager {
    private static final Log log = Log.getLog(SQLManager.class);
    public static final DateTimeFormatter TIME_FORMATTER = DateTimeFormat.forPattern("YYYY-MM-dd HH:mm:ss.SSS");
    //使用堵塞队列实现生产者-消费者模式
    public static final BlockingQueue<SQLBean> PENDING_QUEUE = new LinkedBlockingQueue<>();
    public static final Map<String,SQLResult> RESULT_MAP = new ConcurrentHashMap<>();
    private static AtomicInteger executorThreadNum = new AtomicInteger(0);

    private static int executeDuration = SystemConfig.getInt(SystemConfig.HIVE_SQL_EXECUTE_DURATION);
    private boolean started = false;
    ScheduledExecutorService executorService;


    public void start(){
        if(started) {
            return;
        }
        ScheduledExecutorService executorService = Executors.newScheduledThreadPool(2, new ThreadFactory() {

            @Override
            public Thread newThread(Runnable r) {
                Thread t = new Thread(r);
                t.setName("SQLManager-Thread-" + executorThreadNum.getAndAdd(1));
                return t;
            }
        });
        //定时执行sql计算任务
        executorService.scheduleWithFixedDelay(new Runnable() {
            @Override
            public void run() {
                while (PENDING_QUEUE.size()>0){
                    log.info("PENDING_QUEUE size:" + PENDING_QUEUE.size());
                    SQLBean sqlBean = null;
                    String queryId = null;
                    try {
                        //从堵塞队列中取出一个计算任务
                        sqlBean= PENDING_QUEUE.take();
                        queryId = sqlBean.getQueryId();
                        String result = executeSQL(sqlBean);

                        SQLResult sqlResult = new SQLResult(queryId,result,
                                SQLResult.SUCCESS_STATUS,
                                SQLResult.SUCCESS_MSG);
                        //将计算结果放进Map中,等待前端获取,然后过期删除.目前简单处理仅是在内存中缓存,后续数据量大可以优化为放在redis等服务中
                        RESULT_MAP.put(queryId,sqlResult);

                    } catch (Exception e) {
                        e.printStackTrace();
                        if(sqlBean == null || StringUtils.isEmpty(queryId)){
                            return;
                        }

                        SQLResult sqlResult = new SQLResult(queryId,"",
                                SQLResult.FAILED_STATUS,
                                e.getMessage());
                        RESULT_MAP.put(queryId,sqlResult);
                    }
                    log.info("RESULT_MAP:"+ RESULT_MAP);
                }
            }
        },
        0,
        executeDuration,
        TimeUnit.SECONDS);

        started = true;

    }

    public void stop(){
        if(started){
            executorService.shutdown();
        }
        started = false;
    }


    public static void addSqlBeanToPendingQueue(SQLBean sqlBean){
        PENDING_QUEUE.offer(sqlBean);
    }


    public String executeSQL(SQLBean sqlBean) throws SQLException, ClassNotFoundException, ExecutionException {
        //若是提供queryId的即是计算请求(如count计算),没有的则是非计算请求(如DDL语句),非计算请求使用缓存内的连接执行.
        HiveClient hiveClient = Strings.isNullOrEmpty(sqlBean.getQueryId()) ? Cache.getHiveClientCache().get("common-client")
                : HiveClientFactory.getSinglComputeClient();

        String result = hiveClient.executeQuery(sqlBean);
        if(result == null) return "";
        return result;
    }

    //存放hive计算结果的内部类
    public class SQLResult {

        public static final String SUCCESS_STATUS = "success";
        public static final String SUCCESS_MSG = "ok";
        public static final String FAILED_STATUS = "failed";

        String queryId;
        String result;
        String status;
        String message;
        //记录结果产生时间,用于定时删除
        String generateTime = TIME_FORMATTER.print(System.currentTimeMillis());
        boolean checked = false;

        public SQLResult() {
        }

        public SQLResult(String queryId, String result, String status, String message) {
            this.queryId = queryId;
            this.result = result;
            this.status = status;
            this.message = message;
        }

        public String getQueryId() {
            return queryId;
        }

        public String getResult() {
            return result;
        }

        public String getStatus() {
            return status;
        }

        public String getMessage() {
            return message;
        }

        public String getGenerateTime() {
            return generateTime;
        }

        public SQLResult setChecked(boolean checked) {
            this.checked = checked;
            return this;
        }

        @Override
        public String toString() {
            return "SQLResult{" +
                    "queryId='" + queryId + '\'' +
                    ", result='" + result + '\'' +
                    ", status='" + status + '\'' +
                    ", message='" + message + '\'' +
                    ", generateTime='" + generateTime + '\'' +
                    ", checked=" + checked +
                    '}';
        }
    }

}

4. 总结

  对于hive计算请求执行过长的问题,一开始没有注意到,所以采用了同步执行的方式去处理,结果自己做并发测试时,发现后面的请求陆续出现无法建立连接的情况,一开始以为是连接池连接数的问题,但在增大连接池数量后,同样的问题仍会出现.经过调试才知道,有些请求的耗时过长,执行过程中一直占用http连接无法释放,导致连接池逐渐耗尽.考虑到生产环境中无法估计会有多少并发请求,出于延迟和体验的考虑,最终只能采用异步的方式解决,这中间又产生了结果的存放问题.如今简单处理是放在内存中,但若资源限制或计算任务过多时,建议采用外部缓存如redis服务会比较好.