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（）

[2] 網易資料開發SQL Scan實踐（）

[3] 先設計後開發，先標準後建模，網易 DataOps 實踐

[4] 大資料之路阿里巴巴大資料實踐

在本篇文章中，我們主要介紹了B站大資料系統診斷在SQLScan所做的改造和階段性成果，主要關注任務的事前治理，對於事中和事後的治理診斷，請關注後續文章《BMR大資料元倉》以及《大資料任務診斷系統-資料智囊》