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

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

前提條件

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

使用限制

DELETE、UPDATE功能及對應Transactional表、Delta Table表的使用限制如下：
說明
關於Transaction表和Delta Table表詳細資料，詳情請參見Transaction Table與Delta Table表參數。
- Delta Table表UPDATE文法不支援修改PK列。

注意事項

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

如果需要對錶中較少資料進行刪除或更新操作，且操作和後續讀資料的頻率也不頻繁，建議使用DELETE、UPDATE操作，並且在多次執行刪除或更新操作之後，請合并表的Base檔案和Delta檔案，降低表的實際儲存。更多資訊，請參見合并Transactional表檔案。
如果刪除或更新行數較多（超過5%）並且操作不頻繁，但後續對該表的讀操作比較頻繁，建議使用INSERT OVERWRITE或INSERT INTO操作。更多資訊，請參見插入或覆寫資料（INSERT INTO | INSERT OVERWRITE）。
例如，某業務情境為每次刪除或更新10%的資料，一天更新10次。建議根據實際情況評估DELETE、UPDATE操作產生的費用及後續對讀效能的消耗是否小於每次使用INSERT OVERWRITE或INSERT INTO操作產生的費用及後續對讀效能的消耗，比較兩種方式在具體情境中的效率，選擇更優方案。
刪除資料會產生Delta檔案，所以刪除資料不一定能降低儲存，如果您希望通過DELETE操作刪除資料來降低儲存，請合并表的Base檔案和Delta檔案，降低表的實際儲存。更多資訊，請參見合并Transactional表檔案。
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> [[AS] alias] [WHERE <condition>];

參數說明

參數	是否必選	說明
table_name	是	待執行`DELETE`操作的Transactional或Delta Table表名稱。
alias	否	表別名。
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;
-- 等價於上面寫法
DELETE FROM acid_delete ad WHERE ad.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);
--等價上面寫法。
DELETE FROM acid_delete_t a WHERE NOT EXISTS (SELECT * FROM acid_delete_s b WHERE a.id = b.id);

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

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

樣本4：建立Delta Table表mf_dt，並匯入資料，執行DELETE操作刪除滿足指定條件的行。命令樣本如下：

--建立目標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");

--插入資料
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') VALUES (1, 1), (2, 2), (3, 3);

--查看插入結果                 
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';

--返回結果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

--刪除分區為01和02，且val為2的資料。
DELETE FROM mf_dt WHERE val = 2  AND dd='01' AND hh='02';

--查看結果表中只有val為1、3的資料
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';

--返回結果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
+------------+------------+----+----+

MaxCompute：DELETE