ETL算法详解 etl什么意思

【ETL算法详解 etl什么意思】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 S1on (PK)when Matched thenupdate set Colx = S1.Colx ***when Not Matched thenINSERT (字段***) 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(selectPKfrom <临时表-开链-pre>);–步骤4. 取当日源系统历史变化记录Insert into <临时表-增量-upd>Select 不含开闭链字段***From <临时表-开链-cur>inner join <临时表-开链-pre>on (PK 等值)where (变化字段非等值);–步骤5. 更新历史变化记录，关闭历史旧链，开启新链update <目标表> AS T1SET <变化字段 S1赋值>,结束日期 = date'<数据日期>’FROM <临时表-增量-upd> AS S1WHERE ( <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 ***;在实际工作中所有数据表通常还会包含一些控制字段，即插入日期、更新日期、更新源头字段，这样对于数据变化敏感的数据仓库，可以进一步追踪数据变化历史。

特别声明：本站内容均来自网友提供或互联网，仅供参考，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系，我们将在24小时内删除。