全部产品
MaxCompute

运行SQL

更新时间:2017-06-07 13:26:11   分享:   

大多数用户对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

DDL语句

简单的DDL操作包括创建表,添加分区,查看表和分区信息,修改表,删除表和分区。关于这部分的介绍,请参考创建删除表

Select 语句

  • group by 语句的key可以是输入表的列名,也可以是由输入表的列构成的表达式,不可以是select语句的输出列。
  1. select substr(col2, 2) from tbl group by substr(col2, 2); -- 可以,group bykey可以是输入表的列构成的表达式;
  2. select col2 from tbl group by substr(col2, 2); -- 不可以,group bykey不在select语句的列中;
  3. select substr(col2, 2) as c from tbl group by c; -- 不可以,group bykey 不可以是列的别名,即select语句的输出列;

有这样的限制是因为,在通常的SQL解析中,group by的操作是先于select操作的,因此group by只能接受输入表的列或表达式为key。

  • order by必须与limit 联用;
  • sort by前必须加distribute by;
  • order by/sort by/distribute by的key必须是select语句的输出列,即列的别名:
  1. select col2 as c from tbl order by col2 limit 100 -- 不可以,order bykey不是select语句的输出列,即列的别名
  2. select col2 from tbl order by col2 limit 100; -- 可以,当select语句的输出列没有别名时,使用列名作为别名。

有这样的限制是因为,在通常的SQL解析中, order by/sort by/distribute by是后于select操作的,因此它们只能接受select语句的输出列为key。

Insert语句

  • 向某个分区插入数据时,分区列不可以出现在select列表中:
  1. insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')
  2. select shop_name, customer_id, total_price, sale_date, region from sale_detail;
  3. -- 报错返回,sale_date, region为分区列,不可以出现在静态分区的 insert 语句中。
  • 动态分区插入时,动态分区列必须在select列表中:
  1. insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
  2. select shop_name,customer_id,total_price from sale_detail;
  3. --失败返回,动态分区插入时,动态分区列必须在select列表中

Join操作

  • MaxCompute SQL支持的Join操作类型包括:{LEFT OUTER|RIGHT OUTER|FULL OUTER|INNER} JOIN;
  • 目前最多支持16个并发Join操作;
  • 在mapjoin中,最多支持6张小表的mapjoin;

Union All

Union All可以把多个select操作返回的结果,联合成一个数据集。它会返回所有的结果,但是不会执行去重。 MaxCompute 不支持直接对顶级的两个查询结果进行union操作,需要写成子查询的形式。

另外需要注意的是,union all连接的两个select查询语句,两个select的列个数、列名称、列类型必须严格一致。如果原名称不一致,可以通过别名设置成相同的名称。

其他

  • ODPS SQL目前最多支持128个并发union操作;
  • 最多支持128个并发insert overwrite/into操作;

SQL优化实例

Join语句中where条件的位置

当两个表进行join操作的时候,主表的Where限制可以写在最后,但从表分区限制条件不要写在Where条件里,建议写在ON条件或者子查询。主表的分区限制条件可以写在WHERE条件里(最好先用子查询过滤)。

参考下面几个sql:

  1. select * from A join (select * from B where dt=20150301)B on B.id=A.id where A.dt=20150301
  2. select * from A join B on B.id=A.id where B.dt=20150301 --不允许
  3. select * from (select * from A where dt=20150301)A join (select * from B where dt=20150301)B on B.id=A.id

第2个语句会先join,后进行分区裁剪,数据量变大,性能下降。在实际使用过程中,应该尽量避免第二种用法。

数据倾斜

产生数据倾斜的根本原因是:有少数Worker处理的数据量远远超过其他Worker处理的数据量,从而导致少数Worker的运行时长远远超过其他的平均运行时长,进而导致整个任务运行时间超长,造成任务延迟。

Join造成的数据倾斜

造成join数据倾斜的原因是join on 的 key分布不均匀。 假设还是上面的例子,现在将大表A跟一张小表B进行join操作,运行如下语句:

  1. select * from A join B on A.value= B.value;

此时我们拷贝logview的链接并打开webcosole页面,双击执行join操作的fuxi job可以看见此时在[Long-tails]区域有长尾,表示数据已经倾斜了。如下图所示:

关于如何进行优化,此时我们可以通过如下办法:

由于表B是个小表并且没有超过512MB,我们将上面的语句优化成mapjoin语句再执行,语句如下:

  1. select /*+ MAPJOIN(B) */ * from A join B on A.value= B.value;

或者将倾斜的key用单独的逻辑来处理,例如经常发生两边的key里有大量null数据导致了倾斜。则需要在join前先过滤掉null的数据或者补上随机数,然后再进行join。比如:

  1. select * from A join B
  2. on case when A.value is null then concat('value',rand() ) else A.value end = B.value;

在实际场景中,用户往往知道数据倾斜了,但无法获取导致数据倾斜的key信息。在此有个通用的方案可以查看数据倾斜的办法:

  1. 例如:select * from a join b on a.key=b.key;产生数据倾斜。
  2. 用户可以执行:
  3. ```sql
  4. select left.key, left.cnt * right.cnt from
  5. (select key, count(*) as cnt from a group by key) left
  6. join
  7. (select key, count(*) as cnt from b group by key) right
  8. on left.key=right.key;

查看key的分布,可以判断a join b时是否会有数据倾斜。

group by倾斜

造成group by倾斜的原因是group by的key分布不均匀。

假设表A内有两个字段(key, value),表内的数据量足够大,并且key的值分布不均,我们运行下面一条简单的语句:

  1. select key,count(value) from A group by key;

当表中的数据足够大的时候,我们一样会在webcosole页面看见长尾。

如何解决这个问题,我们一般在执行SQL前设置防倾斜的参数: set odps.sql.groupby.skewindata=true。

错误使用动态分区造成的数据倾斜

动态分区的sql,在odps中会默认增加一个reduce,用来将相同分区的数据合并在一起。这样做的好处有:

  1. 减少 MaxCompute 系统产生的小文件,使后续处理更快;

  2. 避免一个Worker输出文件很多时占用内存过大。

但是也正是因为这个Reduce的引入导致分区数据如果有倾斜的话,会发生长尾。因为相同的数据最多只会有10个 Worker 处理,所以数据量大,则会发生长尾。

例如:

  1. insert overwrite table A2 partition(dt)
  2. select
  3. split_part(value,'\t',1) as field1,
  4. split_part(value,'\t',2) as field2,
  5. dt
  6. from A
  7. where dt='20151010';

这种情况完全没必要使用动态分区。原来的语句可以改成:

  1. insert overwrite table A2 partition(dt='20151010')
  2. select
  3. split_part(value,'\t',1) as field1,
  4. split_part(value,'\t',2) as field2
  5. from A
  6. where dt='20151010';

窗口函数的优化

如果我们的SQL中用到了窗口函数,一般情况下每个窗口函数会形成一个Reduce作业,如果窗口函数略多,那么就会消耗资源。在某些特定场景下,窗口函数是有可优化空间的。首选,窗口函数“over”后面要完全相同,相同的分组和排序条件;其次,多个窗口函数在同一层SQL执行。符合这两个条件的窗口函数会合并为一个Reduce执行。(如下这种SQL):

  1. select
  2. rank()over(partition by A order by B desc) as rank,
  3. row_number()over(partition by A order by B desc) as row_num
  4. from MyTable;

子查询改join

例如有一个子查询如下:

  1. 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语句来代替,如:

  1. 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去掉。
本文导读目录
本文导读目录
以上内容是否对您有帮助?