如何實現一個跨庫連表SQL生成器？
文末福利：藏經閣100本電子書免費下載。
一 概述
ADC（Alibaba DChain Data Converger）專案的主要目的是做一套工具，使用者在前端簡單配置下指標後，就能在系統自動生成的大寬表裡面查詢到他所需要的實時資料，資料來源支援跨庫並支援多種目標介質。說的更高層次一點， 資料的全域性實時視覺化這個事情本身就是解決供應鏈資料“神龍效應”的有效措施（參考施雲老師的《供應鏈架構師》[1]一書）。做ADC也是為了這個目標，整個ADC系統架構如下圖所示：
初始資料來自於元資料中心。
經過元資料適配層後轉換為內部格式資料。
排程中心把內部格式的資料傳到計劃中心，計劃中心分析資料需求並建模，透過SQL生成器生成資源和SQL，分別透過告警中心、對賬中心設定監控標準和對賬標準。
對賬中心定時對賬，檢視資料的對齊情況。
告警中心可以針對任務錯誤、延遲高等情況傳送報警。
資源的生命週期管控在資源管理中心下，view刪除時資源管理中心負責回收資源。
基礎資源適配層主要藉助集團基礎資源管理能力串聯阿里各類資料服務， 比如阿里雲MaxComputer、Flink、阿里雲AnalyticDB等。
其中，SQL生成器的上游和下游主要涉及：
上游計劃中心
配置指標：使用者在前端配置他想看的資料有哪些。
生產原始資料：根據使用者輸入得到哪些表作為資料來源， 以及它們之間的連線關係。
下游Metric介面卡
把SQL釋出到Flink， 根據建表資料建物理表。
本文主要從技術角度介紹下SQL生成器相關的內容。
二 技術實現
在專案實施階段，需要從需求分析、技術方案設計、測試聯調幾個步驟展開工作。本文重點不放在軟體開發流程上， 而是就設計模式選擇和資料結構演算法設計做下重點講解。
需求分析
在需求分析階段， 我們明確了自動生成SQL模組所需要考慮的需求點， 主要包含如下幾點：
需要支援多個事實表（流表）、多個維度表連表，其中一個事實表是主表，其他的均為輔助表。
維表變動也應當引起最終資料庫更新。
主表對輔助表為1:1或N1，也就是說主表的粒度是最細的， 輔表透過唯一鍵來和主表連線。
流表中可能存在唯一鍵一致的多張流表， 需要透過全連線關聯。唯一鍵不同的表之間透過左連線關聯。
只有連表和UDF，沒有groupby操作。
要求同步延時較小，支援多種源和目標介質。由於查詢壓力在目標介質，所以查詢qps沒有要求。
系統流程圖
明確需求後， 我們把SQL生成器總體功能分為兩塊：
同步生成SQL和建表資料
非同步釋出SQL和建表
之所以把生成SQL階段做成同步是因為同步階段記憶體操作為主，如果發現數據有問題無法生成SQL能做到快速失敗。釋出階段呼叫Metrics需要同步等待較長時間， 每個釋出步驟要做到有狀態記錄， 可回滾或者重試。所以非同步實現。SQL生成器同步階段的整體功能細化到小模組，如下圖所示：
檢查原始資料是否有問題， 無法生成SQL則快速失敗。
引數檢查：檢查上游是否提供了基本的引數， 比如事實表資訊（可以沒有維表， 但是必須有事實表）。
表型別檢查：檢查資料來源型別是否支援。
分割槽欄位檢查：是否提供了大寬表分割槽欄位。
連線約束：檢查流表，維表連線資訊是否正確。
主表唯一性約束：檢查主表是否含連線資訊，唯一鍵是否有ETL資訊。
元資料檢查：檢查是否包含HBase配置資訊。
主鍵修正：修正維表連線鍵， 必須是維表的唯一鍵。
資料同步
同步所有原始表和原始表的連線資料（比如源表同步進來， 生成1:1的HBase表）。
生成優先順序佇列：生成連線和釋出等任務的執行優先順序。
同步填充：填充源表對應的同步階段HBase表資料，和對應的配置項， 型別轉換（比如源表是MySQL表，欄位型別要轉換為HBase的型別）， ETL填充， 新增訊息佇列（透過傳送訊息的方式通知下游節點執行）。
重複列修剪：刪除重複的列。
空白列打標：對於滿足一定條件（比如不需要在大寬表展示， 不是唯一鍵列， 連線鍵列， 保序列）的列打上空白列標識。
保序欄位填充：如果上游提供了表示資料建立時間的欄位， 則用該欄位作為資料保序欄位， 沒有則填充系統接收到資料的時間作為保序欄位。
計算階段
生成大寬表，填充SQL。
中間表填充：填充全連線產生的中間表。
連線關係升級：會在本文後面說明。
反向索引填充：填充“反向索引”資訊。
訊息填充：中間表新增訊息佇列（中間表更新可以觸發下游節點）。
大寬表填充：填充大寬表資料。
連線鏈對齊：中間表和大寬表連線鍵對齊。
ETL填充：填充大寬表列的ETL資訊。
分割槽欄位填充：填充大寬表分割槽欄位。
SQL填充：填充Flink同步表對映SQL語句， Flink計算SQL語句， Flink結果表對映SQL語句。
儲存：把SQL和建表資料存入資料庫， 之後的請求可以複用已有的資料， 避免重複建表。
非同步釋出階段會把SQL語句釋出到Flink。
新增反向索引的原因
假如有A、B兩表連線，那麼連線方式為A表的非主鍵連線B表主鍵。從時序上來說可能有以下三種情況：
B表資料先於A表資料多天產生
B表資料後於A表資料多天產生
B表資料和A表資料同時產生
下面我們就這三種情況逐一分析。
場景1：B表資料先於A表資料多天產生
我們假如B表資料儲存於某個支援高qps的資料庫內，我們可以直接讓A表資料到來時直接連線此表（維表）來實現連表。
場景2：B表資料後於A表資料多天產生
這種場景比較麻煩。A表資料先行產生，因此過早的落庫，導致B表資料到來時即使連線B維表也拿不到資料。這種場景還有一個類似的場景：如果AB連線完成後B發生了更新，如何讓B的更新體現在寬表中？
為了解決這種問題，我們增加了一個“反向索引表”。假如A的主鍵是id，連線鍵是ext_id,那麼我們可以將ext_id和id的值儲存在一張表內，當B的資料更新時，用B的主鍵連線這種表的ext_id欄位，拉取到所有的A表id欄位，並將A表id欄位重新流入Flink。
三 設計模式
對系統整體流程有了解以後， 我們再來看看系統的設計模式選擇，選擇設計模式時，我們考慮到資料處理相關的開發工作存在一些共性：
拆解後小功能多
小功能存在複用情況
小功能執行有嚴格的先後順序
需要記錄小功能執行狀態， 流程執行可回滾或者中斷可恢復執行
由於資料處理任務的步奏比較冗長，而且由於每個階段的結果與下階段的執行有關係，又不能分開。
參考 PipeLine（流水線）設計模式[2]，綜合考慮後我們系統的整體設計如下圖所示：
四 資料結構和演算法
問題說明
SQL生成器關鍵點， 就是把各個表（Meta節點）之間的關係表示出來。Meta之間的關係分為兩類，分別是全連線關聯和左連線關聯（因為左連線關聯涉及到資料的時序問題， 需要新增反向索引較為複雜， 所以和全連線區分了一下， 為了簡化問題我們先執行全連線， 再執行左連線）。
我們要解決的問題是， 多個數據源同步資料進來之後， 按一定的優先順序關聯， 最終得到一個大寬表並需要自動釋出。抽象到資料結構層面就是：
每個同步進來的資料來源對應一個葉子節點
節點之間有關聯關係，關聯關係有多類並有執行優先順序
所有節點和關聯關係組成一棵樹
最終得到一個根節點（大寬表）併發布
演算法思路
下面說明下解決該問題的演算法思路。
優先順序佇列
因為葉子節點之間連線執行優先順序不同，先放入優先順序佇列。之後每次取出高優先順序任務執行。相同優先順序任務可以複用， 連續執行多次。優先順序佇列示意圖如下：
有了優先順序佇列的概念， 我們來構建樹。構建主要分以下步驟：
首先得到四種優先順序的任務， 優先順序從高到低分別為：
優先順序1， 六個節點的同步任務
優先順序2，節點1、2、3和節點4、5的Full Join任務
優先順序3，節點1、4和節點6的Left Join任務
優先順序4， 釋出任務
取優先順序1的任務執行，同步進來六個資料來源對應六個葉子。
取優先順序2的任務並執行得到中間表1，2。
取優先順序3的任務並執行，發現節點1、4有父節點， 則執行中間節點1、2分別和節點6 Left Join得到根節點。
取優先順序4的任務並執行，釋出根節點。
問題的解決由一系列不同優先順序的任務組成， 任務需要複用。
透過從佇列取優先順序高的任務的方式構建任務關係樹。
最後遍歷樹完成各個節點任務。
五 總結
限於篇幅， 本文重點在於介紹自動生成sql功能開發中運用到的主要資料結構和設計模式思想。
目前我們實現了任意張表關聯sql自動生成併發布， 整體延遲控制在2s以內。之後SQL生成器主要會針對方便接入更多第三方實時計算平臺（比如Tisplus）， 降低整體系統延遲工作展開。方便接入主要考驗的是架構的設計， 也是本文著重寫的點（包括資料結構和演算法設計、設計模式的選擇）。降低系統延遲則包括訊息中介軟體最佳化，程式碼執行效率提升等。
最後
阿里巴巴供應鏈國際化團隊歡迎廣大有識之士加入，共同打造東半球零售業首選的國際化供應鏈平臺。有意請聯絡：pengcheng.wang@alibaba-inc.com