# 開發一個不需要重寫成 Hive QL 的大資料 SQL 引擎

學習大資料技術的核心原理，掌握一些高效的思考和思維方式，構建自己的技術知識體系。明白了原理，有時甚至不需要學習，順著原理就可以推匯出各種實現細節。

各種知識表象看雜亂無章，若只是學習繁雜知識點，固然自己的知識面是有限的，並且遇到問題的應變能力也很難提高。所以有些高手看起來似乎無所不知，不論談論起什麼技術，都能頭頭是道，其實並不是他們學習、掌握了所有技術，而是他們是在談到這個問題時，才開始進行推導，並迅速得出結論。

高手不一定要很資深、經驗豐富，把握住技術的核心本質，掌握快速分析推導的能力，能迅速將自己的知識技能推到陌生領域，就是高手。

本系列專注大資料開發需要關注的問題及解決方案。跳出繁雜知識表象，掌握核心原理和思維方式，進而融會貫通各種技術，再透過各種實踐訓練，成為終極高手。

## 大資料倉儲 Hive

作為一個成功的大資料倉儲，它將 SQL 語句轉換成 MapReduce 執行過程，並把大資料應用的門檻下降到普通資料分析師和工程師就可以很快上手的地步。

但 Hive 也有問題，由於它使用自定義 Hive QL，對熟悉 Oracle 等傳統資料倉儲的分析師有上手難度。特別是很多企業使用傳統資料倉儲進行資料分析已久，沉澱大量 SQL 語句，非常龐大也非常複雜。某銀行的一條統計報表 SQL 足足兩張 A4 紙，光是完全理解可能就要花很長時間，再轉化成 Hive QL 更費力，還不說可能引入 bug。

開發一款能支援標準資料庫 SQL 的大資料倉儲引擎，讓那些在 Oracle 上執行良好的 SQL 可以直接執行在 Hadoop 上，而不需要重寫成 Hive QL。

## Hive 處理過程

1. 將輸入的 Hive QL 經過語法解析器轉換成 Hive 抽象語法樹（Hive AST）
2. 將 Hive AST 經過語義分析器轉換成 MapReduce 執行計劃
3. 將生成的 MapReduce 執行計劃和 Hive 執行函式程式碼提交到 Hadoop 執行

可見，最簡單的，對第一步改造即可。考慮替換 Hive 語法解析器：能將標準 SQL 轉換成 Hive 語義分析器能處理的 Hive 抽象語法樹，即紅框代替黑框。

![img](https://pic3.zhimg.com/80/v2-6a864f5e7aa2ffa143f129381054f536_720w.jpg)

紅框內：淺藍色是個開源的 SQL 語法解析器，將標準 SQL 解析成標準 SQL 抽象語法樹（SQL AST），後面深藍色定製開發的 SQL 抽象語法樹分析與轉換器，將 SQL AST 轉換成 Hive AST。

那麼關鍵問題就來了：

## 標準 SQL V.S Hive QL

- 語法表達方式，Hive QL 語法和標準 SQL 語法略有不同
- Hive QL 支援的語法元素比標準 SQL 要少很多，比如，資料倉儲領域主要的測試集 TPC-H 所有的 SQL 語句，Hive 都不支援。尤其是 Hive 不支援複雜巢狀子查詢，而資料倉儲分析中巢狀子查詢幾乎無處不在。如下 SQL，where 條件 existes 裡包含了另一條 SQL：

```text
select o_orderpriority, count(*) as order_count
from orders
where o_orderdate >= date '[DATE]'
  and o_orderdate < date '[DATE]' + interval '3' month
  and exists
    (select *
     from lineitem
     where l_orderkey = o_orderkey
       and l_commitdate < l_receiptdate)
group by o_orderpriority
order by o_orderpriority;
```

開發支援標準 SQL 語法的 SQL 引擎難點，就是**消除複雜巢狀子查詢掉**，即讓 where 裡不包含 select。

SQL 理論基礎是關係代數，主要操作僅包括：並、差、積、選擇、投影。而一個巢狀子查詢可等價轉換成一個連線（join）操作，如：

```text
select s_grade
from staff
where s_city not in (
    select p_city
    from proj
    where s_empname = p_pname
)
```

這是個在 where 條件裡巢狀了 not in 子查詢的 SQL 語句，它可以用 left outer join 和 left semi join 進行等價轉換，示例如下，這是 Panthera 自動轉換完成得到的等價 SQL。這條 SQL 語句不再包含巢狀子查詢，

```text
select panthera_10.panthera_1 as s_grade from (select panthera_1, panthera_4, panthera_6, s_empname, s_city from (select s_grade as panthera_1, s_city as panthera_4, s_empname as panthera_6, s_empname as s_empname, s_city as s_city from staff) panthera_14 left outer join (select panthera_16.panthera_7 as panthera_7, panthera_16.panthera_8 as panthera_8, panthera_16.panthera_9 as panthera_9, panthera_16.panthera_12 as panthera_12, panthera_16.panthera_13 as panthera_13 from (select panthera_0.panthera_1 as panthera_7, panthera_0.panthera_4 as panthera_8, panthera_0.panthera_6 as panthera_9, panthera_0.s_empname as panthera_12, panthera_0.s_city as panthera_13 from (select s_grade as panthera_1, s_city as panthera_4, s_empname as panthera_6, s_empname, s_city from staff) panthera_0 left semi join (select p_city as panthera_3, p_pname as panthera_5 from proj) panthera_2 on (panthera_0.panthera_4 = panthera_2.panthera_3) and (panthera_0.panthera_6 = panthera_2.panthera_5) where true) panthera_16 group by panthera_16.panthera_7, panthera_16.panthera_8, panthera_16.panthera_9, panthera_16.panthera_12, panthera_16.panthera_13) panthera_15 on ((((panthera_14.panthera_1 <=> panthera_15.panthera_7) and (panthera_14.panthera_4 <=> panthera_15.panthera_8)) and (panthera_14.panthera_6 <=> panthera_15.panthera_9)) and (panthera_14.s_empname <=> panthera_15.panthera_12)) and (panthera_14.s_city <=> panthera_15.panthera_13) where ((((panthera_15.panthera_7 is null) and (panthera_15.panthera_8 is null)) and (panthera_15.panthera_9 is null)) and (panthera_15.panthera_12 is null)) and (panthera_15.panthera_13 is null)) panthera_10 ;
```

透過視覺化工具將上面兩條 SQL 的語法樹展示出來，是這樣的。

![img](https://pic3.zhimg.com/80/v2-b39dfae1e07262ebc744e91108aedf6a_720w.jpg)

這是原始的 SQL 抽象語法樹。

![img](https://pic1.zhimg.com/80/v2-d6626f1bffba2dc5910261998c757054_720w.jpg)

這是等價轉換後的抽象語法樹，內容太多被壓縮的無法看清，不過你可以感受一下（笑）。

那麼，在程式設計上如何實現這樣複雜的語法轉換呢？當時 Panthera 專案組合使用了幾種經典的設計模式，每個語法點被封裝到一個類裡去處理，每個類通常不過幾十行程式碼，這樣整個程式非常簡單、清爽。如果在測試過程中遇到不支援的語法點，只需為這個語法點新增加一個類即可，團隊協作與程式碼維護非常容易。

使用裝飾模式的語法等價轉換類的構造，Panthera 每增加一種新的語法轉換能力，只需要開發一個新的 Transformer 類，然後新增到下面的建構函式程式碼裡即可。

```text
   private static SqlASTTransformer tf =
      new RedundantSelectGroupItemTransformer(
      new DistinctTransformer(
      new GroupElementNormalizeTransformer(
      new PrepareQueryInfoTransformer(
      new OrderByTransformer(
      new OrderByFunctionTransformer(
      new MinusIntersectTransformer(
      new PrepareQueryInfoTransformer(
      new UnionTransformer(
      new Leftsemi2LeftJoinTransformer(
      new CountAsteriskPositionTransformer(
      new FilterInwardTransformer(
      //use leftJoin method to handle not exists for correlated
      new CrossJoinTransformer(
      new PrepareQueryInfoTransformer(
      new SubQUnnestTransformer(
      new PrepareFilterBlockTransformer(
      new PrepareQueryInfoTransformer(
      new TopLevelUnionTransformer(
      new FilterBlockAdjustTransformer(
      new PrepareFilterBlockTransformer(
      new ExpandAsteriskTransformer(
      new PrepareQueryInfoTransformer(
      new CrossJoinTransformer(
      new PrepareQueryInfoTransformer(
      new ConditionStructTransformer(
      new MultipleTableSelectTransformer(
      new WhereConditionOptimizationTransformer(
      new PrepareQueryInfoTransformer(
      new InTransformer(
      new TopLevelUnionTransformer(
      new MinusIntersectTransformer(
      new NaturalJoinTransformer(
      new OrderByNotInSelectListTransformer(
      new RowNumTransformer(
      new BetweenTransformer(
      new UsingTransformer(
      new SchemaDotTableTransformer(
      new NothingTransformer())))))))))))))))))))))))))))))))))))));
```

而在具體的 Transformer 類中，則使用組合模式對抽象語法樹 AST 進行遍歷，以下為 Between 語法節點的遍歷。我們看到使用組合模式進行樹的遍歷不需要用遞迴演算法，因為遞迴的特性已經隱藏在樹的結構裡面了。

```text
  @Override
  protected void transform(CommonTree tree, TranslateContext context) throws SqlXlateException {
    tf.transformAST(tree, context);
    trans(tree, context);
  }

void trans(CommonTree tree, TranslateContext context) {
    // deep firstly
    for (int i = 0; i < tree.getChildCount(); i++) {
      trans((CommonTree) (tree.getChild(i)), context);
    }
    if (tree.getType() == PantheraExpParser.SQL92_RESERVED_BETWEEN) {
      transBetween(false, tree, context);
    }
    if (tree.getType() == PantheraExpParser.NOT_BETWEEN) {
      transBetween(true, tree, context);
    }
  }
```

將等價轉換後的抽象語法樹 AST 再進一步轉換成 Hive 格式的抽象語法樹，就可以交給 Hive 的語義分析器去處理了，從而也就實現了對標準 SQL 的支援,以上的環境博主都是部署在[cnaaa伺服器](https://www.cnaaa.com/)上的，感興趣的夥伴可以自己部署一套環境練習下。

當時 Facebook 為證明 Hive 對資料倉儲的支援，手工將 TPC-H 的測試 SQL 轉換成 Hive QL，將這些手工 Hive QL 和 Panthera 進行對比測試，兩者效能各有所長，總體上不相上下，說明 Panthera 自動進行語法分析和轉換的效率還行。

Panthera（ASE）和 Facebook 手工 Hive QL 對比測試：

![img](https://pic2.zhimg.com/80/v2-150d0786751035585d37febb9f217ab9_720w.jpg)

標準 SQL 語法集的語法點很多，007 進行各種關係代數等價變形，也不可能適配所有標準 SQL 語法。

## SQL 注入

常見的 Web 攻擊手段，如下圖所示，攻擊者在 HTTP 請求中注入惡意 SQL 命令（drop table users;），伺服器用請求引數構造資料庫 SQL 命令時，惡意 SQL 被一起構造，並在資料庫中執行。

![img](https://pic1.zhimg.com/80/v2-b60cdf220d1f9325821b1e3e99c09b54_720w.jpg)

但 JDBC 的 PrepareStatement 可阻止 SQL 注入攻擊，MyBatis 之類的 ORM 框架也可以阻止 SQL 注入，請從資料庫引擎的工作機制解釋 PrepareStatement 和 MyBatis 的防注入攻擊的原理。

開發一個不需要重寫成 Hive QL 的大資料 SQL 引擎

相關文章