如何使用 DATAX 以 UPSERT 语义更新下游 ORACLE 数据库中的数据?

简介: 如何使用 DATAX 以 UPSERT 语义更新下游 ORACLE 数据库中的数据?

如何使用 DATAX 以 UPSERT 语义更新下游 ORACLE 数据库中的数据?

1 业务背景

在数据架构上,很多大数据项目,都会将 HIVE/SPARK 等离线计算引擎计算获得的结果数据同步到下游业务系统的线上数据库,以对外提供服务,而且很多业务系统需要为客户提供稳定的 7*24小时的数据查询功能,要求底层数据库中的数据,需要是准确的,不能出现部分数据缺失的情况。

具体到数据同步工具的选型上,datax 是阿里开源的一款流行的数据集成工具,通过插件机制实现了多种异构数据之间的高效的离线数据同步,目前开源版本 datax 支持的插件已经将近 30 多种了,所以很多大数据项目都选用了 DATAX 来做离线数据的同步。

为保证数据的完整性与准确性,在使用 DATAX 进行数据同步时,目前很多项目都采用了先删除旧数据再插入计算生成的新数据的方式 (通过在作业中配置preSql执行旧数据的删除),此时当需要同步的数据量比较大时,旧数据的删除与新数据的插入,都需要一段时间,此时下游数据库中的表不可避免地会有一段时间的空档期,查询不到对应的数据。

怎么解决这个问题呢?

DATAX 官方推荐的一种方式是配置使用临时表,先向临时表导入数据,完成后再 rename 到线上表(可以通过在作业中配置postSql完成这类操作)。

除了临时表这种曲线救国的方式,也可以尝试以 UPSERT 语义直接更新下游数据库中线上的目标表数据。

那么 DATAX 中,不同数据库 WRITER 插件都是怎么实现 UPSERT 语义的呢?

2 DATAX 常见数据库 WRITER 插件是怎么实现 UPSERT 语义的?

  • datax 的 MysqlWriter 和 oceanbasev10writer, 支持配置 writeMode 参数为 insert/replace/update,可以通过该参数控制写入数据到目标表时,底层采用 insert into/replace into/INSERT INTO ... ON DUPLICATE KEY UPDATE 语句:
  • 其中 insert into 当主键/唯一性索引冲突时会写不进去冲突的行;
  • 后两者没有遇到主键/唯一性索引冲突时与 insert into 行为一致,遇到冲突时会用新行替换原有行所有字段;
  • datax原生的 OracleWriter 和PostgresqlWriter,不支持配置writeMode 参数,在底层实现上都是通过 JDBC 连接远程 Oracle/PG 数据库,并执行相应的 insert into ... sql 语句将数据写入 Oracle/pg,在内部会分批次提交入库。

那么,能不能更改原生的 OracleWriter 以支持 UPSERT 语义插入 ORALCE 呢?

3. ORACLE 的 MERGE INTO 语句

Oracle 9i 引入了对 merge语句的支持, 通过 merge 能够在一个SQL语句中对一个表同时执行 inserts 和 updates操作, Oracle 10g 对 MERGE 语句又做了如下增强:

  • UPDATE或INSERT子句是可选的
  • UPDATE和INSERT子句可以加WHERE子句
  • 在ON条件中可以使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表
  • UPDATE子句后面可以跟DELETE子句来去除一些不需要的行。

merge into 语句语法如下:

MERGE INTO [target-table] A USING [source-table sql] B 
ON([conditional expression] and [...]...) 
WHEN MATCHED THEN
 [UPDATE sql] 
WHEN NOT MATCHED THEN 
[INSERT sql]

merge into 语句实例如下:

MERGE INTO member_staging x
USING (SELECT member_id, first_name, last_name, rank FROM members) y
ON (x.member_id  = y.member_id)
WHEN MATCHED THEN
    UPDATE SET x.first_name = y.first_name, 
                        x.last_name = y.last_name, 
                        x.rank = y.rank
    WHERE x.first_name <> y.first_name OR 
           x.last_name <> y.last_name OR 
           x.rank <> y.rank 
WHEN NOT MATCHED THEN
    INSERT(x.member_id, x.first_name, x.last_name, x.rank)  
    VALUES(y.member_id, y.first_name, y.last_name, y.rank);

所以,虽然 oracle 不支持类似 MYSQL的 REPLACE INTO 和 INSERT ... ON DUPLICATE KEY UPDATE,但由于 ORACLE 原生支持 MERGE INTO 语句,我们完全可以更改datax 的 OracleWriter 源码,通过 merge into 语句,实现 UPSERT 语义。

4. 更改 DATAX oracleWriter 以通过 MERGE INTO 语句实现 UPSERT 语义

涉及改动的 datax源码中类和方法的改动点主要有:

  • com.alibaba.datax.plugin.writer.oraclewriter.OracleWriter.Job#init:更改该方法以允许用户配置 writeMode;
  • com.alibaba.datax.plugin.rdbms.writer.util.OriginalConfPretreatmentUtil#dealWriteMode:更改该方法以获取用户配置的 uniqueKeys 并在调用 WriterUtil.getWriteTemplate 时传递 uniqueKeys;
  • com.alibaba.datax.plugin.rdbms.writer.util.WriterUtil#getWriteTemplate: 更改该方法,以在用户配置 writeMode 使用 replace 且配置了uniqueKeys时,拼接获取 ORACLE MERGE INTO 语句对应的 preparedStatement 字符串;
  • com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter.Task#init:更改该方法以获取用户配置的 uniqueKeys;
  • com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter.Task#calcWriteRecordSql:更改该方法以在调用 WriterUtil.getWriteTemplate 时传递 uniqueKeys;
  • com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter.Task#fillPreparedStatementColumnType(PreparedStatement, int, int, String, Column): 更改该方法以在用户配置 writeMode 使用 replace 且配置了uniqueKeys时,对 ORACLE MERGE INTO 语句对应的 preparedStatement 的变量进行 setString 等赋值操作;

com.alibaba.datax.plugin.rdbms.writer.util.WriterUtil#getWriteTemplate 方法拼接获取的 ORACLE MERGE INTO 语句对应的 preparedStatement 字符串,示例内容如下:

MERGE INTO %s x
USING (SELECT ? as member_id, ? as first_name, ? as last_name, ? as rank FROM dual) y
ON (x.member_id  = y.member_id and x.xxx = y.xx)
WHEN MATCHED THEN UPDATE SET 
                x.first_name = y.first_name, 
                x.last_name = y.last_name, 
                x.rank = y.rank
WHEN NOT MATCHED THEN INSERT(x.member_id, x.first_name, x.last_name, x.rank)  
    VALUES(?,?,?,?);


相关文章
|
10天前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库(七):RMAN恢复管理器
【Oracle】玩转Oracle数据库(七):RMAN恢复管理器
36 5
|
2天前
|
Oracle 安全 关系型数据库
Oracle数据守卫(DG):数据的“守护者”与“时光机”
【4月更文挑战第19天】Oracle Data Guard保障数据安全,通过实时维护备库实现故障切换,保证业务连续性。它使用日志传输和应用保持数据同步,如同“时光机”,借助闪回技术能恢复误操作数据。此外,它还提供数据压缩、加密和故障转移等功能,提升数据库安全性与性能。作为数据管理员,理解并善用Data Guard是确保企业数据安全的关键。
|
2天前
|
存储 NoSQL Oracle
Oracle 12c的内存列存储:数据的“闪电侠”
【4月更文挑战第19天】Oracle 12c的内存列存储以超高速度革新数据处理,结合列存储与内存技术,实现快速查询与压缩。它支持向量化查询和并行处理,提升效率,但需合理配置以平衡系统资源。作为数据管理员,应善用此功能,适应业务需求和技术发展。
|
2天前
|
存储 Oracle 关系型数据库
Oracle 12c的多重索引:数据的“多维导航仪”
【4月更文挑战第19天】Oracle 12c的多重索引提升数据查询效率,如同多维导航仪。在同一表上创建针对不同列的多个索引,加速检索过程。虽然过多索引会增加存储和维护成本,但合理选择和使用索引策略,结合位图、函数索引等高级特性,能优化查询,应对复杂场景。数据管理员应善用这些工具,根据需求进行索引管理,支持企业数据分析。
|
2天前
|
存储 Oracle 数据管理
Oracle 12c的自动数据优化(ADO)与热图:数据管理的“瘦身”与“透视”艺术
【4月更文挑战第19天】Oracle 12c的ADO和热图技术革新数据管理。ADO智能清理无用数据,优化存储,提升查询速度,实现数据&quot;瘦身&quot;;热图则以直观的视觉表示展示数据分布和状态,助力识别性能瓶颈,犹如数据的&quot;透视&quot;工具。这两项技术结合,强化数据管理,为企业业务发展保驾护航。
|
2天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL游标属性:数据的“导航仪”与“仪表盘”
【4月更文挑战第19天】Oracle PL/SQL游标属性如同车辆的导航仪和仪表盘,提供丰富信息和控制。 `%FOUND`和`%NOTFOUND`指示数据读取状态,`%ROWCOUNT`记录处理行数,`%ISOPEN`显示游标状态。还有`%BULK_ROWCOUNT`和`%BULK_EXCEPTIONS`增强处理灵活性。通过实例展示了如何在数据处理中利用这些属性监控和控制流程,提高效率和准确性。掌握游标属性是提升数据处理能力的关键。
|
2天前
|
SQL Oracle 安全
Oracle的PL/SQL循环语句:数据的“旋转木马”与“无限之旅”
【4月更文挑战第19天】Oracle PL/SQL中的循环语句(LOOP、EXIT WHEN、FOR、WHILE)是处理数据的关键工具,用于批量操作、报表生成和复杂业务逻辑。LOOP提供无限循环,可通过EXIT WHEN设定退出条件;FOR循环适用于固定次数迭代,WHILE循环基于条件判断执行。有效使用循环能提高效率,但需注意避免无限循环和优化大数据处理性能。掌握循环语句,将使数据处理更加高效和便捷。
|
2天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL条件控制:数据的“红绿灯”与“分岔路”
【4月更文挑战第19天】在Oracle PL/SQL中,IF语句与CASE语句扮演着数据流程控制的关键角色。IF语句如红绿灯,依据条件决定程序执行路径;ELSE和ELSIF提供多分支逻辑。CASE语句则是分岔路,按表达式值选择执行路径。这些条件控制语句在数据验证、错误处理和业务逻辑中不可或缺,通过巧妙运用能实现高效程序逻辑,保障数据正确流转,支持企业业务发展。理解并熟练掌握这些语句的使用是成为合格数据管理员的重要一环。
|
2天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL表达式:数据的魔法公式
【4月更文挑战第19天】探索Oracle PL/SQL表达式,体验数据的魔法公式。表达式结合常量、变量、运算符和函数,用于数据运算与转换。算术运算符处理数值计算,比较运算符执行数据比较,内置函数如TO_CHAR、ROUND和SUBSTR提供多样化操作。条件表达式如CASE和NULLIF实现灵活逻辑判断。广泛应用于SQL查询和PL/SQL程序,助你驾驭数据,揭示其背后的规律与秘密,成为数据魔法师。
|
2天前
|
SQL 存储 Oracle
Oracle的PL/SQL定义变量和常量:数据的稳定与灵动
【4月更文挑战第19天】在Oracle PL/SQL中,变量和常量扮演着数据存储的关键角色。变量是可变的“魔术盒”,用于存储程序运行时的动态数据,通过`DECLARE`定义,可在循环和条件判断中体现其灵活性。常量则是不可变的“固定牌”,一旦设定值便保持不变,用`CONSTANT`声明,提供程序稳定性和易维护性。通过 `%TYPE`、`NOT NULL`等特性,可以更高效地管理和控制变量与常量,提升代码质量。善用两者,能优化PL/SQL程序的结构和性能。

推荐镜像

更多