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的資料類型映射如下：

Hive 資料類型	MaxCompute 資料類型
BOOLEAN
TINYINT
SMALLINT
INT
BIGINT
FLOAT
DOUBLE
DEICIMAL
STRING
VARCHAR
CHAR
BINARY
TIMESTAMP
DATE	Datetime
ARRAY
MAP
STRUCT
UNION	不支援

資料操作

更新表資料

如何使用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類型：

類型	含義
Inner Join	輸出符合關聯條件的資料
Left Join	輸出左表的所有記錄，對于右表符合關聯的資料，輸出右表，沒有符合的，右表補NULL
Right Join	輸出右表的所有記錄，對于左表符合關聯的資料，輸出左表，沒有符合的，左表補NULL
Full Join	輸出左表和右表的所有記錄，對于沒有關聯上的資料，未關聯的另一側補null
Left Semi Join	對于左表中的一條資料，如果右表存在符合關聯條件的行，則輸出左表
Left Anti Join	對于左表中的一條資料，如果對于右表所有的行，不存在符合關聯條件的資料，則輸出左表
User Defined Join	指定兩個輸入流，使用者自己實作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類型，兩者可能并不等價，不僅無法将分區條件下推，導緻全表掃描，而且會導緻正确性問題。這裡簡要辨析一下過濾條件分别在以下的的異同

子查詢的WHERE條件
JOIN ON條件
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}

來說，計算順序為

子查詢中的{subquery_where_condition}
JOIN的{on_condition}的條件
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);

key	ds
1	20190121
2
	20190122

則他們的笛卡爾乘積為

a.key	a.ds	b.key	b.ds

		3

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;
過濾後，左右側有兩條，右側有一條，結果有兩條



		NULL

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

此時對于這個結果再進行過濾A.ds='20190121' and B.ds='20190121'，結果隻有1條

可以看到，将過濾條件放在三個不同的地方，得到了三種不同的結果。

2.4 Right Join

Right Join和Left Join是類似的，隻是左右表的差別。

對于右表的過濾條件，放在{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

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，得到結果

可以看到，和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 |

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 |

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

左側能符合ON條件的有一條

|1 | 20190121 |

此時對于這個結果再進行過濾A.ds='20190121'，結果仍然保持1條

可以看到，LEFT SEMI JOIN和INNER JOIN類似，無論過濾條件放在哪裡，結果都是一緻的。

2.7 Left Anti Join

結論：過濾條件寫在{subquery_where_condition}、{on_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條

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

對于左側的三條記錄，隻有第一條有滿足ON條件的結果，是以輸出剩餘的兩條記錄

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的操作，可以簡要記住以下幾點，當然如果還是覺得規則比較複雜的話，那最好的方法就是每次都把過濾條件寫到子查詢中，雖然這樣寫起來會啰嗦一些。

INNER JOIN/LEFT SEMI JOIN 對于兩側的表達式可以随便寫。
LEFT JOIN/LEFT ANTI JOIN 左表的過濾條件要放到{subquery_where_condition}或者{where_condition}，右表的過濾條件要放到{subquery_where_condition}或者{on_condition}中。
RIGHT JOIN和LEFT JOIN相反，右表的過濾條件要放到{subquery_where_condition}或者{where_condition}，左表的過濾條件要放到{subquery_where_condition}或者{on_condition}。
FULL OUTER JOIN 隻能放到{subquery_where_condition}中。