update
操作用于将Transactional分区表或非分区表中行对应的单列或多列数据更新为新值。
前提条件
执行delete
、update
操作前需要具备目标Transactional表的读取表数据权限(Select)及更新表数据权限(Update)。授权操作请参见MaxCompute权限。
使用限制
delete
、update
功能及对应Transactional表的使用限制如下:
- 仅支持Transactional表。更多创建Transactional表信息,请参见CREATE TABLE。
- 在创建表时,不支持将聚簇表、外部表设置为Transactional表。
- 不支持MaxCompute内部表、外部表、聚簇表与Transactional表互转。
- 不支持其他系统的作业(例如MaxCompute Spark、PAI、Graph)访问Transactional表。
- 不支持
clone table
、merge partition
操作。 - 在对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';
命令格式
--方式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> ]
参数说明
- table_name:必填。待执行
update
操作的Transactional表名称。 - col1_name、col2_name:至少更新一个。待修改行对应的列名称。
- value1、value2:至少更新一个列值。修改后的新值。
- where_condition:可选。WHERE子句,用于筛选满足条件的数据。更多WHERE子句信息,请参见WHERE子句(where_condition)。如果不带WHERE子句,会更新表中的所有数据。
- additional_tables:可选,from子句。
update
支持from子句,使用from子句时,update
的使用会更加方便,不使用from子句与使用from子句的对比示例如下。场景 示例代码 不使用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;
- 当用源表的多行数据更新目标表的一行数据的时,由于不知道用哪条源表的数据去更新,所以遇到这种情况需要用户写聚合操作来保证数据源的unique性,可以看出不使用from子句时,代码不够简洁,用from子句的写比较简洁易懂。
- 关联更新的时候,如果只更新交集数据,不使用from子句时需要写额外的where条件,相对于from语法而言也不太简洁。
使用示例
- 示例1:创建非分区表acid_update,并导入数据,执行
update
操作更新满足指定条件的行对应的列数据。命令示例如下:--创建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 | +------------+
- 示例2:创建分区表acid_update,并导入数据,执行
update
操作更新满足指定条件的行对应的列数据。命令示例如下:--创建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 | +------------+------------+
- 示例3:创建目标表acid_update_t和关联表acid_update_s,实现同时更新多列值。命令示例如下:
--创建待更新目标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 | +------------+------------+------------+ | 4 | 40 | 41 | | 2 | 200 | 201 | | 3 | 300 | 301 | +------------+------------+------------+ --方式四:用汇总结果关联更新。 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 | +------------+------------+------------+ | 4 | 40 | 41 | | 2 | 200 | 201 | | 3 | 300 | 301 | +------------+------------+------------+
- 示例4:涉及两个表的简单关联查询,示例命令如下:
--创建更新目标表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); --查询数据 select * from acid_update_t; --返回: +------------+------------+------------+ | id | value1 | value2 | +------------+------------+------------+ | 2 | 20 | 21 | | 3 | 30 | 31 | | 4 | 40 | 41 | +------------+------------+------------+ 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 | +------------+------------+------------+
- 示例5:涉及多个表的复杂关联查询,示例命令如下:
--创建更新目标表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); --查询数据 select * from acid_update_t; --返回 +------------+------------+------------+ | id | value1 | value2 | +------------+------------+------------+ | 2 | 20 | 21 | | 3 | 30 | 31 | | 4 | 40 | 41 | | 5 | 50 | 51 | +------------+------------+------------+ select * from acid_update_s; --返回 +------------+------------+------------+ | id | value1 | value2 | +------------+------------+------------+ | 1 | 100 | 101 | | 2 | 200 | 201 | | 3 | 300 | 301 | | 4 | 400 | 401 | | 5 | 500 | 501 | +------------+------------+------------+ 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 | +------------+------------+------------+
相关命令
- DELETE:用于删除Transactional分区表或非分区表中满足指定条件的单行或多行数据。
- ALTER TABLE:合并Transactional表文件。