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;
命令格式
[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>]
[order by <order_condition>]
[distribute by <distribute_condition> [sort by <sort_condition>]|[ cluster by <cluster_condition>] ]
[limit <number>]
[window <window_clause>]
命令中各字段的执行语序请参见
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使用规则如下:
必填。
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_name
和
customer_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 |
+------------+------------+
排除列(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
子句中,您可以指定分区范围,只扫描表的指定部分,避免全表扫描。命令示例如下。
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行。
可选。
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 by
,
sort 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 by
,
sort 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 by
、
sort by
同时使用,
group by
也不和
distribute by
、
sort by
同时使用。
解除
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
的限制,在单个执行节点有大量数据排序的情况下,资源消耗或处理时长等性能表现会受到影响。
解除屏显限制
当使用无
limit
的
select
语句或
limit
的
number
数量超过设置的屏显上限时,如果您直接从屏显窗口查看结果,最多只能输出屏显上限设置的行数。
每个项目空间的屏显上限可能不同,您可以参考如下方法控制:
如果关闭了项目空间数据保护,修改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行。
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来切分。