如何更新或删除数据_云原生大数据计算服务 MaxCompute(MaxCompute)-阿里云帮助中心

MaxCompute支持通过 delete 、 update 操作，在行级别删除或更新Transactional表中的数据。

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

前提条件

执行 delete 、 update 操作前需要具备目标Transactional表的读取表数据权限（Select）及更新表数据权限（Update）。授权操作请参见 MaxCompute权限。

功能介绍

MaxCompute的 delete 、 update 功能具备与传统数据库用法类似的删除或更新表中指定行的能力。

实际使用 delete 、 update 功能时，系统会针对每一次删除或更新操作自动生成用户不可见的Delta文件，用于记录删除或更新的数据信息。具体实现原理如下：

delete ：Delta文件中使用 txnid(bigint) 和 rowid(bigint) 字段标识Transactional表的Base文件（表在系统中的存储形式）中的记录在哪次删除操作中被删除。

例如，表t1的Base文件为f1，且内容为 a, b, c, a, b ，当执行 delete from t1 where c1='a'; 命令后，系统会生成一个单独的 f1.delta 文件。假设 txnid 是 t0 ，则 f1.delta 的内容是 ((0, t0), (3, t0)) ，标识行0和行3，在txnt0中被删除了。如果再执行一次 delete 操作，系统会又生成一个 f2.delta 文件，该文件仍然是根据Base文件f1编号，读取文件时，基于Base文件f1和当前所有Delta文件的共同表示结果，读取没有被删除的数据。
update ： update 操作会转换为 delete + insert into 的实现逻辑。

delete 、 update 功能具备的优势如下：

写数据量下降

此前，MaxCompute通过 insert into 或 insert overwrite 操作方式删除或更新表数据，更多信息，请参见插入或覆写数据（INSERT INTO | INSERT OVERWRITE）。当用户需要更新表或分区中的少量数据时，如果通过 insert 操作实现，需要先读取表的全量数据，然后通过 select 操作更新数据，最后通过 insert 操作将全量数据写回表中，效率较低。使用 delete 、 update 功能后，系统无需写回全部数据，写数据量会显著下降。

- ```
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>];

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

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

--创建目标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         |
+------------+------------+------------+

ALTER TABLE <table_name> 
           [partition ( <pt_spec>[, <pt_spec>....] )] 
					 CLEAR COLUMN column1[, column2, column3, ...]
			                         [without touch];

- ```
alter table <table_name> change column <old_col_name> null;
```

--创建表
create table if not exists mf_cc(key string, 
     value string, 
     a1 BIGINT , 
     a2 BIGINT , 
     a3 BIGINT , 
     a4 BIGINT)  
     partitioned by(ds string, hr string);
--添加分区
alter table mf_cc add if not exists partition (ds='20230509', hr='1641');
--插入数据
insert into mf_cc partition (ds='20230509', hr='1641') 
     values("key","value",1,22,3,4);
--查询数据     
select * from mf_cc where ds='20230509' and  hr='1641';
--返回结果
+-----+-------+------------+------------+--------+------+---------+-----+
| key | value | a1         | a2         | a3     | a4   | ds      | hr  |
+-----+-------+------------+------------+--------+------+---------+-----+
| key | value | 1          | 22         | 3      | 4    | 20230509| 1641|
+-----+-------+------------+------------+--------+------+---------+-----+
--清空列数据
alter table mf_cc partition(ds='20230509', hr='1641') 
                  clear column key,a1 
    without touch;
--查询数据     
select * from mf_cc where ds='20230509' and  hr='1641';
--返回结果，key和a1的数据已经变成null
+-----+-------+------------+------------+--------+------+---------+-----+
| key | value | a1         | a2         | a3     | a4   | ds      | hr  |
+-----+-------+------------+------------+--------+------+---------+-----+
| null| value | null       | 22         | 3      | 4    | 20230509| 1641|
+-----+-------+------------+------------+--------+------+---------+-----+

--方式1
update <table_name> set <col1_name> = <value1> [, <col2_name> = <value2> ...] [WHERE <where_condition>];
--方式2
update <table_name> set (<col1_name> [, <col2_name> ...]) = (<value1> [, <value2> ...])[WHERE <where_condition>];
--方式3
UPDATE <table_name>
       SET <col1_name> = <value1> [ , <col2_name> = <value2> , ... ]
        [ FROM <additional_tables> ]
        [ WHERE <where_condition> ]

场景

示例代码

不使用from子句

update target set v =
  (select min(v) from src group by k where target.k = src.key)
  where target.k in (select k from src);

使用from子句

update target set v = b.v
  from (select k, min(v) v from src group by k) b
  where target.k = b.k;

--创建Transactional表acid_update。  
create table if not exists acid_update(id bigint) tblproperties ("transactional"="true");
--插入数据。
insert overwrite table acid_update values(1),(2),(3),(2);
--查看插入结果。
select * from acid_update; 
--返回结果
+------------+
| id         |
+------------+
| 1          |
| 2          |
| 3          |
| 2          |
+------------+
--将所有id为2的行，id值更新为4。
update acid_update set id = 4 where id = 2; 
--查看更新结果，2被更新为4。
select * from acid_update; 
--返回结果
+------------+
| id         |
+------------+
| 1          |
| 3          |
| 4          |
| 4          |
+------------+

--创建Transactional表acid_update_pt。 
create table if not exists acid_update_pt(id bigint) partitioned by(ds string) tblproperties ("transactional"="true");
--添加分区。
alter table acid_update_pt add if not exists partition (ds= '2019');
--插入数据。
insert overwrite table acid_update_pt partition (ds='2019') values(1),(2),(3);
--查看插入结果
select * from acid_update_pt where ds = '2019';
--返回结果
+------------+------------+
| id         | ds         |
+------------+------------+
| 1          | 2019       |
| 2          | 2019       |
| 3          | 2019       |
+------------+------------+
--更新指定行的一列数据，将分区为2019的所有id=2的行，id值更新为4。
update acid_update_pt set id = 4 where ds = '2019' and id = 2; 
--查看更新结果，2被更新为4。
select * from acid_update_pt where ds = '2019';
--返回结果
+------------+------------+
| id         | ds         |
+------------+------------+
| 4          | 2019       |
| 1          | 2019       |
| 3          | 2019       |
+------------+------------+

--创建待更新目标Transactional表acid_update_t和关联表acid_update_s。
create table if not exists acid_update_t(id int,value1 int,value2 int) tblproperties ("transactional"="true");
create table if not exists acid_update_s(id int,value1 int,value2 int);
--插入数据。
insert overwrite table acid_update_t values(2,20,21),(3,30,31),(4,40,41);
insert overwrite table acid_update_s values(1,100,101),(2,200,201),(3,300,301);
--方式一：用常量更新。
update acid_update_t set (value1, value2) = (60,61);
--查询方式一目标表结果数据。
select * from acid_update_t;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 60         | 61         |
| 3          | 60         | 61         |
| 4          | 60         | 61         |
+------------+------------+------------+
--方式二：关联更新，规则为acid_update_t表左关联acid_update_s表。
update acid_update_t set (value1, value2) = (select value1, value2 from acid_update_s where acid_update_t.id = acid_update_s.id);
--查询方式二目标表结果数据。
select * from acid_update_t;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | NULL       | NULL       |
+------------+------------+------------+
--方式三：关联更新，规则为增加过滤条件，只更新交集。
update acid_update_t set (value1, value2) = (select value1, value2 from acid_update_s where acid_update_t.id = acid_update_s.id) where acid_update_t.id in (select id from acid_update_s);
--查询方式三目标表结果数据。
select * from acid_update_t;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | NULL       | NULL       |
+------------+------------+------------+
--方式四：用汇总结果关联更新。
update acid_update_t set (id, value1, value2) = (select id, max(value1),max(value2) from acid_update_s where acid_update_t.id = acid_update_s.id group by acid_update_s.id) where acid_update_t.id in (select id from acid_update_s);
--查询方式四目标表结果数据。
select * from acid_update_t;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | NULL       | NULL       |
+------------+------------+------------+

--创建更新目标表acid_update_t和关联表acid_update_s
create table if not exists acid_update_t(id bigint,value1 bigint,value2 bigint) tblproperties ("transactional"="true");
create table if not exists acid_update_s(id bigint,value1 bigint,value2 bigint);
--插入数据
insert overwrite table acid_update_t values(2,20,21),(3,30,31),(4,40,41);
insert overwrite table acid_update_s values(1,100,101),(2,200,201),(3,300,301);
--查询acid_update_t表数据
select * from acid_update_t;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 20         | 21         |
| 3          | 30         | 31         |
| 4          | 40         | 41         |
+------------+------------+------------+
--查询acid_update_s表数据
select * from acid_update_s;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 1          | 100        | 101        |
| 2          | 200        | 201        |
| 3          | 300        | 301        |
+------------+------------+------------+
--关联更新，目标表增加过滤条件，只取交集
update acid_update_t set value1 = b.value1, value2 = b.value2
from acid_update_s b where acid_update_t.id = b.id;
--查看更新结果，20被更新为200，21被更新为201，30被更新为300，31被更新为301
select * from acid_update_t;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 4          | 40         | 41         |
| 2          | 200        | 201        |
| 3          | 300        | 301        |
+------------+------------+------------+

--创建更新目标表acid_update_t和关联表acid_update_s
create table if not exists acid_update_t(id bigint,value1 bigint,value2 bigint) tblproperties ("transactional"="true");
create table if not exists acid_update_s(id bigint,value1 bigint,value2 bigint);
create table if not exists acid_update_m(id bigint,value1 bigint,value2 bigint);
--插入数据
insert overwrite table acid_update_t
values(2,20,21),(3,30,31),(4,40,41),(5,50,51);
insert overwrite table acid_update_s
values (1,100,101),(2,200,201),(3,300,301),(4,400,401),(5,500,501);
insert overwrite table acid_update_m
values(3,30,101),(4,400,201),(5,300,301);
--查询acid_update_t表数据
select * from acid_update_t;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 20         | 21         |
| 3          | 30         | 31         |
| 4          | 40         | 41         |
| 5          | 50         | 51         |
+------------+------------+------------+
--查询acid_update_s表数据
select * from acid_update_s;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 1          | 100        | 101        |
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | 400        | 401        |
| 5          | 500        | 501        |
+------------+------------+------------+
--查询acid_update_m表数据
select * from acid_update_m;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 3          | 30         | 101        |
| 4          | 400        | 201        |
| 5          | 300        | 301        |
+------------+------------+------------+
--关联更新，并且在where中同时对原表和目标表进行过滤
update acid_update_t
set value1 = acid_update_s.value1, value2 = acid_update_s.value2
from acid_update_s
where acid_update_t.id = acid_update_s.id
and acid_update_s.id > 2
and acid_update_t.value1 not in
(select value1 from acid_update_m where id = acid_update_t.id)
and acid_update_s.value1 not in
(select value1 from acid_update_m where id = acid_update_s.id);
--查看更新结果，acid_update_t表只有id为5的数据符合条件，对应value1被更新尾500，valu2被更新尾501
select * from acid_update_t;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 5          | 500        | 501        |
| 2          | 20         | 21         |
| 3          | 30         | 31         |
| 4          | 40         | 41         |
+------------+------------+------------+

alter table <table_name> [partition (<partition_key> = '<partition_value>' [, ...])] compact {minor|major};

alter table acid_delete compact minor;

Summary:
Nothing found to merge, set odps.merge.cross.paths=true if cross path merge is permitted.
OK

alter table acid_update_pt partition (ds = '2019') compact major;

Summary:
table name: acid_update_pt /ds=2019  instance count: 2  run time: 6
  before merge, file count:        8  file size: 2613  file physical size: 7839
   after merge, file count:        2  file size: 679  file physical size: 2037
OK

- ```
update store set (s_county, s_manager) = (select d_country, d_manager from store_delta sd where sd.s_store_sk = store.s_store_sk) where s_store_sk in (select s_store_sk from store_delta);
```

前提条件

功能介绍

应用场景

使用限制

注意事项

删除数据（DELETE）

清空列数据

更新数据（UPDATE）

合并Transactional表文件

常见问题