etl什么意思(ETL算法详解)

藏斋文玩 2021-05-17 阅读:39
披毛犀角,虬角,独角,抹香微信号:5735363
etl什么意思(ETL算法详解)
**、ETL定义 **
ETL是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程,目的是将企业中的分散、零乱、标准不统一的数据整合到一起,为企业的决策提供分析依据。
、ETL算法导图
、算法应用场景
这8种ETL算法,其中主要分成4大类,增量累加、拉链算法是更符合数据仓库历史数据追踪的算法,但现实中基于业务及性能考虑,往往存在全删全插、增量累全算法的数据表应用。
四、算法详解
1、全删全插算法
用DML语句中的Delete/Insert实现逻辑,主要应用在维表、参数表、主档表加载上,即适合源表是全量数据表,该数据表业务逻辑只需保存当前最新全量数据,不需跟踪过往历史信息。
SQL代码模型:
–步骤1. 清空目标表
TRUNCATE TABLE <目标表名>;
–步骤2. 全量插入
INSERT INTO <目标表名> (字段1,***)
SELECT 字段***
FROM <源表>
***JOIN <关联数据>
WheRE ***;
2、增量累全算法
用Upsert实现逻辑,主要应用在参数表、主档表加载上,即源表可以是增量或全量数据表,目标表始终最新最全记录。
SQL代码模型:
–步骤1. 生成加工源表 Create temp Table <临时表> ***;
INSERT INTO <临时表> (字段***)
SELECT 字段***
FROM <源表>
***JOIN <关联数据>
WHERE ***;
— 步骤2. 可利用Merge Into实现累全,当前也可以采用分步Delete/Insert或Update/Insert操作
Merge INTO <目标表> As T1 (字段***)
Using <临时表> as S1
on (PK)
when Matched then
update set Colx = S1.Colx ***
when Not Matched then
INSERT (字段***) values (字段*** );
3、增量累加
用Append实现逻辑,主要应用在流水表加载上,即每日产生的流水、事件数据,追加到目标表中保留全历史数据。流水表、快照表、统计分析表等均是通过该逻辑实现。
SQL代码模型:
–步骤1.插入目标表
INSERT INTO <目标表> (字段1***)
SELECT 字段***
FROM <源表>
***JOIN <关联数据>
WHERE ***;
4、全历史拉链算法
拉链表定义:是一张至少存在PK字段、跟踪变化的字段、开链日期、闭链日期组成的数据仓库ETL数据表。
拉链表优势:根据开链、闭链日期可以快速提取对应日期有效数据,对于跟踪源系统非事件流水类表数据,拉链算法发挥越大作用,源业务系统通常每日变化数据有限,通过拉链加工可以大大降低每日打快照带来的空间开销,且不损失数据变化历史。
全历史拉链表算法实现逻辑:提取当前有效记录-提取当日源系统最新数据-根据PK字段比对当前有效记录与最新源表,更新目标表当前有效记录,进行闭链操作-根据全字段比对最新源表与当前有效记录,插入目标表。
SQL代码模型:
–步骤1. 提取当前有效记录
Insert into <临时表-开链-pre> (不含开闭链字段***)
Select 不含开闭链字段***
From <目标表>
Where 结束日期 =date'<最大日期>’;
–步骤2. 提取当日源系统最新数据
<源表临时表-cur>
— 步骤3 今天全部开链的数据,即包含今天全新插入、数据发生变化的记录
Insert Into <临时表-增量-ins>
Select 不含开闭链字段***
From <源表临时表-cur>
where (不含开闭链字段***) not in
(Select 不含开闭链字段***
From <临时表-开链-pre> );
— 4 今天需要闭链的数据,即今天发生变化的记录
Insert into <临时表-增量-upd>
Select 不含开闭链字段***,开始时间
From <临时表-开链-pre>
where (不含开闭链字段***) not in
(Select 不含开闭链字段***
From <临时表-开链-cur>
);
–步骤5 更新闭链数据,即历史记录闭链(删除-插入替代更新)
DELETE FROM <目标表>
WHERE (PK***) IN
(Select PK*** From <临时表-增量-upd>)
AND 结束日期=date'<最大日期>’;
INSERT INTO <目标表>
  (不含开闭链字段***,开始时间,结束日期)
Select 不含开闭链字段***,开始时间,date'<数据日期>’
From <临时表-增量-upd>;
— 6 插入开链数据,即当日新增记录
INSERT INTO <目标表>
  (不含开闭链字段***,开始时间,结束日期)
Select 不含开闭链字段***,date'<数据日期>’,date'<最大日期>’
From <临时表-增量-ins>;
5、增量拉链算法
算法实现逻辑是提取上日开链数据-PK相同变化记录,关闭旧记录链,开启新记录链-PK不同,源表存在,新增开链记录。增量拉链,目的是追踪数据增量变化历史,根据PK比对新拉一条开链数据。
SQL代码模型:
— 步骤1. 提取当前有效记录
Insert into <临时表-开链-pre> (不含开闭链字段***)
Select 不含开闭链字段***
From <目标表>
Where 结束日期 =date'<最大日期>’;
–步骤2. 提取当日源系统增量记录
<源表临时表-cur>
— 步骤3. 提取当日源系统新增记录
Insert into <临时表-增量-ins>
Select 不含开闭链字段***
From <临时表-开链-cur>
where (PK) not in
(select PK from <临时表-开链-pre>);
–步骤4. 提取当日源系统历史变化记录
Insert into <临时表-增量-upd>
Select 不含开闭链字段***
From <临时表-开链-cur>
inner join <临时表-开链-pre>
on (PK 等值)
where (变化字段 非等值);
–步骤5. 更新历史变化记录,关闭历史旧链,开启新链
update <目标表> AS T1
SET <变化字段 S1赋值>,结束日期 = date'<数据日期>’
FROM <临时表-增量-upd> AS S1
WHERE ( <PK 等值> )
AND T1.结束日期 =date'<最大日期>’
INSERT INTO <目标表>
(不含开闭链字段***,开始时间,结束日期)
SELECT 不含开闭链字段***,date'<数据日期>’,date'<最大日期>’
FROM <临时表-增量-upd>;
–步骤6. 插入全新开链数据
INSERT INTO <目标表>
  (不含开闭链字段***,开始时间,结束日期)
SELECT 不含开闭链字段***,date'<数据日期>’,date'<最大日期>’
FROM <临时表-增量-ins>;
6、增删拉链算法
算法实现逻辑是,提取上日开链数据-提取源表非删除记录-PK相同变化记录,关闭旧记录链,开启新记录链-PK比对,源表存在,新增开链记录-提取源表删除记录-PK比对,旧开链记录存在,关闭旧记录链。
SQL代码模型:
— 步骤1. 清理目标表
TRUNCATE TABLE <目标表>;
— 步骤2. 全量插入
INSERT INTO <目标表> (字段***)
SELECT 字段***
FROM <源表>
***JOIN <关联数据>
WHERE ***;
7、全量增删拉链算法
算法实现逻辑是提取上日开链数据-提取源表非删除记录_PK相同变化记录,关闭旧记录链,开启新记录链-PK比对,源表存在,新增开链记录-提取源表删除记录-PK比对,旧开链记录存在,关闭旧记录链-PK比对,提取旧开链存在但源表不存在记录,关闭旧记录链;主要是利用业务字段跟踪全量数据中包含删除的变化历史。
SQL代码模型:
— 步骤1. 清理目标表
TRUNCATE TABLE <目标表>;
— 步骤2. 全量插入
INSERT INTO <目标表> (字段***)
SELECT 字段***
FROM <源表>
***JOIN <关联数据>
WHERE ***;
8、自拉链算法
根据源表业务日期字段,和目标表开链、闭链日期比对,首尾相接,拉出全历史拉链,主要将流水表数据转化成拉链表数据。
SQL代码模型:
–步骤1. 清理目标表
TRUNCATE TABLE <目标表>;
–步骤2. 全量插入
INSERT INTO <目标表> (字段***)
SELECT 字段***
FROM <源表>
***JOIN <关联数据>
WHERE ***;
在实际工作中所有数据表通常还会包含一些控制字段,即插入日期、更新日期、更新源头字段,这样对于数据变化敏感的数据仓库,可以进一步追踪数据变化历史。



能够收藏一件好的文玩是一种缘分,很多朋友想入手文玩,可是因为了解不是很深,所以担心买到假货,您可以关注【微信号:5735363】,我们为您免费提供更多文玩资讯、鉴定等咨询服务。