全部產品
Search
文件中心

MaxCompute:SELECT文法

更新時間:Jun 25, 2024

MaxCompute支援通過SELECT語句查詢資料。本文為您介紹SELECT命令格式及如何?巢狀查詢、分組查詢、排序等操作。

執行SELECT操作前需要具備目標表的讀取資料許可權(SELECT)。授權操作請參見MaxCompute許可權

本文中的命令您可以在如下工具平台執行:

功能介紹

SELECT語句用於從表中選取滿足指定條件的資料。您可以根據實際情境結合以下功能完成多樣化的查詢操作。

類型

功能

子查詢(SUBQUERY)

在某個查詢的執行結果基礎上進一步執行查詢操作時,可以通過子查詢操作實現。

交集(INTERSECT)、並集(UNION)和補集(EXCEPT)

對查詢結果資料集執行取交集、並集或補集操作。

JOIN

通過join操作串連表並返回符合串連條件和查詢條件的資料資訊。

SEMI JOIN(半串連)

通過右表過濾左表的資料,右表的資料不出現在結果集中。

MAPJOIN HINT

對一個大表和一個或多個小表執行join操作時,可以在SELECT語句中顯式指定mapjoin Hint提示以提升查詢效能。

SKEWJOIN HINT

當兩張表Join存在熱點,導致出現長尾問題時,您可以通過取出熱點key,將資料分為熱點資料和非熱點資料兩部分處理,最後合并的方式,提高Join效率。

Lateral View

通過Lateral View與UDTF(表產生函數)結合,將單行資料拆成多行資料。

GROUPING SETS

對資料進行多維度彙總分析。

SELECT TRANSFORM

SELECT TRANSFORM文法允許您啟動一個指定的子進程,將輸入資料按照一定的格式通過標準輸入至子進程,並且通過解析子進程的標準輸出擷取輸出資料。

Split Size Hint

通過修改Split Size來控制並發度數量。

Time travel查詢與增量查詢

對於Delta類型的表,支援:

  • 通過TimeTravel查詢,可以回溯到源表某個歷史時間或者版本進行歷史Snapshot查詢。

  • 通過Incremental查詢,指定源表某個歷史時間區間或者版本區間進行歷史增量查詢。

使用限制

  • 當使用SELECT語句時,屏顯最多隻能顯示10000行結果,同時返回結果要小於10 MB。當SELECT語句作為子句時則無此限制,SELECT子句會將全部結果返回給上層查詢。

  • SELECT語句查詢分區表時預設禁止全表掃描。

    自2018年1月10日20:00:00後,在新建立的專案上執行SQL語句時,預設情況下,針對該專案裡的分區表不允許執行全表掃描操作。在查詢分區表資料時必須指定分區,由此減少SQL的不必要I/O,從而減少計算資源的浪費以及隨用隨付模式下不必要的計算費用。

    如果您需要對分區表進行全表掃描,可以在全表掃描的SQL語句前加上命令set odps.sql.allow.fullscan=true;,並和SQL語句一起提交執行。假設sale_detail表為分區表,需要同時執行如下語句進行全表查詢:

    set odps.sql.allow.fullscan=true;
    SELECT * from sale_detail;
  • 當查詢聚簇表(cluster表)時,目前版本只對單表掃描分區數小於等於400時進行分桶裁剪最佳化。當分桶裁剪最佳化未生效時,會導致掃描資料增加。如果您使用的是按需付費模式,則導致費用增加;如果您使用訂用帳戶付費模式,則會導致SQL計算效能下降。

命令格式

[with <cte>[, ...] ]
SELECT [all | distinct] <SELECT_expr>[, <except_expr>][, <replace_expr>] ...
       from <table_reference>
       [where <where_condition>]
       [group by {<col_list>|rollup(<col_list>)}]
       [having <having_condition>]
       [window <window_clause>]
       [order by <order_condition>]
       [distribute by <distribute_condition> [sort by <sort_condition>]|[ cluster by <cluster_condition>] ]
       [limit <number>]
       

命令中各欄位的執行語序請參見SELECT語序

樣本資料

為便於理解使用方法,本文為您提供來源資料,基於來源資料提供相關樣本。建立表sale_detail,並添加資料,命令樣本如下。

--建立一張分區表sale_detail。
create table if not exists sale_detail
(
shop_name     string,
customer_id   string,
total_price   double
)
partitioned by (sale_date string, region string);

--向源表增加分區。
alter table sale_detail add partition (sale_date='2013', region='china');

--向源表追加資料。
insert into sale_detail partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);

查詢分區表sale_detail中的資料,命令樣本如下:

SELECT * from sale_detail;
--返回結果。
+------------+------------+------------+------------+------------+
| shop_name  | price      | customer   | sale_date  | region     |
+------------+------------+------------+------------+------------+
| s1         | 100.1      | c1         | 2013       | china      |
| s2         | 100.2      | c2         | 2013       | china      |
| s3         | 100.3      | c3         | 2013       | china      |
+------------+------------+------------+------------+------------+

WITH子句(cte)

可選。WITH子句包含一個或多個常用的運算式CTE。CTE充噹噹前運行環境中的暫存資料表,您可以在之後的查詢中引用該表。CTE使用規則如下:

  • 在同一WITH子句中的CTE必須具有唯一的名字。

  • 在WITH子句中定義的CTE僅對在同一WITH子句中的其他CTE可以使用。

    假設A是子句中的第一個CTE,B是子句中的第二個CTE:

    • A引用A:無效。錯誤命令樣本如下。

      with 
      A as (SELECT 1 from A) 
      SELECT * from A;
    • A引用B,B引用A:無效,不允許循環參考。錯誤命令樣本如下

      with 
      A as (SELECT * from B ), 
      B as (SELECT * from A ) 
      SELECT * from B;

正確命令樣本如下。

with 
A as (SELECT 1 as C),
B as (SELECT * from A) 
SELECT * from B;

返回結果如下。

+---+
| c |
+---+
| 1 |
+---+

列運算式(SELECT_expr)

必填。SELECT_expr格式為col1_name, col2_name, 列運算式,...,表示待查詢的普通列、分區列或Regex。列運算式使用規則如下:

  • 用列名指定要讀取的列。

    讀取表sale_detail的列shop_name。命令樣本如下。

    SELECT shop_name from sale_detail;

    返回結果如下。

    +------------+
    | shop_name  |
    +------------+
    | s1         |
    | s2         |
    | s3         |
    +------------+
  • 用星號(*)代表查詢所有的列。可配合where子句指定過濾條件。

    • 讀取表sale_detail中所有的列。命令樣本如下。

      --開啟全表掃描,僅此Session有效。
      set odps.sql.allow.fullscan=true;
      SELECT * from sale_detail;

      返回結果如下。

      +------------+-------------+-------------+------------+------------+
      | shop_name  | customer_id | total_price | sale_date  | region     |
      +------------+-------------+-------------+------------+------------+
      | s1         | c1          | 100.1       | 2013       | china      |
      | s2         | c2          | 100.2       | 2013       | china      |
      | s3         | c3          | 100.3       | 2013       | china      |
      +------------+-------------+-------------+------------+------------+
    • where子句中指定過濾條件。命令樣本如下。

      SELECT * from sale_detail where shop_name='s1';

      返回結果如下。

      +------------+-------------+-------------+------------+------------+
      | shop_name  | customer_id | total_price | sale_date  | region     |
      +------------+-------------+-------------+------------+------------+
      | s1         | c1          | 100.1       | 2013       | china      |
      +------------+-------------+-------------+------------+------------+
  • 可以使用Regex。

    • 選出sale_detail表中所有列名以sh開頭的列。命令樣本如下。

      SELECT `sh.*` from sale_detail;

      返回結果如下。

      +------------+
      | shop_name  |
      +------------+
      | s1         |
      | s2         |
      | s3         |
      +------------+
    • 選出sale_detail表中列名不為shop_name的所有列。命令樣本如下。

      SELECT `(shop_name)?+.+` from sale_detail;

      返回結果如下。

      +-------------+-------------+------------+------------+
      | customer_id | total_price | sale_date  | region     |
      +-------------+-------------+------------+------------+
      | c1          | 100.1       | 2013       | china      |
      | c2          | 100.2       | 2013       | china      |
      | c3          | 100.3       | 2013       | china      |
      +-------------+-------------+------------+------------+
    • 選出sale_detail表中排除shop_namecustomer_id兩列的其他列。命令樣本如下。

      SELECT `(shop_name|customer_id)?+.+` from sale_detail;

      返回結果如下。

      +-------------+------------+------------+
      | total_price | sale_date  | region     |
      +-------------+------------+------------+
      | 100.1       | 2013       | china      |
      | 100.2       | 2013       | china      |
      | 100.3       | 2013       | china      |
      +-------------+------------+------------+
    • 選出sale_detail表中排除列名以t開頭的其他列。命令樣本如下。

      SELECT `(t.*)?+.+` from sale_detail;

      返回結果如下。

      +------------+-------------+------------+------------+
      | shop_name  | customer_id | sale_date  | region     |
      +------------+-------------+------------+------------+
      | s1         | c1          | 2013       | china      |
      | s2         | c2          | 2013       | china      |
      | s3         | c3          | 2013       | china      |
      +------------+-------------+------------+------------+
      說明

      在排除多個列時,如果col2是col1的首碼,則需保證col1寫在col2的前面(較長的col寫在前面)。例如,一個表有2個分區無需被查詢,一個分區名為ds,另一個分區名為dshh,由於前者是後者的首碼,正確運算式為SELECT `(dshh|ds)?+.+` from t;;錯誤運算式為SELECT `(ds|dshh)?+.+` from t;

  • 在選取的列名前可以使用distinct去掉重複欄位,只返回去重後的值。使用all會返回欄位中所有重複的值。不指定此選項時,預設值為all

    • 查詢表sale_detail中region列資料,如果有重複值時僅顯示一條。命令樣本如下。

      SELECT distinct region from sale_detail;

      返回結果如下。

      +------------+
      | region     |
      +------------+
      | china      |
      +------------+
    • 去重多列時,distinct的範圍是SELECT的列集合,不是單個列。命令樣本如下。

      SELECT distinct region, sale_date from sale_detail;

      返回結果如下。

      +------------+------------+
      | region     | sale_date  |
      +------------+------------+
      | china      | 2013       |
      +------------+------------+
    • distinct可以對視窗函數的計算結果進行去重,即distinct可以配合視窗函數使用。命令樣本如下:

      set odps.sql.allow.fullscan=true;
      SELECT distinct sale_date, row_number() over (partition by customer_id order by total_price) as rn from sale_detail;

      返回結果如下。

      +-----------+------------+
      | sale_date | rn         |
      +-----------+------------+
      | 2013      | 1          |
      +-----------+------------+

      目前不支援distinctgroup by聯合使用,例如執行如下命令會報錯。

      SELECT distinct shop_name from sale_detail group by shop_name;
      --報錯資訊: GROUP BY cannot be used with SELECT DISTINCT

排除列(except_expr)

可選。except_expr格式為except(col1_name, col2_name, ...)。當您希望讀取表內大多數列的資料,同時要排除表中少數列的資料時,可以通過SELECT * except(col1_name, col2_name, ...) from ...;語句實現,表示讀取表資料時會排除指定列(col1、col2)的資料。

命令樣本如下。

--讀取sale_detail表的資料,並排除region列的資料。
SELECT * except(region) from sale_detail;

返回結果如下。

+-----------+-------------+-------------+-----------+
| shop_name | customer_id | total_price | sale_date |
+-----------+-------------+-------------+-----------+
| s1        | c1          | 100.1       | 2013      |
| s2        | c2          | 100.2       | 2013      |
| s3        | c3          | 100.3       | 2013      |
+-----------+-------------+-------------+-----------+

修改列(replace_expr)

可選。replace_expr格式為replace(exp1 [as] col1_name, exp2 [as] col2_name, ...)。當您希望讀取表內大多數列的資料,同時要對錶中少數列的資料進行修改時,可以通過SELECT * replace(exp1 as col1_name, exp2 as col2_name, ...) from ...;實現,表示讀取表資料時會將col1的資料修改為exp1,將col2的資料修改為exp2。

命令樣本如下。

--讀取sale_detail表的資料,並修改total_price、region兩列的資料。
SELECT * replace(total_price+100 as total_price, 'shanghai' as region) from sale_detail;

返回結果如下。

+-----------+-------------+-------------+-----------+--------+
| shop_name | customer_id | total_price | sale_date | region |
+-----------+-------------+-------------+-----------+--------+
| s1        | c1          | 200.1       | 2013      | shanghai |
| s2        | c2          | 200.2       | 2013      | shanghai |
| s3        | c3          | 200.3       | 2013      | shanghai |
+-----------+-------------+-------------+-----------+--------+

目標表資訊(table_reference)

必填。table_reference表示查詢的目標表資訊。目標表使用規則如下:

  • 直接指定目標表名。命令樣本如下。

    SELECT customer_id from sale_detail;

    返回結果如下。

    +-------------+
    | customer_id |
    +-------------+
    | c1          |
    | c2          |
    | c3          |
    +-------------+
  • 嵌套子查詢。命令樣本如下。

    SELECT * from (SELECT region,sale_date from sale_detail) t where region = 'china';

    返回結果如下。

    +------------+------------+
    | region     | sale_date  |
    +------------+------------+
    | china      | 2013       |
    | china      | 2013       |
    | china      | 2013       |
    +------------+------------+

WHERE子句(where_condition)

可選。where子句為過濾條件。如果表是分區表,可以實現列裁剪。使用規則如下:

  • 配合關係運算子,篩選滿足指定條件的資料。關係運算子包含:

    • ><=>=<=<>

    • likerlike

    • innot in

    • between…and

    詳情請參見關係運算子

    where子句中,您可以指定分區範圍,只掃描表的指定部分,避免全表掃描。命令樣本如下。

    SELECT * 
    from sale_detail
    where sale_date >= '2008' and sale_date <= '2014';
    --等價於如下語句。
    SELECT * 
    from sale_detail 
    where sale_date between '2008' and '2014';

    返回結果如下。

    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | s1         | c1          | 100.1       | 2013       | china      |
    | s2         | c2          | 100.2       | 2013       | china      |
    | s3         | c3          | 100.3       | 2013       | china      |
    +------------+-------------+-------------+------------+------------+
    說明

    您可以通過EXPLAIN語句查看分區裁剪是否生效。普通的UDF或JOIN的分區條件寫法都有可能導致分區裁剪不生效,詳情請參見分區剪裁合理性評估

  • 通過UDF實現分區裁剪,將UDF語句先當作一個小作業執行,再將執行的結果替換到原來UDF出現的位置。

    • 實現方式

      • 在編寫UDF的時候,UDF類上加入Annotation。

        @com.aliyun.odps.udf.annotation.UdfProperty(isDeterministic=true)
        說明

        com.aliyun.odps.udf.annotation.UdfProperty定義在odps-sdk-udf.jar檔案中。您需要把引用的odps-sdk-udf版本提高到0.30.x或以上。

      • 在SQL語句前增加set odps.sql.udf.ppr.deterministic = true;語句,此時SQL中所有的UDF均被視為deterministic。該操作執行的原理是進行執行結果回填,但是結果回填最多回填1000個分區。因此,如果UDF類加入Annotation,則可能會導致出現超過1000個回填結果的報錯。此時您如果需要忽視此錯誤,可以通過設定set odps.sql.udf.ppr.to.subquery = false;全域關閉此功能。關閉後,UDF分區裁剪也會失效。

    • 注意事項

      使用UDF實現分區裁剪時,UDF必須在查詢表的where條件裡才會生效。

      • 用UDF實現分區裁剪正確樣本如下。

        --UDF必須放在查詢的源表的where條件中:
        SELECT key, value from srcp where udf(ds) = 'xx';
      • 用UDF實現分區裁剪錯誤樣本如下。

        --放在join on後面分區裁剪不會生效
        SELECT A.c1, A.c2 from srcp1 A  join srcp2  B on A.c1 = B.c1 and udf(A.ds) ='xx';
  • 列運算式(SELECT_expr)中,如果被重新命名的欄欄位(賦予了列別名)使用了函數,則不能在where子句中引用列別名。錯誤命令樣本如下。

    SELECT  task_name
            ,inst_id
            ,settings
            ,GET_JSON_OBJECT(settings, '$.SKYNET_ID') as skynet_id
            ,GET_JSON_OBJECT(settings, '$.SKYNET_NODENAME') as user_agent
    from    Information_Schema.TASKS_HISTORY
    where   ds = '20211215' and skynet_id is not null
    limit 10;

GROUP BY分組查詢(col_list)

可選。通常,group by彙總函式配合使用,根據指定的普通列、分區列或Regex進行分組。group by使用規則如下:

  • group by操作優先順序高於SELECT操作,因此group by的取值是SELECT輸入表的列名或由輸入表的列構成的運算式。需要注意的是:

    • group by取值為Regex時,必須使用列的完整運算式。

    • SELECT語句中沒有使用彙總函式的列必須出現在GROUP BY中。

    使用樣本:

    • 直接使用輸入表列名region作為group by的列,即以region值分組。命令樣本如下。

      SELECT region from sale_detail group by region;

      返回結果如下。

      +------------+
      | region     |
      +------------+
      | china      |
      +------------+
    • 以region值分組,返回每一組的銷售額總量。命令樣本如下。

      SELECT sum(total_price) from sale_detail group by region;

      返回結果如下。

      +------------+
      | _c0        |
      +------------+
      | 300.6      |
      +------------+
    • 以region值分組,返回每一組的region值(組內唯一)及銷售額總量。命令樣本如下。

      SELECT region, sum (total_price) from sale_detail group by region;

      返回結果如下。

      +------------+------------+
      | region     | _c1        |
      +------------+------------+
      | china      | 300.6      |
      +------------+------------+
    • SELECT列的別名分組,命令樣本如下。

      SELECT region as r from sale_detail group by r;
      --等效於如下語句。
      SELECT region as r from sale_detail group by region;

      返回結果如下。

      +------------+
      | r          |
      +------------+
      | china      |
      +------------+
    • 以列運算式分組,命令樣本如下。

      SELECT 2 + total_price as r from sale_detail group by 2 + total_price;

      返回結果如下。

      +------------+
      | r          |
      +------------+
      | 102.1      |
      | 102.2      |
      | 102.3      |
      +------------+
    • SELECT的所有列中沒有使用彙總函式的列,必須出現在GROUP BY中,否則返回報錯。錯誤命令樣本如下。

      SELECT region, total_price from sale_detail group by region;

      正確命令樣本如下。

      SELECT region, total_price from sale_detail group by region, total_price;

      返回結果如下。

      +------------+-------------+
      | region     | total_price |
      +------------+-------------+
      | china      | 100.1       |
      | china      | 100.2       |
      | china      | 100.3       |
      +------------+-------------+
  • 當SQL語句設定了屬性,即set odps.sql.groupby.position.alias=true;group by中的整型常量會被當作SELECT的列序號處理。命令樣本如下。

    --與下一條SQL語句一起執行。
    set odps.sql.groupby.position.alias=true;
    --1代表SELECT的列中第一列即region,以region值分組,返回每一組的region值(組內唯一)及銷售額總量。
    SELECT region, sum(total_price) from sale_detail group by 1;

    返回結果如下。

    +------------+------------+
    | region     | _c1        |
    +------------+------------+
    | china      | 300.6      |
    +------------+------------+

HAVING子句(having_condition)

可選。通常HAVING子句與彙總函式一起使用,實現過濾。命令樣本如下。

--為直觀展示資料呈現效果,向sale_detail表中追加資料。
insert into sale_detail partition (sale_date='2014', region='shanghai') values ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
--使用having子句配合彙總函式實現過濾。
SELECT region,sum(total_price) from sale_detail 
group by region 
having sum(total_price)<305;

返回結果如下。

+------------+------------+
| region     | _c1        |
+------------+------------+
| china      | 300.6      |
| shanghai   | 200.9      |
+------------+------------+

ORDER BY全域排序(order_condition)

可選。order by用於對所有資料按照指定普通列、分區列或指定常量進行全域排序。order by使用規則如下:

  • 預設對資料進行升序排序,如果降序排序,需要使用desc關鍵字。

  • order by預設要求帶limit資料行數限制,沒有limit會返回報錯。如您需要解除order by必須帶limit的限制,詳情請參見LIMIT NUMBER限制輸出行數>解除ORDER BY必須帶LIMIT的限制

    • 查詢表sale_detail的資訊,並按照total_price升序排列前2條。命令樣本如下。

      SELECT * from sale_detail order by total_price limit 2;

      返回結果如下。

      +------------+-------------+-------------+------------+------------+
      | shop_name  | customer_id | total_price | sale_date  | region     |
      +------------+-------------+-------------+------------+------------+
      | s1         | c1          | 100.1       | 2013       | china      |
      | s2         | c2          | 100.2       | 2013       | china      |
      +------------+-------------+-------------+------------+------------+
    • 查詢表sale_detail的資訊,並按照total_price降序排列前2條。命令樣本如下。

      SELECT * from sale_detail order by total_price desc limit 2;

      返回結果如下。

      +------------+-------------+-------------+------------+------------+
      | shop_name  | customer_id | total_price | sale_date  | region     |
      +------------+-------------+-------------+------------+------------+
      | s3         | c3          | 100.3       | 2013       | china      |
      | s2         | c2          | 100.2       | 2013       | china      |
      +------------+-------------+-------------+------------+------------+
  • 在使用order by排序時,NULL會被認為比任何值都小,這個行為與MySQL一致,但是與Oracle不一致。

    查詢表sale_detail的資訊,並按照total_price升序排列前2條。命令樣本如下。

    SELECT * from sale_detail order by total_price limit 2;

    返回結果如下。

    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | s1         | c1          | 100.1       | 2013       | china      |
    | s2         | c2          | 100.2       | 2013       | china      |
    +------------+-------------+-------------+------------+------------+
  • order by後面需要加上SELECT列的別名。當SELECT某列時,如果沒有指定列的別名,則列名會被作為列的別名。

    order by加列的別名。命令樣本如下。

    SELECT total_price as t from sale_detail order by total_price limit 3;
    --等效於如下語句。
    SELECT total_price as t from sale_detail order by t limit 3;

    返回結果如下。

    +------------+
    | t          |
    +------------+
    | 100.1      |
    | 100.2      |
    | 100.3      |
    +------------+
  • 當SQL語句設定了屬性,即set odps.sql.orderby.position.alias=true;order by中的整型常量會被當作SELECT的列序號處理。命令樣本如下。

    --與下一條SQL語句一起執行。
    set odps.sql.orderby.position.alias=true;
    SELECT * from sale_detail order by 3 limit 3;

    返回結果如下。

    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | s1         | c1          | 100.1       | 2013       | china      |
    | s2         | c2          | 100.2       | 2013       | china      |
    | s3         | c3          | 100.3       | 2013       | china      |
    +------------+-------------+-------------+------------+------------+
  • offset可以和order by...limit語句配合使用,用於指定跳過的行數,格式為order by...limit m offset n,也可以簡寫為order by...limit n, m。其中:limit m控制輸出m行資料,offset n表示在開始返回資料之前跳過的行數。offset 0與省略offset子句效果相同。

    將表sale_detail按照total_price升序排序後,輸出從第3行開始的3行資料。命令樣本如下。

    SELECT customer_id,total_price from sale_detail order by total_price limit 3 offset 2;
    --等效於如下語句。
    SELECT customer_id,total_price from sale_detail order by total_price limit 2, 3;

    返回結果如下。

    +-------------+-------------+
    | customer_id | total_price |
    +-------------+-------------+
    | c3          | 100.3       |
    +-------------+-------------+

    由於查詢到的資料從第3行開始僅剩1行資料,不足3行,所以返回結果只有1行。

  • Range Clustering可以用來做全域排序加速。在普通的ORDER BY情境,為保證全域有序,所有的排序資料合併到一個單獨的Instance運行,這就無法發揮平行處理的優勢。利用Range Clustering的partition步驟,可以實現並發多路全排序。首先對資料取樣並劃分Range,然後對各個Range做並發排序,最後得到的就是全域有序的結果,詳情請參見全域排序加速

DISTRIBUTE BY雜湊分區(distribute_condition)

可選。distribute by用於對資料按照某幾列的值做Hash分區。

distribute by控制Map(讀資料)的輸出在Reducer中是如何劃分的,如果不希望Reducer的內容存在重疊,或需要對同一分組的資料一起處理,您可以使用distribute by來保證同組資料分發到同一個Reducer中。

必須使用SELECT的輸出資料行別名,當SELECT某列時,如果沒有指定列的別名,則列名會被作為列的別名。命令樣本如下:

--查詢表sale_detail中的列region值並按照region值進行雜湊分區。
SELECT region from sale_detail distribute by region;
--等價於如下語句。
SELECT region as r from sale_detail distribute by region;
SELECT region as r from sale_detail distribute by r;

SORT BY局部排序(sort_condition)

可選。通常,配合distribute by使用。sort by使用規則如下:

  • sort by預設對資料進行升序排序,如果降序排序,需要使用desc關鍵字。

  • 如果sort by語句前有distribute bysort by會對distribute by的結果按照指定的列進行排序。

    • 查詢表sale_detail中的列region和total_price的值並按照region值進行雜湊分區,然後按照total_price對雜湊分區結果進行局部升序排序。命令樣本如下。

      --為直觀展示資料呈現效果,向sale_detail表中追加資料。
      insert into sale_detail partition (sale_date='2014', region='shanghai') values ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
      SELECT region,total_price from sale_detail distribute by region sort by total_price;

      返回結果如下。

      +------------+-------------+
      | region     | total_price |
      +------------+-------------+
      | shanghai   | NULL        |
      | china      | 100.1       |
      | china      | 100.2       |
      | china      | 100.3       |
      | shanghai   | 100.4       |
      | shanghai   | 100.5       |
      +------------+-------------+
    • 查詢表sale_detail中的列region和total_price的值並按照region值進行雜湊分區,然後按照total_price對雜湊分區結果進行局部降序排序。命令樣本如下。

      SELECT region,total_price from sale_detail distribute by region sort by total_price desc;

      返回結果如下。

      +------------+-------------+
      | region     | total_price |
      +------------+-------------+
      | shanghai   | 100.5       |
      | shanghai   | 100.4       |
      | china      | 100.3       |
      | china      | 100.2       |
      | china      | 100.1       |
      | shanghai   | NULL        |
      +------------+-------------+
  • 如果sort by語句前沒有distribute bysort by會對每個Reduce中的資料進行局部排序。

    保證每個Reduce的輸出資料都是有序的,從而增加儲存壓縮率,同時讀取時如果有過濾,能夠減少真正從磁碟讀取的資料量,提高後續全域排序的效率。命令樣本如下。

    SELECT region,total_price from sale_detail sort by total_price desc;

    返回結果如下。

    +------------+-------------+
    | region     | total_price |
    +------------+-------------+
    | china      | 100.3       |
    | china      | 100.2       |
    | china      | 100.1       |
    | shanghai   | 100.5       |
    | shanghai   | 100.4       |
    | shanghai   | NULL        |
    +------------+-------------+
說明
  • order by|distribute by|sort by的取值必須是SELECT語句的輸出資料行,即列的別名。列的別名可以為中文。

  • 在MaxCompute SQL解析中,order by|distribute by|sort by執行順序在SELECT操作之後,因此它們的取值只能為SELECT語句的輸出資料行。

  • order by不和distribute bysort by同時使用,group by也不和distribute bysort by同時使用。

LIMIT限制輸出行數(number)

可選。limit <number>中的number是常數,用於限制輸出行數,取值範圍為int32位取值範圍,即最大值不可超過2,147,483,647。

說明

limit基於分布式系統對資料進行掃描後過濾,您無法通過limit減少返回資料量進而減少計算費用。

當您涉及到如下情境時,可參考對應解決方案處理:

  • 解除order by必須帶limit的限制。

    因為order by需要對單個執行節點做全域排序,所以預設帶limit限制,避免誤用導致單點處理大量資料。如果您的使用情境確實需要order by放開limit限制,可以通過如下兩種方式實現:

    • Project層級:設定setproject odps.sql.validate.orderby.limit=false;關閉order by必須帶limit的限制。

    • Session層級:設定set odps.sql.validate.orderby.limit=false;關閉order by必須帶limit的限制,需要與SQL語句一起提交。

      說明

      如果關閉order by必須帶limit的限制,在單個執行節點有大量資料排序的情況下,資源消耗或處理時間長度等效能表現會受到影響。

  • 解除屏顯限制

    當使用無limitSELECT語句或limitnumber數量超過設定的屏顯上限時,如果您直接從屏顯視窗查看結果,最多隻能輸出屏顯上限設定的行數。

    每個專案空間的屏顯上限可能不同,您可以參考如下方法控制:

    • 如果關閉了專案空間資料保護,修改odpscmd_config.ini檔案。

      設定odpscmd_config.ini檔案中的use_instance_tunnel=true,如果不配置instance_tunnel_max_record參數,則屏顯行數不受限制;否則,屏顯行數受instance_tunnel_max_record參數值限制。instance_tunnel_max_record參數值上限為10000行。Instance Tunnel詳情請參見使用說明

    • 如果開啟了專案空間資料保護,屏顯行數受READ_TABLE_MAX_ROW參數值限制,配置上限為10000行。

    說明

    您可以執行show SecurityConfiguration;命令查看ProjectProtection屬性配置。如果ProjectProtection=true,根據專案空間資料保護需求判斷是否關閉資料保護機制。如果可以關閉,通過set ProjectProtection=false;命令關閉。ProjectProtection屬性預設不開啟。專案空間資料保護機制詳情請參見資料保護機制

視窗子句(window_clause)

詳細視窗子句資訊,請參見視窗函數文法

Split Size Hint

可選。您可通過調整Split Size來控制並發度,調整計算效能。Split Size功能可以作用到表層級。指定的值單位為MB,預設值為256MB。

  • 使用情境。

    • 當您發現作業有很多子任務都在等待資源,沒有辦法申請到更多資源的情況,可以通過調高Split Size值,減少並發度,可以節省啟停子任務的時間。

    • 當並發度比較低,例如並發度只有幾百,而且當前執行的子任務運行很久都沒有出結果,但是資源集區中還有很多資源時,可以調低值來提高並發度,降低整個作業的已耗用時間。

  • 注意事項。

    • 此Hint對於Cluster表,如果最佳化中會使用Cluster的分桶屬性,那麼Split Size Hint會失效。

    • Split Size的值按照256MB的倍數進行調整,例如128MB、512MB等。

    • 一個SQL中有重複讀同張表,那麼Hint會被合并成為指定的最小值。

      • SQL中有兩個讀表src的地方,一個Hint為1MB一個為10MB,那麼最後會按照1MB來切分。

      • SQL中有兩個讀表src的地方,一個Hint為1MB一個沒有,那麼最後會按照1MB來切分。

  • 使用樣本。

    --設定split size大小為1MB,此hint會在讀表src時,按照1M的大小來切分task
    SELECT a.key from src a /*+split_size(1)*/ join src2 b on a.key=b.key;