共計 3914 個字符，預計需要花費 10 分鐘才能閱讀完成。

這篇文章將為大家詳細講解有關如何實現一個跨庫連表 SQL 生成器，文章內容質量較高，因此丸趣 TV 小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

一 概述

ADC（Alibaba DChain Data Converger）項目的主要目的是做一套工具，用戶在前端簡單配置下指標后，就能在系統自動生成的大寬表里面查詢到他所需要的實時數據，數據源支持跨庫并支持多種目標介質。說的更高層次一點，數據的全局實時可視化這個事情本身就是解決供應鏈數據“神龍效應”的有效措施。做 ADC 也是為了這個目標，整個 ADC 系統

架構解析：

初始數據來自于元數據中心。

經過元數據適配層后轉換為內部格式數據。

調度中心把內部格式的數據傳到計劃中心，計劃中心分析數據需求并建模，通過 SQL 生成器生成資源和 SQL，分別通過告警中心、對賬中心設定監控標準和對賬標準。

對賬中心定時對賬，查看數據的對齊情況。

告警中心可以針對任務錯誤、延遲高等情況發送報警。

資源的生命周期管控在資源管理中心下，view 刪除時資源管理中心負責回收資源。

基礎資源適配層主要借助集團基礎資源管理能力串聯阿里各類數據服務，比如阿里云 MaxComputer、Flink、阿里云 AnalyticDB 等。

其中，SQL 生成器的上游和下游主要涉及：

上游計劃中心

配置指標：用戶在前端配置他想看的數據有哪些。

生產原始數據：根據用戶輸入得到哪些表作為數據源，以及它們之間的連接關系。

下游基礎資源適配層適配器

把 SQL 發布到 Flink，根據建表數據建物理表。

主要從技術角度介紹下 SQL 生成器相關的內容。

二 技術實現

在項目實施階段，需要從需求分析、技術方案設計、測試聯調幾個步驟展開工作。本文重點不放在軟件開發流程上，而是就設計模式選擇和數據結構算法設計做下重點講解。

需求分析

在需求分析階段，我們明確了自動生成 SQL 模塊所需要考慮的需求點，主要包含如下幾點：

需要支持多個事實表（流表）、多個維度表連表，其中一個事實表是主表，其他的均為輔助表。

維表變動也應當引起最終數據庫更新。

主表對輔助表為 1:1 或 N1，也就是說主表的粒度是最細的，輔表通過唯一鍵來和主表連接。

流表中可能存在唯一鍵一致的多張流表，需要通過全連接關聯。唯一鍵不同的表之間通過左連接關聯。

只有連表和 UDF，沒有 groupby 操作。

要求同步延時較小，支持多種源和目標介質。由于查詢壓力在目標介質，所以查詢 qps 沒有要求。

系統流程圖

明確需求后，我們把 SQL 生成器總體功能分為兩塊：

同步生成 SQL 和建表數據

異步發布 SQL 和建表

之所以把生成 SQL 階段做成同步是因為同步階段內存操作為主，如果發現數據有問題無法生成 SQL 能做到快速失敗。發布階段調用基礎資源適配層需要同步等待較長時間，每個發布步驟要做到有狀態記錄，可回滾或者重試。所以異步實現。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]，綜合考慮后我們系統的整體設計如下圖所示：

首先有一個全局的 PipeLineContainer 管理多個 pipeLine 和 pipeline context，每個 pipeline 可獨立執行一個任務，比如 pipeline1 執行同步生成 sql 任務。pipeline2 執行異步發布任務。發布必須在生成 SQL 結束后執行，pipeline 有狀態并且按一定順序串聯。每個 pipeline 包含多個可重用的 valve（功能）。valve 可以重用，任意組合，方便完成更多的數據處理任務（比如以后如果要支持 Tisplus dump 平臺接入，則簡單拼接現有的 valve 就可以）。

四 數據結構和算法問題說明

SQL 生成器關鍵點，就是把各個表（Meta 節點）之間的關系表示出來。Meta 之間的關系分為兩類，分別是全連接關聯和左連接關聯（因為左連接關聯涉及到數據的時序問題，需要添加反向索引較為復雜，所以和全連接區分了一下，為了簡化問題我們先執行全連接，再執行左連接）。

我們要解決的問題是，多個數據源同步數據進來之后，按一定的優先級關聯，最終得到一個大寬表并需要自動發布。抽象到數據結構層面就是：

每個同步進來的數據源對應一個葉子節點

節點之間有關聯關系，關聯關系有多類并有執行優先級

所有節點和關聯關系組成一棵樹

最終得到一個根節點（大寬表）并發布

算法思路

下面說明下解決該問題的算法思路。

優先級隊列

因為葉子節點之間連接執行優先級不同，先放入優先級隊列。之后每次取出高優先級任務執行。相同優先級任務可以復用，連續執行多次。優先級隊列示意圖如下：

構建樹

有了優先級隊列的概念，我們來構建樹。構建主要分以下步驟：

1. 首先得到四種優先級的任務，優先級從高到低分別為：

優先級 1，六個節點的同步任務

優先級 2，節點 1、2、3 和節點 4、5 的 Full Join 任務

優先級 3，節點 1、4 和節點 6 的 Left Join 任務

優先級 4，發布任務

2. 取優先級 1 的任務執行，同步進來六個數據源對應六個葉子。

3. 取優先級 2 的任務并執行得到中間表 1，2。

4. 取優先級 3 的任務并執行，發現節點 1、4 有父節點，則執行中間節點 1、2 分別和節點 6 Left Join 得到根節點。

5. 取優先級 4 的任務并執行，發布根節點。

可以看到最終的數據結構是一棵樹，通過這種方式我們能支持復雜 sql 的自動構建。進一步抽象，這種“一個隊列驅動一棵樹生成”的模式可以解決一類問題：

問題的解決由一系列不同優先級的任務組成，任務需要復用。

通過從隊列取優先級高的任務的方式構建任務關系樹。

最后遍歷樹完成各個節點任務。

限于篇幅，重點在于介紹自動生成 sql 功能開發中運用到的主要數據結構和設計模式思想。

目前我們實現了任意張表關聯 sql 自動生成并發布，整體延遲控制在 2s 以內。之后 SQL 生成器主要會針對方便接入更多第三方實時計算平臺（比如 Tisplus），降低整體系統延遲工作展開。方便接入主要考驗的是架構的設計，也是本文著重寫的點（包括數據結構和算法設計、設計模式的選擇）。降低系統延遲則包括消息中間件優化，代碼執行效率提升等。

關于如何實現一個跨庫連表 SQL 生成器就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。