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
有兩種模式,預設模式為local zorder
。local模式只是單個檔案內部按照zorder排序,並不是對全域資料做一個重分布,所以如果資料分散在各個檔案,那麼資料的聚集程度可能也不高,無法做到最有效Data Skipping。鑒於該問題,在新版本中支援了global zorder
。local zorder。
global zorder:如果使用
global zorder
模式,需要增加參數set odps.sql.default.zorder.type=global;
。
sort by
語句用於指定單個檔案內部排序的方式,如果不寫sort by
,則單個檔案內部按照local zorder
排序。zorder by
的使用限制如下:對於分區表,一次只允許對1個分區進行
zorder by
排序。zorder by
欄位數目只能在2~4之間。
目標表為聚簇表時,不支援
zorder by
子句。zorder by
可以與distribute by
一起使用,不能與order by
、cluster by
或sort by
一起使用。
說明使用
zorder by
子句寫入資料時,會佔用較多資源,比不排序花費時間更多。
使用樣本:普通表
樣本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)。 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
子句中列的順序,而不是表與表之間列名的對應關係。命令樣本如下: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='02';
--返回結果。
+------------+------------+----+----+
| 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方式產出資料的代價會比較高,尤其是基準任務會明顯影響產出的時間。