net.wicp.tams.common.http.flink.SqlGateWay Maven / Gradle / Ivy
The newest version!
package net.wicp.tams.common.http.flink;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;
import org.apache.commons.lang3.tuple.Pair;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.JsonNodeFactory;
import com.fasterxml.jackson.databind.node.ObjectNode;
import lombok.extern.slf4j.Slf4j;
import net.wicp.tams.common.Result;
import net.wicp.tams.common.apiext.StringUtil;
import net.wicp.tams.common.apiext.TimeAssist;
import net.wicp.tams.common.apiext.json.JSONUtil;
import net.wicp.tams.common.constant.StrPattern;
import net.wicp.tams.common.exception.ExceptAll;
import net.wicp.tams.common.exception.ProjectExceptionRuntime;
import net.wicp.tams.common.http.HttpClient;
import net.wicp.tams.common.http.HttpResult;
@Slf4j
public class SqlGateWay {
private final String sessionHandle;
private final String url;
private final String VERSION = "v1";
private final String host;
private final int port;
private final SqlCmdWrapper sqlCmdWrapper = new SqlCmdWrapper();// "mycatalog", "duckula"
private volatile ScheduledExecutorService timerService;
public String getHost() {
return host;
}
public int getPort() {
return port;
}
/***
* 初始化构造
*
* @param url
*/
public SqlGateWay(String url) {
this.url = url.endsWith("/") ? url.substring(0, url.length() - 1) : url;
String callSessionUrl = String.format("%s/%s/sessions", this.url, this.VERSION);
HttpResult sessionRes = null;
while (true) {
try {
sessionRes = HttpClient.doPost(callSessionUrl, null);
if (sessionRes == null || !sessionRes.getResult().isSuc()) {// read timeout
throw new ProjectExceptionRuntime(ExceptAll.flink_gateway_nosession,
"拿不到session" + callSessionUrl + ":" + sessionRes.getErrMsg());
}
break;
} catch (Throwable e) {
boolean reDoWait = TimeAssist.reDoWait("connect sqlgateway", 6);// 4次还没做完？
if (reDoWait) {// 达到最大值就出
log.error("重试6次,未获取到sessionHandle！");
break;
} else {
continue;
}
}
}
ObjectNode parseObject = JSONUtil.parserStr(new ObjectMapper(), sessionRes.getBodyStr());
this.sessionHandle = parseObject.get("sessionHandle").asText();
// 初始化host和port
String[] group = StrPattern.weburl.group(this.url);
String[] hostport = group[2].split(":");
if (hostport.length == 2) {
this.host = hostport[0];
this.port = Integer.parseInt(hostport[1]);
} else {
this.host = hostport[0];
this.port = 80;
}
heartbeatPer();
// this.sqlCmdWrapper = initCurCatalogAndDb();
}
// 通过k8s的服务名组装url
public SqlGateWay(String serviceNamForK8s, String namespaceForK8s, int port) {
// 参考K8sAssit.getInnerUrl
this(port == 80 ? String.format("http://%s.%s", serviceNamForK8s, namespaceForK8s)
: String.format("http://%s.%s:%s", serviceNamForK8s, namespaceForK8s, port));
}
// 通过主机端口
public SqlGateWay(String host, int port) {
this(port == 80 ? String.format("http://%s", host) : String.format("http://%s:%s", host, port));
}
public SqlGateWay(String url, String sessionHandle) {
this.url = url.endsWith("/") ? url.substring(0, url.length() - 1) : url;
this.sessionHandle = sessionHandle;
// 初始化host和port
String[] group = StrPattern.weburl.group(this.url);
String[] hostport = group[2].split(":");
if (hostport.length == 2) {
this.host = hostport[0];
this.port = Integer.parseInt(hostport[1]);
} else {
this.host = hostport[0];
this.port = 80;
}
heartbeatPer();
// gateway做过了初始化就不需要再做了。
// init(initContext, params);
// 得到默认的catalog和db
// this.sqlCmdWrapper = initCurCatalogAndDb();
}
//此方法不能在构造函数里调用，因为catalog_get、db_get都需要sessionid。
// private SqlCmdWrapper initCurCatalogAndDb() {
// String catalogSql = SqlCmd.catalog_get.getSql(null, null);
// Result catalogSqlrs = statementsResultForDDL(catalogSql);
// String dbSql = SqlCmd.db_get.getSql(null, null);
// Result dbSqlrs = statementsResultForDDL(dbSql);
// if (!catalogSqlrs.isSuc() || !dbSqlrs.isSuc()||dbSqlrs.getMessage().contains("\"errors\"")) {//包有错误
// throw new ProjectExceptionRuntime(ExceptAll.project_other, "连接失败，没有拿到当前catalog和db");
// }
// return new SqlCmdWrapper(catalogSqlrs.getMessage(),dbSqlrs.getMessage());
// }
// 是否存在session
public boolean isExit() {
HttpResult sessionRes = HttpClient
.doGet(String.format("%s/%s/sessions/%s", this.url, this.VERSION, this.sessionHandle));
ObjectNode res = JSONUtil.parserStr(new ObjectMapper(), sessionRes.getBodyStr());
return res.has("properties");
}
// TODO 关闭session
public void close() {
try {
if (this.timerService != null) {
this.timerService.shutdown();
}
} catch (Exception e) {
}
}
// 得到info信息
public Pair getInfo() {
HttpResult sessionRes = HttpClient.doGet(String.format("%s/%s/info", this.url, this.VERSION));
ObjectNode res = JSONUtil.parserStr(new ObjectMapper(), sessionRes.getBodyStr());
return Pair.of(res.get("productName").asText(), res.get("version").asText());
}
public synchronized void cancelJob(String operationHandle) {
String url = String.format("%s/%s/sessions/%s/operations/%s/cancel", this.url, this.VERSION, this.sessionHandle,
operationHandle);
HttpClient.doPost(url, null);
}
// 续命timeout时间，默认为10 min
public boolean heartbeat() {
HttpResult sessionRes = HttpClient
.doPost(String.format("%s/%s/sessions/%s/heartbeat", this.url, this.VERSION, this.sessionHandle), null);
ObjectNode res = JSONUtil.parserStr(new ObjectMapper(), sessionRes.getBodyStr());
return !res.has("errors");
}
// 每个构造函数都会调用,8分钟续命一次
private void heartbeatPer() {
// 设置为批模式
statementsResultForDDL("RESET 'execution.checkpointing.interval'", false);
statementsResultForDDL("SET 'execution.runtime-mode' = 'batch'", false);
// 流模式
// statementsResultForDDL("SET 'execution.checkpointing.interval'='30s'");
// statementsResultForDDL("SET 'execution.runtime-mode' = 'streaming'");
// 添加心跳
this.timerService = Executors.newScheduledThreadPool(1);
this.timerService.scheduleAtFixedRate(new Runnable() {
@Override
public void run() {
heartbeat();
}
}, 400, 400, TimeUnit.SECONDS);
}
public void init(String initContext, Map params) {
if (StringUtil.isNotNull(initContext)) {
List sqls = StringUtil.sqlScriptToLine(initContext, System.lineSeparator(), params);
for (int i = 0; i < sqls.size(); i++) {
log.info("sql:{}", sqls.get(i));
String statements = statements(sqls.get(i));
log.info("index:{} handle:{}", i, statements);
}
// 设置默认的catalog和db
this.sqlCmdWrapper.setCatalog("mycatalog");
this.sqlCmdWrapper.setDb("duckula");
}
}
// 初始化catalog和db
public void init(String catalog, String db) {
setCatalog(catalog);
setDb(db);
}
protected String statements(String sql) {
String url = String.format("%s/%s/sessions/%s/statements/", this.url, this.VERSION, this.sessionHandle);
ObjectNode param = JsonNodeFactory.instance.objectNode();
param.put("statement", sql);
HttpResult httpResult = HttpClient.doPost(url, param);
String operationHandle = httpResult == null ? ""
: JSONUtil.parserStr(new ObjectMapper(), httpResult.getBodyStr()).get("operationHandle").asText();
return operationHandle;
}
// 得到执行状态
private String statementsStatus(String operationHandle) {// FINISHED和RUNNING
String url = String.format("%s/%s/sessions/%s/operations/%s/status", this.url, this.VERSION, this.sessionHandle,
operationHandle);
HttpResult httpResult = HttpClient.doGet(url);
String retstr = null;
if (httpResult != null) {
ObjectNode parseObject = JSONUtil.parserStr(new ObjectMapper(),httpResult.getBodyStr());
retstr = parseObject.get("status").asText();
}
return retstr;
}
// 异步改同步
protected Result statementsResultByPage(String operationHandle, int page, boolean needWaite) {
// 由于是异步的，不知道啥时候拿到结果。
ObjectNode parseObject = null;
int statuscode = 200;
int num = 0;
while (true) {
num++;
if (num > 90) {// 最多等90*2秒，防止无限等待
log.error("查询超过60秒，退出");
break;
}
if (needWaite) {// 这个参数用于区分catalog操作与flink表的查询。catalog操作不需要等待，flink表的查询需要等待，区别对待于加快执行速度
String status = statementsStatus(operationHandle);
if (!"FINISHED".equals(status)) {// 只有完成后才能进去查询
log.info("已查询{}次，运行结果是:{}", num, status);
// 先做再等，这样翻页的时候就不需要等了
try {
Thread.sleep(2000);
} catch (InterruptedException e) {
}
continue;
} else {
// !!!sqlgateway什么垃圾bug，查询到FINISHED还不能拉到数据，需等待一段时间，再拉不到就不知道怎么弄了。
try {
Thread.sleep(2000);
} catch (InterruptedException e) {
}
}
}
String url = String.format("%s/v1/sessions/%s/operations/%s/result/%s", this.url, this.sessionHandle,
operationHandle, page);
HttpResult httpResult = HttpClient.doGet(url);
parseObject = JSONUtil.parserStr(new ObjectMapper(),httpResult.getBodyStr());
statuscode = httpResult.getHttpStatus();
// {"nextResultUri":"/v1/sessions/d7b7853f-055e-4cfe-9e58-b98e42c7377e/operations/cfec1d00-2422-4a19-af87-980970dcfcaf/result/0","results":{"data":[],"columns":[]},"resultType":"NOT_READY"}
if (!ResultType.NOT_READY.name().equals(parseObject.get("resultType").asText())) {// 没有准备好，再次循环,NOT_READY
log.info("operationHandle:{} result:{}", operationHandle, parseObject);
break;
}
if (!needWaite) {// 不需要等时走这条路，不至于空载最大次数
// 先做再等，这样翻页的时候就不需要等了
try {
Thread.sleep(1000);
} catch (InterruptedException e) {
}
}
}
Result ret = statuscode == 200 ? Result.getSuc() : Result.getError("");
if (ret.isSuc()) {
SqlGateResultPo parseResult = parseResult(parseObject);
if (parseResult == null) {// 超过我们限定的时间60秒还是没有得到结果
parseResult = new SqlGateResultPo();
parseResult.setErrors("查询结果超时：" + num * 2 + "秒");
ret = Result.getError("");
ret.setRetObjs(parseResult);
} else {
ret.setRetObjs(parseResult);
}
} else {// 错误的结果
SqlGateResultPo parseResult = new SqlGateResultPo();
String msg = null;
try {
ArrayNode errorObj =(ArrayNode) parseObject.get("errors");
int beginIndex = errorObj.get(1).asText().indexOf("org.apache.flink.table.api.ValidationException");
int endIndex = errorObj.get(1).asText().indexOf("\n", beginIndex);
msg = String.format("%s,原因：%s", errorObj.get(0).asText(),
errorObj.get(1).asText().subSequence(beginIndex + 47, endIndex));
} catch (Exception e) {
msg = parseObject.get("errors").asText();
}
parseResult.setErrors(msg);
ret.setRetObjs(parseResult);
}
return ret;
}
// 翻到下一页
public SqlGateResultPo next(SqlGateResultPo curPageDate) {
Result result = statementsResultByPage(curPageDate.getOperationHandle(), curPageDate.getCurPage() + 1, true);
if (result.isSuc()) {
SqlGateResultPo parseResult = (SqlGateResultPo) result.retObjs(0);
return parseResult;
}
return null;
}
private static SqlGateResultPo parseResult(ObjectNode retJson) {
if (retJson == null) {
return null;
}
SqlGateResultPo ret = new SqlGateResultPo();
ResultType resultType = ResultType.find(retJson.get("resultType").asText());
ret.setResultType(resultType);
ret.setNextResultUri(retJson.get("nextResultUri").asText());
ret.setJobID(retJson.get("jobID").asText());
ObjectNode resultsJson = (ObjectNode)retJson.get("results");
ArrayNode cols = (ArrayNode)resultsJson.get("columns");
for (int i = 0; i < cols.size(); i++) {
ObjectNode colJson = (ObjectNode)cols.get(i);
SqlGateResultCol temp = new SqlGateResultCol(colJson.get("name").asText(),
colJson.get("logicalType").get("type").asText());
temp.setComment(colJson.get("comment").asText());
temp.setLogicalNullable(colJson.get("logicalType").get("nullable").asBoolean());
if (colJson.get("logicalType").has("length")) {// {"nullable":true,"type":"BOOLEAN"}
temp.setLogicalLength(colJson.get("logicalType").get("length").asLong());
}
ret.getCols().add(temp);
}
ArrayNode datas = (ArrayNode)resultsJson.get("data");
for (int i = 0; i < datas.size(); i++) {
ObjectNode dataJson = (ObjectNode)datas.get(i);
// kind 暂不做处理
ArrayNode jsonArray = (ArrayNode)dataJson.get("fields");
String[] singleData = new String[jsonArray.size()];
for (int j = 0; j < jsonArray.size(); j++) {
if (jsonArray.get(j) != null) {
singleData[j] = String.valueOf(jsonArray.get(j));
}
}
ret.getCurData().add(singleData);
}
return ret;
}
public Result statementsResultForDDL(String sql, boolean needWaite) {
String operationHandle = statements(sql);
Result statementsRes = statementsResultByPage(operationHandle, 0, needWaite);
return statementsRes;
}
public String getCatalog() {
if (StringUtil.isNull(this.sqlCmdWrapper.getCatalog())) {
Result result = statementsResultForDDL(this.sqlCmdWrapper.getSql(SqlCmd.catalog_get), false);
if (result.isSuc()) {
SqlGateResultPo parseResult = (SqlGateResultPo) result.retObjs(0);
this.sqlCmdWrapper.setCatalog(parseResult.getSimpleData()[0]);
}
}
return this.sqlCmdWrapper.getCatalog();
}
public String getDb() {
if (StringUtil.isNull(this.sqlCmdWrapper.getDb())) {
Result result = statementsResultForDDL(this.sqlCmdWrapper.getSql(SqlCmd.db_get), false);
if (result.isSuc()) {
SqlGateResultPo parseResult = (SqlGateResultPo) result.retObjs(0);
this.sqlCmdWrapper.setDb(parseResult.getCurData().get(0)[0]);
}
}
return this.sqlCmdWrapper.getDb();
}
public void setCatalog(String catalog) {
Result result = statementsResultForDDL(this.sqlCmdWrapper.getSql(SqlCmd.catalog_set, catalog), false);
if (result.isSuc()) {
this.sqlCmdWrapper.setCatalog(catalog);
} else {
throw new ProjectExceptionRuntime(ExceptAll.Project_default, "设置catalog失败:" + result.getMessage());
}
}
public void setDb(String db) {
Result result = statementsResultForDDL(this.sqlCmdWrapper.getSql(SqlCmd.db_set, db), false);
if (result.isSuc()) {
this.sqlCmdWrapper.setDb(db);
} else {
throw new ProjectExceptionRuntime(ExceptAll.Project_default, "设置db失败:" + result.getMessage());
}
}
public Result execRs(SqlCmd sqlCmd, Object... params) {
// synchronized (this) {// 初始化catalog和db
// String catalog = getCatalog();
// this.sqlCmdWrapper.setCatalog(catalog);
// String db = getDb();
// this.sqlCmdWrapper.setDb(db);
// }
String sql = this.sqlCmdWrapper.getSql(sqlCmd, params);
return statementsResultForDDL(sql, true);// errors
}
public SqlGateResultPo exec(SqlCmd sqlCmd, Object... params) {
Result execRs = execRs(sqlCmd, params);
return (SqlGateResultPo) execRs.retObjs(0);
}
public SqlGateResultPo execPutCols(SqlCmd sqlCmd, String[] cols, Object... params) {
SqlGateResultPo retPo = exec(sqlCmd, params);
if (!retPo.isSuc()) {
return retPo;
}
retPo.getCols().clear();
for (String col : cols) {
SqlGateResultCol temp = new SqlGateResultCol(col);
retPo.getCols().add(temp);
}
return retPo;
}
public String getSessionHandle() {
return sessionHandle;
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy