一、背景
在系统的版本发现过程,随需求的叠加,出现了多次版本的升级脚本填写不对或忘写的情况,虽然项目内严格要求研发必须针对SQL脚本手动执行且无报错前提下,才能验证通过。但效果仍然较差,基于此,组件急需一种可以自动化校验的能力。
参考如下链接中,使用了自已解析SQL进行语法的校验。
https://www.pudn.com/news/628f8474bf399b7f351eff74.html
二、依赖
系统提供一前置检查框架,所以在前置检查框架中叠加对应的校验项即可,通过技术选型,使用alibaba的SQLParserUtils进行检查。依赖的POM
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.11</version>
</dependency>
Druid 是阿里巴巴开源平台上一个数据库连接池实现,结合了 C3P0、DBCP 等 DB 池的优点,同时加入了日志监控。
Druid 可以很好的监控 DB 池连接和 SQL 的执行情况,天生就是针对监控而生的 DB 连接池。
Druid已经在阿里巴巴部署了超过600个应用,经过一年多生产环境大规模部署的严苛考验。
Spring Boot 2.0 以上默认使用 Hikari 数据源,可以说 Hikari 与 Driud 都是当前 Java Web 上最优秀的数据源,我们来重点介绍 Spring Boot 如何集成 Druid 数据源,如何实现数据库监控。
Github地址:https://github.com/alibaba/druid/
public static void format(String sql, DbType dbType) {
String sqlFormat = SQLUtils.format(sql, dbType);
if (sql.equals(sqlFormat)) {
throw new RuntimeException("SQL格式错误");
}
}
三、测试
public static void main(String[] args) {
String sql = "FROM (SELECT p.datekey datekey, p.userid userid, c.clienttype FROM detail.usersequence_client c JOIN fact.orderpayment p ON p.orderid = c.orderid JOIN default.user du ON du.userid = p.userid WHERE p.datekey = 20131118 ) base INSERT OVERWRITE TABLE `test`.`customer_kpi` SELECT base.datekey, base.clienttype, count(distinct base.userid) buyer_count GROUP BY base.datekey, base.clienttype";
SQLStatementParser hive = SQLParserUtils.createSQLStatementParser(sql, DbType.hive);
SQLStatement statement = hive.parseStatement();
System.out.println(statement);
// as you can see , using this parseDriver will cause an error
// ParseDriver pd = new ParseDriver();
// ASTNode ast = pd.parse(sql);
// System.out.println(ast.dump());
sql = "create index IDX_ltp_basic_info_inventory_name on ltp_basic_info(inventory_name)";
PgSqlChecker.format(sql, DbType.postgresql);
SQLStatementParser pg = SQLParserUtils.createSQLStatementParser(sql, DbType.postgresql);
statement = pg.parseStatement();
System.out.println(statement);
}
在具体使用中,发现是存在SQL语句一定的误判,比如:
create index IDX_ltp_basic_info_inventory_name on ltp_basic_info(inventory_name varchar_pattern_ops)
ALTER TABLE eth_resource ADD COLUMN if not exists tpid_set_pg TEXT
系统中,所以对于特定不支持的语法SQL,代码中进行了人为的异常保护。
一些常见的低级问题还是可以检查出来,将功能嵌入到微服务的前置检查中,一定程度上可以避免人为的失误。
四、实践
- 当sql语句中有ALTER和IF时会检测会不通过
- token IDENTIFIER varchar_pattern_ops
private boolean sqlCheck(String sql) {
SQLStatementParser parser = null;
if (isUnSupportSyntax(sql)) {
return true;
}
try {
parser = SQLParserUtils.createSQLStatementParser(sql, "postgresql");
parser.parseStatementList();
} catch (ParserException e) {
String error = e.getMessage();
// 当sql语句中有ALTER和IF时会检测会不通过,例如:ALTER TABLE eth_resource ADD COLUMN if not exists tpid_set_pg TEXT;
// 会报异常:illegal name, pos 14, line 1, column 13, token IF
if (error.startsWith("illegal name") && error.endsWith("token IF")) {
return true;
}
//无法校验create index IDX_ltp_basic_info_lower_fd_ref on ltp_basic_info(lower_fd_ref varchar_pattern_ops);
if (error.endsWith("token IDENTIFIER varchar_pattern_ops")) {
return true;
}
LOG.error("SQL is {}, 转换中发生了错误:{} ", sql, e.getMessage());
checkResult = false;
return false;
}
return true;
}