MaxCompute_SQL_开发指南

背景及目的

本文结果都是在SQL标准语义模式下的推导结果，希望大家都能够按照标准的SQL语义来写SQL，这样才能保证后续SQL的可移植性。

SQL概述

MaxCompute SQL适用于海量数据（GB、TB、EB级别），离线批量计算的场合。MaxCompute作业提交后会有几十秒到数分钟不等的排队调度，所以适合处理跑批作业，一次作业批量处理海量数据，不适合直接对接需要每秒处理几千至数万笔事务的前台业务系统。

MaxCompute SQL采用的是类似于SQL的语法，可以看作是标准SQL的子集，但不能因此简单地把MaxCompute等价成一个数据库，它在很多方面并不具备数据库的特征，如事务、主键约束、索引等，更多差异请参见与其他SQL语法的差异。目前在MaxCompute中允许的最大SQL长度是3MB。

类型转换说明

MaxCompute SQL允许数据类型之间的转换，类型转换方式包括显式类型转换和隐式类型转换。更多详情请参见类型转换。

• 显式类型转换：是指用cast将一种数据类型的值转换为另一种类型的值的行为。
• 隐式类型转换：是指在运行时，由MaxCompute依据上下文使用环境及类型转换规则自动进行的类型转换。隐式转换作用域包括各种运算符、内建函数等作用域。

分区表

MaxCompute SQL支持分区表。指定分区表会对您带来诸多便利，例如提高SQL运行效率、减少计费等。关于分区的详情请参见基本概念>分区。

UNION ALL

参与UNION ALL运算的所有列的数据类型、列个数、列名称必须完全一致，否则会报异常。

使用限制

SQL限制项请参见SQL限制项汇总，不支持的DDL及DML语法请参见与其他SQL语法的差异。
其他限制：

• SCALAR SUBQUERY限制
• Insert values限制：values必须是常量
• MaxCompute最多允许256个表的union all/union
• Mapjoin的小表不能超过512MB
• 由于国际标准化组织发布的中国时区信息调整，执行相关SQL时，日期显示某些时间段会存在时间差异：1900-1928年的日期时间差异5分52秒，1900年之前的日期时间差异9秒。

快速体验

通过MaxCompute客户端，快速查看基础语句：

• 进入项目空间：use ;
• 查看Tables：
  show tables; --列出当前项目空间下所有的表。

show tables like 'daniel'; --列出当前项目空间下表名与'daniel'匹配上的表，支持正则表达式。
show partitions; --table_name：指定查询的表名称（表不存在或非分区表报错）

• 创建Table：

CREATE TABLE [IF NOT EXISTS] table_name
 [(col_name data_type [COMMENT col_comment], ...)]
 [COMMENT table_comment]
 [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
 [LIFECYCLE days]
 [AS select_statement]
 CREATE TABLE [IF NOT EXISTS] table_name
 LIKE existing_table_name

• Describe Table

DESC <table_name>;      -- table_name：表名或视图名称
DESC extended <table_name>;--查看外部表信息

查看分区：

desc table_name partition(pt_spec)

• 删除Table

DROP TABLE [IF EXISTS] table_name; -- table_name：要删除的表名。

DDL语句（表操作、生命周期操作、分区和列操作、视图操作）参考更多。

数据类型

MaxCompute类型

基本数据类型：

复杂数据类型

RDBMS与MaxCompute

Hive与MaxCompute

MaxCompute与Hive的数据类型映射如下：

数据操作

更新表数据

如何使用Insert into和Insert overwrite两种命令更新表数据。

insert操作

介绍如何使用Insert into和Insert overwrite两种命令更新表数据。

INSERT OVERWRITE|INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [(col1,col2 ...)]
select_statement
FROM from_statement;

说明：

• MaxCompute的Insert语法与通常使用的MySQL或Oracle的Insert语法有差别，在insert overwrite|into后需要加入table关键字，而非直接使用tablename。
• 当Insert的目标表是分区表时，指定分区值[PARTITION (partcol1=val1, partcol2=val2 …)]语法中不允许使用函数等表达式。
• 目前INSERT OVERWRITE还不支持指定插入列的功能，暂时只能用INSERT INTO。
• 不支持insert into到hash clustering表

Insert into与Insert overwrite的区别是：Insert into会向表或表的分区中追加数据，而Insert overwrite会在向表或分区中插入数据前清空表中的原有数据。

在使用MaxCompute处理数据的过程中， Insert overwrite/into是最常用到的语句，它们会将计算的结果保存到一个表中，以供下一步计算使用。比如计算sale_detail表中不同地区的销售额，操作如下：

create table sale_detail_insert like sale_detail;
alter table sale_detail_insert add partition(sale_date='2013', region='china');
insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')
select shop_name, customer_id,total_price from sale_detail;

向某个分区插入数据时，分区列不允许出现在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语句中。

同时，partition的值只能是常量，不可以出现表达式。以下用法是非法的：

insert overwrite table sale_detail_insert partition (sale_date=datepart('2016-09-18 01:10:00', 'yyyy') , region='china')
select shop_name, customer_id, total_price from sale_detail;

动态分区操作

动态分区使用注意事项：

• 在您insert into partition时，如果分区不存在，会自动创建分区。
• 如果多个insert into partition作业并发，同时发现分区不存在，都会主动创建分区，但是同时只有一个会创建成功，其它的都会失败。
• insert into partition作业如果不能控制并发，只能通过预创建分区来避免问题。

MULTI INSERT

MaxCompute SQL支持在一个语句中插入不同的结果表或者分区实现多路输出。

FROM from_statement
INSERT OVERWRITE | INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)]
select_statement1 [FROM from_statement]
[INSERT OVERWRITE | INTO TABLE tablename2 [PARTITION (partcol1=val3, partcol2=val4 ...)]
select_statement2 [FROM from_statement]]

说明：

• 一般情况下，单个SQL中最多可以写256路输出，超过256路，则报语法错误。
• 在一个multi insert中：对于分区表，同一个目标分区不允许出现多次。对于未分区表，该表不能出现多次。
• 对于同一张分区表的不同分区，不能同时有Insert overwrite和Insert into操作，否则报错返回。
  示例如下：

create table sale_detail_multi like sale_detail;
from sale_detail
insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' ) 
select shop_name, customer_id, total_price where .....
insert overwrite table sale_detail_multi partition (sale_date='2011', region='china' )
select shop_name, customer_id, total_price where .....;
-- 成功返回，将sale_detail的数据插入到sales里的 2010 年及2011年中国大区的销售记录中。
from sale_detail
insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
select shop_name, customer_id, total_price
insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
select shop_name, customer_id, total_price;
-- 出错返回，同一分区出现多次。
from sale_detail
insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
select shop_name, customer_id, total_price
insert into table sale_detail_multi partition (sale_date='2011', region='china' )
select shop_name, customer_id, total_price;
-- 出错返回，同一张表的不同分区，不能同时有insert overwrite和insert into操作。

VALUES

通常在业务测试阶段，需要给一个小数据表准备些基本数据，您可以通过 INSERT … VALUES的方法快速对测试表写入一些测试数据。
命令格式如下：

INSERT  INTO  TABLE  tablename 
[PARTITION (partcol1=val1, partcol2=val2 ...)][co1name1,colname2...] 
[VALUES (col1_value,col2_value,...),(col1_value,col2_value,...),...]

示例一：

drop table if exists srcp;
create table if not exists srcp (key string ,value bigint) partitioned by (p string);
insert into table srcp partition (p='abc') values ('a',1),('b',2),('c',3);

INSERT … VALUES语句执行成功后，查询表srcp分区p=abc，结果如下：

+-----+------------+---+
| key | value      | p |
+-----+------------+---+
| a   | 1          | abc |
| b   | 2          | abc |
| c   | 3          | abc |
+-----+------------+---+

当表有很多列，而准备数据的时候希望只插入部分列的数据，此时可以使用插入列表功能。
示例二：

drop table if exists srcp;
create table if not exists srcp (key string ,value bigint) partitioned by (p string);
insert into table srcp partition (p)(key,p) values ('d','20170101'),('e','20170101'),('f','20170101');

INSERT … VALUES语句执行成功后，查询表srcp分区p=20170101，结果如下：

+-----+------------+---+
| key | value      | p |
+-----+------------+---+
| d   | NULL       | 20170101 |
| e   | NULL       | 20170101 |
| f   | NULL       | 20170101 |
+-----+------------+---+

对于在values中没有制定的列，可以看到取缺省值为NULL。插入列表功能不一定和values一起用，对于insert into…select…，同样可以使用。

实际上，values表并不限于在Insert语句中使用，任何DML语句都可以使用。

INSERT … VALUES有一个限制：values必须是常量，但是有时候希望在插入的数据中进行一些简单的运算，此时可以使用MaxCompute的values table功能，详情见示例三。

示例三：

drop table if exists srcp;
create table if not exists srcp (key string ,value bigint) partitioned by (p string);
insert into table srcp partition (p) select concat(a,b), length(a)+length(b),'20170102' from  values ('d',4),('e',5),('f',6) t(a,b);

其中的values (…), (…) t (a, b)相当于定义了一个名为t，列为a，b的表，类型为（a string，b bigint），其中的类型从values列表中推导。这样在不准备任何物理表的时候，可以模拟一个有任意数据的，多行的表，并进行任意运算。

INSERT … VALUES语句执行成功后，查询表srcp分区p=‘20170102’，结果如下：

+-----+------------+---+
| key | value      | p |
+-----+------------+---+
| d4  | 2          | 20170102 |
| e5  | 2          | 20170102 |
| f6  | 2          | 20170102 |
+-----+------------+---+

VALUES TABLE这个用法还可以取代 select * from dual与 union all组合的方式，来拼出常量表。如下：

select 1 c from dual 
union all
select 2 c from dual;
--等同于 
select * from values (1), (2) as t (c);

还有一种values表的特殊形式，如下所示：

select abs(-1), length('abc'), getdate();

如上述语句所示，可以不写from语句，直接执行select，只要select的表达式列表不用任何上游表数据就可以。其底层实现为从一个1行，0列的匿名values表选取。这样，在希望测试一些函数，比如自己的UDF等时，便不用再手工创建DUAL表。

SQL查询

MaxCompute SQL中，很常用的一个操作就是关联(Join)。目前MaxCompute提供了一下几种Join类型：

根据不同的场景，用户可以使用不同的Join类型来实现对应的关联操作。但是在实际使用过程当中，经常有用户分不清楚过滤条件在JOIN ON语句中还是在WHERE中有什么区别，或者认为他们的效果都是一样的，例如在生产的环境中经常可以看到用户写了

A (LEFT/RIGHT/FULL/LEFT SEMI/LEFT ANTI) JOIN B
ON a.key = b.key and A.ds='20190121' and B.ds='20190121';

这里用户的本意是希望在A和B中获取某一个分区的数据进行JOIN操作，也就是

(SELECT * FROM A WHERE ds='20190121') A
(LEFT/RIGHT/FULL/LEFT SEMI/LEFT ANTI)  JOIN
(SELECT * FROM B WHERE ds='20190121') B
ON a.key = b.key

然而针对不同的Join类型，两者可能并不等价，不仅无法将分区条件下推，导致全表扫描，而且会导致正确性问题。这里简要辨析一下过滤条件分别在以下的的异同

1. 子查询的WHERE条件
2. JOIN ON条件
3. JOIN ON后的WHERE条件

1 原理

这里先说明一个JOIN和WHERE条件的计算顺序，对于

(SELECT * FROM A WHERE {subquery_where_condition} A) A
JOIN
(SELECT * FROM B WHERE {subquery_where_condition} B) B
ON {on_condition}
WHERE {where_condition}

来说，计算顺序为

1. 子查询中的{subquery_where_condition}
2. JOIN的{on_condition}的条件
3. JOIN结果集合{where_condition}的计算

对于不同的JOIN类型，滤语句放在{subquery_where_condition}、{on_condition}和{where_condition}中，有时结果是一致的，有时候结果又是不一致的。下面分情况进行讨论：

2 实验

2.1 准备

首先构造表A

CREATE TABLE A AS SELECT * FROM VALUES (1, 20190121),(2, 20190121),(2, 20190122) t (key, ds);

则他们的笛卡尔乘积为

2.2 Inner Join

结论：过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}中都是等价的。
Inner Join的处理逻辑是将左右表进行笛卡尔乘积，然后选择满足ON表达式的行进行输出。
第一种情况，子查询中过滤：

SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20190121') A
JOIN
(SELECT * FROM B WHERE ds='20190121') B
ON a.key = b.key;

非常简单，结果只有一条

第二种情况，JOIN 条件中过滤

SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key and A.ds='20190121' and B.ds='20190121';

笛卡尔积的结果有9条，满足ON条件的结果同样只有1条。

第三种情况，JOIN后的WHERE条件过滤

SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key
WHERE A.ds='20190121' and B.ds='20190121';

来说，笛卡尔积的结果有9条，满足ON条件a.key = b.key的结果有3条，分别是

此时对于这个结果再进行过滤A.ds='20190121' and B.ds='20190121'，结果只有1条，和刚才的结果一致

2.3 Left Join

结论：过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}不一定等价。
对于左表的过滤条件，放在{subquery_where_condition}和{where_condition}是等价的。
对于右表的过滤条件，放在{subquery_where_condition}和{on_condition}中是等价的。

Left Join的处理逻辑是将左右表进行笛卡尔乘积，然后对于满足ON表达式的行进行输出，对于左表中不满足ON表达式的行，输出左表，右表补NULL。

第一种情况，子查询中过滤：

SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20190121') A
LEFT JOIN
(SELECT * FROM B WHERE ds='20190121') B
ON a.key = b.key;
过滤后，左右侧有两条，右侧有一条，结果有两条

第二种情况，JOIN 条件中过滤

SELECT A.*, B.*
FROM A LEFT JOIN B
ON a.key = b.key and A.ds='20190121' and B.ds='20190121';

笛卡尔积的结果有9条，满足ON条件的结果同样只有1条，则对于左表剩余的两条输出左表，右表补NULL。

第三种情况，JOIN后的WHERE条件过滤：

SELECT A.*, B.*
FROM A LEFT JOIN B
ON a.key = b.key
WHERE A.ds='20190121' and B.ds='20190121';

来说，笛卡尔积的结果有9条，满足ON条件a.key = b.key的结果有3条，分别是

此时对于这个结果再进行过滤A.ds='20190121' and B.ds='20190121'，结果只有1条

可以看到，将过滤条件放在三个不同的地方，得到了三种不同的结果。

2.4 Right Join

Right Join和Left Join是类似的，只是左右表的区别。
结论：过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}不一定等价。
对于右表的过滤条件，放在{subquery_where_condition}和{where_condition}是等价的。
对于左表的过滤条件，放在{subquery_where_condition}和{on_condition}中是等价的。

2.5 Full Join

结论：过滤条件写在{subquery_where_condition}、{on_condition}和{where_condition}均不等价。

FULL Join的处理逻辑是将左右表进行笛卡尔乘积，然后对于满足ON表达式的行进行输出，对于两侧表中不满足ON表达式的行，输出有数据的表，另一侧补NULL。

第一种情况，子查询中过滤：

SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20190121') A
FULL JOIN
(SELECT * FROM B WHERE ds='20190121') B
ON a.key = b.key;

过滤后，左右侧有两条，右侧有两条，结果有三条

第二种情况，JOIN 条件中过滤：

SELECT A.*, B.*
FROM A FULL JOIN B
ON a.key = b.key and A.ds='20190121' and B.ds='20190121';

笛卡尔积的结果有9条，满足ON条件的结果同样只有1条，则对于左表剩余的两条输出左表，右表补NULL。右表剩余的两条输出右表，左表补NULL

第三种情况，JOIN后的WHERE条件过滤

SELECT A.*, B.*
FROM A FULL JOIN B
ON a.key = b.key
WHERE A.ds='20190121' and B.ds='20190121';

笛卡尔积的结果有9条，满足ON条件a.key = b.key的结果有3条，分别是

再对没有JOIN上的数据进行输出，另一侧补NULL，得到结果

此时对于这个结果再进行过滤A.ds='20190121' and B.ds='20190121'，结果只有1条

可以看到，和LEFT JOIN类似，得到了三种不同的结果。

2.6 Left Semi Join

结论：过滤条件写在{subquery_where_condition}、{on_condition}和{where_condition}是等价的。

LEFT SEMI Join的处理逻辑是对于左表的每一条记录，都去和右表进行匹配，如果匹配成功，则输出左表。这里需要注意的是由于只输出左表，所以JOIN后的Where条件中不能写右侧的过滤条件。LEFT SEMI JOIN常常用来实现exists的语义

第一种情况，子查询中过滤：

SELECT A.*
FROM
(SELECT * FROM A WHERE ds='20190121') A
LEFT SEMI JOIN
(SELECT * FROM B WHERE ds='20190121') B
ON a.key = b.key;

过滤后，左右侧有两条，最终符合a.key = b.key的只有一条

| a.key | a.ds |
| -------- | -------- |
| 1 | 20190121 |
第二种情况，JOIN 条件中过滤：

SELECT A.*
FROM A LEFT SEMI JOIN B
ON a.key = b.key and A.ds='20190121' and B.ds='20190121';

对于左侧的三条记录，满足ON条件的结果同样只有1条

| a.key | a.ds|
| -------- | -------- |
|1 | 20190121 |
第三种情况，JOIN后的WHERE条件过滤

SELECT A.*
FROM A LEFT SEMI JOIN
(SELECT * FROM B WHERE ds='20190121') B
ON a.key = b.key
WHERE A.ds='20190121';

左侧能符合ON条件的有一条

| a.key | a.ds|
| -------- | -------- |
|1 | 20190121 |
此时对于这个结果再进行过滤A.ds='20190121'，结果仍然保持1条

| a.key | a.ds|
| -------- | -------- |
| 1 | 20190121 |
可以看到，LEFT SEMI JOIN和INNER JOIN类似，无论过滤条件放在哪里，结果都是一致的。

2.7 Left Anti Join

结论：过滤条件写在{subquery_where_condition}、{on_condition}和{where_condition}不一定等价。
对于左表的过滤条件，放在{subquery_where_condition}和{where_condition}是等价的。
对于右表的过滤条件，放在{subquery_where_condition}和{on_condition}中是等价的，右表表达式不能放在{where_condition}中。

LEFT ANTI Join的处理逻辑是对于左表的每一条记录，都去和右表进行匹配，如果右表所有的记录都没有匹配成功，则输出左表。同样由于只输出左表，所以JOIN后的Where条件中不能写右侧的过滤条件。LEFT SEMI JOIN常常用来实现not exists的语义。

第一种情况，子查询中过滤：

SELECT A.*
FROM
(SELECT * FROM A WHERE ds='20190121') A
LEFT ANTI JOIN
(SELECT * FROM B WHERE ds='20190121') B
ON a.key = b.key;

过滤后，左侧有两条，右侧有两条，结果有1条

第二种情况，JOIN 条件中过滤：

SELECT A.*
FROM A LEFT ANTI JOIN B
ON a.key = b.key and A.ds='20190121' and B.ds='20190121';

对于左侧的三条记录，只有第一条有满足ON条件的结果，所以输出剩余的两条记录

第三种情况，JOIN后的WHERE条件过滤

SELECT A.*
FROM A LEFT ANTI JOIN
(SELECT * FROM B WHERE ds='20190121') B
ON a.key = b.key
WHERE A.ds='20190121';

左侧能通过ON条件的有两条

此时对于这个结果再进行过滤A.ds='20190121'，结果为1条

可以看到，LEFT ANTI JOIN中，过滤条件放在JOIN ON条件中和前后的WHERE条件中，结果是不相同的。

以上只是针对一个常用场景的几种不同的写法做的简单的测试，没有具体的推导过程，对于涉及到不等值表达式的场景会更加复杂，有兴趣的同学可以自己尝试推导一下。

3 总结

过滤条件放在不同的位置语义可能大不相同，对于用户而言，如果只是进行过滤数据后再JOIN的操作，可以简要记住以下几点，当然如果还是觉得规则比较复杂的话，那最好的方法就是每次都把过滤条件写到子查询中，虽然这样写起来会啰嗦一些。

1. INNER JOIN/LEFT SEMI JOIN 对于两侧的表达式可以随便写。
2. LEFT JOIN/LEFT ANTI JOIN 左表的过滤条件要放到{subquery_where_condition}或者{where_condition}，右表的过滤条件要放到{subquery_where_condition}或者{on_condition}中。
3. RIGHT JOIN和LEFT JOIN相反，右表的过滤条件要放到{subquery_where_condition}或者{where_condition}，左表的过滤条件要放到{subquery_where_condition}或者{on_condition}。
4. FULL OUTER JOIN 只能放到{subquery_where_condition}中。