大数据-玩转数据-Maxcompute DML

一、插入或覆写数据（INSERT INTO | INSERT OVERWRITE）

1、数据插入使用说明

MaxCompute支持通过insert into或insert overwrite操作向目标表或静态分区中插入、更新数据。

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

• MaxCompute客户端
• MaxCompute控制台（查询编辑器）
• DataWorks控制台
• MaxCompute Studio

前提条件

执行insert into和insert overwrite操作前需要具备目标表的修改权限（Alter）及源表的元信息读取权限（Describe）。

功能介绍

在使用MaxCompute SQL处理数据时，insert into或insert overwrite操作可以将select查询的结果保存至目标表中。二者的区别是：

insert into：直接向表或静态分区中插入数据。您可以在insert语句中直接指定分区值，将数据插入指定的分区。如果您需要插入少量测试数据，可以配合VALUES使用。

insert overwrite：先清空表中的原有数据，再向表或静态分区中插入数据。

说明
MaxCompute的insert语法与通常使用的MySQL或Oracle的insert语法有差别。在insert overwrite后需要加table关键字，非直接使用table_name。insert into可以省略table关键字。

在反复对同一个分区执行insert overwrite操作时，您通过desc命令查看到的数据分区Size会不同。这是因为从同一个表的同一个分区select出来再insert overwrite回相同分区时，文件切分逻辑发生变化，从而导致数据的Size发生变化。数据的总长度在insert overwrite前后是不变的，您不必担心存储计费会产生问题。
并发写入场景，MaxCompute会根据ACID保障并发写入操作。关于ACID的具体语义，请参见ACID语义。
向动态分区插入数据的操作请参见插入或覆写动态分区数据（DYNAMIC PARTITION）。

使用限制
执行insert into和insert overwrite操作更新表或静态分区数据的使用限制如下：

• insert into：不支持向聚簇表中追加数据。
• insert overwrite：不支持指定插入列，只能使用insert into。例如create table t(a string,
  b string); insert into t(a) values (‘1’);，a列插入1，b列为NULL或默认值。
• MaxCompute对正在操作的表没有锁机制，不要同时对一个表执行insert into或insert overwrite操作。
  命令格式

insert {into|overwrite} table <table_name> [partition (<pt_spec>)] [(<col_name> [,<col_name> ...)]
<select_statement>
from <from_statement>
[zorder by <zcol_name> [, <zcol_name> ...]];

说明
源表与目标表的对应关系依赖于select子句中列的顺序，而不是表与表之间列名的对应关系。
如果目标表是静态分区，向某个分区插入数据时，分区列不允许出现在select子句中。
（select_statement对应表中的列），把排序列数据相近的行排列在一起，提升查询时的过滤性能，在一定程度上降低存储成本。需要注意的是，order by x, y会严格地按照先x后y的顺序对数据进行排序，zorder by x, y会把相近的<x, y>尽量排列在一起。当SQL查询语句的过滤条件中包含排序列时，order by后的数据仅对包含x的表达式有较好的过滤效果，zorder by后的数据对包含x或同时包含x、y的表达式均有较好的过滤效果，列压缩比例更高。
zorder by的使用限制如下：

• 目标表为聚簇表时，不支持zorder by子句。
• zorder by可以与distribute by一起使用，不能与order by、cluster by或sort by一起使用。
• 说明 使用zorder by子句写入数据时，会占用较多资源，比不排序花费时间更多。

2、数据插入使用示例

示例1： 执行insert into命令向分区表sale_detail中追加数据。命令示例如下：

创建一张分区表sale_detail。

create table if not exists sale_detail
(
shop_name     string,
customer_id   string,
total_price   double
)
partitioned by (sale_date string, region string);

向源表增加分区

alter table sale_detail add partition (sale_date='2013', region='china');

向源表追加数据

insert into sale_detail partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);

开启全表扫描，仅此Session有效。执行select语句查看表sale_detail中的数据。

set odps.sql.allow.fullscan=true; 

select * from sale_detail;

示例2：执行insert overwrite命令更新表sale_detail_insert中的数据。命令示例如下：
创建目标表sale_detail_insert，与sale_detail有相同的结构。

create table sale_detail_insert like sale_detail;

给目标表增加分区。

alter table sale_detail_insert add partition (sale_date='2013', region='china');

从源表sale_detail中取出数据插入目标表sale_detail_insert。注意不需要声明目标表字段，也不支持重排目标表字段顺序。
对于静态分区目标表，分区字段赋值已经在partition()部分声明，不需要在select_statement中包含，只要按照目标表普通列顺序查出对应字段，按顺序映射到目标表即可。动态分区表则需要在select中包含分区字段，详情请参见插入或覆写动态分区数据（DYNAMIC PARTITION）。

insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')
  select 
  shop_name, 
  customer_id,
  total_price 
  from sale_detail
  zorder by customer_id, total_price;

开启全表扫描，仅此Session有效。执行select语句查看表sale_detail_insert中的数据。

set odps.sql.allow.fullscan=true;
select * from sale_detail_insert;

3、数据插入使用说明

sale_detail_insert中的数据，调整select子句中列的顺序。源表与目标表的对应关系依赖于select子句中列的顺序，而不是表与表之间列名的对应关系。命令示例如下：

insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')
select customer_id, shop_name, total_price from sale_detail;    

4、数据插入使用说明

向某个分区插入数据时，分区列不允许出现在select子句中。

如下语句会返回报错，sale_date和region为分区列，不允许出现在静态分区的select子句中。错误命令示例如下：

insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')
select shop_name, customer_id, total_price, sale_date, region from sale_detail;

5、数据插入使用说明

partition的值只能是常量，不可以为表达式。错误命令示例如下：

insert overwrite table sale_detail_insert partition (sale_date=datepart('2016-09-18 01:10:00', 'yyyy') , region='china')
select shop_name, customer_id, total_price from sale_detail;

二、 插入或覆写动态分区数据（DYNAMIC PARTITION）

MaxCompute支持通过insert into或insert overwrite操作向动态分区中插入数据。

前提条件

执行insert into和insert overwrite操作前需要具备目标表的修改权限（Alter）及源表的元信息读取权限（Describe）。授权操作请参见权限列表。

功能介绍
在使用MaxCompute SQL处理数据时，insert into或insert overwrite语句中不直接指定分区值，只指定分区列名（分区字段）。分区列的值在select子句中提供，系统自动根据分区列的值将数据插入到相应分区。

使用限制
通过insert into和insert overwrite操作向动态分区中插入数据的使用限制如下：

• insert into最多可以生成10000个动态分区，insert overwrite最多可以生成60000个动态分区。
• 分布式环境下，在使用动态分区功能的SQL语句中，单个进程最多只能输出512个动态分区，否则会运行异常。
• 动态生成的分区值不允许为NULL，也不支持特殊字符和中文，否则会报错FAILED: ODPS-0123031:Partition
  exception - invalid dynamic partition value: province=xxx。
• 聚簇表不支持动态分区。

注意事项
如果您需要更新表数据到动态分区，需要注意：

• insert into partition时，如果分区不存在，会自动创建分区。
• 多个insert into partition作业并发时，如果分区不存在，优先执行成功的作业会自动创建分区，但只会成功创建一个分区。
• 如果不能控制insert into partition作业并发，建议您通过alter table命令提前创建分区，详情请参见分区和列操作。
• 如果目标表有多级分区，在执行insert操作时，允许指定部分分区为静态分区，但是静态分区必须是高级分区。
• 向动态分区插入数据时，动态分区列必须在select列表中，否则会执行失败。

1、数据插入使用说明

将源表中的数据插入到目标表中。在运行SQL语句之前，您无法得知会产生哪些分区。只有在语句运行结束后，才能通过region字段产生的值确定产生的分区。命令示例如下：

创建目标表total_revenues。

create table total_revenues (revenue double) partitioned by (region string);

将源表sale_detail中的数据插入到目标表total_revenues。源表信息请参见插入或覆写数据（INSERT INTO | INSERT OVERWRITE）。

insert overwrite table total_revenues partition(region)
select total_price as revenue, region from sale_detail;

执行show partitions语句查看表total_revenues的分区。

show partitions total_revenues;

set odps.sql.allow.fullscan=true; 
select * from total_revenues;    

2、数据插入使用说明

将源表中的数据插入到目标表中。多级分区，指定一级分区sale_date。命令示例如下：
–创建目标表sale_detail_dypart。

create table sale_detail_dypart like sale_detail; 

指定一级分区，将数据插入目标表。

insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
select shop_name,customer_id,total_price,region from sale_detail;

3、数据插入使用说明

动态分区中，select_statement字段和目标表动态分区的对应关系是由字段顺序决定，并不是由列名称决定的。命令示例如下：
将源表sale_detail中的数据插入到目标表sale_detail_dypart。

insert overwrite table sale_detail_dypart partition (sale_date, region)
select shop_name,customer_id,total_price,sale_date,region from sale_detail;

–将源表sale_detail中的数据插入到目标表sale_detail_dypart，调整select字段顺序。

insert overwrite table sale_detail_dypart partition (sale_date, region)
select shop_name,customer_id,total_price,region,sale_date from sale_detail;

–开启全表扫描，仅此Session有效。执行select语句查看表sale_detail_dypart中的数据。

set odps.sql.allow.fullscan=true; 
select * from sale_detail_dypart;

4、数据插入使用说明

向动态分区插入数据时，动态分区列必须在select列表中，否则会执行失败。错误命令示例如下：

insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
select shop_name,customer_id,total_price from sale_detail;

5、数据插入使用说明

向动态分区插入数据时，不能仅指定低级子分区，而动态插入高级分区，否则会执行失败。错误命令示例如下：

insert overwrite table sale_detail_dypart partition (region='china', sale_date)
select shop_name,customer_id,total_price,sale_date from sale_detail_dypart;

6、数据插入使用说明

MaxCompute在向动态分区插入数据时，如果分区列的类型与对应select中列的类型不严格一致，会隐式转换，命令示例如下：
–创建源表src。

create table src (c int, d string) partitioned by (e int);

向源表src增加分区。

alter table src add if not exists partition (e=201312);

向源表src追加数据。

insert into src partition (e=201312) values (1,100.1),(2,100.2),(3,100.3);

创建目标表parttable。

create table parttable(a int, b double) partitioned by (p string);

将源表src数据插入目标表parttable。

insert into parttable partition (p) select c, d, current_timestamp() from src;

三、更新或删除数据（UPDATE | DELETE）

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

功能介绍
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功能后，系统无需写回全部数据，写数据量会显著下降。

说明
对于按量计费场景，delete、update和insert overwrite任务的写数据部分不收费，但是delete、update任务需要按分区过滤读取需要变更的数据，用于标注删除的记录或写回更新的记录，而读取数据部分依然遵照SQL作业按量计费模型收费，所以delete、update任务相比insert overwrite任务，费用并不能因为写数据量减少而减少。
对于包年包月场景，delete、update减少了写数据资源消耗，与insert overwrite相比，相同资源可以运行更多的任务。
可直接读取最新状态的表
此前，MaxCompute在批量更新表数据场景使用的是拉链表，该方式需要在表中增加start_date和end_date辅助列，标识某一行记录的生命周期。当查询表的最新状态时，系统需要从大量数据中根据时间戳获取表的最新状态，使用不够直观。使用delete、update功能后，系统可以基于表的Base文件和Delta文件的共同表示结果，直接读取最新状态的表。

注意 多次delete、update操作会使Transactional表的底层存储增大，会提高存储和后续查询费用，且影响后续查询效率，建议定期合并（Compact）后台数据。更多合并操作信息，请参见合并Transactional表文件。
当作业并发运行且操作的目标表相同时，可能会出现作业冲突问题，更多信息，请参见ACID语义。

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

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

使用限制
delete、update功能及对应Transactional表的使用限制如下：
仅支持Transactional表。更多创建Transactional表信息，请参见表操作。
在创建表时，不支持将聚簇表、外部表设置为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 | 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）
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为2的行，如果在MaxCompute客户端（odpscmd）执行，需要输入yes|no确认。

delete from acid_delete where id = 2; 

查看结果表中数据只有1、3。

select * from acid_delete; 

示例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;

删除分区为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;

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

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

命令格式

update <table_name> set <col1_name> = <value1> [, <col2_name> = <value2> ...] [where <where_condition>];
update <table_name> set (<col1_name> [, <col2_name> ...]) = (<value1> [, <value2> ...])[where <where_condition>];

参数说明
table_name：必填。待执行update操作的Transactional表名称。
col1_name、col2_name：至少更新一个。待修改行对应的列名称。
value1、value2：至少更新一个列值。修改后的新值。
where_condition：可选。WHERE子句，用于筛选满足条件的数据。更多WHERE子句信息，请参见WHERE子句（where_condition）。如果不带WHERE子句，会更新表中的所有数据。
使用示例
示例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：创建分区表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';

–更新指定行的一列数据，将分区为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';

示例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;

–方式二：关联更新，规则为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;

–方式三：关联更新，规则为增加过滤条件，只更新交集。

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

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

对同一表或分区，执行多次update或delete操作，会生成较多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客户端版本。
上一篇：插入或覆写动态分区数据（DYNAMIC PARTITION）

四、MERGE INTO

当您需要对Transactional表执行insert、update、delete操作时，可以通过merge into功能将这些操作合并为一条SQL语句，根据与源表关联的结果，对目标Transactional表执行插入、更新或删除操作，提升执行效率。

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

功能介绍
MaxCompute支持了delete、update功能，但当您需要使用多个insert、update、delete对目标表进行批量操作时，需要编写多条SQL语句，然后进行多次全表扫描才能完成操作。MaxCompute提供的merge into功能，只需要进行一次全表扫描操作，就可以完成全部操作，执行效率要高于insert+update+delete。

merge into操作具备原子性，作业中的insert、update、delete操作都执行成功时，作业才算执行成功；任一内部逻辑处理失败，则整体作业执行失败。

同时，merge into可以为您避免分别执行insert、update、delete操作时，可能导致部分操作执行成功，部分操作执行失败，其中成功部分无法回退的问题。

使用限制
merge into功能的使用限制如下：

• 仅支持Transactional表。更多创建Transactional表信息，请参见表操作。
• 不允许在同一条merge into语句中对相同的行执行多次insert或update操作。

命令格式

merge into <target_table> as <alias_name_t> using <source expression|table_name> as <alias_name_s>

–从on开始对源表和目标表的数据进行关联判断。
on
–when matched…then指定on的结果为True的行为。多个when matched…then之间的数据无交集。
when matched [and ] then update set <set_clause_list>
when matched [and ] then delete
–when not matched…then指定on的结果为False的行为。
when not matched [and ] then insert values <value_list>
target_table：必填。目标表名称，必须是实际存在的表。
alias_name_t：必填。目标表的别名。
source expression|table_name：必填。关联的源表名称、视图或子查询。
alias_name_s：必填。关联的源表、视图或子查询的别名。
boolean expression1：必填。BOOLEAN类型判断条件，判断结果必须为True或False。
boolean expression2、boolean expression3、boolean expression4：可选。update、delete、insert操作相应的BOOLEAN类型判断条件。需要注意的是：
当出现三个WHEN子句时，update、delete、insert都只能出现一次。
如果update和delete同时出现，出现在前的操作必须包括[and ]。
when not matched只能出现在最后一个WHEN子句中，并且只支持insert操作。
set_clause_list：当出现update操作时必填。待更新数据信息。更多update信息，请参见更新数据（UPDATE）。
value_list：当出现insert操作时必填。待插入数据信息。更多values信息，请参见VALUES。
使用示例
创建目标表acid_address_book_base1及源表exists tmp_table1，并插入数据。执行merge into操作，对符合on条件的数据用源表的数据对目标表进行更新操作，对不符合on条件并且源表中满足event_type为I的数据插入目标表。命令示例如下：
–创建目标表acid_address_book_base1。

create table if not exists acid_address_book_base1 
(id bigint,first_name string,last_name string,phone string) 
partitioned by(year string, month string, day string, hour string) 
tblproperties ("transactional"="true"); 

–创建源表exists tmp_table1。

create table if not exists tmp_table1 
(id bigint, first_name string, last_name string, phone string, _event_type_ string);

–向目标表acid_address_book_base1插入测试数据。

insert overwrite table acid_address_book_base1 
partition(year='2020', month='08', day='20', hour='16') 
values (4, 'nihaho', 'li', '222'), (5, 'tahao', 'ha', '333'), 
(7, 'djh', 'hahh', '555');

–向源表exists tmp_table1插入测试数据。

insert overwrite table tmp_table1 values 
(1, 'hh', 'liu', '999', 'I'), (2, 'cc', 'zhang', '888', 'I'),
(3, 'cy', 'zhang', '666', 'I'),(4, 'hh', 'liu', '999', 'U'),
(5, 'cc', 'zhang', '888', 'U'),(6, 'cy', 'zhang', '666', 'U');

–执行merge into操作。

merge into acid_address_book_base1 as t using tmp_table1 as s 
on s.id = t.id and t.year='2020' and t.month='08' and t.day='20' and t.hour='16' 
when matched then update set t.first_name = s.first_name, t.last_name = s.last_name, t.phone = s.phone 
when not matched and (s._event_type_='I') then insert values(s.id, s.first_name, s.last_name,s.phone,'2020','08','20','16');

–查询目标表的数据确认merge into操作结果。

select * from acid_address_book_base1;

+------------+------------+------------+------------+------------+------------+------------+------------+
| id         | first_name | last_name  | phone      | year       | month      | day        | hour       |
+------------+------------+------------+------------+------------+------------+------------+------------+
| 4          | hh         | liu        | 999        | 2020       | 08         | 20         | 16         |
| 5          | cc         | zhang      | 888        | 2020       | 08         | 20         | 16         |
| 7          | djh        | hahh       | 555        | 2020       | 08         | 20         | 16         |
| 1          | hh         | liu        | 999        | 2020       | 08         | 20         | 16         |
| 2          | cc         | zhang      | 888        | 2020       | 08         | 20         | 16         |
| 3          | cy         | zhang      | 666        | 2020       | 08         | 20         | 16         |
+------------+------------+------------+------------+------------+------------+------------+-

五、多路输出（MULTI INSERT）

MaxCompute SQL支持您在一条SQL语句中通过insert into或insert overwrite操作将数据插入不同的目标表或者分区中，实现多路输出。

本文中的命令您可以在如下工具平台执行：
MaxCompute客户端
MaxCompute控制台（查询编辑器）
DataWorks控制台
MaxCompute Studio
前提条件
执行操作前需要具备目标表的修改权限（Alter）及源表的元信息读取权限（Describe）。授权操作请参见授权。

功能介绍
在使用MaxCompute SQL处理数据时，multi insert操作可以将数据插入不同的目标表或分区中，实现多路输出。

使用限制
multi insert操作的使用限制如下：
单条multi insert语句中最多可以写255路输出。超过255路，会上报语法错误。
单条multi insert语句中，对于分区表，同一个目标分区不允许出现多次。
单条multi insert语句中，对于非分区表，该表不能出现多次。
命令格式

from <from_statement>
insert overwrite | into table <table_name1> [partition (<pt_spec1>)]
<select_statement1>
insert overwrite | into table <table_name2> [partition (<pt_spec2>)]
<select_statement2>
...;

from_statement：必填。from子句，代表数据来源。例如，源表名称。
table_name：必填。需要插入数据的目标表名称。
pt_spec：可选。需要插入数据的目标分区信息，此参数不允许使用函数等表达式，只能是常量。格式为(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, …)。插入多个分区时，例如pt_spec1和pt_spec2，目标分区不允许出现多次，即pt_spec1和pt_spec2的分区信息不相同。
select_statement：必填。select子句，从源表中查询需要插入的数据。
使用示例
示例1：将表sale_detail的数据插入到表sale_detail_multi的2010年及2011年中国的销售记录中。命令示例如下：
–创建表sale_detail_multi。

create table sale_detail_multi like sale_detail;

–开启全表扫描，仅此Session有效。将表sale_detail中的数据插入到表sale_detail_multi。

set odps.sql.allow.fullscan=true; 
from sale_detail
insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
select shop_name, customer_id, total_price
insert overwrite table sale_detail_multi partition (sale_date='2011', region='china' )
select shop_name, customer_id, total_price;

–开启全表扫描，仅此Session有效。执行select语句查看表sale_detail_multi中的数据。

set odps.sql.allow.fullscan=true;
select * from sale_detail_multi;

–返回结果。

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2010       | china      |
| s2         | c2          | 100.2       | 2010       | china      |
| s3         | c3          | 100.3       | 2010       | china      |
| s1         | c1          | 100.1       | 2011       | china      |
| s2         | c2          | 100.2       | 2011       | china      |
| s3         | c3          | 100.3       | 2011       | china      |
+------------+-------------+-------------+------------+------------

示例2：同一目标分区不允许出现多次，否则会返回报错。错误命令示例如下：

from sale_detail
insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
select shop_name, customer_id, total_price
insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
select shop_name, customer_id, total_price;

六、VALUES

如果需要向表中插入少量数据，您可以通过insert … values或values table操作向数据量小的表中插入数据。

执行insert into操作前需要具备目标表的修改权限（Alter）及源表的元信息读取权限（Describe）。授权操作请参见授权。

本文中的命令您可以在如下工具平台执行：
MaxCompute客户端
MaxCompute控制台（查询编辑器）
DataWorks控制台
MaxCompute Studio
功能介绍
MaxCompute支持您通过insert … values或values table操作向表中插入少量数据。
功能 说明
insert … values 在业务测试阶段，您可以通过insert … values操作向表中插入数据执行简单测试：
如果插入几条或十几条数据，您可以通过insert … values语句快速向测试表中写入数据。
如果插入几十条数据，您可以通过Tunnel上传一个TXT或CSV格式的数据文件导入数据，详情请参见导入数据。您还可以通过DataWorks的导入功能快速导入一个数据文件，详情请参见界面功能点介绍。
values table 如果您需要对插入的数据进行简单的运算，推荐使用MaxCompute的values table。values table可以在insert语句和任何DML语句中使用。功能如下：
在没有任何物理表时，您可以模拟一个有任意数据的、多行的表，并进行任意运算。
取代select * from与union all组合的方式，构造常量表。
values table支持特殊形式。您可以不通过from子句，直接执行select，select表达式列表中不可以出现其它表中的数据。其底层实现为从一个1行0列的匿名VALUES表中进行选取操作。在测试UDF或其它函数时，您可以通过该方式免去手工创建DUAL表的过程。
使用限制
通过insert … values或values table操作向表中插入数据时，不支持通过insert overwrite操作指定插入列，只能通过insert into操作指定插入列。

命令格式

insert … values
insert into table <table_name>
[partition (<pt_spec>)][(<col1_name> ,<col2_name>,...)] 
values (<col1_value>,<col2_value>,...),(<col1_value>,<col2_value>,...),...
values table
values (<col1_value>,<col2_value>,...),(<col1_value>,<col2_value>,...),<table_name> (<col1_name> ,<col2_name>,...)...

table_name：必填。待插入数据的表名称。该表为已经存在的表。
pt_spec：可选。需要插入数据的目标分区信息，格式为(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, …)。如果需要更新的表为分区表，您需要指定该参数。
col_name：可选。需要插入数据的目标列名称。
col_value：可选。目标表中列对应的列值。多个列值之间用英文逗号（,）分隔。该列值支持常量，同时还支持非常量表达式，例如自定义函数或内建函数表达式。未指定列值时，默认值为NULL。
说明
复杂数据类型无法构造对应的常量，例如ARRAY，您可以在values中使用ARRAY类型，请参见示例3。
通过values写入DATETIME或TIMESTAMP数据类型时，需要在values中指定类型名称，请参见示例4。
使用示例
示例1：通过insert … values操作向特定分区内插入数据。命令示例如下：
–创建分区表srcp。

create table if not exists srcp (key string,value bigint) partitioned by (p string);

–向分区表srcp添加分区。

alter table srcp add if not exists partition (p='abc');

–向表srcp的指定分区abc中插入数据。
insert into table srcp partition (p='abc') values ('a',1),('b',2),('c',3);

–查询表srcp。

select * from srcp where p='abc';

–返回结果。

+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| a          | 1          | abc        |
| b          | 2          | abc        |
| c          | 3          | abc        |
+------------+------------+------------+

示例2：通过insert … values操作向非特定分区内插入数据。命令示例如下：
–创建分区表srcp。

create table if not exists srcp (key string,value bigint) partitioned by (p string);

–向表srcp中插入数据，不指定分区。

insert into table srcp partition (p)(key,p) values ('d','20170101'),('e','20170101'),('f','20170101');

–查询表srcp。

select * from srcp where p='20170101';

–返回结果。

+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| d          | NULL       | 20170101   |
| e          | NULL       | 20170101   |
| f          | NULL       | 20170101   |
+------------+------------+------------+

示例3：使用复杂数据类型构造常量，通过insert操作导入数据。命令示例如下：
–创建分区表srcp。

create table if not exists srcp (key string,value array<int>) partitioned by (p string);

–向分区表srcp添加分区。

alter table srcp add if not exists partition (p='abc');

–向表srcp的指定分区abc中插入数据。

insert into table srcp partition (p='abc') select 'a', array(1, 2, 3);

–查询表srcp。

select * from srcp where p='abc';

–返回结果。

+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| a          | [1,2,3]    | abc        |
+------------+------------+------------+

示例4：通过insert … values操作写入DATETIME或TIMESTAMP数据类型，需要在values中指定类型名称。命令示例如下：
–创建分区表srcp。

create table if not exists srcp (key string, value timestamp) partitioned by (p string);

–向分区表srcp添加分区。
alter table srcp add if not exists partition (p=‘abc’);

–向表srcp的指定分区abc中插入数据。

insert into table srcp partition (p='abc') values (datetime'2017-11-11 00:00:00',timestamp'2017-11-11 00:00:00.123456789');

–查询表srcp。

select * from srcp where p='abc';

–返回结果。

+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| 2017-11-11 00:00:00 | 2017-11-11 00:00:00.123 | abc        |
+------------+------------+------------+

示例5：通过values table操作插入数据。命令示例如下：
–创建分区表srcp。

create table if not exists srcp (key string,value bigint) partitioned by (p string);

–向表srcp中插入数据。

insert into table srcp partition (p) select concat(a,b), length(a)+length(b),'20170102' from values ('d',4),('e',5),('f',6) t(a,b);

–查询表srcp。

select * from srcp where p='20170102';

–返回结果。

+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| d4         | 2          | 20170102   |
| e5         | 2          | 20170102   |
| f6         | 2          | 20170102   |
+------------+------------+------------+

values (…), (…) t(a, b)相当于定义了一个名为t，列为a和b，数据类型分别为STRING和BIGINT的表。列的类型需要从values列表中推导。
示例6：取代select * from与union all组合的方式，构造常量表。命令示例如下：

select 1 c union all select 2 c;

–等价于如下语句。

select * from values (1), (2) t(c);

–返回结果。

+------------+
| c          |
+------------+
| 1          |
| 2          |
+------------+

示例7：通过values table的特殊形式插入数据，不带from子句。命令示例如下：
–创建分区表srcp。

create table if not exists srcp (key string,value bigint) partitioned by (p string);

–向表srcp中插入数据。

insert into table srcp partition (p) select abs(-1), length('abc'), getdate();

–查询表srcp。

select * from srcp;

–返回结果。

+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| 1          | 3          | 2020-11-25 18:39:48 |
+------------+------------+------------+

示例8：使用非常量表达式，命令示例如下：

select * from values ('a'),(to_date('20190101', 'yyyyMMdd')),(getdate()) t(d);

返回结果如下：

+------------+
| d          |
+------------+
| 2021-02-01 18:01:38 |
| 2019-01-01 00:00:00 |
| a          |
+------------+