如何使用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