B站大数据系统诊断实践-SQLSCAN篇

随着B站业务的高速发展以及大数据平台产品生态的逐渐丰富，我们提供了数据开发，数据集成，数据服务，数据管理，数据安全，数据治理以及数据诊断等优秀的产品去构建开发和治理一体的全生命周期开发平台，提升数据开发效率和质量，加速数据分析效率。

^{图1 产品总览}

B站离线ETL和查询任务每天有几十万次，期间遇了多次数据质量相关的问题。

记得上半年数据开发同学向我们反馈线上数据质量问题导致业务报表丢失200万的用户数，调查下来发现是由于某个字段从int升级为bigint后由于发生了隐式转换导致的。这类数据质量问题我们了解下来在公司内部发生的频率并不低且很多未被及时发现，潜在的损失难以估量。

为什么会出现这些问题呢？

1. 缺少发布管控: 任务的发布缺少发布审核，涉及核心资产的任务变更缺少管控，任务发布上线更改随意，有时没有经过发布审核，甚至有些任务不经过测试就提交上线。SQLScan,  Code review相关环节持续交付过程中漏洞较多。

2. 缺少自动化的任务诊断: 无法在数据开发阶段真正发现任务潜在的风险，比如数据量扫描过多，Join有笛卡尔积等，资源损耗过大影响集群, 没办法做到事前治理。

为此我们开始着手搭建基于DataOps的全生命周期开发管理平台， 强调可持续集成，可持续交付和可持续部署。而SQLScan则是持续交付环节中非常重要的一个诊断工具。目的是做到事前治理，SQLScan融入到开发流程，摒弃先开发后治理，提高整体数据和SQL的质量。

SQLSCAN 是一款SQL领域静态代码扫描的工具, 它将任务开发中遇到的各种问题，如用户SQL的质量差、性能低、不遵守规范等，总结后形成规则，并通过系统及研发流程保障，事前解决故障隐患。

目前SQLSCAN主要可以审核三大类问题，包括代码规范类，代码质量类，代码性能类，可以识别但不限于以下SQL常见问题：

^{图2 SQLScan扫描项}

^图3

SQLSCAN 分别由解析, 编译, 服务三个模块构成见图3

1. 解析层主要是使用Antlr4解析不同引擎语法文件(目前支持了hive, spark, presto, flink语法), 然后通过访问者模式构建出一套通用的AST树（这样设计的目的是将解析和之后的编译和服务层解偶，因为编译和服务的逻辑相对稳定。AST转换逻辑随着不同引擎的差异而有所变化，所以接入方不用关心之后的编译和服务的实现）

2. 编译层主要是基于上一步产生的AST结合不同引擎所需要的元数据插件进行AST的遍历， 元数据服务我们提供了一套spi比如getTable, getPartition，getField 等，不同的引擎提供各自实现即可。对于有上下文场景下的多条语句，比如第一条是create table tbl(a int)，第二条是select a from tbl，因为SQLScan是事前解析，所以解析到第二条语句时候会找不到tbl元数据，我们的做法是在底层元数据服务之上会构建元数据缓存层，ctas产生的临时中间表会先注册到缓存层，下游语句会优先访问缓存层的meta，如果不存在再访问底层meta。同时在编译的过程中我们内置了一些系统级别的拦截规则如下:

• 表和字段是否存在

• 笛卡尔积

• CTE循环依赖

• 视图多层嵌套

• ANSI规范性检测

• 字段数量和类型不一致

3. 服务层作为定制化功能比较集中的模块主要提供了以下4点特性：

• 自定义的规则拦截（已经支持的规则可以参考图2）

• 成本分析（计算SQL扫描多少数据，SQL中涉及到的算子，产生多少费用）

• 表的访问权限控制（对接公司内部权限系统）

• 字段血缘（输出字段和输入字段的血缘传递关系）

^{图4 字段血缘}

我们这里取一个非常容易的case来描述下血缘计算的逻辑实现，首先通过前期的解析层我们会将不同引擎的语法文件统一生成如上的AST树见图4，然后进行血缘的遍历，这里我们将RegularQuery 看作一个完整的QueryBlock, 这个QueryBlock中AggClause的字段和SelectClause的字段都来源自FromClause节点下TableNameFromSource节点，TableNameFromSource节点则会从外部元数据加载其引用的表及字段，我们可以看到图中所有ColumnRefValue 中的col字段都来自上游的mid.table_t.col1字段，所以这个RegularQuery的输出字段就是 col1 as b，对于CreateTableAsSelectStatement节点其上游RegularQuery的输出字段就是下游TableName的输入字段，所以可以得到mid.table_t2.b 来源于mid.table_t.col1。

成本分析我们基于外部元数据(比如hive metastore, Clickhouse系统表)提供的Table/Column Statistic信息(表级别的Statistics信息包括行数，bytes，rows等，字段级别包括min, max, histogram, distinct count数等)结合编译后产生的字段画像（包含字段的取值范围，访问的分区明细等）进行前置的成本估算。如果发现statistics信息缺失会异步构建一个analyze table的sql请求发送给Presto集群进行表/分区/列信息采集记录到自己的元数据中，一方面下次查询时候成本预估会更准确，另一方面计算引擎本身也会利用这个信息做cbo执行计划优化。成本分析综合了作业输入量的大小，SQL算子个数（比如GroupBy, Join, SortBy等）来计算该作业的费用。

字段的成本评估我们基于以下的公式见图5

^图5

^{图6 作业成本计费}

我们的SQLScan分强规则和弱规则，强规则触发后会阻断任务的提交，用户需要按照诊断项修复SQL后再提交，弱规则下用户会看到相关检查错误提示，但是也可以继续跑任务。

SQLScan作为语句提交执行的一环，接入了trace系统中，以甘特图的方式展示SQLScan的执行耗时和检查结果。

效果案例：

1. 字段不存在

^{图7 字段不存在}

2. 违反ANSI规范

^{图8 违反ANSI规范}

^{图9 违反ANSI规范}

3.禁止某些DDL操作

^{图10 禁止truncate表}

目前SQLScan已经稳定上线8个月，日均支撑36万次的查询诊断，每日拦截SQL量2万，平均执行时间300ms, 有效保障了线上数据和SQL的高质量产出。

^{图11 规则命中效果}

对于敏感字段我们不希望暴露给用户，所以通过现有的血缘传递能力我们是可以将字段的敏感级别进行向上传递

^图12

adhoc场景下，有些用户会绕过ads/dws层直接访问底层的ods表做聚合查询，大表扫描会造成资源浪费，数据质量也不可控。我们希望SQLScan在检查到这张底层表的查询语义完全可以由更上层的ads表替换时候，告知用户，尽量提高ads层的查询覆盖度。ETL场景下，有时用户任务会跨层引用，比如ads层直接引用dwd/ods层，理论上跨层引用率越低越好，我们希望SQL中检查到有跨层引用的表的时候也能给到用户提醒。

在我们的仓库中，每个表列都被分配了一个固定列表中的“物理”类型，例如int或string。此外，每个列可以有一个可选的用户自定义类型；虽然它不影响数据在磁盘上的编码方式，但这个类型可以提供字段的语义信息（例如，电子邮件、毫秒级时间戳或用户ID）。

SQLSCAN可以利用这些用户定义的类型语义信息来改善对SQL查询的静态类型检查。比如一个SQL查询用户可能希望将两个包含不同登录事件信息的表的数据进行UNION操作，其中一个是timestamp milliseconds，另一个是timestamp nanoseconds，这两者虽然都是timestamp但是语义不一样，就会给到用户提醒。

[1] Enabling static analysis of SQL queries at Meta（https://engineering.fb.com/2022/11/30/data-infrastructure/static-analysis-sql-queries/）

[2] 网易数据开发SQL Scan实践（https://www.bilibili.com/video/BV1LP411v72f）

[3] 先设计后开发，先标准后建模，网易 DataOps 实践

[4] 大数据之路阿里巴巴大数据实践

在本篇文章中，我们主要介绍了B站大数据系统诊断在SQLScan所做的改造和阶段性成果，主要关注任务的事前治理，对于事中和事后的治理诊断，请关注后续文章《BMR大数据元仓》以及《大数据任务诊断系统-数据智囊》

以上是今天的分享内容，如果你有什么想法或疑问，欢迎大家在留言区与我们互动，如果喜欢本期内容的话，欢迎点个“在看”吧！