MaxCompute SQL 不支持对数据的 Update 和 Delete 操作,但是实际工作中可能确实有一些场景需要这样处理,怎么办呢?实际上,MaxCompute SQL 的 Insert 语法支持 Insert Into/Overwrite 两种数据导入的方式。分别对应数据导入的追加写入和覆盖写入两种场景。追加写入比较容易理解,覆盖写入是指,如果覆盖写入的表是非分区表,那就清空这个表的内容然后用新的结果覆盖进去。如果是非分区表,那就清空相关的分区,然后重新写入数据。本文就各种场景下的的解决方法做一个说明。
从前面的描述可以看到,Insert Overwrite 的就是一个现成的 Update 功能,我们要 Update 不正是要用 Update 后的结果来覆盖 Update 前的结果吗。所以我们可以把需要 Update 后的结果 Select 出来,然后 Insert Overwrite 回去,就能实现了 Update 的结果了。这里用几个简单的例子来说明下方便理解:
- --元数据
- +------------+------------+------------+------------+------------+------------+------------+------------+
- | empno | ename | job | mgr | hiredate | sal | comm | deptno |
- +------------+------------+------------+------------+------------+------------+------------+------------+
- | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.0 | 300.0 | 30 |
- | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.0 | 500.0 | 30 |
- | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.0 | 1400.0 | 30 |
- | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.0 | NULL | 30 |
- | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.0 | NULL | 10 |
- | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.0 | 0.0 | 30 |
- | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.0 | NULL | 30 |
- | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.0 | NULL | 20 |
- | 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 01:00:00 | 3000.0 | NULL | 20 |
- | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 01:00:00 | 1100.0 | NULL | 20 |
- +------------+------------+------------+------------+------------+------------+------------+------------+
- --原SQL
- Update Table emp Set comm = 0 where comm is null;
- --新SQL
- --其实这里有个内建函数coalesce会更简单,但是用case when比较容易理解
- Insert Overwrite table emp Select empno,ename, job,mgr,hiredate,sal,case when comm is null then 0.0 else comm end as comm,deptno from emp;
- +------------+------------+------------+------------+------------+------------+------------+------------+
- | empno | ename | job | mgr | hiredate | sal | comm | deptno |
- +------------+------------+------------+------------+------------+------------+------------+------------+
- | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.0 | 0.0 | 30 |
- | 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 01:00:00 | 3000.0 | 0.0 | 20 |
- | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.0 | 0.0 | 10 |
- | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.0 | 0.0 | 20 |
- | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 01:00:00 | 1100.0 | 0.0 | 20 |
- | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.0 | 0.0 | 30 |
- | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.0 | 300.0 | 30 |
- | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.0 | 1400.0 | 30 |
- | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.0 | 0.0 | 30 |
- | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.0 | 500.0 | 30 |
- +------------+------------+------------+------------+------------+------------+------------+------------+
如果是分区表的数据需要修改数据的话,就套用分区表的 Insert 语法和 Select 语法,都是一样的道理,就不一一举例了。
Delete 删除数据,分为是要删除某个表或者分区里的全部数据,还是只是删除一部分的数据。
如果需要删除某个非分区表,使用
删除这个表里的全部数据
- TRUNCATE TABLE table_name;
如果需要删除的是分区表里的数据,需要
的方式将这个分区删掉从而实现删除对应的分区里的数据 (分区连同分区里的数据一起被删除)。
- ALTER TABLE table_name DROP PARTITION;
- odps@ aliyun2014>show partitions partition_table;
- ds=20160101
- ds=20160102
- OK
- odps@ aliyun2014>alter table partition_table drop if exists partition(ds='20160102');
- Confirm to "alter table partition_table drop if exists partition(ds='20160102');" (yes/no)? yes
- OK
- >show partitions partition_table;
- ds=20160101
- OK
删除部分数据,就是指通过 where 语句过滤,只删除部分数据的情况。Delete 也是用 Insert Overwrite 来实现的。如果需要删除一部分的数据,那其实就是覆盖写入剩下的数据。如果要删除全部的数据,那就是覆盖写入空数据。这里还是只提到普通表的例子,和 Update 一样,普通表和分区表的区别,就是 Insert 的时候,需要指定修改的分区,或者使用动态分区。
- --数据集使用上个例子的Update之前的emp进行操作
- --原SQL 希望删除里面的job为SALESMAN的记录
- Delete from emp where comm >500;
- --新SQL,注意对空值的处理,一些常见下可以考虑用Union all
- Insert Overwrite Table Emp Select * From Emp Where comm <=500 or comm is null;
- --结果集
- +------------+------------+------------+------------+------------+------------+------------+------------+
- | empno | ename | job | mgr | hiredate | sal | comm | deptno |
- +------------+------------+------------+------------+------------+------------+------------+------------+
- | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.0 | NULL | 30 |
- | 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 01:00:00 | 3000.0 | NULL | 20 |
- | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.0 | NULL | 10 |
- | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.0 | NULL | 20 |
- | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 01:00:00 | 1100.0 | NULL | 20 |
- | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.0 | NULL | 30 |
- | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.0 | 300.0 | 30 |
- | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.0 | 0.0 | 30 |
- | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.0 | 500.0 | 30 |
- +------------+------------+------------+------------+------------+------------+------------+------------+
总的看来,这些都不是太有难度的东西,都只是一些比较常用的小技巧。希望这些小技巧能帮助到大家,让工作变得更加有效率。
在最后还是提醒大家,在工作中为避免误操作,尽量避免直接对数据进行直接的修改和删除,建议是创建一张新的表,把结果表进过加工后写入新的表。写入结束核对好了数据后,再把数据导过来。请务必注意数据备份!
来源: