插入或覆寫動態分區資料（DYNAMIC PARTITION） - MaxCompute

MaxCompute支援通過insert into或insert overwrite操作向動態分區中插入資料。

本文中的命令您可以在如下工具平台執行：

前提條件

執行insert into和insert overwrite操作前需要具備目標表的修改許可權（Alter）及源表的元資訊讀取許可權（Describe）。授權操作請參見MaxCompute許可權。

功能介紹

在使用MaxCompute SQL處理資料時，insert into或insert overwrite語句中不直接指定分區值，只指定分區列名（分區欄位）。分區列的值在select子句中提供，系統自動根據分區列的值將資料插入到相應分區。

向靜態分區插入資料的操作請參見插入或覆寫資料（INSERT INTO | INSERT OVERWRITE）。

使用限制

通過insert into和insert overwrite操作向動態分區中插入資料的使用限制如下：

insert into最多可以產生10000個動態分區，insert overwrite最多可以產生60000個動態分區。
分布式環境下，在使用動態資料分割函數的SQL語句中，單個進程最多隻能輸出512個動態分區，否則會運行異常。
動態產生的分區值不允許為NULL，也不支援特殊字元和中文，否則會報錯FAILED: ODPS-0123031:Partition exception - invalid dynamic partition value: province=xxx。
說明
分區值不能包含雙位元組字元（如中文），必須以字母開頭，包含字母、數字和允許的字元，長度不超過255位元組。允許的字元包括空格、冒號（:）、底線（_）、貨幣符號（$）、井號（#）、英文句點（.）、驚嘆號（!）和at（@），其他字元的行為未定義，例如逸出字元\t、\n和/。
聚簇表不支援動態分區。

注意事項

如果您需要更新表資料到動態分區，需要注意：

insert into partition時，如果分區不存在，會自動建立分區。
多個insert into partition作業並發時，如果分區不存在，優先執行成功的作業會自動建立分區，但只會成功建立一個分區。
如果不能控制insert into partition作業並發，建議您通過alter table命令提前建立分區，詳情請參見分區和列操作。
如果目標表有多級分區，在執行insert操作時，允許指定部分分區為靜態分區，但是靜態分區必須是進階分區。
向動態分區插入資料時，動態分區列必須在select列表中，否則會執行失敗。

命令格式

insert {into|overwrite} table <table_name> partition (<ptcol_name>[, <ptcol_name> ...]) 
<select_statement> from <from_statement>;

table_name：必填。需要插入資料的目標表名。
ptcol_name：必填。目標表分區列的名稱。
select_statement：必填。select子句，從源表中查詢需要插入目標表的資料。
如果目標表只有一級動態分區，則select子句的最後一個欄位值即為目標表的動態分區值。源表select的值和輸出分區的值的關係是由欄位順序決定，並不是由列名稱決定的。當源表的欄位與目標表欄位順序不一致時，建議您按照目標表順序在select_statement語句中指定欄位。
from_statement：必填。from子句，表示資料來源。例如，源表名稱。

使用樣本

樣本1：將源表中的資料插入到目標表中。在運行SQL語句之前，您無法得知會產生哪些分區。只有在語句運行結束後，才能通過region欄位產生的值確定產生的分區。命令樣本如下：

--建立目標表total_revenues。
create table total_revenues (revenue double) partitioned by (region string);

--將源表sale_detail中的資料插入到目標表total_revenues。源表資訊請參見插入或覆寫資料（INSERT INTO | INSERT OVERWRITE）。
set odps.sql.allow.fullscan=true; 
insert overwrite table total_revenues partition(region)
select total_price as revenue, region from sale_detail;

--執行show partitions語句查看錶total_revenues的分區。
show partitions total_revenues;
   
--返回結果。
region=china  

--開啟全表掃描，僅此Session有效。執行select語句查看錶total_revenues中的資料。  
set odps.sql.allow.fullscan=true; 
select * from total_revenues;    

--返回結果。
+------------+------------+
| revenue    | region     |
+------------+------------+
| 100.1      | china      |
| 100.2      | china      |
| 100.3      | china      |
+------------+------------+

樣本2：將源表中的資料插入到目標表中。多級分區，指定一級分區sale_date。命令樣本如下：

--建立目標表sale_detail_dypart。 
create table sale_detail_dypart like sale_detail; 

--指定一級分區，將資料插入目標表。
insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
select shop_name,customer_id,total_price,region from sale_detail;

--開啟全表掃描，僅此Session有效。執行select語句查看錶sale_detail_dypart中的資料。
set odps.sql.allow.fullscan=true; 
select * from sale_detail_dypart;

--返回結果。
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

樣本3：動態分區中，select_statement欄位和目標表動態分區的對應關係是由欄位順序決定，並不是由列名稱決定的。命令樣本如下：

--將源表sale_detail中的資料插入到目標表sale_detail_dypart。
insert overwrite table sale_detail_dypart partition (sale_date, region)
select shop_name,customer_id,total_price,sale_date,region from sale_detail;

--開啟全表掃描，僅此Session有效。執行select語句查看錶sale_detail_dypart中的資料。
set odps.sql.allow.fullscan=true; 
select * from sale_detail_dypart;

--返回結果。決定目標表sale_detail_dypart動態分區的欄位sale_date為源表sale_detail的欄位sale_date；決定目標表sale_detail_dypart動態分區的欄位region為源表sale_detail的欄位region。
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

--將源表sale_detail中的資料插入到目標表sale_detail_dypart，調整select欄位順序。
insert overwrite table sale_detail_dypart partition (sale_date, region)
select shop_name,customer_id,total_price,region,sale_date from sale_detail;

--開啟全表掃描，僅此Session有效。執行select語句查看錶sale_detail_dypart中的資料。
set odps.sql.allow.fullscan=true; 
select * from sale_detail_dypart;

--返回結果。決定目標表sale_detail_dypart動態分區的欄位sale_date為源表sale_detail的欄位region；決定目標表sale_detail_dypart動態分區的欄位region為源表sale_detail的欄位sale_date。
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | china      | 2013       |
| s2         | c2          | 100.2       | china      | 2013       |
| s3         | c3          | 100.3       | china      | 2013       |
+------------+-------------+-------------+------------+------------+

樣本4：向動態分區插入資料時，動態分區列必須在select列表中，否則會執行失敗。錯誤命令樣本如下：

insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
select shop_name,customer_id,total_price from sale_detail;

樣本5：向動態分區插入資料時，不能僅指定低級子分區，而動態插入進階分區，否則會執行失敗。錯誤命令樣本如下：
```
insert overwrite table sale_detail_dypart partition (sale_date, region='china')
select shop_name,customer_id,total_price,sale_date from sale_detail_dypart;
```

樣本6：MaxCompute在向動態分區插入資料時，如果分區列的類型與對應select中列的類型不嚴格一致，會隱式轉換，命令樣本如下：

--建立源表src。
create table src (c int, d string) partitioned by (e int);

--向源表src增加分區。
alter table src add if not exists partition (e=201312);

--向源表src追加資料。
insert into src partition (e=201312) values (1,100.1),(2,100.2),(3,100.3);

--建立目標表parttable。
create table parttable(a int, b double) partitioned by (p string);

--將源表src資料插入目標表parttable。
insert into parttable partition (p) select c, d, current_timestamp() from src;

--查詢目標表parttable。
set odps.sql.allow.fullscan=true;
select * from parttable;

--返回結果如下。
+------------+------------+------------+
| a          | b          | p          |
+------------+------------+------------+
| 1          | 100.1      | 2020-11-25 15:13:28.686 |
| 2          | 100.2      | 2020-11-25 15:13:28.686 |
| 3          | 100.3      | 2020-11-25 15:13:28.686 |
+------------+------------+------------+

說明

如果您的資料是有序的，動態分區插入會把資料隨機打散，導致壓縮率較低。推薦您使用Tunnel命令上傳資料到動態分區，以擷取較好的壓縮率。使用該命令的詳細樣本請參見RDS遷移至MaxCompute實現動態分區。