文档

更新或删除数据(UPDATE | DELETE)

更新时间:

MaxCompute支持通过deleteupdate操作,在行级别删除或更新Transactional表中的数据。

前提条件

执行deleteupdate操作前需要具备目标Transactional表的读取表数据权限(Select)及更新表数据权限(Update)。授权操作请参见MaxCompute权限

功能介绍

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

实际使用deleteupdate功能时,系统会针对每一次删除或更新操作自动生成用户不可见的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文件。假设txnidt0,则f1.delta的内容是((0, t0), (3, t0)),标识行0和行3,在txnt0中被删除了。如果再执行一次delete操作,系统会又生成一个f2.delta文件,该文件仍然是根据Base文件f1编号,读取文件时,基于Base文件f1和当前所有Delta文件的共同表示结果,读取没有被删除的数据。

  • updateupdate操作会转换为delete+insert into的实现逻辑。

deleteupdate功能具备的优势如下:

  • 写数据量下降

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

    说明
    • 对于按量计费场景,deleteupdateinsert overwrite任务的写数据部分不收费,但是deleteupdate任务需要按分区过滤读取需要变更的数据,用于标注删除的记录或写回更新的记录,而读取数据部分依然遵照SQL作业按量计费模型收费,所以deleteupdate任务相比insert overwrite任务,费用并不能因为写数据量减少而减少。

    • 对于包年包月场景,deleteupdate减少了写数据资源消耗,与insert overwrite相比,相同资源可以运行更多的任务。

  • 可直接读取最新状态的表

    此前,MaxCompute在批量更新表数据场景使用的是拉链表,该方式需要在表中增加start_dateend_date辅助列,标识某一行记录的生命周期。当查询表的最新状态时,系统需要从大量数据中根据时间戳获取表的最新状态,使用不够直观。使用deleteupdate功能后,系统可以基于表的Base文件和Delta文件的共同表示结果,直接读取最新状态的表。

重要

多次deleteupdate操作会使Transactional表的底层存储增大,会提高存储和后续查询费用,且影响后续查询效率,建议定期合并(Compact)后台数据。更多合并操作信息,请参见合并Transactional表文件

当作业并发运行且操作的目标表相同时,可能会出现作业冲突问题,更多信息,请参见ACID语义

应用场景

deleteupdate功能适用于随机、低频删除或更新表或分区中的少量数据。例如,按照T+1周期性地批量对表或分区中5%以下的行删除或更新数据。

deleteupdate功能不适用于高频更新、删除数据或实时写入目标表场景。

使用限制

deleteupdate功能及对应Transactional表的使用限制如下:

  • 仅支持Transactional表。更多创建Transactional表信息,请参见表操作

注意事项

通过deleteupdate操作删除或更新表或分区内的数据时,注意事项如下:

  • 如果需要对表中较少数据进行删除或更新操作,且操作和后续读数据的频率也不频繁,建议使用deleteupdate操作,并且在多次执行删除或更新操作之后,请合并表的Base文件和Delta文件,降低表的实际存储。更多信息,请参见合并Transactional表文件

  • 如果删除或更新行数较多(超过5%)并且操作不频繁,但后续对该表的读操作比较频繁,建议使用insert overwriteinsert into操作。更多信息,请参见插入或覆写数据(INSERT INTO | INSERT OVERWRITE)

    例如,某业务场景为每次删除或更新10%的数据,一天更新10次。建议根据实际情况评估deleteupdate操作产生的费用及后续对读性能的消耗是否小于每次使用insert overwriteinsert into操作产生的费用及后续对读性能的消耗,比较两种方式在具体场景中的效率,选择更优方案。

  • 删除数据会生成Delta文件,所以删除数据不一定能降低存储,如果您希望通过delete操作删除数据来降低存储,请合并表的Base文件和Delta文件,降低表的实际存储。更多信息,请参见合并Transactional表文件

  • MaxCompute会按照批处理方式执行deleteupdate作业,每一条语句都会使用资源并产生费用,建议您使用批量方式删除或更新数据。例如您通过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)

delete操作用于删除Transactional分区表或非分区表中满足指定条件的单行或多行数据。

  • 命令格式

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

清空列数据

使用clear column命令清空普通表的列,将不再使用的列数据从磁盘删除并置NULL,从而达到降低存储成本的目的。

  • 命令格式

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

    • table_name:将要执行清空列数据的表名称。

    • column1 , column2 ... :将要被清空数据的列名称。

    • partition:指定分区,若未指定,则表示操作所有分区。

    • pt_spec:分区描述,格式为(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)

    • without touch:表示不更新LastDataModifiedTime若未指定,则会更新LastDataModifiedTime

    说明

    目前默认指定without touch,计划下一阶段支持未指定without touch的清空列数据行为,即若未指定without touch,则会更新LastDataModifiedTime

  • 使用限制

    • 不支持对具有非空属性的列进行clear column操作,可以手动取消not nullable属性:

      alter table <table_name> change column <old_col_name> null;
    • ACID表不支持清空列数据。

    • Cluster不支持清空列数据。

    • 不支持对嵌套类型内部执行清空列数据操作。

    • 不支持清空所有列数据(DROP TABLE可以起到相同效果,且性能更好)。

  • 注意事项

    • Clear Column操作不会改变表的Archive属性。

    • 对嵌套类型的列执行Clear Column操作可能会失败。

      失败的情况为在列式(Columnar)嵌套类型关闭的情况下对含有Columnar嵌套类型的表做Clear Column操作。

    • Clear Column命令执行需要依赖Storage Service在线服务,在作业量多的情况下可能需要排队导致任务变慢。

    • Clear Column操作需要使用计算资源对数据进行读取与写入,所以针对包年包月用户,会占用计算资源,对按量付费用户,会产生和SQL一样的费用。(目前正在邀测中,暂时不进行收费。

  • 使用示例

    • --创建表
      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|
      +-----+-------+------------+------------+--------+------+---------+-----+
    • 下图为lineitem表(aliorc格式)的总存储大小随着对每一列进行Clear Column后的变化过程。lineitem表共16列,有Bigint、Decimal、Char、Date、Varchar几种类型。image.png

      可以看出随着表的16个列陆续被Clear Column命令置为Null,存储空间总共下降了99.97%(由最初的186783526byte下降到了236715byte)。

      说明
      • 列的数据类型和实际存储的值和Clear Column操作节省的空间有关,比如在这个例子中,对Decimal类型的l_extendedprice列,Clear Column操作节省了24.2%的空间(146538799 byte -> 111138117 byte)明显好于平均水平。

      • 当所有列被置Null后表的大小是236715 byte,而不是0。这是因为表的文件结构还存在,Null字段会占用少量的存储空间,系统也需要保留文件Footer信息。

更新数据(UPDATE)

update操作用于将Transactional分区表或非分区表中行对应的单列或多列数据更新为新值。

  • 命令格式

    --方式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_namecol2_name:至少更新一个。待修改行对应的列名称。

    • value1value2:至少更新一个列值。修改后的新值。

    • 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     |
      +------------+------------+------------+
      | 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       |
      +------------+------------+------------+
    • 示例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);
      
      --查询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        |
      +------------+------------+------------+
                                  
    • 示例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);
      
      --查询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         |
      +------------+------------+------------+

合并Transactional表文件

Transactional表底层物理存储为不支持直接读取的Base文件和Delta文件。对Transactional表执行updatedelete操作,不会修改Base文件,只会追加Delta文件,所以会出现更新或删除次数越多,表实际占用存储越大的情况,多次累积的Delta文件会产生较高的存储和后续查询费用。

对同一表或分区,执行多次updatedelete操作,会生成较多Delta文件。系统读数据时,需要加载这些Delta文件来确定哪些行被更新或删除,较多的Delta文件会影响数据读取效率。此时您可以将Base文件和Delta合并,减少存储以便提升数据读取效率。

  • 命令格式

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

    • table_name:必填。待合并文件的Transactional表名称。

    • partition_key:可选。当Transactional表为分区表时,指定分区列名。

    • partition_value:可选。当Transactional表为分区表时,指定分区列名对应的列值。

    • major|minor:至少选择其中一个。二者的区别是:

      • minor:只将Base文件及其下所有的Delta文件合并,消除Delta文件。

      • major:不仅将Base文件及其下所有的Delta文件合并,消除Delta文件,还会把表对应的Base文件中的小文件进行合并。当Base文件较小(小于32 MB)或有Delta文件的情况下,等价于重新对表执行insert overwrite操作,但当Base文件足够大(大于等于32 MB ),且不存在Delta文件的情况下,不会重写。

  • 使用示例

    • 示例1:基于Transactional表acid_delete,合并表文件。命令示例如下:

      alter table acid_delete compact minor;

      返回结果如下:

      Summary:
      Nothing found to merge, set odps.merge.cross.paths=true if cross path merge is permitted.
      OK
    • 示例2:基于Transactional表acid_update_pt,合并表文件。命令示例如下:

      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操作时,报错ODPS-0010000:System internal error - fuxi job failed, caused by: Data Set should contain exactly one row

    • 问题原因:待更新的行数据与子查询结果中的数据无法一一对应,系统无法判断对哪一行数据进行更新。命令示例如下:

      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);

      通过子查询select d_country, d_manager from store_delta sd where sd.s_store_sk = store.s_store_sk与store_delta关联,并用store_delta的数据更新store。假设store的s_store_sk中有[1, 2, 3]三行数据,如果store_delta的s_store_sk有[1, 1]两行数据,数据无法一一对应,执行报错。

    • 解决措施:确保待更新的行数据与子查询结果中的数据一一对应。

  • 问题二:

    • 问题现象:在DataWorks DataStudio中使用compact命令时,报错ODPS-0130161:[1,39] Parse exception - invalid token 'minor', expect one of 'StringLiteral','DoubleQuoteStringLiteral'

    • 问题原因:DataWorks独享资源组中的MaxCompute客户端版本不支持compact命令。

    • 解决措施:请通过DataWorks交流群联系技术支持团队升级独享资源组中的MaxCompute客户端版本。

  • 本页导读 (1)
文档反馈