在人群画像、离线数仓、报表分析体系中会涉及大量的sql录入、验证、压缩、解析、自动维护数仓的血缘关系,确保录入数据的的准确性。我们需要一些自动化工具校验、识别,维护相关数据确保录入数据的准确性。
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.6</version> </dependency>
alibaba druid封装的小而美,jdbc的查询性能也很高,而且生态相关的工具比较全面。springboot 官方推荐[HikariCP],大家可以根据自己的喜好选择使用那款jdbc jar。
sql format 支持多种数据源解析,非常全面。一行代码搞定,详细如下:
SQLUtils.format(sql, JdbcConstants.HIVE);
sql parse 实现比较强大。识别表的查询类型,支持多种数据源。相关代码实现如下:
/** * 解析sql中的表,库,操作 * 查询过程中表名一定是库名.表名 * @param sql * @return */ public static Map<String,Object> sqlParser(String sql) { Map<String,Object> resultMap = Maps.newHashMap(); SQLStatementParser parser = new HiveStatementParser(sql); SQLStatement statement = parser.parseStatement(); HiveSchemaStatVisitor visitor = new HiveSchemaStatVisitor(); statement.accept(visitor); Map<TableStat.Name, TableStat> tableOpt = visitor.getTables(); Set<String> tableSet = new HashSet<>(); String sinkTable = null; for (TableStat.Name tableStatKey : tableOpt.keySet()) { if (null != tableStatKey ) { String key = tableStatKey.toString(); if(key.contains(".")){ String operation = visitor.getTableStat(key).toString(); if(TableStat.Mode.Insert.name().equals(operation)){ sinkTable = key; }else { tableSet.add(key); } } } } resultMap.put("sourceTableList",tableSet); resultMap.put("sinkTable",sinkTable); return resultMap; }
<dependency> <groupId>com.github.mnadeem</groupId> <artifactId>sql-table-name-parser</artifactId> <version>0.0.5</version> </dependency>
SQL Format 其核心剔除\r\n、把多个空格替换为一个空格。详细如下:
/** * sql format 处理 * @param sql * @return */ public static String formatSql (String sql) { String fromatSql = null ; if(StringUtils.isBlank(sql)){ return fromatSql ; } fromatSql = sql; fromatSql = fromatSql.trim(); fromatSql = fromatSql.toLowerCase(); fromatSql = fromatSql.replaceAll("\r\n", " ").replaceAll("\r"," ").replaceAll("\n"," ").replaceAll(" +"," "); return fromatSql; }
sql-table-name-parser 它的识别能力还是一定的偏弱,不能识别sink 表,以及with等相关实现。我在使用的过程中简单处理一下相关逻辑如下:
private static final String WITH_PREFIX = "with "; private static final String SELECT_PREFIX =" select "; private static final String INSERT_INTO_PREFIX ="insert into"; /** * sql format 处理 * @param formatSql * @return */ public static Map<String,Object> getTableNameMap(String formatSql) throws Exception{ Map<String,Object> resultMap = Maps.newHashMap(); if(StringUtils.isBlank(formatSql)){ return resultMap; } List<String> sqlList = JSON.parseArray(formatSql.toLowerCase(),String.class); Set<String> tableSet = new HashSet<>(); String sinkTable = null; for (String sql : sqlList){ sql = formatSql(sql); if(sql.startsWith(WITH_PREFIX)){ throw new Exception(" sql format not support with keyword sql"); } //处理 insert into table 无法解析的问题 if(sql.startsWith(INSERT_INTO_PREFIX)){ sql = sql.replaceAll(INSERT_INTO_PREFIX,"insert overwrite "); } TableNameParser tableNameParser = new TableNameParser(sql); Collection<String> tables = tableNameParser.tables(); tableSet.addAll(tables); String sinkSql = sql.substring(0,sql.indexOf(SELECT_PREFIX)); TableNameParser sinkTableNameParser = new TableNameParser(sinkSql); Collection<String> sinkTables = sinkTableNameParser.tables(); if(CollectionUtils.isNotEmpty(sinkTables) && StringUtils.isEmpty(sinkTable)){ List<String> sinkTableList = Lists.newArrayList(sinkTables); sinkTable = sinkTableList.get(0); tableSet.remove(sinkTable); } resultMap.put("sourceTableList",tableSet); resultMap.put("sinkTable",sinkTable); } return resultMap; }
Sql Format & Sql 解析在大数据场景中应用还是比较广泛的。把底层的标准建设好,后面引入其他的组件还是比较方便的。整体对比下来推荐使用alibaba druid, 如果大家有其他更好的组件欢迎大家一起交流。