最近需要使用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服务会比较好.