大數(shù)據(jù)場景下，每天可能都要在離線集群，運(yùn)行大量的任務(wù)來支持產(chǎn)品、運(yùn)營的分析查詢。任務(wù)越來越多的時候，就會有越來越多的依賴關(guān)系，每一個任務(wù)都需要等需要的input表生產(chǎn)出來后，再去生產(chǎn)自己的output表。最開始的時候，依賴關(guān)系自然是可以通過管理員來管理，隨著任務(wù)量的加大，就需要一個分析工具來解析SQL的血緣關(guān)系，并且自行依賴上血緣表。

本文就介紹一個使用druid parser，來解析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