這篇文章將為大家詳細講解有關(guān)如何實現(xiàn)一個跨庫連表SQL生成器，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。
讓客戶滿意是我們工作的目標，不斷超越客戶的期望值來自于我們對這個行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價值的長期合作伙伴，公司提供的服務項目有：國際域名空間、虛擬主機、營銷軟件、網(wǎng)站建設、衢江網(wǎng)站維護、網(wǎng)站推廣。
一 概述
ADC（Alibaba DChain Data Converger）項目的主要目的是做一套工具，用戶在前端簡單配置下指標后，就能在系統(tǒng)自動生成的大寬表里面查詢到他所需要的實時數(shù)據(jù)，數(shù)據(jù)源支持跨庫并支持多種目標介質(zhì)。說的更高層次一點， 數(shù)據(jù)的全局實時可視化這個事情本身就是解決供應鏈數(shù)據(jù)“神龍效應”的有效措施。做ADC也是為了這個目標，整個ADC系統(tǒng)
架構(gòu)解析：
初始數(shù)據(jù)來自于元數(shù)據(jù)中心。
經(jīng)過元數(shù)據(jù)適配層后轉(zhuǎn)換為內(nèi)部格式數(shù)據(jù)。
調(diào)度中心把內(nèi)部格式的數(shù)據(jù)傳到計劃中心，計劃中心分析數(shù)據(jù)需求并建模，通過SQL生成器生成資源和SQL，分別通過告警中心、對賬中心設定監(jiān)控標準和對賬標準。
對賬中心定時對賬，查看數(shù)據(jù)的對齊情況。
告警中心可以針對任務錯誤、延遲高等情況發(fā)送報警。
資源的生命周期管控在資源管理中心下，view刪除時資源管理中心負責回收資源。
基礎資源適配層主要借助集團基礎資源管理能力串聯(lián)阿里各類數(shù)據(jù)服務， 比如阿里云MaxComputer、Flink、阿里云AnalyticDB等。
其中，SQL生成器的上游和下游主要涉及：
上游計劃中心
配置指標：用戶在前端配置他想看的數(shù)據(jù)有哪些。
生產(chǎn)原始數(shù)據(jù)：根據(jù)用戶輸入得到哪些表作為數(shù)據(jù)源， 以及它們之間的連接關(guān)系。
下游基礎資源適配層適配器
把SQL發(fā)布到Flink， 根據(jù)建表數(shù)據(jù)建物理表。
主要從技術(shù)角度介紹下SQL生成器相關(guān)的內(nèi)容。
在項目實施階段，需要從需求分析、技術(shù)方案設計、測試聯(lián)調(diào)幾個步驟展開工作。本文重點不放在軟件開發(fā)流程上， 而是就設計模式選擇和數(shù)據(jù)結(jié)構(gòu)算法設計做下重點講解。
在需求分析階段， 我們明確了自動生成SQL模塊所需要考慮的需求點， 主要包含如下幾點：
需要支持多個事實表（流表）、多個維度表連表，其中一個事實表是主表，其他的均為輔助表。
維表變動也應當引起最終數(shù)據(jù)庫更新。
主表對輔助表為1:1或N1，也就是說主表的粒度是最細的， 輔表通過唯一鍵來和主表連接。
流表中可能存在唯一鍵一致的多張流表， 需要通過全連接關(guān)聯(lián)。唯一鍵不同的表之間通過左連接關(guān)聯(lián)。
只有連表和UDF，沒有g(shù)roupby操作。
要求同步延時較小，支持多種源和目標介質(zhì)。由于查詢壓力在目標介質(zhì)，所以查詢qps沒有要求。
明確需求后， 我們把SQL生成器總體功能分為兩塊：
同步生成SQL和建表數(shù)據(jù)
異步發(fā)布SQL和建表
之所以把生成SQL階段做成同步是因為同步階段內(nèi)存操作為主，如果發(fā)現(xiàn)數(shù)據(jù)有問題無法生成SQL能做到快速失敗。發(fā)布階段調(diào)用基礎資源適配層需要同步等待較長時間， 每個發(fā)布步驟要做到有狀態(tài)記錄， 可回滾或者重試。所以異步實現(xiàn)。SQL生成器同步階段的整體功能細化到小模塊，如下圖所示：
檢查階段
檢查原始數(shù)據(jù)是否有問題， 無法生成SQL則快速失敗。
參數(shù)檢查：檢查上游是否提供了基本的參數(shù)， 比如事實表信息（可以沒有維表， 但是必須有事實表）。
表類型檢查：檢查數(shù)據(jù)來源類型是否支持。
分區(qū)字段檢查：是否提供了大寬表分區(qū)字段。
連接約束：檢查流表，維表連接信息是否正確。
主表唯一性約束：檢查主表是否含連接信息，唯一鍵是否有ETL信息。
元數(shù)據(jù)檢查：檢查是否包含HBase配置信息。
主鍵修正：修正維表連接鍵， 必須是維表的唯一鍵。
數(shù)據(jù)同步
同步所有原始表和原始表的連接數(shù)據(jù)（比如源表同步進來， 生成1:1的HBase表）。
生成優(yōu)先級隊列：生成連接和發(fā)布等任務的執(zhí)行優(yōu)先級。
同步填充：填充源表對應的同步階段HBase表數(shù)據(jù)，和對應的配置項， 類型轉(zhuǎn)換（比如源表是MySQL表，字段類型要轉(zhuǎn)換為HBase的類型）， ETL填充， 添加消息隊列（通過發(fā)送消息的方式通知下游節(jié)點運行）。
重復列修剪：刪除重復的列。
空白列打標：對于滿足一定條件（比如不需要在大寬表展示， 不是唯一鍵列， 連接鍵列， 保序列）的列打上空白列標識。
保序字段填充：如果上游提供了表示數(shù)據(jù)創(chuàng)建時間的字段， 則用該字段作為數(shù)據(jù)保序字段， 沒有則填充系統(tǒng)接收到數(shù)據(jù)的時間作為保序字段。
計算階段
生成大寬表，填充SQL。
中間表填充：填充全連接產(chǎn)生的中間表。
連接關(guān)系升級：會在本文后面說明。
反向索引填充：填充“反向索引”信息。
消息填充：中間表添加消息隊列（中間表更新可以觸發(fā)下游節(jié)點）。
大寬表填充：填充大寬表數(shù)據(jù)。
連接鏈對齊：中間表和大寬表連接鍵對齊。
ETL填充：填充大寬表列的ETL信息。
分區(qū)字段填充：填充大寬表分區(qū)字段。
SQL填充：填充Flink同步表映射SQL語句， Flink計算SQL語句， Flink結(jié)果表映射SQL語句。
保存：把SQL和建表數(shù)據(jù)存入數(shù)據(jù)庫， 之后的請求可以復用已有的數(shù)據(jù)， 避免重復建表。
異步發(fā)布階段會把SQL語句發(fā)布到Flink。
添加反向索引的原因
假如有A、B兩表連接，那么連接方式為A表的非主鍵連接B表主鍵。從時序上來說可能有以下三種情況：
B表數(shù)據(jù)先于A表數(shù)據(jù)多天產(chǎn)生
B表數(shù)據(jù)后于A表數(shù)據(jù)多天產(chǎn)生
B表數(shù)據(jù)和A表數(shù)據(jù)同時產(chǎn)生
下面我們就這三種情況逐一分析。
場景1：B表數(shù)據(jù)先于A表數(shù)據(jù)多天產(chǎn)生
我們假如B表數(shù)據(jù)存儲于某個支持高qps的數(shù)據(jù)庫內(nèi)，我們可以直接讓A表數(shù)據(jù)到來時直接連接此表（維表）來實現(xiàn)連表。
場景2：B表數(shù)據(jù)后于A表數(shù)據(jù)多天產(chǎn)生
這種場景比較麻煩。A表數(shù)據(jù)先行產(chǎn)生，因此過早的落庫，導致B表數(shù)據(jù)到來時即使連接B維表也拿不到數(shù)據(jù)。這種場景還有一個類似的場景：如果AB連接完成后B發(fā)生了更新，如何讓B的更新體現(xiàn)在寬表中？
為了解決這種問題，我們增加了一個“反向索引表”。假如A的主鍵是id，連接鍵是ext_id,那么我們可以將ext_id和id的值存儲在一張表內(nèi)，當B的數(shù)據(jù)更新時，用B的主鍵連接這種表的ext_id字段，拉取到所有的A表id字段，并將A表id字段重新流入Flink。
對系統(tǒng)整體流程有了解以后， 我們再來看看系統(tǒng)的設計模式選擇，選擇設計模式時，我們考慮到數(shù)據(jù)處理相關(guān)的開發(fā)工作存在一些共性：
拆解后小功能多
小功能存在復用情況
小功能執(zhí)行有嚴格的先后順序
需要記錄小功能運行狀態(tài)， 流程執(zhí)行可回滾或者中斷可恢復執(zhí)行
由于數(shù)據(jù)處理任務的步奏比較冗長，而且由于每個階段的結(jié)果與下階段的執(zhí)行有關(guān)系，又不能分開。
參考 PipeLine（流水線）設計模式[2]，綜合考慮后我們系統(tǒng)的整體設計如下圖所示：
首先有一個全局的PipeLineContainer管理多個pipeLine和pipeline context， 每個pipeline可獨立執(zhí)行一個任務， 比如pipeline1執(zhí)行同步生成sql任務。pipeline2執(zhí)行異步發(fā)布任務。發(fā)布必須在生成SQL結(jié)束后執(zhí)行， pipeline有狀態(tài)并且按一定順序串聯(lián)。每個pipeline包含多個可重用的valve（功能）。valve可以重用， 任意組合，方便完成更多的數(shù)據(jù)處理任務（比如以后如果要支持Tisplus dump平臺接入， 則簡單拼接現(xiàn)有的valve就可以）。
SQL生成器關(guān)鍵點， 就是把各個表（Meta節(jié)點）之間的關(guān)系表示出來。Meta之間的關(guān)系分為兩類，分別是全連接關(guān)聯(lián)和左連接關(guān)聯(lián)（因為左連接關(guān)聯(lián)涉及到數(shù)據(jù)的時序問題， 需要添加反向索引較為復雜， 所以和全連接區(qū)分了一下， 為了簡化問題我們先執(zhí)行全連接， 再執(zhí)行左連接）。
我們要解決的問題是， 多個數(shù)據(jù)源同步數(shù)據(jù)進來之后， 按一定的優(yōu)先級關(guān)聯(lián)， 最終得到一個大寬表并需要自動發(fā)布。抽象到數(shù)據(jù)結(jié)構(gòu)層面就是：
每個同步進來的數(shù)據(jù)源對應一個葉子節(jié)點
節(jié)點之間有關(guān)聯(lián)關(guān)系，關(guān)聯(lián)關(guān)系有多類并有執(zhí)行優(yōu)先級
所有節(jié)點和關(guān)聯(lián)關(guān)系組成一棵樹
最終得到一個根節(jié)點（大寬表）并發(fā)布
下面說明下解決該問題的算法思路。
優(yōu)先級隊列
因為葉子節(jié)點之間連接執(zhí)行優(yōu)先級不同，先放入優(yōu)先級隊列。之后每次取出高優(yōu)先級任務執(zhí)行。相同優(yōu)先級任務可以復用， 連續(xù)執(zhí)行多次。優(yōu)先級隊列示意圖如下：
構(gòu)建樹
有了優(yōu)先級隊列的概念， 我們來構(gòu)建樹。構(gòu)建主要分以下步驟：
1.首先得到四種優(yōu)先級的任務， 優(yōu)先級從高到低分別為：
優(yōu)先級1， 六個節(jié)點的同步任務
優(yōu)先級2，節(jié)點1、2、3和節(jié)點4、5的Full Join任務
優(yōu)先級3，節(jié)點1、4和節(jié)點6的Left Join任務
優(yōu)先級4， 發(fā)布任務
2.取優(yōu)先級1的任務執(zhí)行，同步進來六個數(shù)據(jù)源對應六個葉子。
3.取優(yōu)先級2的任務并執(zhí)行得到中間表1，2。
4.取優(yōu)先級3的任務并執(zhí)行，發(fā)現(xiàn)節(jié)點1、4有父節(jié)點， 則執(zhí)行中間節(jié)點1、2分別和節(jié)點6 Left Join得到根節(jié)點。
5.取優(yōu)先級4的任務并執(zhí)行，發(fā)布根節(jié)點。
可以看到最終的數(shù)據(jù)結(jié)構(gòu)是一棵樹， 通過這種方式我們能支持復雜sql的自動構(gòu)建。進一步抽象， 這種“一個隊列驅(qū)動一棵樹生成”的模式可以解決一類問題：
問題的解決由一系列不同優(yōu)先級的任務組成， 任務需要復用。
通過從隊列取優(yōu)先級高的任務的方式構(gòu)建任務關(guān)系樹。
最后遍歷樹完成各個節(jié)點任務。
限于篇幅， 重點在于介紹自動生成sql功能開發(fā)中運用到的主要數(shù)據(jù)結(jié)構(gòu)和設計模式思想。
目前我們實現(xiàn)了任意張表關(guān)聯(lián)sql自動生成并發(fā)布， 整體延遲控制在2s以內(nèi)。之后SQL生成器主要會針對方便接入更多第三方實時計算平臺（比如Tisplus）， 降低整體系統(tǒng)延遲工作展開。方便接入主要考驗的是架構(gòu)的設計， 也是本文著重寫的點（包括數(shù)據(jù)結(jié)構(gòu)和算法設計、設計模式的選擇）。降低系統(tǒng)延遲則包括消息中間件優(yōu)化，代碼執(zhí)行效率提升等。
關(guān)于如何實現(xiàn)一個跨庫連表SQL生成器就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
重慶分公司
重慶分公司
