文档

SELECT语法

更新时间:

MaxCompute支持通过SELECT语句查询数据。本文为您介绍SELECT命令格式及如何实现嵌套查询、分组查询、排序等操作。

执行SELECT操作前需要具备目标表的读取数据权限(SELECT)。授权操作请参见MaxCompute权限

功能介绍

SELECT语句用于从表中选取满足指定条件的数据。您可以根据实际场景结合以下功能完成多样化的查询操作。

类型

功能

子查询(SUBQUERY)

在某个查询的执行结果基础上进一步执行查询操作时,可以通过子查询操作实现。

交集(INTERSECT)、并集(UNION)和补集(EXCEPT)

对查询结果数据集执行取交集、并集或补集操作。

JOIN

通过join操作连接表并返回符合连接条件和查询条件的数据信息。

SEMI JOIN(半连接)

通过右表过滤左表的数据,右表的数据不出现在结果集中。

MAPJOIN HINT

对一个大表和一个或多个小表执行join操作时,可以在SELECT语句中显式指定mapjoin Hint提示以提升查询性能。

DISTRIBUTED MAPJOIN

distributed mapjoinmapjoin的升级版,适用于小表join大表的场景。

SKEWJOIN HINT

当两张表Join存在热点,导致出现长尾问题时,您可以通过取出热点key,将数据分为热点数据和非热点数据两部分处理,最后合并的方式,提高Join效率。

Lateral View

通过Lateral View与UDTF(表生成函数)结合,将单行数据拆成多行数据。

GROUPING SETS

对数据进行多维度的聚合分析。

SELECT TRANSFORM

SELECT TRANSFORM语法允许您启动一个指定的子进程,将输入数据按照一定的格式通过标准输入至子进程,并且通过解析子进程的标准输出获取输出数据。

Split Size Hint

通过修改Split Size来控制并发度数量。

TimeTravel查询与Incremental查询

对于Transaction Table2.0类型的表,支持:

  • 通过TimeTravel查询,可以回溯到源表某个历史时间或者版本进行历史Snapshot查询。

  • 通过Incremental查询,指定源表某个历史时间区间或者版本区间进行历史增量查询。

    说明

    目前Transaction Table2.0处于邀测阶段,默认不支持直接使用,如果您需要使用该功能,请单击申请开通,在新功能试用申请页面申请开通使用Transaction Table2.0功能后,再运行相关命令。详情请参见Transaction Table2.0概述

使用限制

  • 当使用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;

    如果整个项目都需要开启全表扫描,项目空间Owner执行如下命令打开开关:

    setproject odps.sql.allow.fullscan=true;
  • 当查询聚簇表(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, 列表达式,...,表示待查询的普通列、分区列或正则表达式。列表达式使用规则如下:

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

    读取表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      |
      +------------+-------------+-------------+------------+------------+
  • 可以使用正则表达式。

    • 选出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聚合函数配合使用,根据指定的普通列、分区列或正则表达式进行分组。group by使用规则如下:

  • group by操作优先级高于SELECT操作,因此group by的取值是SELECT输入表的列名或由输入表的列构成的表达式。需要注意的是:

    • group by取值为正则表达式时,必须使用列的完整表达式。

    • 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;

  • 本页导读 (1)
文档反馈