MaxCompute - ODPS重裝上陣 第七彈 - Grouping Set, Cube and Rollup

MaxCompute（原ODPS）是阿裡雲自主研發的具有業界領先水準的分布式大資料處理平台, 尤其在集團内部得到廣泛應用，支撐了多個BU的核心業務。 MaxCompute除了持續優化性能外，也緻力于提升SQL語言的使用者體驗和表達能力，提高廣大ODPS開發者的生産力。

MaxCompute基于ODPS2.0新一代的SQL引擎，顯著提升了SQL語言編譯過程的易用性與語言的表達能力。我們在此推出MaxCompute(ODPS2.0)重裝上陣系列文章

• 第一彈 - 善用MaxCompute編譯器的錯誤和警告
• 第二彈 - 新的基本資料類型與内建函數
• 第三彈 - 複雜類型
• 第四彈 - CTE，VALUES，SEMIJOIN
• 第五彈 - SELECT TRANSFORM
• 第六彈 - User Defined Type
• 第七彈 - Grouping Set, Cube and Rollup

第六彈向您介紹了User Defined Type，本篇将向您介紹MaxCompute對GROUPING SETS的支援。

場景

由于業務需求，需要經常對資料進行多元度的聚合分析，如既需要對a列做聚合也要對b列做聚合，同時也要按照a、b兩列同時做聚合，是以，不得不寫很多很多的UNION ALL，是以造成了很多重複代碼，維護起來不友善。

該場景的問題，可以通過使用Grouping Sets能夠非常好地解決。

本文中很多例子采用 MaxCompute Studio 作展示，沒有安裝MaxCompute Studio的使用者，可以參照wiki 安裝MaxCompute Studio ， 導入測試MaxCompute項目，建立工程 。

功能簡介

MaxCompute中的GROUPING SETS功能是SELECT語句中GROUP BY子句的擴充。允許采用多種方式對結果分組，而不必使用多個SELECT語句來實作這一目的。這樣能夠使MaxCompute的引擎給出更有的執行計劃，進而提高執行性能。

如下例子：

• 準備資料。資料源requests表記錄了某系統收到的請求。

  create table requests LIFECYCLE 20 as
  select * from values
      (1, 'windows', 'PC', 'Beijing'),
      (2, 'windows', 'PC', 'Shijiazhuang'),
      (3, 'linux', 'Phone', 'Beijing'),
      (4, 'windows', 'PC', 'Beijing'),
      (5, 'ios', 'Phone', 'Shijiazhuang'),
      (6, 'linux', 'PC', 'Beijing'),
      (7, 'windows', 'Phone', 'Shijiazhuang')
  as t(id, os, device, city);           

• 需求：在city次元和os、device次元計算請求數量，同時計算總的請求數。
• 之前解法，必須使用多個 SELECT 語句計算多個分組，并且用UNION ALL把它們連接配接起來：

  SELECT NULL, NULL, NULL, COUNT(*)
    FROM requests
    UNION ALL
    SELECT os, device, NULL, COUNT(*)
    FROM requests GROUP BY os, device
    UNION ALL
    SELECT NULL, NULL, city, COUNT(*)
    FROM requests GROUP BY city;           

  通過MaxCompute Studio的執行圖，我們可以看出，實體執行計劃是做了3次聚合，然後再UNION起來。

  

• GROUPING SETS文法可以做到相同的邏輯，同時，使用更少的代碼，消耗更少的叢集資源：

  SELECT os,device, city ,COUNT(*)
    FROM requests
    GROUP BY os, device, city GROUPING SETS((os, device), (city), ());            

  • GROUPING SETS文法和普通GROUP BY類似，但需要額外執行所需的多個GROUP BY組合。例如以上SQL的((os, device), (city), ())， 請注意這是一個2層的括号，每個内層括号執行一個GROUP BY組合；空括号表示GROUP BY清單為空，即COUNT所有列。
  • 觀察MaxCompute Studio的執行圖，我們發現，實體執行計劃隻包含一個Reduce階段，無需進行UNION操作。

    

• 兩種方法均産生相同的結果，如下所示：

  

請注意:

• 若分組集裡不使用表達式，系統會使用NULL充當占位符，使得這些結果集可以做UNION操作。例如，結果第 1-5 行的city列。

• 相比于UNION多個group by的實作，GROUPINGSETS方式在總的資源消耗上面占優的。但使用GROUPINGSETS會使Reducer的階段變少，如上例，從3個（R2_1, R3_1, R4_1）變為1個(R2_1), 進而導緻總的Reducer instance數變少，可能會使任務端到端時間變長。

  這種情況建議使用

  odps.sql.reducer.instances

  手動調大reducer的instance數目。例如上面的示例，可以

  set odps.sql.reducer.instances=3;

  來保持和原來instance數不變。

CUBE and ROLLUP

CUBE和ROLLUP可以認為是特殊的GROUPING SETS。

CUBE會枚舉指定列的所有可能組合作為GROUPING SETS。而ROLLUP會以按層級聚合的方式産生GROUPING SETS。

例如：

• GROUP BY CUBE(a, b, c)

  等價于

  GROUPING SETS( (a,b,c), (a,b), (a,c), (b,c), (a) ,(b), (c), () )

• GROUP BY ROLLUP(a,b,c)

  GROUPING SETS( (a,b,c), (a,b), (a), () )

CUBE會把GROUP BY列進行全量組合，即N個列會産生 2^N中組合，故不建議N的數目超過5。

GROUPING() and GROUPING_ID()

前面提到，系統在GROUPING SETS結果中用 NULL 用作占位符，當出現此情況後，将無法區分占位符 NULL 與資料中真正的 NULL，針對這個問題，MaxCompute提供了GROUPING 函數。

GROUPING 函數接受一個列名作為參數，如果結果對應行使用了參數列做聚合，傳回0，此時意味着NULL來自輸入資料。否則傳回1，此時意味着NULL是GROUPING SETS的占位符。

此外，MaxCompute還提供了GROUPING_ID函數，此函數接受1個或多個列名作為參數。結果是将參數列的GROUPING結果按照BitMap的方式組成整數。如：

SELECT 
    a,b,c ,COUNT(*),
    GROUPING(a) ga, GROUPING(b) gb, GROUPING(c) gc, GROUPING_ID(a,b,c) groupingid
FROM VALUES (1,2,3) as t(a,b,c)
GROUP BY CUBE(a,b,c);           

結果：

預設情況，GROUP BY清單中不被使用的列，會被填充為NULL。我們可以通過GROUPING函數輸出更有實際意義的值。如：

SELECT 
  IF(GROUPING(os) == 0, os, 'ALL') as os,
  IF(GROUPING(device) == 0, device, 'ALL') as device, 
  IF(GROUPING(city) == 0, city, 'ALL') as city ,
  COUNT(*) as count
FROM requests
GROUP BY os, device, city GROUPING SETS((os, device), (city), ());           

輸出結果：

注意事項

CUBE會把GROUP BY列進行全量組合，即N個列會産生 2^N中組合，目前我們設定了GROUP BY列上限為13個。

小節

GROUPING SETS擴充了GROUP BY的聚合功能，在易用性，相容性和性能方面，可以更好的滿足您的需求。

對于SQL比較熟悉的專家會發現，上述功能大部分是标準的SQL支援的功能。MaxCompute會持續提升與标準SQL和業界常用産品的相容性。