如何使用DELETE刪除表中行的資料 - MaxCompute

DELETE操作用於刪除Transactional分區表或非分區表中滿足指定條件的單行或多行資料。

前提條件

執行delete、update操作前需要具備目標Transactional表的讀取表資料許可權（Select）及更新表資料許可權（Update）。授權操作請參見MaxCompute許可權。

使用限制

delete、update功能及對應Transactional表的使用限制如下：

僅支援Transactional表。更多建立Transactional表資訊，請參見CREATE TABLE。

MaxCompute只允許在建立表時設定Transactional屬性。已建立的表不允許通過alter table方式修改Transactional屬性，執行如下語句會報錯：

alter table not_txn_tbl set tblproperties("transactional"="true");
--報錯。
FAILED: Catalog Service Failed, ErrorCode: 151, Error Message: Set transactional is not supported

在建立表時，不支援將聚簇表、外部表格設定為Transactional表。
不支援MaxCompute內部表、外部表格、聚簇表與Transactional表互轉。
不支援自動合并Transactional表檔案，需要手動執行合併作業，詳情請參見合并Transactional表檔案。
不支援merge partition操作。
其他系統的作業訪問Transactional表有一些限制，例如Graph不支援讀寫；Spark、PAI只支援讀，不支援寫。
在對Transactional表的重要資料執行update、delete或insert overwrite操作前需要手動通過select+insert操作將資料備份至其他表中。

注意事項

通過delete、update操作刪除或更新表或分區內的資料時，注意事項如下：

如果需要對錶中較少資料進行刪除或更新操作，且操作和後續讀資料的頻率也不頻繁，建議使用delete、update操作，並且在多次執行刪除操作之後，請合并表的Base檔案和Delta檔案，降低表的實際儲存。更多資訊，請參見合并Transactional表檔案。
如果刪除或更新行數較多（超過5%）並且操作不頻繁，但後續對該表的讀操作比較頻繁，建議使用insert overwrite或insert into操作。更多資訊，請參見INSERT INTO|OVERWRITE。
例如，某業務情境為每次刪除或更新10%的資料，一天更新10次。建議根據實際情況評估delete、update操作產生的費用及後續對讀效能的消耗是否小於每次使用insert overwrite或insert into操作產生的費用及後續對讀效能的消耗，比較兩種方式在具體情境中的效率，選擇更優方案。
MaxCompute會按照批處理方式執行delete、update作業，每一條語句都會使用資源併產生費用，建議您使用批量方式刪除或更新資料。例如您通過Python指令碼產生並提交了大量行層級更新作業，且每條語句只操作一行或者少量行資料，則每條語句都會產生與SQL掃描輸入資料量對應的費用，並使用相應的計算資源，多條語句累加時將明顯增加費用成本，降低系統效率。命令樣本如下。
```
--推薦方案。
update table1 set col1= (select value1 from table2 where table1.id = table2.id and table1.region = table2.region);

--不推薦方案。
update table1 set col1=1 where id='2021063001'and region='beijing';                  
update table1 set col1=2 where id='2021063002'and region='beijing';
```

命令格式

delete from <table_name> [where <where_condition>];

參數說明

參數	是否必選	說明
table_name	是	待執行`delete`操作的Transactional表名稱。
where_condition	否	WHERE子句，用於篩選滿足條件的資料。更多WHERE子句資訊，請參見WHERE子句（where_condition）。如果不帶WHERE子句，會刪除表中的所有資料。

使用樣本

樣本1：建立非分區表acid_delete，並匯入資料，執行delete操作刪除滿足指定條件的行資料。命令樣本如下：

--建立Transactional表acid_delete。
create table if not exists acid_delete(id bigint) tblproperties ("transactional"="true"); 

--插入資料。
insert overwrite table acid_delete values(1),(2),(3),(2); 

--查看插入結果。
select * from acid_delete; 

+------------+
| id         |
+------------+
| 1          |
| 2          |
| 3          |
| 2          |
+------------+

--刪除id為2的行，如果在MaxCompute用戶端（odpscmd）執行，需要輸入yes|no確認。
delete from acid_delete where id = 2; 

--查看結果表中資料只有1、3。
select * from acid_delete; 

+------------+
| id         |
+------------+
| 1          |
| 3          |
+------------+

樣本2：建立分區表acid_delete_pt，並匯入資料，執行delete操作刪除滿足指定條件的行。命令樣本如下：

--建立Transactional表acid_delete_pt。 
create table if not exists acid_delete_pt(id bigint) partitioned by(ds string) tblproperties ("transactional"="true");

--添加分區。
alter table acid_delete_pt add if not exists partition (ds= '2019');
alter table acid_delete_pt add if not exists partition (ds= '2018');

--插入資料。
insert overwrite table acid_delete_pt partition (ds='2019') values(1),(2),(3);
insert overwrite table acid_delete_pt partition (ds='2018') values(1),(2),(3);

--查看插入結果。
select * from acid_delete_pt;

+------------+------------+
| id         | ds         |
+------------+------------+
| 1          | 2018       |
| 2          | 2018       |
| 3          | 2018       |
| 1          | 2019       |
| 2          | 2019       |
| 3          | 2019       |
+------------+------------+

--刪除分區為2019且id為2的資料，如果在MaxCompute用戶端（odpscmd）執行，需要輸入yes|no確認。
delete from acid_delete_pt where ds='2019' and id = 2;

--查看結果表中已刪除分區為2019且id為2的資料。
select * from acid_delete_pt;

+------------+------------+
| id         | ds         |
+------------+------------+
| 1          | 2018       |
| 2          | 2018       |
| 3          | 2018       |
| 1          | 2019       |
| 3          | 2019       |
+------------+------------+

樣本3：建立目標表acid_delete_t和關聯表acid_delete_s，通過關聯操作刪除滿足指定條件的行。命令樣本如下：

--建立目標Transactional表acid_delete_t和關聯表acid_delete_s。
create table if not exists acid_delete_t(id int,value1 int,value2 int) tblproperties ("transactional"="true");
create table if not exists acid_delete_s(id int,value1 int,value2 int);

--插入資料。
insert overwrite table acid_delete_t values(2,20,21),(3,30,31),(4,40,41);
insert overwrite table acid_delete_s values(1,100,101),(2,200,201),(3,300,301);

--刪除acid_delete_t表中id與acid_delete_s表中id不匹配的行。如果在MaxCompute用戶端（odpscmd）執行，需要輸入yes|no確認。
delete from acid_delete_t where not exists (select * from acid_delete_s where acid_delete_t.id=acid_delete_s.id);

--查看結果表中只有id為2、3的資料。
select * from acid_delete_t;

+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 20         | 21         |
| 3          | 30         | 31         |
+------------+------------+------------+

MaxCompute：DELETE