正则表达式解析StarRocks雾化视图中的血缘关系

解析SQL中的底表

主要目标是获取出StarRocks雾化中的底表和字段备注，之后给字段赋予备注值，存入库表，可以动态生成数据字典，web可以利用该表
实现mybatis的动态sql拼接，动态化的excel导出导入，魔板等功能。尝试使用了Jsqlparser解析sql语句，发现遇到部分
复杂的子查询内包含union all情况解析非常慢，解析完成20多个视图需要至少3min不能满足集成到web的需求，尝试了Calcite
这块有待于进一步研究，解析中构造语法树ast就出现了问题，需要细细研究javacc编译器等，复杂度上升，直接利用正则表达式来搞定。

calcite解析中遇到的问题
一种是case when (left() = 'bbb') then 无法识别（left
另外一种是 interval （ 7 day) 无法解析 interval (
如果单纯的避免关键字，采用反引号注释掉，后面还有个 7 day,无法解析。
效率较Jsqlparser高很多

代码中部分是无用的，使用时候看一下吧

import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;

import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ViewDictByRege {

    private static Connection conn = null;

    private static Map<String, List<String>> viewMapLists = new HashMap<>();

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        try {
            conn = DriverManager.getConnection("jdbc:mysql://starip:9030/ic_cyb_cdm", "staruser", "123456");

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //1、show  MATERIALIZED VIEW from ic_cyb_cdm  where name like '%_v'  查询所有的物化视图
//    2、show create   table   ic_cyb_cdm.cyb_dwd_sales_week_global_v 查询字段构建map
//    3、解析出所有的底表
//    4、遍历底表构建所有字段的map，取有备注的赋值
//    5、比对给目标map赋值
//    todo 待优化项，加数据库连接池
    public static void main(String[] args) {
        long start = System.currentTimeMillis();
        getMeterialViewDDL();
        long end = System.currentTimeMillis();
        System.out.println("检测耗费时间"+(end-start));
    }

    public static Map<String, String> getMeterialViewDDL() {
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement("show  MATERIALIZED VIEW from ic_cyb_cdm  where name like '%_v'");
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                String viewName = "ic_cyb_cdm." + rs.getString(2);
                String viewDdl = rs.getString(4).trim();
                String viewLower = viewDdl.toLowerCase(Locale.ROOT);
                if (viewLower != null) {
                    int select = viewLower.indexOf("select");
                    String viewSelect = viewLower.substring(select);
//                    inner table remarks
                    Set<String> viewUnderTables = parserTableName(viewSelect, viewName);
                    String colRemarksSql = getRemarksSql(viewUnderTables);
                    Map<String, String> innerRemarksMap = tableRemarks(colRemarksSql);
//                   star view remarks
                   String viewRemarksSql = getColRemarksSql(viewName);
                    Map<String, String> viewStarRemarksMap = tableRemarks(viewRemarksSql);
                    for (Map.Entry<String, String> entry : viewStarRemarksMap.entrySet()) {
                        String columnName = entry.getKey();
                        String columnRemark = entry.getValue();
                        if ("".equalsIgnoreCase(columnRemark)) {
                            if (innerRemarksMap.containsKey(columnName)) {
                                entry.setValue(innerRemarksMap.get(columnName));
                            } else if ("search_time".equalsIgnoreCase(columnName)) {
                                entry.setValue("搜索时间");
                            } else if ("uuid".equalsIgnoreCase(columnName)) {
                                entry.setValue("uuid");
                            } else {
                                entry.setValue("暂无备注名，请手动添加");
                            }
                        }

                    }
//                    pg view remarks
                    String pgViewColRemark = "select tfi.field ,tfi.notes  from zcb_dev.t_table_info tti " +
                            " left join zcb_dev.t_field_info tfi on tti.id  = tfi.table_id" +
                            " where tti.table_name = '" + viewName.split("\\.")[1] + "'";
                    Map<String, String> pgColRemark = pgTableRemark(pgViewColRemark);
                    if (pgColRemark.size() > viewStarRemarksMap.size()) {
//                    说明有字段删除，匹配出来删除的字段做提示，手动人工pg库表去删除该字段
                        String delColumns = compareFieldMap(pgColRemark, viewStarRemarksMap);
                        System.out.println("需要在pg库t_filed_info表内删除视图：" + viewName + "的字段和备注有：" + delColumns);

                    } else if (pgColRemark.size() < viewStarRemarksMap.size()) {
//                        说明有新增字段，新增字段直接插入
                        String allColumns = compareFieldMap(viewStarRemarksMap, pgColRemark);
                        System.out.println("需要在pg库t_filed_info表内添加视图：" + viewName + "的字段和备注有：" + allColumns);
                    } else {
//                        字段数没有变化，如果pg字段为空，则做修改，不然不用修改
                        String diffRemarks = compareValueMap(viewStarRemarksMap, pgColRemark);
                        if(!"".equalsIgnoreCase(diffRemarks)){
                            System.out.println("需要在pg库t_filed_info表内修改视图：" + viewName + "的字段和备注有：" + diffRemarks);
                        }else {
                            System.out.println("pg库t_filed_info表内没有需要修改的字段和备注视图：" + viewName);
                        }


                    }
                }

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                pst.close();
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        return null;

    }

    /**
     * 比对map的值，如果map的值不一致，返回不一致的结构
     *
     * @param oriViewMap
     * @param targetRemark
     * @return
     */
    private static String compareValueMap(Map<String, String> oriViewMap, Map<String, String> targetRemark) {
        String diffRemarks = "";
        for (Map.Entry<String, String> entry : oriViewMap.entrySet()) {
            String columnName = entry.getKey();
            String columnRemark = entry.getValue();
//            如果pg库里面是空
            if("".equalsIgnoreCase(targetRemark.get(columnName)) || null == targetRemark.get(columnName)){
                diffRemarks += "columnName:" + columnName + "  view new remark: " + columnRemark + "  pg column remark: " + targetRemark.get(columnName);
//                System.out.println("columnName:" + columnName + " view new remark: " + columnRemark + "   pg column remark: " + targetRemark.get(columnName));
            }
        }
        return diffRemarks;
    }

    /**
     * 原始map和目标map做比对，返回原始map中的不同的值
     *
     * @param oriMap
     * @param targetMap
     */
    private static String compareFieldMap(Map<String, String> oriMap, Map<String, String> targetMap) {
        String compareResult = "";
        for (Map.Entry<String, String> pgMap : oriMap.entrySet()) {
            String colName = pgMap.getKey();
            String colRemark = pgMap.getValue();
            if (!targetMap.containsKey(colName)) {
                compareResult += (" colName: " + colName + " remark: " + colRemark);
            }
        }
        return compareResult;
    }

    //待优化
    private static Map<String, String> pgTableRemark(String pgViewColRemark) {
        Map<String, String> colRemarkMap = new HashMap<>();
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        try {
            Connection conn = DriverManager.getConnection("jdbc:postgresql://pgip:5432/z_test", "user", "#123");
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(pgViewColRemark);

            while (rs.next()) {
                String colName = rs.getString(1);
                String colRemarks = rs.getString(2);
//                System.out.println(colName + "----" + colRemarks);
                colRemarkMap.put(colName, colRemarks);

            }

            statement.close();
            conn.close();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return colRemarkMap;
    }

    private static Map<String, String> tableRemarks(String colRemarksSql) throws SQLException {
        PreparedStatement preparedColumns = conn.prepareStatement(colRemarksSql);
        ResultSet remarkRs = preparedColumns.executeQuery();
        Map<String, String> innerRemarks = new HashMap<>();
        while (remarkRs.next()) {
            String columnName = remarkRs.getString(3);
            String columnRemarks = remarkRs.getString(4);
            innerRemarks.put(columnName, columnRemarks);
        }

        try {
            preparedColumns.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return innerRemarks;
    }

    private static String getRemarksSql(Set<String> viewUnderTables) {
        return getColRemarksSql(viewUnderTables);
    }


    // 目标获取 视图对应的底表名
    public static Set<String> parserTableName(String sql, String viewName) {
        // 使用正则表达式匹配表名称
        Pattern pattern = Pattern.compile("(?:from|join)\\s+(\\w+.\\w+)");
        Matcher matcher = pattern.matcher(sql.replaceAll("`", "").replaceAll("hive.", "").toLowerCase(Locale.ROOT));
        Set<String> underTables = new HashSet<>();
        while (matcher.find()) {
            String tableName = matcher.group(1);
//            System.out.println(viewName + "    Regluar tableName: " + tableName);//  ic_cyb_ods.cyb_ods_byd_week_production_ds_s
            underTables.add(tableName);
        }
        return underTables;
    }

    /*select  table_schema,table_name,COLUMN_NAME ,COLUMN_COMMENT   from information_schema.columns where 1=1 and  COLUMN_COMMENT is not null  and COLUMN_COMMENT != ''  and
    ((table_schema ='ic_cyb_ods' and TABLE_NAME ='cyb_ods_t_api_byd_used_car_ds_s') or  (table_schema ='ic_cyb_cdm' and TABLE_NAME ='cyb_dwd_catarc_sales_business_v'))
    */
    public static String getColRemarksSql(Set<String> innerTableSet) {
        String whereSql = "select  table_schema,table_name,COLUMN_NAME ,COLUMN_COMMENT   from information_schema.columns where 1=1 and  COLUMN_COMMENT is not null  and COLUMN_COMMENT != '' ";
        if (!innerTableSet.isEmpty()) {
            whereSql += " and (";

            String[] innerTables = innerTableSet.toArray(new String[innerTableSet.size()]);
            for (int i = 0; i < innerTables.length; i++) {
                String innerTable = innerTables[i];
                String[] split = innerTable.split("\\.");
                String schema = split[0];
                String tableName = split[1];
                if (i < innerTables.length - 1) {
                    whereSql += "(table_schema ='" + schema + "'" + "  and TABLE_NAME = '" + tableName + "') or ";
                } else {
                    whereSql += "(table_schema ='" + schema + "'" + "  and TABLE_NAME = '" + tableName + "') ) ";
                }
            }
        }
//        System.out.println("备注Sql查询：  " + whereSql);

        return whereSql;
    }


    public static String getColRemarksSql(String wholeTableName) {
        String whereSql = "select  table_schema,table_name,COLUMN_NAME ,COLUMN_COMMENT  " +
                " from information_schema.columns where 1=1 ";
        String[] split = wholeTableName.split("\\.");
        String schema = split[0];
        String tableName = split[1];
        whereSql += " and table_schema ='" + schema + "'" + "  and TABLE_NAME = '" + tableName + "' ";
        return whereSql;
    }


    public static void getAllColumns(Select selectStatement, Map<String, String> aliasMap, Map<String, String> remarkMap) {
        PlainSelect plainSelect = (PlainSelect) selectStatement.getSelectBody();
        List<SelectItem> selectItems = plainSelect.getSelectItems();
//        System.out.println(((SelectExpressionItem)selectItems.get(0)).getAlias().getName());
//获取字段名
        System.out.println("====================");
        for (SelectItem selectItem : selectItems) {
//            SimpleNode astNode = selectItem.
            // 处理每个选项，这里假设只有一列为ColumnSelectExpression。
            if (selectItem instanceof SelectExpressionItem) {
                SelectExpressionItem expressionItem = (SelectExpressionItem) selectItem;
                // 获取列名
                String columnName = expressionItem.getExpression().toString();
                String columnNameOri = null;
//                包含函数的字段肯定是加工出来的，这种备注在原表内无法匹配到需要手动维护备注名称，不包含函数的是可以原表内找到字段的
                if (columnName.contains(".") && !columnName.contains("(")) {
                    int split_char = columnName.lastIndexOf(".");
                    columnNameOri = columnName.substring(split_char + 1);
                } else {
                    columnNameOri = columnName;
                }

                Alias alias = expressionItem.getAlias();
                if (alias != null) {
// 带有别名的处理      Column original Name: `order_num`  aliasName:`order_id`
                    String aliasName = alias.getName();
                    aliasMap.put(aliasName, columnNameOri);
                    remarkMap.put(aliasName, "无");//暂时为空
//                    System.out.println("Column Name: " + columnName + "    Column original Name: " + columnNameOri + "  aliasName:" + alias.getName());
                } else {
                    remarkMap.put(columnNameOri, "无");//暂时为空
//                    System.out.println("Column Name: " + columnNameOri);
                }

            }
        }

    }


}