大多數使用者對SQL的文法並不陌生，簡單來說，MaxCompute SQL是用於查詢和分析MaxCompute中的大規模資料。目前SQL的主要功能如下所示。
  • 支援各類運算子。
  • 通過DDL語句對錶、分區以及視圖進行管理。
  • 通過Select語句查詢表中的記錄，通過Where語句過濾表中的記錄。
  • 通過Insert語句插入資料、更新資料。
  • 通過等值串連Join操作，支援兩張表的關聯，並支援多張小表的Mapjoin。
  • 支援通過內建函數和自訂函數來進行計算。
  • 支援Regex。
本文將為您簡單介紹MaxCompute SQL使用中需要注意的問題，不再做操作樣本。
说明
  • MaxCompute SQL不支援事務、索引及Update/Delete等操作，同時MaxCompute的SQL文法與Oracle，MySQL有一定差別，您無法將其他資料庫中的SQL語句無縫遷移到MaxCompute上來。
  • 在使用方式上，MaxCompute作業提交後會有幾十秒到數分鐘不等的排隊調度，所以適合處理跑批作業，一次作業批量處理海量資料，不適合直接對接需要每秒處理幾千至數萬筆事務的前台業務系統。
  • 關於SQL操作的詳細樣本，請參見SQL模組。

DDL語句

簡單的DDL操作包括建立表、添加分區、查看錶和分區資訊、修改表、刪除表和分區，更多詳情請參見建立/查看/刪除表。

Select語句
  • group by語句的key可以是輸入表的列名，也可以是由輸入表的列構成的運算式，不可以是Select語句的輸出資料行。
    select substr(col2, 2) from tbl group by substr(col2, 2);-- 可以，group by的key可以是輸入表的列構成的運算式；
 select col2 from tbl group by substr(col2, 2); -- 不可以，group by的key不在select語句的列中；
 select substr(col2, 2) as c from tbl group by c; -- 不可以，group by的key 不可以是列的別名，即select語句的輸出資料行；

    之所以有這樣的限制，是因為在通常的SQL解析中，group by的操作先於Select操作，因此group by只能接受輸入表的列或運算式為key。

  • order by必須與limit連用。
  • sort by前必須加distribute by。
  • order by/sort by/distribute by的key必須是Select語句的輸出資料行，即列的別名。如下所示：
    select col2 as c from tbl order by col2 limit 100 --不可以，order by的key不是select語句的輸出資料行，即列的別名
  select col2 from tbl order by col2 limit 100; -- 可以，當select語句的輸出資料行沒有別名時，使用列名作為別名。

    之所以有這樣的限制，是因為在通常的SQL解析中，order by/sort by/distribute by後於Select操作，因此它們只能接受Select語句的輸出資料行為key。

Insert語句

  • 向某個分區插入資料時，分區列不可以出現在Select列表中。
    insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')  
    select shop_name, customer_id, total_price, sale_date, region  from sale_detail;
  -- 報錯返回，sale_date，region為分區列，不可以出現在靜態分區的insert語句中。
  • 動態分區插入時，動態分區列必須在Select列表中。
    insert overwrite table sale_detail_dypart partition (sale_date='2013', region) 
select shop_name,customer_id,total_price from sale_detail; 
--失敗返回，動態分區插入時，動態分區列必須在select列表中。

Join操作

  • MaxCompute SQL支援的Join操作類型包括{LEFT OUTER|RIGHT OUTER|FULL OUTER|INNER} JOIN。
  • 目前最多支援16個並發Join操作。
  • 在Mapjoin中，最多支援8張小表的Mapjoin。

Union All

Union All可以把多個Select操作返回的結果，聯合成一個資料集。它會返回所有的結果，但是不會執行去重。MaxCompute不支援直接對頂級的兩個查詢結果進行Union操作，需要寫成子查詢的形式。
说明
  • Union All串連的兩個Select查詢語句，兩個Select的列個數、列名稱、列類型必須嚴格一致。
  • 如果原名稱不一致，可以通過別名設定成相同的名稱。

其他

  • MaxCompute SQL目前最多支援128個並發Union操作。
  • 最多支援128個並發insert overwrite/into操作。

MaxCompute SQL的更多限制請參見SQL限制項匯總。

SQL最佳化樣本

  • Join語句中Where條件的位置
    當兩個表進行Join操作時，主表的Where限制可以寫在最後，但從表分區限制條件不要寫在Where條件中，建議寫在ON條件或者子查詢中。主表的分區限制條件可以寫在Where條件中（最好先用子查詢過濾）。樣本如下： 
    select * from A join (select * from B where dt=20150301)B on B.id=A.id where A.dt=20150301； 
select * from A join B on B.id=A.id where B.dt=20150301； --不允許 
select * from (select * from A where dt=20150301)A join (select * from B where dt=20150301)B on B.id=A.id；

    第二個語句會先Join，後進行分區裁剪，資料量變大，效能下降。在實際使用過程中，應該盡量避免第二種用法。

  • 資料扭曲

    產生資料扭曲的根本原因是有少數Worker處理的資料量遠遠超過其他Worker處理的資料量，從而導致少數Worker的運行時間長度遠遠超過其他的平均運行時間長度，從而導致整個任務已耗用時間超長，造成任務延遲。

    更多資料扭曲最佳化的詳情請參見計算長尾調優。

    • Join造成的資料扭曲
      造成Join資料扭曲的原因是Join on的key分布不均勻。假設還是上述樣本語句，現在將大表A和小表B進行Join操作，運行如下語句。 
      select * from A join B on A.value= B.value;
      此時，複製logview的連結並開啟webcosole頁面，雙擊執行Join操作的fuxi job，可以看到此時在[Long-tails]地區有長尾，表示資料已經傾斜了。

      此時您可通過如下方法進行最佳化。
      • 由於表B是個小表並且沒有超過512MB，您可將上述語句最佳化為mapjoin語句再執行，語句如下。
        select /*+ MAPJOIN(B) */ * from A join B on A.value= B.value;
      • 您也可將傾斜的key用單獨的邏輯來處理，例如經常發生兩邊的key中有大量null資料導致了傾斜。則需要在Join前先過濾掉null的資料或者補上隨機數，然後再進行Join，樣本如下。
        select * from A join B
on case when A.value is null then concat('value',rand() ) else A.value end = B.value;
    在實際情境中，如果您知道資料扭曲了，但無法擷取導致資料扭曲的key資訊，那麼可以使用一個通用的方案，查看資料扭曲，如下所示。 
    
例如：select * from a join b on a.key=b.key; 產生資料扭曲。 
您可以執行： 
```sql
select left.key, left.cnt * right.cnt from 
(select key, count(*) as cnt from a group by key) left 
join
(select key, count(*) as cnt from b group by key) right
on left.key=right.key;

    查看key的分布，可以判斷a join b時是否會有資料扭曲。

  • group by傾斜

    造成group by傾斜的原因是group by的key分布不均勻。

    假設表A內有兩個欄位（key，value），表內的資料量足夠大，並且key的值分布不均，運行語句如下所示： 
    select key,count(value) from A group by key;

    當表中的資料足夠大時，您會在webcosole頁面看見長尾。若想解決這個問題，您需要在執行SQL前設定防傾斜的參數，設定語句為set odps.sql.groupby.skewindata=true。

  • 錯誤使用動態分區造成的資料扭曲
    動態分區的SQL，在MaxCompute中會預設增加一個Reduce，用來將相同分區的資料合併在一起。這樣做的好處，如下所示。
    • 可減少MaxCompute系統產生的小檔案，使後續處理更快速。
    • 可避免一個Worker輸出檔案很多時佔用記憶體過大。
    但也正是因為這個Reduce的引入，導致分區資料如果有傾斜的話，會發生長尾。因為相同的資料最多隻會有10個Worker處理，所以資料量大，則會發生長尾，樣本如下。 
    
insert overwrite table A2 partition(dt) 
select
split_part(value,'\t',1) as field1,
split_part(value,'\t',2) as field2, 
dt 
from A 
where dt='20151010';
    這種情況下，沒有必要使用動態分區，所以可以改為如下語句： 
    
insert overwrite table A2 partition(dt='20151010') 
select
split_part(value,'\t',1) as field1,
split_part(value,'\t',2) as field2
from A 
where dt='20151010';
  • 視窗函數的最佳化
    如果您的SQL語句中用到了視窗函數，一般情況下每個視窗函數會形成一個Reduce作業。如果視窗函數略多，那麼就會消耗資源。在某些特定情境下，視窗函數是可以進行最佳化的。
    • 視窗函數over後面要完全相同，相同的分組和排序條件。
    • 多個視窗函數在同一層SQL執行。
    符合上述兩個條件的視窗函數會合并為一個Reduce執行。SQL樣本如下所示。 
    
select
rank()over(partition by A order by B desc) as rank,
row_number()over(partition by A order by B desc) as row_num
from MyTable;
  • 子查詢改Join
    例如有一個子查詢，如下所示。 
    SELECT * FROM table_a a WHERE a.col1 IN (SELECT col1 FROM table_b b WHERE xxx);
    當此語句中的table_b子查詢返回的col1的個數超過1000個時，系統會報錯為 records returned from subquery exceeded limit of 1000。此時您可以使用Join語句來代替，如下所示。 
    SELECT a.* FROM table_a a JOIN (SELECT DISTINCT col1 FROM table_b b WHERE xxx) c ON (a.col1 = c.col1)
    说明
    • 如果沒用Distinct，而子查詢c返回的結果中有相同的col1的值，可能會導致a表的結果數變多。
    • 因為Distinct子句會導致查詢全落到一個Worker裡，如果子查詢資料量比較大的話，可能會導致查詢比較慢。
    • 如果已經從業務上控制了子查詢裡的col1不可能會重複，比如查的是主鍵欄位，為了提高效能，可以把Distinct去掉。