增量ETL源数据的几种方式和细节

Kopei article
  2022-01-10 2022-01-10 00 Created   2024-02-05 16:13:32 2024-02-05 16:13 Updated      

Introduction 介绍

ETL是常见的把数据从 OLTP 系统导入数据仓库的方式，一般分为全量，增量，固定范围和推送方式。本文主要讲如何增量抽取，和抽取过程中遇到其它一些问题的解决方法。

增量抽取

增量抽取一般可以使用表中列的时间戳，标识符，交易日期和触发器等组合来判断是否需要增量抽取。使用时间戳，标识符和交易日期的方法大致一样，通过比较元数据中记录的上一次成功etl点和当前时间或位置做比较，进行增量抽取。

原始数据记录删除了数仓该如何处理？

  • 方法 1：比较原始数据和数仓表的主键，如果发现原始数据库主键已经不存在，那么需要在数据表做软删除。
  • 方法 2：如果原始数据库支持删除触发器，那么数仓只需 etl删除审计表或者事件表，标记记录已删除就行。（此方法也是用update,insert）
    如果希望保存所有的更改记录，使用temporal table时态表是个很好的选择，但是需要业务系统支持。

使用固定范围etl

如果有些数据源没有自增键或者可用的时间戳，可用系统赋值的RowID作为参考值，固定范围etl数据。在SQL Server中可用如下代码定位，

1
2
3
SELECT %%physloc%% AS [%%physloc%%],
       sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]
FROM Table1

但是数据的更新还是需要使用checksum来比对，如果字段较多性能还是会有瓶颈。

1
2
3
4
5
6
7
8
9
10
alter table table1 add col4 as checksum(col1, col2, col3)
alter table table2 add col4 as checksum(col1, col2, col3)
go
select * from table1
select * from table2
select * from table1 t1
where not exists
( select * from table2 t2
where t1.col4 = t2.col4 )
go
  • Post title：增量ETL源数据的几种方式和细节
  • Post author：Kopei
  • Create time：2022-01-10 00:00:00
  • Post link：https://kopei.github.io/2022/01/09/data-warehouse-2022-01-10-增量ETL源数据的几种方式和细节/
  • Copyright Notice：All articles in this blog are licensed under BY-NC-SA unless stating additionally.
 Comments
On this page
增量ETL源数据的几种方式和细节
  1. Introduction 介绍
    1. 增量抽取
      1. 原始数据记录删除了数仓该如何处理？
      2. 使用固定范围etl