Maxcompute运⾏SQL
Maxcompute 运⾏SQL
⼤多数⽤户对SQL的语法并不陌⽣，简单地说，MaxCompute SQL就是⽤于查询和分析MaxCompute中的⼤规模数据。⽬前SQL的主要功能如下所⽰：
⽀持各类运算符。
通过DDL语句对表、分区以及视图进⾏管理。
通过Select语句查询表中的记录，通过Where语句过滤表中的记录。
通过Insert语句插⼊数据、更新数据。
通过等值连接Join操作，⽀持两张表的关联。⽀持多张⼩表的mapjoin。
⽀持通过内置函数和⾃定义函数来进⾏计算。
⽀持正则表达式。
本⽂将为您简单介绍MaxCompute SQL使⽤中需要注意的问题，不再做操作⽰例。
说明:
MaxCompute SQL不⽀持事务、索引及Update/Delete等操作，同时MaxCompute的SQL语法与Oracle，MySQL有⼀定差别，您⽆法将其他数据库中的SQL语句⽆缝迁移到MaxCompute上来，更多差异请参见与其他SQL语法的差异。
在使⽤⽅式上，MaxCompute作业提交后会有⼏⼗秒到数分钟不等的排队调度，所以适合处理跑批作业，⼀次作业批量处理海量数据，不适合直接对接需要每秒处理⼏千⾄数万笔事务的前台业务系统。
关于SQL操作的详细⽰例，请参见SQL模块。
1.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。
2.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列表中。
3.Join操作
MaxCompute SQL⽀持的Join操作类型包括：{LEFT OUTER|RIGHT OUTER|FULL OUTER|INNER} JOIN。
⽬前最多⽀持16个并发Join操作。
在mapjoin中，最多⽀持8张⼩表的mapjoin。
4.Union All
Union All可以把多个Select操作返回的结果，联合成⼀个数据集。它会返回所有的结果，但是不会执⾏去重。MaxCompute不⽀持直接对顶级的两个查询结果进⾏Union操作，需要写成⼦查询的形式。
说明:
Union All连接的两个Select查询语句，两个Select的列个数、列名称、列类型必须严格⼀致。
如果原名称不⼀致，可以通过别名设置成相同的名称。
5.其他
MaxCompute SQL⽬前最多⽀持128个并发Union操作。
最多⽀持128个并发insert overwrite/into操作。
MaxCompute SQL的更多限制请参见SQL限制项汇总.
6.SQL优化⽰例
(1)Join语句中Where条件的位置
当两个表进⾏Join操作时，主表的Where限制可以写在最后，但从表分区限制条件不要写在Where条件中，建议写在ON条件或者⼦查询中。主表的分区限制条件可以写在Where条件中（最好先⽤⼦查询过滤）。⽰例如下：
select * from A join (select * from B where dt=20150301)B on where A.dt=20150301；
select * from A join B on where B.dt=20150301； --不允许
select * from (select * from A where dt=20150301)A join (select * from B where dt=20150301)B on ；
第⼆个语句会先Join，后进⾏分区裁剪，数据量变⼤，性能下降。在实际使⽤过程中，应该尽量避免第⼆种⽤法。
(2)数据倾斜
产⽣数据倾斜的根本原因是有少数Worker处理的数据量远远超过其他Worker处理的数据量，从⽽导致少数Worker的运⾏时长远远超过其他的平均运⾏时长，从⽽导致整个任务运⾏时间超长，造成任务延迟。
更多数据倾斜优化的详情请参见计算长尾调优。
(3)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, leftt * rightt 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时是否会有数据倾斜。
** (4)group by倾斜**
造成group by倾斜的原因是group by的key分布不均匀。
假设表A内有两个字段（key，value），表内的数据量⾜够⼤，并且key的值分布不均，运⾏语句如下所⽰：select key,count(value) from A group by key;
当表中的数据⾜够⼤时，您会在webcosole页⾯看见长尾。若想解决这个问题，您需要在执⾏SQL前设置防倾斜的参数，设置语句为set upby.skewindata=true。
(5)错误使⽤动态分区造成的数据倾斜
动态分区的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’;
(6)窗⼝函数的优化
如果您的SQL语句中⽤到了窗⼝函数，⼀般情况下每个窗⼝函数会形成⼀个Reduce作业。如果窗⼝函数略多，那么就会消耗资源。在某些特定场景下，窗⼝函数是可以进⾏优化的。
窗⼝函数over后⾯要完全相同，相同的分组和排序条件。
多个窗⼝函数在同⼀层SQL执⾏。
符合上述两个条件的窗⼝函数会合并为⼀个Reduce执⾏。SQL⽰例如下所⽰：
(7)⼦查询改Join
例如有⼀个⼦查询，如下所⽰：
SELECT * FROM table_a a l1 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)
说明sql优化的几种方式
如果没⽤Distinct，⽽⼦查询c返回的结果中有相同的col1的值，可能会导致a表的结果数变多。
因为Distinct⼦句会导致查询全落到⼀个Worker⾥，如果⼦查询数据量⽐较⼤的话，可能会导致查询⽐较慢。
如果已经从业务上控制了⼦查询⾥的col1不可能会重复，⽐如查的是主键字段，为了提⾼性能，可以把Distinct去掉。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。