文档章节

如何用SQL对MaxCompute数据进行修改和删除

_夜枫
 _夜枫
发布于 2017/03/30 21:48
字数 1339
阅读 66
收藏 0

MaxCompute SQL不支持对数据的Update和Delete操作,但是实际工作中可能确实有一些场景需要这样处理,怎么办呢?实际上,MaxCompute SQL的Insert语法支持Insert Into/Overwrite两种数据导入的方式。分别对应数据导入的追加写入和覆盖写入两种场景。追加写入比较容易理解,覆盖写入是指,如果覆盖写入的表是非分区表,那就清空这个表的内容然后用新的结果覆盖进去。如果是非分区表,那就清空相关的分区,然后重新写入数据。本文就各种场景下的的解决方法做一个说明。

Update

从前面的描述可以看到,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

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

总的看来,这些都不是太有难度的东西,都只是一些比较常用的小技巧。希望这些小技巧能帮助到大家,让工作变得更加有效率。

在最后还是提醒大家,在工作中为避免误操作,尽量避免直接对数据进行直接的修改和删除,建议是创建一张新的表,把结果表进过加工后写入新的表。写入结束核对好了数据后,再把数据导过来。请务必注意数据备份!

本文转载自:https://yq.aliyun.com/articles/66890

_夜枫
粉丝 10
博文 506
码字总数 0
作品 0
朝阳
后端工程师
私信 提问
MaxCompute常见问题(2019.04新版)

计量计费 购买开通 MaxCompute计费常见问题 现在公司有数据统计分析的需求,已经在用RDS数据库,还应该开通什么服务? 从MaxCompute上下载数据,使用华东2的ECS云主机使用内网,收取流量费用...

云花
04/09
0
0
MaxCompute命令行工具——odpscmd的操作使用

摘要:在MaxCompute生态中,命令行工具究竟处于什么样的位置?它又发挥着什么样的作用?能够帮助开发者如何更好使用MaxCompute?在本文中,阿里巴巴计算平台产品专家曲宁将通过一个完整简单的...

许此一生
2018/09/05
194
0
MaxCompute如何对SQL查询结果实现分页获取

由于MaxCompute SQL本身不提供类似数据库的select * from table limit x offset y的分页查询逻辑。但是有很多用户希望在一定场景下能够使用获取类似数据库分页的逻辑,对查询结果进行分页/分...

圣远
04/06
0
0
最好用的工兵铲—MaxCompute Studio,来了解下!

摘要: 在大数据计算北京高端峰会上,阿里云计算平台高级专家薛明深入介绍了阿里巴巴大数据计算平台开发利器—MaxCompute Studio。一站式的 IDE,可以快速完成数据浏览和管理、进行基于 SQL ...

猫耳m
2018/06/25
59
0
专家教你使用MaxCompute玩转大数据分析

摘要: 摘要传统的数据分析经常使用的工具是Hadoop或Spark在使用之前环境是需要用户自己去搭建的。随着业务逐渐向云迁移如何在云上进行大数据分析是需要解决的问题。为此阿里云提供了一项很重...

小暖忆
2018/08/04
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Rust 的 GUI 框架生态概览

本文比较全面比较了目前主流的 Rust 的 GUI 框架的表现。其中 ++ 表示非常好,-- 表示非常差,而 o 表示处于平均水平。 参与对比的框架共有 8 个,详细的比较结果如下: Electron + Neon El...

红薯
18分钟前
3
0
golang微服务框架go-micro 入门笔记2.1 micro工具之micro api

micro api micro 功能非常强大,本文将详细阐述micro api 命令行的功能 重要的事情说3次 本文全部代码https://idea.techidea8.com/open/idea.shtml?id=6 本文全部代码https://idea.techidea8....

非正式解决方案
今天
3
0
Spring Context 你真的懂了吗

今天介绍一下大家常见的一个单词 context 应该怎么去理解,正确的理解它有助于我们学习 spring 以及计算机系统中的其他知识。 1. context 是什么 我们经常在编程中见到 context 这个单词,当...

Java知其所以然
昨天
3
0
Spring Boot + Mybatis-Plus 集成与使用(二)

前言: 本章节介绍MyBatis-Puls的CRUD使用。在开始之前,先简单讲解下上章节关于Spring Boot是如何自动配置MyBatis-Plus。 一、自动配置 当Spring Boot应用从主方法main()启动后,首先加载S...

伴学编程
昨天
7
0
用最通俗的方法讲spring [一] ──── AOP

@[TOC](用最通俗的方法讲spring [一] ──── AOP) 写这个系列的目的(可以跳过不看) 自己写这个系列的目的,是因为自己是个比较笨的人,我曾一度怀疑自己的智商不适合干编程这个行业.因为在我...

小贼贼子
昨天
7
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部