MaxCompute SQL中的更新和删除如何实现

MaxCompute（原ODPS）是一项大数据计算服务，它能提供快速、完全托管的PB级数据仓库解决方案，使您可以经济并高效的分析处理海量数据。

 

由于 MaxCompute是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，MaxCompute不支持对数据的Update和Delete，所有的数据都是在加载的时候中确定好的。而数据库中的数据通常是需要经常进行修改的，因此可以使用 INSERT INTO ...  VALUES 添加数据，使用 UPDATE ... SET 修改数据。但是随着云计算和大数据的发展越来越多的传统系统需要云化，这其中必然涉及到数据的增删改查，如何根据MaxCompute的特点实现相应的功能是不能回避的，以下就是本人在上云实践中的一些解决办法。

 

准备工作，先创建一张测试数据表ODS_A；

CREATE TABLE ODS_A   (
 id string,
 name string
)
PARTITIONED BY (
    ds STRING
);

分别插入数据id、name 、ds

insert into table ODS_A partition (ds='20170621') select '1','aaa' from (select count(1) from ODS_A) t;

效果如下

id(String)        name(String)        ds(partition) 

-------------------------------------------------------------------

1                       aaa           20170621

2                       bbb           20170621

3                       ccc            20170621

--------------------------------------------------------------------

 

Update语法：

 

要求更新2       bbb   为   2         ddd，场景：保险公司客户信息每天都会发生变化；

create table TMP_A like ODS_A;
insert overwrite table TMP_A partition (ds='one') select id,"ddd" from ODS_A  where id = 2;
insert overwrite table TMP_A partition (ds='two') select a.id,a.name from ODS_A a left outer join (select id from TMP_A where name='ddd') b on (a.id=b.id) where b.id is NULL;
insert overwrite table ODS_A partition (ds='20170621') select id,name from TMP_A where ds='one' or ds='two';

Delete语法：

例如：ODS_A表数据如下

id(String)        name(String)        ds(partition) 

-------------------------------------------------------------------

1                       aaa           20170621

2                       bbb           20170621

3                       ccc            20170621

--------------------------------------------------------------------

要求删除分区表ds=20170621中的bbb数据，场景：客户数据错误，要求修正；

MaxCompute SQL脚本如下：

insert overwrite table ODS_A partition (ds='20170621') select id,name from ODS_A where id !=2;

  

例如：ODS_A表数据如下

id(String)        name(String)        ds(partition) 

-------------------------------------------------------------------

1                       aaa           20170621

2                       bbb           20170621

3                       bbb           20170621

4                       bbb           20170621

5                       ccc            20170621

--------------------------------------------------------------------

要求删除分区表ds=20170621中的重复多余的bbb数据 ，场景：采集日志的时候由于误操作产生了冗余数据；

MaxCompute SQL脚本如下：

insert overwrite table ODS_A partition (ds='20170621')  select id,name from ODS_A group by id,name;