oracle merge into在大数据离线批处理中的应用

  • 前言

目前仅在一家公司做过大数据相关,也不太清楚其他公司情况。东家这常用的大数据离线处理基本就是sqoop导入到hive中,然后使用spark或者hive计算出结果再导出到oracle中。很多情况下是把oracle中整个表或者某个时间条件的筛选出来的数据整个删掉,再把最新的这部分数据全部导数回到oracle中。

  • 目的

很多时候全部删除在全回导是一个很耗时的处理,特别是有时候计算出来的数据需要对比元数据需要更新(非sql得update语法,泛指数据的变动)的仅仅是很小一部分,但是却做了大批量的delete和insert操作,所有部门大数据探索了一下merge into,先把表导入oracle中的一张临时表,旨在减少oracle目标表的操作时间。

  • 语法介绍
MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
WHEN MATCHED THEN
[UPDATE sql (DELETE sql)(WHERE)]
WHEN NOT MATCHED THEN
[INSERT sql]

语法比较复杂,具体请去查阅相关文章,这里只做简单实用介绍。

  1. 上述语法中不管做的update、delete、insert都都不需要再写出表名,都是对a表操作。
  2. matched 和not matched可以只存在一个,也可以都写
  3. matched子句中只能写update和delete,并且必须写update,delete可有可无,where只能出现一次(待验证),跟在update后就是mathed后的二次筛选update,跟在delete之后表示update后还有一部分数据根据where条件delete掉
  4. not matched中只能有insert写法,可以有where
  5. A表和B表只能有一对一关系,不能一对多
  • 优势

上文已说,需要把表事先导入一张临时表,既然导入了临时表,那么实际上完全可以不用merge into,写一般的insert、delete、update也能完成,这就涉及到效率了,就我浅薄的了解来说,merge into使用的是hash join,仅仅只需要把a表和b表扫描各一次便可以完成任务,如果是update关联查询结果集、或者delete中做exists、in条件,很有可能是做nested loop,实际情况比较复杂,涉及oracle优化器等知识,这里不过多展开,可以说大部分情况下是merge into的效率更高。

  • 开发要点

从语法中可以看出,merge into很多坑,开发难度也比较大,同时限制也比较多,这就要求oracle表设计得很好,提取当前任务目标数据需要写更复杂的sql。

以hive中以天为分区的表为例,每天把当天最新数据导到oracle,因为merge into的delete子句只能在matched中写,所以若是oracle表需要删除时候,得先把前一天的数据存在但是今天不存在的数据,也一起导到oracle中,可以对临时表用一个字段做标记;若是oracle中的表是做假删除,即用一个字段标记为不可用,那就仅仅使用update就足够了,把这个是否可用的字段和其他相关业务字段一同update。这里是oracle中临时表的数据形式,hive中就不限于此,可能需要些复杂sql将数据满足上述场景。

 

oracle merge into在大数据离线批处理中的应用

上一篇:sqlmap注入实例


下一篇:修改配置文件解决MySQL5.7中文乱码