插入或覆寫資料（INSERT INTO | INSERT OVERWRITE） - MaxCompute

MaxCompute支援通過INSERT INTO或INSERT OVERWRITE操作向目標表或靜態分區中插入、覆寫資料。

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

前提條件

執行INSERT INTO和INSERT OVERWRITE操作前需要具備目標表的更新許可權（Update）及源表的元資訊讀取許可權（Select）。授權操作請參見MaxCompute許可權。

功能介紹

在使用MaxCompute SQL處理資料時，INSERT INTO或INSERT OVERWRITE操作可以將SELECT查詢的結果儲存至目標表中。二者的區別是：

INSERT INTO：直接向表或靜態分區中插入資料。您可以在INSERT語句中直接指定分區值，將資料插入指定的分區。如果您需要插入少量測試資料，可以配合VALUES使用。
INSERT OVERWRITE：先清空表或靜態分區中的原有資料，再向表或靜態分區中插入資料。
說明
- MaxCompute的INSERT文法與通常使用的MySQL或Oracle的INSERT文法有差別。在INSERT OVERWRITE後需要加TABLE關鍵字，非直接使用table_name。INSERT INTO可以省略TABLE關鍵字。
- 在反覆對同一個分區執行INSERT OVERWRITE操作時，您通過DESC命令查看到的資料分區Size會不同。這是因為從同一個表的同一個分區SELECT出來再INSERT OVERWRITE回相同分區時，檔案切分邏輯發生變化，從而導致資料的Size發生變化。資料的總長度在INSERT OVERWRITE前後是不變的，您不必擔心儲存計費會產生問題。

向動態分區插入資料的操作請參見插入或覆寫動態分區資料（DYNAMIC PARTITION）。

使用限制

執行INSERT INTO和INSERT OVERWRITE操作更新表或靜態分區資料的使用限制如下：
- INSERT INTO：不支援向聚簇表中追加資料。
- INSERT OVERWRITE：不支援指定插入列，若需指定插入列，請使用INSERT INTO。例如CREATE TABLE t(a STRING, b STRING); INSERT INTO t(a) VALUES ('1');，a列插入1，b列為NULL或預設值。
- MaxCompute對正在操作的表沒有鎖機制，不要同時對一個表執行INSERT INTO或INSERT OVERWRITE操作。

對於Delta Table類型的表有如下限制。
- Delta Table表用INSERT OVERWRITE寫入資料時，相同PK值的多行記錄在寫入表之前會先去重，只選擇第一行寫入，最終寫入的結果依賴於計算過程的記錄順序，無法手動指定。由於該操作寫入的是全量資料，因此預設去重也是儘可能保證PK唯一性的屬性。
- Delta Table表用INSERT INTO寫入資料時，相同PK值的多行預設不去重，都會寫入表中，但如果設定Flag（odps.sql.insert.acidtable.deduplicate.enable）的值為true，則會去重後再寫入表中。

命令格式

INSERT {INTO|OVERWRITE} TABLE <table_name> [PARTITION (<pt_spec>)] [(<col_name> [,<col_name> ...)]]
<select_statement>
FROM <from_statement>
[ZORDER BY <zcol_name> [, <zcol_name> ...]];

參數說明如下：

參數名	是否必填	描述
table_name	是	需要插入資料的目標表名稱。
pt_spec	否	需要插入資料的分區資訊，不允許使用函數等運算式，只能是常量。格式為`(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)`。
col_name	否	需要插入資料的目標表的列名稱。 `INSERT OVERWRITE`不支援指定`[(<col_name> [,<col_name> ...)]`。
select_statement	是	`SELECT`子句，從源表中查詢需要插入目標表的資料。更多`SELECT`資訊，請參見SELECT文法。說明源表與目標表的對應關係依賴於`SELECT`子句中列的順序，而不是表與表之間列名的對應關係。如果目標表是靜態分區，向某個分區插入資料時，分區列不允許出現在`SELECT`子句中。
from_statement	是	`FROM`子句，表示資料來源。例如，源表名稱。
ZORDER BY <zcol_name> [, <zcol_name> ...]	否	向表或分區寫入資料時，支援根據指定的一列或多列（select_statement對應表中的列），把排序列資料相近的行排列在一起，提升查詢時的過濾效能，在一定程度上降低儲存成本。需要注意的是，`ORDER BY x, y`會嚴格按照先x後y的順序對資料進行排序，`ZORDER BY x, y`會把相近的<x, y>盡量排列在一起。當SQL查詢語句的過濾條件中包含排序列時，`ORDER BY`後的資料僅對包含x的運算式有較好的過濾效果，`ZORDER BY`後的資料對包含x或同時包含x、y的運算式均有較好的過濾效果，列壓縮比例更高。

ZORDER BY與SORT BY區別如下：

ZORDER BY有local zorder和global zorder兩種模式，預設模式為local zorder。local模式只是單個檔案內部按照zorder排序，並不是對全域資料做一個重分布，所以如果資料分散在各個檔案，那麼資料的聚集程度可能也不高，無法做到最有效Data Skipping。鑒於該問題，在新版本中支援了global zorder。如果使用global zorder模式，需要增加參數SET odps.sql.default.zorder.type=global;。
ZORDER BY的使用限制如下：
- 對於分區表，一次只允許對1個分區進行ZORDER BY排序。
- ZORDER BY欄位數目只能在2~4之間。
- 目標表為聚簇表時，不支援ZORDER BY子句。
- ZORDER BY可以與DISTRIBUTE BY一起使用，不能與ORDER BY、CLUSTER BY或SORT BY一起使用。
說明
使用ZORDER BY子句寫入資料時，會佔用較多資源，比不排序花費的時間更多。
SORT BY語句用於指定單個檔案內部排序的方式，如果不寫SORT BY，則單個檔案內部按照local zorder排序。

使用樣本：普通表

樣本1：執行INSERT INTO命令向非分區表websites中追加資料。命令樣本如下：

--建立一張非分區表websites。
CREATE TABLE IF NOT EXISTS websites
(id INT,
 name STRING,
 url STRING
);
--建立一張非分區表apps
CREATE TABLE IF NOT EXISTS apps
(id INT,
 app_name STRING,
 url STRING
);
--向表apps追加資料。其中：insert into table table_name可以簡寫為insert into table_name
INSERT INTO apps (id,app_name,url) VALUES 
(1,'Aliyun','https://www.aliyun.com');
--複製apps的表資料追加至websites表
INSERT INTO websites (id,name,url) SELECT id,app_name,url
FROM apps;
--執行select語句查看錶websites中的資料。
SELECT * FROM websites;

返回結果如下：

--返回結果。
+------------+------------+------------+
| id         | name       | url        |
+------------+------------+------------+
| 1          | Aliyun     | https://www.aliyun.com |
+------------+------------+------------+

樣本2：執行INSERT INTO命令向分區表sale_detail中追加資料。命令樣本如下：

--建立一張分區表sale_detail。
CREATE TABLE IF NOT EXISTS sale_detail
(
shop_name     STRING,
customer_id   STRING,
total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

--向源表增加分區。非必需操作，如果不提前建立，寫入時會自動建立該分區。
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china');

--向源表追加資料。其中：insert into table table_name可以簡寫為insert into table_name，但insert overwrite table table_name不可以省略table關鍵字。
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);

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

返回結果如下：

+------------+-------------+-------------+------------+------------+
| 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：執行INSERT OVERWRITE命令向表sale_detail_insert中覆寫資料。命令樣本如下：

--建立目標表sale_detail_insert，與sale_detail有相同的結構。
CREATE TABLE sale_detail_insert LIKE sale_detail;

--給目標表增加分區。非必需操作，如果不提前建立，寫入時會自動建立該分區。
ALTER TABLE sale_detail_insert ADD PARTITION (sale_date='2013', region='china');

--從源表sale_detail中取出資料插入目標表sale_detail_insert。注意不需要聲明目標表欄位，也不支援重排目標表欄位順序。
--對於靜態分區目標表，分區欄位賦值已經在partition()部分聲明，不需要在select_statement中包含，只要按照目標表普通列順序查出對應欄位，按順序映射到目標表即可。動態分區表則需要在select中包含分區欄位，詳情請參見插入或覆寫動態分區資料（DYNAMIC PARTITION）。
SET odps.sql.allow.fullscan=true;
INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date='2013', region='china')
  SELECT 
  shop_name, 
  customer_id,
  total_price 
  FROM sale_detail
  ZORDER BY customer_id, total_price;

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

返回結果如下：

+------------+-------------+-------------+------------+------------+
| 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      |
+------------+-------------+-------------+------------+------------+

樣本4：執行INSERT OVERWRITE命令向表sale_detail_insert中覆寫資料，調整SELECT子句中列的順序。源表與目標表的對應關係依賴於SELECT子句中列的順序，而不是表與表之間列名的對應關係。命令樣本如下：

SET odps.sql.allow.fullscan=true;
INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date='2013', region='china')
    SELECT customer_id, shop_name, total_price FROM sale_detail;    

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail_insert;

返回結果如下：

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| c1         | s1          | 100.1       | 2013       | china      |
| c2         | s2          | 100.2       | 2013       | china      |
| c3         | s3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

在建立sale_detail_insert表時，列的順序為shop_name string、customer_id string、total_price bigint，而從sale_detail向sale_detail_insert插入資料的順序為customer_id、shop_name、total_price。此時，會將sale_detail.customer_id的資料插入sale_detail_insert.shop_name，將sale_detail.shop_name的資料插入sale_detail_insert.customer_id。

樣本5：向某個分區插入資料時，分區列不允許出現在SELECT子句中。如下語句會返回報錯，sale_date和region為分區列，不允許出現在靜態分區的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;
```

樣本6：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;

樣本7：執行INSERT OVERWRITE命令向表mf_src和表mf_zorder_src中覆寫資料，並使用global zorder模式對錶mf_zorder_src進行排序。命令樣本如下：

--建立目標表mf_src。
CREATE TABLE mf_src (key STRING, value STRING);

INSERT OVERWRITE TABLE mf_src
SELECT a, b FROM VALUES ('1', '1'),('3', '3'),('2', '2')
AS t(a, b);

SELECT * FROM mf_src;

--返回結果
+-----+-------+
| key | value |
+-----+-------+
| 1   | 1     |
| 3   | 3     |
| 2   | 2     |
+-----+-------+

--建立目標表mf_zorder_src，與mf_src有相同的結構。
CREATE TABLE mf_zorder_src LIKE mf_src;

--使用global zorder模式排序。
SET odps.sql.default.zorder.type=global;
INSERT OVERWRITE TABLE mf_zorder_src
SELECT key, value FROM mf_src 
ZORDER BY key, value;

SELECT * FROM mf_zorder_src;

返回結果如下：

+-----+-------+
| key | value |
+-----+-------+
| 1   | 1     |
| 2   | 2     |
| 3   | 3     |
+-----+-------+

樣本8：執行INSERT OVERWRITE命令覆寫存量表target資料。命令樣本如下：

-- target表是存量表
SET odps.sql.default.zorder.type=global;
INSERT OVERWRITE TABLE target
SELECT key, value FROM target 
ZORDER BY key, value;

使用樣本：Delta Table類型表

樣本：建立Delta Table表mf_dt，並執行INSERT命令插入並覆寫資料。

--建立Delta Table表mf_dt。
CREATE TABLE IF NOT EXISTS mf_dt (pk BIGINT NOT NULL PRIMARY KEY, 
                  val BIGINT NOT NULL) 
                  PARTITIONED BY (dd STRING, hh STRING) 
                  tblproperties ("transactional"="true");
                  
--向mf_dt表dd='01'和hh='01'的分區中插入測試資料。
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='01') 
                 VALUES (1, 1), (2, 2), (3, 3);

--查詢mf_dt表目標資料分割中的資料                
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
--返回結果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

--使用insert into向mf_dt表目標資料分割中追加資料。
INSERT INTO TABLE mf_dt PARTITION(dd='01', hh='01') 
            VALUES (3, 30), (4, 4), (5, 5);
            
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
--返回結果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

--使用insert overwrite向mf_dt表目標資料分割的覆蓋寫入資料。
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='01') 
                 VALUES (1, 1), (2, 2), (3, 3);
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
--返回結果。
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

--使用insert into向mf_dt表dd='01'和hh='02'的分區寫入資料。
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') 
                 VALUES (1, 11), (2, 22), (3, 32);
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
--返回結果。
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 11         | 01 | 02 |
| 3          | 32         | 01 | 02 |
| 2          | 22         | 01 | 02 |
+------------+------------+----+----+

--開啟全表掃描，僅此Session有效。執行select語句查看錶mf_dt中的資料。
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_dt;
--返回結果。
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 11         | 01 | 02 |
| 3          | 32         | 01 | 02 |
| 2          | 22         | 01 | 02 |
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

最佳實務

Z-Order功能並不是適用於所有情境，也沒有統一的規則來指導是否應該用Z-Order及如何使用。很多時候都需要根據具體案例去嘗試改造，綜合評估改造Z-Order後產生資料帶來的額外計算成本，相對於儲存成本的節省和下遊消費計算成本的節省，是否有收益。下面提供一些經驗上的建議，同時也需要靠各位使用者在使用過程中一起提煉和總結。

優先考慮Clustered Index而不是Z-Order的情境

如果過濾條件基本都是某個首碼的組合，比如a、a和b、a和b和c，那麼使用Clustered Index（即ORDER BY a, b, c）更有效，此時不要使用ZORDER BY。因為ORDER BY對第一個欄位有非常好的排序效果，對後面欄位影響較少；而ZORDER BY對每個欄位給予了相同的權重，僅看某一列的排序是不如ORDER BY的第一個欄位的。
如果某些欄位經常出現在JOIN KEY上，這些欄位使用Hash或Range Clustering更合適。因為MaxCompute Z-Order的實現僅僅在檔案內進行了排序，而SQL引擎對Z-Order的資料分布沒有感知；但是SQL引擎是能夠感知Clustered Index的，因此在做查詢計劃階段能夠更好地最佳化JOIN的效能。
如果某些欄位經常需要進行GROUP BY和ORDER BY，那麼使用Clustered Index可以獲得更好的效能。

Z-Order使用建議

選取經常出現在過濾條件中的欄位，尤其是經常聯合在一起過濾的欄位。
ZORDER BY的欄位數越多，每個欄位的排序效能會越差，因此欄位數不宜超過4個。如果只有一個欄位，那就應該使用Clustered Index而不是Z-Order。
選取的欄位的distinct value不宜太小或太大。太小的極端情況就是性別欄位，只有兩個值，排序並沒有多大意義。太大的極端情況就是基本沒有重複的，這樣排序的代價會很高，因為MaxCompute的Z-Order實現需要將欄位出現的所有值緩衝在記憶體中來計算ZValue。
表的資料量也不宜太小或太大。如果資料量太小，Z-Order無法看出效果。而資料量太大，按照Z-Order方式產出資料的代價會比較高，尤其是基準任務會明顯影響產出的時間。