一、实现hive钩子函数,解析hive日志
我这边写了MyHive类,继承LineageLogger类,实现ExecuteWithHookContext接口
package com.zsins.hook;
import com.zsins.hook.utils.HiveSqlBloodFigureUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.hadoop.hive.metastore.api.Database;
import org.apache.hadoop.hive.ql.QueryPlan;
import org.apache.hadoop.hive.ql.hooks.*;
import org.apache.hadoop.hive.ql.optimizer.lineage.LineageCtx;
import org.apache.hadoop.hive.ql.session.SessionState;
import org.codehaus.jackson.map.ObjectMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.*;
import java.util.Set;
public class MyHiveHook extends LineageLogger implements ExecuteWithHookContext {
private static Logger logger = LoggerFactory.getLogger(MyHiveHook.class);
// private static final HashSet<String> OPERATION_NAMES = new HashSet<>();
// static {
// // 建表
// OPERATION_NAMES.add(HiveOperation.CREATETABLE.getOperationName());
// // 修改数据库属性
// OPERATION_NAMES.add(HiveOperation.ALTERDATABASE.getOperationName());
// // 修改数据库属主
// OPERATION_NAMES.add(HiveOperation.ALTERDATABASE_OWNER.getOperationName());
// // 修改表属性,添加列
// OPERATION_NAMES.add(HiveOperation.ALTERTABLE_ADDCOLS.getOperationName());
// // 修改表属性,表存储路径
// OPERATION_NAMES.add(HiveOperation.ALTERTABLE_LOCATION.getOperationName());
// // 修改表属性
// OPERATION_NAMES.add(HiveOperation.ALTERTABLE_PROPERTIES.getOperationName());
// // 表重命名
// OPERATION_NAMES.add(HiveOperation.ALTERTABLE_RENAME.getOperationName());
// // 列重命名
// OPERATION_NAMES.add(HiveOperation.ALTERTABLE_RENAMECOL.getOperationName());
// // 更新列,先删除当前的列,然后加入新的列
// OPERATION_NAMES.add(HiveOperation.ALTERTABLE_REPLACECOLS.getOperationName());
// // 创建数据库
// OPERATION_NAMES.add(HiveOperation.CREATEDATABASE.getOperationName());
// // 删除数据库
// OPERATION_NAMES.add(HiveOperation.DROPDATABASE.getOperationName());
// // 删除表
// OPERATION_NAMES.add(HiveOperation.DROPTABLE.getOperationName());
// }
private static String toJson(Entity entity) throws Exception {
ObjectMapper mapper = new ObjectMapper();
switch (entity.getType()) {
case DATABASE:
Database db = entity.getDatabase();
return mapper.writeValueAsString(db);
case TABLE:
return mapper.writeValueAsString(entity.getTable().getTTable());
}
return null;
}
public void run(HookContext hookContext) {
// logger.info("this is my hive hook");
try{
assert (hookContext.getHookType() == HookContext.HookType.POST_EXEC_HOOK);
QueryPlan plan = hookContext.getQueryPlan();
LineageCtx.Index index = hookContext.getIndex();
SessionState ss = SessionState.get();
String operationName = plan.getOperationName();
logger.info("执行的SQL语句: " + plan.getQueryString());
LineageUtils lep = new LineageUtils();
lep.getLineageInfo(plan.getQueryString());
logger.info("Input tables = " + lep.getInputTableList());
logger.info("Output tables = " + lep.getOutputTableList());
logger.info("with tables = " + lep.getWithTableList());
if(!lep.getInputTableList().isEmpty()&&!lep.getOutputTableList().isEmpty()&&lep.getInputTableList().size()!=0&&lep.getOutputTableList().size()!=0){
HiveSqlBloodFigureUtil.parseAndCrud(plan.getQueryString());
}else{
logger.info("输出表或输入表为空,无需解析");
}
if(ss != null && index != null && !plan.isExplain() ) {
plan.getOutputs();
// if (OPERATION_NAMES.contains(operationName) && !plan.isExplain()) {
// logger.info("监控SQL操作");
// Set<ReadEntity> inputs = hookContext.getInputs();
Set<WriteEntity> outputs = hookContext.getOutputs();
//
for (Entity entity : inputs) {
logger.info("Hook metadata输入值: " + toJson(entity));
}
//
for (Entity entity : outputs) {
logger.info("Hook metadata输出值: " + toJson(entity));
}
} else {
logger.info("不在监控范围,忽略该hook!");
}
} catch ( Exception e) {
}
}
}
在"执行的SQL语句"处可以拿到执行的整条sql语句,然后结合hive sql解析工具类,实现整条sql语句的解析
这个解析工具类为LineageUtils
package com.zsins.hook;
import org.apache.hadoop.hive.ql.lib.*;
import org.apache.hadoop.hive.ql.parse.*;
import java.io.IOException;
import java.util.*;
public class LineageUtils implements NodeProcessor {
// 存放输入表
TreeSet<String> inputTableList = new TreeSet<String>();
// 存放目标表
TreeSet<String> outputTableList = new TreeSet<String>();
//存放with子句中的别名, 最终的输入表是 inputTableList减去withTableList
TreeSet<String> withTableList = new TreeSet<String>();
public TreeSet getInputTableList() {
return inputTableList;
}
public TreeSet getOutputTableList() {
return outputTableList;
}
public TreeSet getWithTableList() {
return withTableList;
}
@Override
public Object process(Node nd, Stack stack, NodeProcessorCtx procCtx, Object... nodeOutputs) throws SemanticException {
ASTNode pt = (ASTNode) nd;
switch (pt.getToken().getType()) {
//create语句
case HiveParser.TOK_CREATETABLE: {
String createName = BaseSemanticAnalyzer.getUnescapedName((ASTNode) pt.getChild(0));
outputTableList.add(createName);
break;
}
//insert语句
case HiveParser.TOK_TAB: {
// System.out.println(pt.getChildCount() + "tab");
String insertName = BaseSemanticAnalyzer.getUnescapedName((ASTNode) pt.getChild(0));
outputTableList.add(insertName);
// System.out.println("insertName " + insertName);
break;
}
//from语句
case HiveParser.TOK_TABREF: {
ASTNode tabTree = (ASTNode) pt.getChild(0);
String fromName = (tabTree.getChildCount() == 1) ? BaseSemanticAnalyzer.getUnescapedName((ASTNode) tabTree.getChild(0)) : BaseSemanticAnalyzer.getUnescapedName((ASTNode) tabTree.getChild(0)) + "." + tabTree.getChild(1);
inputTableList.add(fromName);
break;
}
// with.....语句
case HiveParser.TOK_CTE: {
for (int i = 0; i < pt.getChildCount(); i++) {
ASTNode temp = (ASTNode) pt.getChild(i);
String cteName = BaseSemanticAnalyzer.getUnescapedName((ASTNode) temp.getChild(1));
withTableList.add(cteName);
}
break;
}
}
return null;
}
public void getLineageInfo(String query) throws ParseException, SemanticException {
ParseDriver pd = new ParseDriver();
ASTNode tree = pd.parse(query);
while ((tree.getToken() == null) && (tree.getChildCount() > 0)) {
tree = (ASTNode) tree.getChild(0);
}
inputTableList.clear();
outputTableList.clear();
withTableList.clear();
Map<Rule, NodeProcessor> rules = new LinkedHashMap<Rule, NodeProcessor>();
Dispatcher disp = new DefaultRuleDispatcher(this, rules, null);
GraphWalker ogw = new DefaultGraphWalker(disp);
ArrayList topNodes = new ArrayList();
topNodes.add(tree);
ogw.startWalking(topNodes, null);
}
//进行测试,sql语句是瞎写的,但是语法是对的
public static void main(String[] args) throws IOException, ParseException, SemanticException {
//String query = "insert into qc.tables_lins_cnt partition(dt='2016-09-15') select a.x from (select x from cc group by x) a left join yy b on a.id = b.id left join (select * from zz where id=1) c on c.id=b.id";
// String query ="from (select id,name from xx where id=1) a insert overwrite table dsl.dwm_all_als_active_d partition (dt='main') select id group by id insert overwrite table dsl.dwm_all_als_active_d2 partition (dt='main') select name group by name";
String query = "CREATE TABLE dcl.dcl_cmp_crm_ddsupplier_feeback\n" +
"as\n" +
"select \n" +
" ddsupplier_feeback_id ,\n" +
" policy_no ,\n" +
" workorder_id ,\n" +
" user_name ,\n" +
" id_no ,\n" +
" policy_licenseno ,\n" +
" rescue_project ,\n" +
" rescue_name ,\n" +
" specification ,\n" +
" num ,\n" +
" use_num ,\n" +
" residue_num ,\n" +
" equity_id ,\n" +
" phone_no ,\n" +
" concrrnphone ,\n" +
" indent_status ,\n" +
" random_num ,\n" +
" sign_time ,\n" +
" supplier_no ,\n" +
" supplier_name ,\n" +
" sysource ,\n" +
" opercode ,\n" +
" opername ,\n" +
" createdate ,\n" +
" updatedate ,\n" +
" remake ,\n" +
" islocked ,\n" +
" use_time ,\n" +
" order_time ,\n" +
" apply_phone_no ,\n" +
" mile ,\n" +
" start_poi_name ,\n" +
" end_poi_name ,\n" +
" start_poi_address ,\n" +
" end_poi_address ,\n" +
" use_property ,\n" +
" seatcount ,\n" +
" payamount ,\n" +
" road_rescue_totalnum ,\n" +
" substitute_driver_totalnum ,\n" +
" behalf_inspection_totalnum ,\n" +
" vehicle_safety_totalnum ,\n" +
" reserve01 ,\n" +
" risk_code \n" +
" from ods.cmp_crm_ddsupplier_feeback";
LineageUtils lep = new LineageUtils();
lep.getLineageInfo(query);
System.out.println("Input tables = " + lep.getInputTableList());
System.out.println("Output tables = " + lep.getOutputTableList());
System.out.println("with tables = " + lep.getWithTableList());
}
}
二、实现结果
实现结果如下: