341      阿里云  技术社区[云栖]

运行SQL__快速开始_大数据计算服务-阿里云

大多数用户对SQL的语法并不陌生，简单地说，MaxCompute SQL就是用于查询和分析MaxCompute中的大规模数据。目前SQL的主要功能可以概括如下：

• 支持各类运算符
• 通过DDL语句对表、分区以及视图进行管理。
• 通过Select语句查询表中的记录，通过Where字句过滤表中的记录。
• 通过Insert语句插入数据、更新数据。
• 通过等值连接Join操作，支持两张表的关联。支持多张小表的mapjoin。
• 支持通过内置函数和自定义函数来进行计算。
• 支持正则表达式。

这里我们只简要介绍MaxCompute SQL使用中需要注意的问题。不再做操作示例。

注意：

• 需要注意的是，MaxCompute SQL不支持事务、索引及Update/Delete等操作，同时MaxCompute的SQL语法与Oracle，MySQL有一定差别，用户无法将其他数据库中得SQL语句无缝迁移到 MaxCompute 上来。此外，在使用方式上，MaxCompute 无法满足实时查询，查询计算时间在分钟级别，无法在秒、乃至毫秒级别返回用户结果。
• 关于SQL的操作详细示例，请参考SQL。

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。

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列表中

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:

select * from A join (select * from B where dt=20150301)B on B.id=A.id where A.dt=20150301；  
select * from A join B on B.id=A.id where B.dt=20150301；  --不允许  
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操作，运行如下语句：

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, left.cnt * right.cnt 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;

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

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

select
    rank()over(partition by A order by B desc) as rank,
    row_number()over(partition by A order by B desc) as row_num
from MyTable;

SELECT * FROM table_a a WHERE a.col1 IN (SELECT col1 FROM table_b b WHERE xxx);

SELECT a.* FROM table_a a JOIN (SELECT DISTINCT col1 FROM table_b b WHERE xxx) c ON (a.col1 = c.col1)