如何实现一个跨库连表SQL生成器？
一 概述
ADC（Alibaba DChain Data Converger）項(xiàng)目的主要目的是做一套工具，用戶在前端簡(jiǎn)單配置下指標(biāo)后，就能在系統(tǒng)自動(dòng)生成的大寬表里面查詢到他所需要的實(shí)時(shí)數(shù)據(jù)，數(shù)據(jù)源支持跨庫(kù)并支持多種目標(biāo)介質(zhì)。說(shuō)的更高層次一點(diǎn)， 數(shù)據(jù)的全局實(shí)時(shí)可視化這個(gè)事情本身就是解決供應(yīng)鏈數(shù)據(jù)“神龍效應(yīng)”的有效措施（參考施云老師的《供應(yīng)鏈架構(gòu)師》[1]一書(shū)）。做ADC也是為了這個(gè)目標(biāo)，整個(gè)ADC系統(tǒng)架構(gòu)如下圖所示：
架構(gòu)解析：
- 初始數(shù)據(jù)來(lái)自于元數(shù)據(jù)中心。
- 經(jīng)過(guò)元數(shù)據(jù)適配層后轉(zhuǎn)換為內(nèi)部格式數(shù)據(jù)。
- 調(diào)度中心把內(nèi)部格式的數(shù)據(jù)傳到計(jì)劃中心，計(jì)劃中心分析數(shù)據(jù)需求并建模，通過(guò)SQL生成器生成資源和SQL，分別通過(guò)告警中心、對(duì)賬中心設(shè)定監(jiān)控標(biāo)準(zhǔn)和對(duì)賬標(biāo)準(zhǔn)。
- 對(duì)賬中心定時(shí)對(duì)賬，查看數(shù)據(jù)的對(duì)齊情況。
- 告警中心可以針對(duì)任務(wù)錯(cuò)誤、延遲高等情況發(fā)送報(bào)警。
- 資源的生命周期管控在資源管理中心下，view刪除時(shí)資源管理中心負(fù)責(zé)回收資源。
- 基礎(chǔ)資源適配層主要借助集團(tuán)基礎(chǔ)資源管理能力串聯(lián)阿里各類數(shù)據(jù)服務(wù)， 比如阿里云MaxComputer、Flink、阿里云AnalyticDB等。
其中，SQL生成器的上游和下游主要涉及：
- 上游計(jì)劃中心
- 配置指標(biāo)：用戶在前端配置他想看的數(shù)據(jù)有哪些。
- 生產(chǎn)原始數(shù)據(jù)：根據(jù)用戶輸入得到哪些表作為數(shù)據(jù)源， 以及它們之間的連接關(guān)系。
- 下游Metric適配器
- 把SQL發(fā)布到Flink， 根據(jù)建表數(shù)據(jù)建物理表。
本文主要從技術(shù)角度介紹下SQL生成器相關(guān)的內(nèi)容。
二 技術(shù)實(shí)現(xiàn)
在項(xiàng)目實(shí)施階段，需要從需求分析、技術(shù)方案設(shè)計(jì)、測(cè)試聯(lián)調(diào)幾個(gè)步驟展開(kāi)工作。本文重點(diǎn)不放在軟件開(kāi)發(fā)流程上， 而是就設(shè)計(jì)模式選擇和數(shù)據(jù)結(jié)構(gòu)算法設(shè)計(jì)做下重點(diǎn)講解。
需求分析
在需求分析階段， 我們明確了自動(dòng)生成SQL模塊所需要考慮的需求點(diǎn)， 主要包含如下幾點(diǎn)：
- 需要支持多個(gè)事實(shí)表（流表）、多個(gè)維度表連表，其中一個(gè)事實(shí)表是主表，其他的均為輔助表。
- 維表變動(dòng)也應(yīng)當(dāng)引起最終數(shù)據(jù)庫(kù)更新。
- 主表對(duì)輔助表為1:1或N1，也就是說(shuō)主表的粒度是最細(xì)的， 輔表通過(guò)唯一鍵來(lái)和主表連接。
- 流表中可能存在唯一鍵一致的多張流表， 需要通過(guò)全連接關(guān)聯(lián)。唯一鍵不同的表之間通過(guò)左連接關(guān)聯(lián)。
- 只有連表和UDF，沒(méi)有g(shù)roupby操作。
- 要求同步延時(shí)較小，支持多種源和目標(biāo)介質(zhì)。由于查詢壓力在目標(biāo)介質(zhì)，所以查詢qps沒(méi)有要求。
系統(tǒng)流程圖
明確需求后， 我們把SQL生成器總體功能分為兩塊：
- 同步生成SQL和建表數(shù)據(jù)
- 異步發(fā)布SQL和建表
之所以把生成SQL階段做成同步是因?yàn)橥诫A段內(nèi)存操作為主，如果發(fā)現(xiàn)數(shù)據(jù)有問(wèn)題無(wú)法生成SQL能做到快速失敗。發(fā)布階段調(diào)用Metrics需要同步等待較長(zhǎng)時(shí)間， 每個(gè)發(fā)布步驟要做到有狀態(tài)記錄， 可回滾或者重試。所以異步實(shí)現(xiàn)。SQL生成器同步階段的整體功能細(xì)化到小模塊，如下圖所示：
檢查階段
檢查原始數(shù)據(jù)是否有問(wèn)題， 無(wú)法生成SQL則快速失敗。
- 參數(shù)檢查：檢查上游是否提供了基本的參數(shù)， 比如事實(shí)表信息（可以沒(méi)有維表， 但是必須有事實(shí)表）。
- 表類型檢查：檢查數(shù)據(jù)來(lái)源類型是否支持。
- 分區(qū)字段檢查：是否提供了大寬表分區(qū)字段。
- 連接約束：檢查流表，維表連接信息是否正確。
- 主表唯一性約束：檢查主表是否含連接信息，唯一鍵是否有ETL信息。
- 元數(shù)據(jù)檢查：檢查是否包含HBase配置信息。
- 主鍵修正：修正維表連接鍵， 必須是維表的唯一鍵。
數(shù)據(jù)同步
- 同步所有原始表和原始表的連接數(shù)據(jù)（比如源表同步進(jìn)來(lái)， 生成1:1的HBase表）。
- 生成優(yōu)先級(jí)隊(duì)列：生成連接和發(fā)布等任務(wù)的執(zhí)行優(yōu)先級(jí)。
- 同步填充：填充源表對(duì)應(yīng)的同步階段HBase表數(shù)據(jù)，和對(duì)應(yīng)的配置項(xiàng)， 類型轉(zhuǎn)換（比如源表是MySQL表，字段類型要轉(zhuǎn)換為HBase的類型）， ETL填充， 添加消息隊(duì)列（通過(guò)發(fā)送消息的方式通知下游節(jié)點(diǎn)運(yùn)行）。
- 重復(fù)列修剪：刪除重復(fù)的列。
- 空白列打標(biāo)：對(duì)于滿足一定條件（比如不需要在大寬表展示， 不是唯一鍵列， 連接鍵列， 保序列）的列打上空白列標(biāo)識(shí)。
- 保序字段填充：如果上游提供了表示數(shù)據(jù)創(chuàng)建時(shí)間的字段， 則用該字段作為數(shù)據(jù)保序字段， 沒(méi)有則填充系統(tǒng)接收到數(shù)據(jù)的時(shí)間作為保序字段。
計(jì)算階段
生成大寬表，填充SQL。
- 中間表填充：填充全連接產(chǎn)生的中間表。
- 連接關(guān)系升級(jí)：會(huì)在本文后面說(shuō)明。
- 反向索引填充：填充“反向索引”信息。
- 消息填充：中間表添加消息隊(duì)列（中間表更新可以觸發(fā)下游節(jié)點(diǎn)）。
- 大寬表填充：填充大寬表數(shù)據(jù)。
- 連接鏈對(duì)齊：中間表和大寬表連接鍵對(duì)齊。
- ETL填充：填充大寬表列的ETL信息。
- 分區(qū)字段填充：填充大寬表分區(qū)字段。
- SQL填充：填充Flink同步表映射SQL語(yǔ)句， Flink計(jì)算SQL語(yǔ)句， Flink結(jié)果表映射SQL語(yǔ)句。
- 保存：把SQL和建表數(shù)據(jù)存入數(shù)據(jù)庫(kù)， 之后的請(qǐng)求可以復(fù)用已有的數(shù)據(jù)， 避免重復(fù)建表。
異步發(fā)布階段會(huì)把SQL語(yǔ)句發(fā)布到Flink。
添加反向索引的原因
假如有A、B兩表連接，那么連接方式為A表的非主鍵連接B表主鍵。從時(shí)序上來(lái)說(shuō)可能有以下三種情況：
- B表數(shù)據(jù)先于A表數(shù)據(jù)多天產(chǎn)生
- B表數(shù)據(jù)后于A表數(shù)據(jù)多天產(chǎn)生
- B表數(shù)據(jù)和A表數(shù)據(jù)同時(shí)產(chǎn)生
下面我們就這三種情況逐一分析。
場(chǎng)景1：B表數(shù)據(jù)先于A表數(shù)據(jù)多天產(chǎn)生
我們假如B表數(shù)據(jù)存儲(chǔ)于某個(gè)支持高qps的數(shù)據(jù)庫(kù)內(nèi)，我們可以直接讓A表數(shù)據(jù)到來(lái)時(shí)直接連接此表（維表）來(lái)實(shí)現(xiàn)連表。
場(chǎng)景2：B表數(shù)據(jù)后于A表數(shù)據(jù)多天產(chǎn)生
這種場(chǎng)景比較麻煩。A表數(shù)據(jù)先行產(chǎn)生，因此過(guò)早的落庫(kù)，導(dǎo)致B表數(shù)據(jù)到來(lái)時(shí)即使連接B維表也拿不到數(shù)據(jù)。這種場(chǎng)景還有一個(gè)類似的場(chǎng)景：如果AB連接完成后B發(fā)生了更新，如何讓B的更新體現(xiàn)在寬表中？
為了解決這種問(wèn)題，我們?cè)黾恿艘粋€(gè)“反向索引表”。假如A的主鍵是id，連接鍵是ext_id,那么我們可以將ext_id和id的值存儲(chǔ)在一張表內(nèi)，當(dāng)B的數(shù)據(jù)更新時(shí)，用B的主鍵連接這種表的ext_id字段，拉取到所有的A表id字段，并將A表id字段重新流入Flink。
三 設(shè)計(jì)模式
對(duì)系統(tǒng)整體流程有了解以后， 我們?cè)賮?lái)看看系統(tǒng)的設(shè)計(jì)模式選擇，選擇設(shè)計(jì)模式時(shí)，我們考慮到數(shù)據(jù)處理相關(guān)的開(kāi)發(fā)工作存在一些共性：
- 拆解后小功能多
- 小功能存在復(fù)用情況
- 小功能執(zhí)行有嚴(yán)格的先后順序
- 需要記錄小功能運(yùn)行狀態(tài)， 流程執(zhí)行可回滾或者中斷可恢復(fù)執(zhí)行
由于數(shù)據(jù)處理任務(wù)的步奏比較冗長(zhǎng)，而且由于每個(gè)階段的結(jié)果與下階段的執(zhí)行有關(guān)系，又不能分開(kāi)。
參考 PipeLine（流水線）設(shè)計(jì)模式[2]，綜合考慮后我們系統(tǒng)的整體設(shè)計(jì)如下圖所示：
首先有一個(gè)全局的PipeLineContainer管理多個(gè)pipeLine和pipeline context， 每個(gè)pipeline可獨(dú)立執(zhí)行一個(gè)任務(wù)， 比如pipeline1執(zhí)行同步生成sql任務(wù)。pipeline2執(zhí)行異步發(fā)布任務(wù)。發(fā)布必須在生成SQL結(jié)束后執(zhí)行， pipeline有狀態(tài)并且按一定順序串聯(lián)。每個(gè)pipeline包含多個(gè)可重用的valve（功能）。valve可以重用， 任意組合，方便完成更多的數(shù)據(jù)處理任務(wù)（比如以后如果要支持Tisplus dump平臺(tái)接入， 則簡(jiǎn)單拼接現(xiàn)有的valve就可以）。
四 數(shù)據(jù)結(jié)構(gòu)和算法
問(wèn)題說(shuō)明
SQL生成器關(guān)鍵點(diǎn)， 就是把各個(gè)表（Meta節(jié)點(diǎn)）之間的關(guān)系表示出來(lái)。Meta之間的關(guān)系分為兩類，分別是全連接關(guān)聯(lián)和左連接關(guān)聯(lián)（因?yàn)樽筮B接關(guān)聯(lián)涉及到數(shù)據(jù)的時(shí)序問(wèn)題， 需要添加反向索引較為復(fù)雜， 所以和全連接區(qū)分了一下， 為了簡(jiǎn)化問(wèn)題我們先執(zhí)行全連接， 再執(zhí)行左連接）。
我們要解決的問(wèn)題是， 多個(gè)數(shù)據(jù)源同步數(shù)據(jù)進(jìn)來(lái)之后， 按一定的優(yōu)先級(jí)關(guān)聯(lián)， 最終得到一個(gè)大寬表并需要自動(dòng)發(fā)布。抽象到數(shù)據(jù)結(jié)構(gòu)層面就是：
- 每個(gè)同步進(jìn)來(lái)的數(shù)據(jù)源對(duì)應(yīng)一個(gè)葉子節(jié)點(diǎn)
- 節(jié)點(diǎn)之間有關(guān)聯(lián)關(guān)系，關(guān)聯(lián)關(guān)系有多類并有執(zhí)行優(yōu)先級(jí)
- 所有節(jié)點(diǎn)和關(guān)聯(lián)關(guān)系組成一棵樹(shù)
- 最終得到一個(gè)根節(jié)點(diǎn)（大寬表）并發(fā)布
算法思路
下面說(shuō)明下解決該問(wèn)題的算法思路。
優(yōu)先級(jí)隊(duì)列
因?yàn)槿~子節(jié)點(diǎn)之間連接執(zhí)行優(yōu)先級(jí)不同，先放入優(yōu)先級(jí)隊(duì)列。之后每次取出高優(yōu)先級(jí)任務(wù)執(zhí)行。相同優(yōu)先級(jí)任務(wù)可以復(fù)用， 連續(xù)執(zhí)行多次。優(yōu)先級(jí)隊(duì)列示意圖如下：
構(gòu)建樹(shù)
有了優(yōu)先級(jí)隊(duì)列的概念， 我們來(lái)構(gòu)建樹(shù)。構(gòu)建主要分以下步驟：
1.首先得到四種優(yōu)先級(jí)的任務(wù)， 優(yōu)先級(jí)從高到低分別為：
- 優(yōu)先級(jí)1， 六個(gè)節(jié)點(diǎn)的同步任務(wù)
- 優(yōu)先級(jí)2，節(jié)點(diǎn)1、2、3和節(jié)點(diǎn)4、5的Full Join任務(wù)
- 優(yōu)先級(jí)3，節(jié)點(diǎn)1、4和節(jié)點(diǎn)6的Left Join任務(wù)
- 優(yōu)先級(jí)4， 發(fā)布任務(wù)
2.取優(yōu)先級(jí)1的任務(wù)執(zhí)行，同步進(jìn)來(lái)六個(gè)數(shù)據(jù)源對(duì)應(yīng)六個(gè)葉子。
3.取優(yōu)先級(jí)2的任務(wù)并執(zhí)行得到中間表1，2。
4.取優(yōu)先級(jí)3的任務(wù)并執(zhí)行，發(fā)現(xiàn)節(jié)點(diǎn)1、4有父節(jié)點(diǎn)， 則執(zhí)行中間節(jié)點(diǎn)1、2分別和節(jié)點(diǎn)6 Left Join得到根節(jié)點(diǎn)。
5.取優(yōu)先級(jí)4的任務(wù)并執(zhí)行，發(fā)布根節(jié)點(diǎn)。
可以看到最終的數(shù)據(jù)結(jié)構(gòu)是一棵樹(shù)， 通過(guò)這種方式我們能支持復(fù)雜sql的自動(dòng)構(gòu)建。進(jìn)一步抽象， 這種“一個(gè)隊(duì)列驅(qū)動(dòng)一棵樹(shù)生成”的模式可以解決一類問(wèn)題：
- 問(wèn)題的解決由一系列不同優(yōu)先級(jí)的任務(wù)組成， 任務(wù)需要復(fù)用。
- 通過(guò)從隊(duì)列取優(yōu)先級(jí)高的任務(wù)的方式構(gòu)建任務(wù)關(guān)系樹(shù)。
- 最后遍歷樹(shù)完成各個(gè)節(jié)點(diǎn)任務(wù)。
五 總結(jié)
限于篇幅， 本文重點(diǎn)在于介紹自動(dòng)生成sql功能開(kāi)發(fā)中運(yùn)用到的主要數(shù)據(jù)結(jié)構(gòu)和設(shè)計(jì)模式思想。
目前我們實(shí)現(xiàn)了任意張表關(guān)聯(lián)sql自動(dòng)生成并發(fā)布， 整體延遲控制在2s以內(nèi)。之后SQL生成器主要會(huì)針對(duì)方便接入更多第三方實(shí)時(shí)計(jì)算平臺(tái)（比如Tisplus）， 降低整體系統(tǒng)延遲工作展開(kāi)。方便接入主要考驗(yàn)的是架構(gòu)的設(shè)計(jì)， 也是本文著重寫的點(diǎn)（包括數(shù)據(jù)結(jié)構(gòu)和算法設(shè)計(jì)、設(shè)計(jì)模式的選擇）。降低系統(tǒng)延遲則包括消息中間件優(yōu)化，代碼執(zhí)行效率提升等。
最后
阿里巴巴供應(yīng)鏈國(guó)際化團(tuán)隊(duì)歡迎廣大有識(shí)之士加入，共同打造東半球零售業(yè)首選的國(guó)際化供應(yīng)鏈平臺(tái)。有意請(qǐng)聯(lián)系：pengcheng.wang@alibaba-inc.com
相關(guān)鏈接
[1]https://book.douban.com/subject/26995807/
[2]https://blog.csdn.net/buyoufa/article/details/51912262
原文鏈接：https://developer.aliyun.com/article/771748?
版權(quán)聲明：本文內(nèi)容由阿里云實(shí)名注冊(cè)用戶自發(fā)貢獻(xiàn)，版權(quán)歸原作者所有，阿里云開(kāi)發(fā)者社區(qū)不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。具體規(guī)則請(qǐng)查看《阿里云開(kāi)發(fā)者社區(qū)用戶服務(wù)協(xié)議》和《阿里云開(kāi)發(fā)者社區(qū)知識(shí)產(chǎn)權(quán)保護(hù)指引》。如果您發(fā)現(xiàn)本社區(qū)中有涉嫌抄襲的內(nèi)容，填寫侵權(quán)投訴表單進(jìn)行舉報(bào)，一經(jīng)查實(shí)，本社區(qū)將立刻刪除涉嫌侵權(quán)內(nèi)容。總結(jié)
以上是生活随笔為你收集整理的如何实现一个跨库连表SQL生成器？的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： 他来了他来了！阿里云混合云全新升级高燃来
- 下一篇： 企业应用人工智能面临的挑战