大數(shù)據(jù)場(chǎng)景下婆赠，每天可能都要在離線集群绵脯，運(yùn)行大量的任務(wù)來(lái)支持產(chǎn)品、運(yùn)營(yíng)的分析查詢页藻。任務(wù)越來(lái)越多的時(shí)候桨嫁，就會(huì)有越來(lái)越多的依賴關(guān)系，每一個(gè)任務(wù)都需要等需要的input表生產(chǎn)出來(lái)后份帐，再去生產(chǎn)自己的output表。最開(kāi)始的時(shí)候楣导，依賴關(guān)系自然是可以通過(guò)管理員來(lái)管理废境，隨著任務(wù)量的加大，就需要一個(gè)分析工具來(lái)解析SQL的血緣關(guān)系筒繁，并且自行依賴上血緣表噩凹。
本文就介紹一個(gè)使用druid parser，來(lái)解析SQL的血緣關(guān)系毡咏。
一驮宴、SQL血緣關(guān)系含義
SQL血緣關(guān)系的含義是將sql中包含的表全部輸出。
例：如下sql呕缭，需要解析出該sql包含supindb.student堵泽、supindb.college兩張表。
String sql = "select * from " +
"(select * from supindb.student d where dt='20190202')a " +
"left join " +
"(select * from supindb.college c where dt='20190202')b " +
" on a.uid=b.uid " +
"where a.uid > 0";
二恢总、SQL血緣關(guān)系解析實(shí)現(xiàn)
- SQL血緣關(guān)系解析pom依賴
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.14</version>
</dependency>
- SQL血緣關(guān)系解析代碼
SQL血緣關(guān)系解析代碼僅解析select迎罗、update、insert片仿、delete四種sql纹安，其他種類，可依據(jù)需求自行添加相應(yīng)邏輯。
public class Parent {
public static Map<String, TreeSet<String>> getFromTo (String sql) throws ParserException {
Map<String, TreeSet<String>> result = new HashMap<String, TreeSet<String>>();
List<SQLStatement> stmts = SQLUtils.parseStatements(sql, JdbcConstants.HIVE);
TreeSet<String> selectSet = new TreeSet<String>();
TreeSet<String> updateSet = new TreeSet<String>();
TreeSet<String> insertSet = new TreeSet<String>();
TreeSet<String> deleteSet = new TreeSet<String>();
if (stmts == null) {
return null;
}
String database = "DEFAULT";
for (SQLStatement stmt : stmts) {
SchemaStatVisitor statVisitor = SQLUtils.createSchemaStatVisitor(stmts,JdbcConstants.HIVE);
if (stmt instanceof SQLUseStatement) {
database = ((SQLUseStatement) stmt).getDatabase().getSimpleName();
}
stmt.accept(statVisitor);
Map<TableStat.Name, TableStat> tables = statVisitor.getTables();
if (tables != null) {
final String db = database;
for (Map.Entry<TableStat.Name, TableStat> table : tables.entrySet()) {
TableStat.Name tableName = table.getKey();
TableStat stat = table.getValue();
if (stat.getCreateCount() > 0 || stat.getInsertCount() > 0) { //create
String insert = tableName.getName();
if (!insert.contains("."))
insert = db + "." + insert;
insertSet.add(insert);
} else if (stat.getSelectCount() > 0) { //select
String select = tableName.getName();
if (!select.contains("."))
select = db + "." + select;
selectSet.add(select);
}else if (stat.getUpdateCount() > 0 ) { //update
String update = tableName.getName();
if (!update.contains("."))
update = db + "." + update;
updateSet.add(update);
}else if (stat.getDeleteCount() > 0) { //delete
String delete = tableName.getName();
if (!delete.contains("."))
delete = db + "." + delete;
deleteSet.add(delete);
}
}
}
}
result.put("select",selectSet);
result.put("insert",insertSet);
result.put("update",updateSet);
result.put("delete",deleteSet);
return result;
}
public static void main(String[] args) {
String sql = "select * from " +
"(select * from supindb.student d where dt='20190202')a " +
"left join " +
"(select * from supindb.college c where dt='20190202')b " +
" on a.uid=b.uid " +
"where a.uid > 0";
//sql = "update supindb.college set uid='22333' where name='小明'";
//sql = "delete from supindb.college where uid= '22223333'";
Map<String, TreeSet<String>> getfrom = getFromTo(sql);
for (Map.Entry<String, TreeSet<String>> entry : getfrom.entrySet()){
System.out.println("================");
System.out.println("key=" + entry.getKey());
for (String table : entry.getValue()){
System.out.println(table);
}
}
}
}
-
運(yùn)行結(jié)果
image.png
