数据仓库开发技术的重要一环：ETL
大家都知道数据仓库，是为企业所有级别的决策制定过程，提供所有类型数据支持的战略集合。因此越来越多的企业和管理者对数据仓库格外关注。做好数据仓库开发，一靠工具，二靠技术。就工具方面来说，很多云厂商已经提供了相当成熟和完备的解决方案。
以我们华为云举例，目前华为云的数据仓库服务 gaussdb（dws）已经做到了：实时、简单、安全可信的企业级融合数据仓库，并可借助dws express将查询分析扩展至数据湖。基于华为gaussdb产品的云原生服务，也可以兼容标准sql和postgresql/oracle生态。所以，在工具完备的情况下，开发者们更关注的应该是技术层面。今天小编要和大家分享的，就是构建数据仓库的重要一环：etl。
etl是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程，是构建数据仓库的重要一环，用户从数据源抽取出所需的数据，经过数据清洗，最终按照预先定义好的数据仓库模型，将数据加载到数据仓库中。目的是将企业中的分散、零乱、标准不统一的数据整合到一起，为企业的决策提供分析依据。
etl算法概览
算法应用场景概览
以上共计累积了8种etl算法，其中主要分成4大类，增量累加、拉链算法是更符合数据仓库历史数据追踪的算法，但现实中基于业务及性能考虑，往往存在全删全插、增量累全算法的数据表应用。
全删全插模型
即delete/insert实现逻辑；
应用场景
主要应用在维表、参数表、主档表加载上，即适合源表是全量数据表，该数据表业务逻辑只需保存当前最新全量数据，不需跟踪过往历史信息。
算法实现逻辑
1.清空目标表； 2.源表全量插入；
etl代码原型
--1.清理目标表 truncate table ; -- 2. 全量插入 insert into (字段***) select 字段*** from ***join where ***;
增量累全模型
即upsert实现逻辑；
应用场景
主要应用在参数表、主档表加载上，即源表可以是增量或全量数据表，目标表始终最新最全记录。
算法实现逻辑
1.利用pk主键比对； 2.目标表和源表pk一致的变化记录，更新目标表； 3.源表存在但目标表不存在，直接插入；
etl代码原型
--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 (字段*** )
增量累加模型
即append实现逻辑；
应用场景
主要应用在流水表加载上，即每日产生的流水、事件数据，追加到目标表中保留全历史数据。流水表、快照表、统计分析表等均是通过该逻辑实现。
算法实现逻辑
1.源表直接插入目标表；
etl代码原型
-- 1.插入目标表 insert into (字段***) select 字段*** from ***join where ***;
全历史拉链模型
拉链表背景知识
概念 拉链表是一张至少存在pk字段、跟踪变化的字段、开链日期、闭链日期组成的数据仓库etl数据表；
益处 根据开链、闭链日期可以快速提取对应日期有效数据； 对于跟踪源系统非事件流水类表数据，拉链算法发挥越大作用，源业务系统通常每日变化数据有限，通过拉链加工可以大大降低每日打快照带来的空间开销，且不损失数据变化历史；
示例 提取指定日期有效数据
提取2020年2月5日当日有效数据
select* from where 开始日期date'2020-02-05';
最终提取到数据：
应用场景
全历史拉链，跟踪源表全量变化历史，若源表记录不存在，则说明数据闭链；根据pk新拉一条有效记录。
算法实现逻辑
1.提取当前有效记录； 2.提取当日源系统最新数据； 3.根据pk字段比对当前有效记录与最新源表，更新目标表当前有效记录，进行闭链操作； 4.根据全字段比对最新源表与当前有效记录，插入目标表；
etl代码原型
--1.提取当前有效记录 insert into (不含开闭链字段***) select 不含开闭链字段*** from where 结束日期 =date''; ; -- 2. 提取当日源系统最新数据 -- 3 今天全部开链的数据，即包含今天全新插入、数据发生变化的记录 insert into select 不含开闭链字段*** from where (不含开闭链字段***) not in (select 不含开闭链字段*** from ); -- 4 今天需要闭链的数据，即今天发生变化的记录 insert into select 不含开闭链字段***,开始时间 from where (不含开闭链字段***) not in (select 不含开闭链字段*** from ); -- 5 更新闭链数据，即历史记录闭链（删除-插入替代更新） delete from where (pk***) in (select pk*** from ) and 结束日期=date''; insert into (不含开闭链字段***,开始时间,结束日期) select 不含开闭链字段***,开始时间,date'' from ; -- 6 插入开链数据，即当日新增记录 insert into . (不含开闭链字段***,开始时间,结束日期) select 不含开闭链字段***,date'',date'' from ;
增量拉链模型
应用场景
增量拉链，目的是追踪数据增量变化历史，根据pk比对新拉一条开链数据；
算法实现逻辑
1.提取上日开链数据； 2.pk相同变化记录，关闭旧记录链，开启新记录链； 3.pk不同，源表存在，新增开链记录
etl代码原型
--1.提取当前有效记录 insert into (不含开闭链字段***) select 不含开闭链字段*** from where 结束日期 =date''; -- 2. 提取当日源系统增量记录 -- 3. 提取当日源系统新增记录 insert into select 不含开闭链字段*** from where (***pk***) not in (select ***pk*** from ); -- 4. 提取当日源系统历史变化记录 insert into select 不含开闭链字段*** from inner join on (***pk 等值***) where (***变化字段 非等值***); -- 5. 更新历史变化记录，关闭历史旧链，开启新链 update as t1 set ,结束日期 = date'' from as s1 where ( ) and t1.结束日期 =date'' ; insert into (不含开闭链字段***,开始时间,结束日期) select 不含开闭链字段***,date'',date'' from ; -- 6. 插入全新开链数据 insert into (不含开闭链字段***,开始时间,结束日期) select 不含开闭链字段***,date'',date'' from ;
增删拉链模型
应用场景
主要是利用业务字段跟踪增量数据中包含删除的变化历史。
算法实现逻辑
1.提取上日开链数据； 2.提取源表非删除记录； 3.pk相同变化记录，关闭旧记录链，开启新记录链； 4.pk比对，源表存在，新增开链记录； 5.提取源表删除记录； 6.pk比对，旧开链记录存在，关闭旧记录链；
etl代码原型
--1.清理目标表《待续...》 truncate table ; -- 2. 全量插入 insert into (字段***) select 字段*** from ***join where ***;
全量增删拉链模型
应用场景
主要是利用业务字段跟踪全量数据中包含删除的变化历史。
算法实现逻辑
1.提取上日开链数据； 2.提取源表非删除记录； 3.pk相同变化记录，关闭旧记录链，开启新记录链； 4.pk比对，源表存在，新增开链记录； 5.提取源表删除记录； 6.pk比对，旧开链记录存在，关闭旧记录链； 7.pk比对，提取旧开链存在但源表不存在记录，关闭旧记录链；
etl代码原型
-- 1. 清理目标表，《待续...》 truncate table ; -- 2. 全量插入 insert into (字段***) select 字段*** from ***join where ***;
自拉链模型
应用场景
主要将流水表数据转化成拉链表数据。
算法实现逻辑
借助源表业务日期字段，和目标表开链、闭链日期比对，首尾相接，拉出全历史拉链；
etl代码原型
--1.清理目标表，《待续...》 truncate table ; -- 2. 全量插入 insert into (字段***) select 字段*** from ***join where ***;
其它说明
1.根据数据仓库最佳实践，所有数据表通常还会包含一些控制字段，即插入日期、更新日期、更新源头字段，这样对于数据变化敏感的数据仓库，可以进一步追踪数据变化历史； 2.etl算法本身是为了更好服务于数据加工过程，实际业务实现过程中，并不局限于传统算法，即涉及到更多适应业务的自定义的etl算法。
原文标题：8种优秀etl算法推荐！数据仓库开发者看过来～
文章出处：【微信公众号：华为开发者社区】欢迎添加关注！文章转载请注明出处。
恒大汽车再获战略投资,用于新能源汽车业务发展
实例S7-1200“运动”控制,插入工艺对象“轴”
工业数据采集平台可以实现哪些PLC的通信与控制
iphone8什么时候上市?iphone8即将发布:苹果在中国业绩继续下滑,iPhone8能否挽回局势?
7015：带有6.25Gbps SerDes收发器和PCIe Gen2接口的低端Zynq Soc
数据仓库开发技术的重要一环：ETL
高精度授时如何改变5G基础设施游戏规则
电池跌落试验机出现故障的处理方法-贝尔试验箱
基于FPGA实现CAN通信的设计方案
88E1510-A0-NNB2C000以太网 IC Marwell
生物识别技术布局智能家居领域，未来将如何？
新品发布|| 三相整流模块N0/N1产品
盘点全球五大MEMS领军企业，都有谁？
佳歌集成灶荣获权威媒体新浪家居双料大奖！
基于Linux的中频磁场测量系统
细节决定成败，PCB layout工程师的7个好习惯
场效应管的检测与测量
4美元的微控制器Pico与RP2040芯片
LCD液晶拼接与DLP背投及PDP等离子技术对比
面向制造的线束设计分析(DFM分析)
以我们华为云举例，目前华为云的数据仓库服务 gaussdb（dws）已经做到了：实时、简单、安全可信的企业级融合数据仓库，并可借助dws express将查询分析扩展至数据湖。基于华为gaussdb产品的云原生服务，也可以兼容标准sql和postgresql/oracle生态。所以，在工具完备的情况下，开发者们更关注的应该是技术层面。今天小编要和大家分享的，就是构建数据仓库的重要一环：etl。
etl是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程，是构建数据仓库的重要一环，用户从数据源抽取出所需的数据，经过数据清洗，最终按照预先定义好的数据仓库模型，将数据加载到数据仓库中。目的是将企业中的分散、零乱、标准不统一的数据整合到一起，为企业的决策提供分析依据。
etl算法概览
算法应用场景概览
以上共计累积了8种etl算法，其中主要分成4大类，增量累加、拉链算法是更符合数据仓库历史数据追踪的算法，但现实中基于业务及性能考虑，往往存在全删全插、增量累全算法的数据表应用。
全删全插模型
即delete/insert实现逻辑；
应用场景
主要应用在维表、参数表、主档表加载上，即适合源表是全量数据表，该数据表业务逻辑只需保存当前最新全量数据，不需跟踪过往历史信息。
算法实现逻辑
1.清空目标表； 2.源表全量插入；
etl代码原型
--1.清理目标表 truncate table ; -- 2. 全量插入 insert into (字段***) select 字段*** from ***join where ***;
增量累全模型
即upsert实现逻辑；
应用场景
主要应用在参数表、主档表加载上，即源表可以是增量或全量数据表，目标表始终最新最全记录。
算法实现逻辑
1.利用pk主键比对； 2.目标表和源表pk一致的变化记录，更新目标表； 3.源表存在但目标表不存在，直接插入；
etl代码原型
--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 (字段*** )
增量累加模型
即append实现逻辑；
应用场景
主要应用在流水表加载上，即每日产生的流水、事件数据，追加到目标表中保留全历史数据。流水表、快照表、统计分析表等均是通过该逻辑实现。
算法实现逻辑
1.源表直接插入目标表；
etl代码原型
-- 1.插入目标表 insert into (字段***) select 字段*** from ***join where ***;
全历史拉链模型
拉链表背景知识
概念 拉链表是一张至少存在pk字段、跟踪变化的字段、开链日期、闭链日期组成的数据仓库etl数据表；
益处 根据开链、闭链日期可以快速提取对应日期有效数据； 对于跟踪源系统非事件流水类表数据，拉链算法发挥越大作用，源业务系统通常每日变化数据有限，通过拉链加工可以大大降低每日打快照带来的空间开销，且不损失数据变化历史；
示例 提取指定日期有效数据
提取2020年2月5日当日有效数据
select* from where 开始日期date'2020-02-05';
最终提取到数据：
应用场景
全历史拉链，跟踪源表全量变化历史，若源表记录不存在，则说明数据闭链；根据pk新拉一条有效记录。
算法实现逻辑
1.提取当前有效记录； 2.提取当日源系统最新数据； 3.根据pk字段比对当前有效记录与最新源表，更新目标表当前有效记录，进行闭链操作； 4.根据全字段比对最新源表与当前有效记录，插入目标表；
etl代码原型
--1.提取当前有效记录 insert into (不含开闭链字段***) select 不含开闭链字段*** from where 结束日期 =date''; ; -- 2. 提取当日源系统最新数据 -- 3 今天全部开链的数据，即包含今天全新插入、数据发生变化的记录 insert into select 不含开闭链字段*** from where (不含开闭链字段***) not in (select 不含开闭链字段*** from ); -- 4 今天需要闭链的数据，即今天发生变化的记录 insert into select 不含开闭链字段***,开始时间 from where (不含开闭链字段***) not in (select 不含开闭链字段*** from ); -- 5 更新闭链数据，即历史记录闭链（删除-插入替代更新） delete from where (pk***) in (select pk*** from ) and 结束日期=date''; insert into (不含开闭链字段***,开始时间,结束日期) select 不含开闭链字段***,开始时间,date'' from ; -- 6 插入开链数据，即当日新增记录 insert into . (不含开闭链字段***,开始时间,结束日期) select 不含开闭链字段***,date'',date'' from ;
增量拉链模型
应用场景
增量拉链，目的是追踪数据增量变化历史，根据pk比对新拉一条开链数据；
算法实现逻辑
1.提取上日开链数据； 2.pk相同变化记录，关闭旧记录链，开启新记录链； 3.pk不同，源表存在，新增开链记录
etl代码原型
--1.提取当前有效记录 insert into (不含开闭链字段***) select 不含开闭链字段*** from where 结束日期 =date''; -- 2. 提取当日源系统增量记录 -- 3. 提取当日源系统新增记录 insert into select 不含开闭链字段*** from where (***pk***) not in (select ***pk*** from ); -- 4. 提取当日源系统历史变化记录 insert into select 不含开闭链字段*** from inner join on (***pk 等值***) where (***变化字段 非等值***); -- 5. 更新历史变化记录，关闭历史旧链，开启新链 update as t1 set ,结束日期 = date'' from as s1 where ( ) and t1.结束日期 =date'' ; insert into (不含开闭链字段***,开始时间,结束日期) select 不含开闭链字段***,date'',date'' from ; -- 6. 插入全新开链数据 insert into (不含开闭链字段***,开始时间,结束日期) select 不含开闭链字段***,date'',date'' from ;
增删拉链模型
应用场景
主要是利用业务字段跟踪增量数据中包含删除的变化历史。
算法实现逻辑
1.提取上日开链数据； 2.提取源表非删除记录； 3.pk相同变化记录，关闭旧记录链，开启新记录链； 4.pk比对，源表存在，新增开链记录； 5.提取源表删除记录； 6.pk比对，旧开链记录存在，关闭旧记录链；
etl代码原型
--1.清理目标表《待续...》 truncate table ; -- 2. 全量插入 insert into (字段***) select 字段*** from ***join where ***;
全量增删拉链模型
应用场景
主要是利用业务字段跟踪全量数据中包含删除的变化历史。
算法实现逻辑
1.提取上日开链数据； 2.提取源表非删除记录； 3.pk相同变化记录，关闭旧记录链，开启新记录链； 4.pk比对，源表存在，新增开链记录； 5.提取源表删除记录； 6.pk比对，旧开链记录存在，关闭旧记录链； 7.pk比对，提取旧开链存在但源表不存在记录，关闭旧记录链；
etl代码原型
-- 1. 清理目标表，《待续...》 truncate table ; -- 2. 全量插入 insert into (字段***) select 字段*** from ***join where ***;
自拉链模型
应用场景
主要将流水表数据转化成拉链表数据。
算法实现逻辑
借助源表业务日期字段，和目标表开链、闭链日期比对，首尾相接，拉出全历史拉链；
etl代码原型
--1.清理目标表，《待续...》 truncate table ; -- 2. 全量插入 insert into (字段***) select 字段*** from ***join where ***;
其它说明
1.根据数据仓库最佳实践，所有数据表通常还会包含一些控制字段，即插入日期、更新日期、更新源头字段，这样对于数据变化敏感的数据仓库，可以进一步追踪数据变化历史； 2.etl算法本身是为了更好服务于数据加工过程，实际业务实现过程中，并不局限于传统算法，即涉及到更多适应业务的自定义的etl算法。
原文标题：8种优秀etl算法推荐！数据仓库开发者看过来～
文章出处：【微信公众号：华为开发者社区】欢迎添加关注！文章转载请注明出处。
恒大汽车再获战略投资,用于新能源汽车业务发展
实例S7-1200“运动”控制,插入工艺对象“轴”
工业数据采集平台可以实现哪些PLC的通信与控制
iphone8什么时候上市?iphone8即将发布:苹果在中国业绩继续下滑,iPhone8能否挽回局势?
7015：带有6.25Gbps SerDes收发器和PCIe Gen2接口的低端Zynq Soc
数据仓库开发技术的重要一环：ETL
高精度授时如何改变5G基础设施游戏规则
电池跌落试验机出现故障的处理方法-贝尔试验箱
基于FPGA实现CAN通信的设计方案
88E1510-A0-NNB2C000以太网 IC Marwell
生物识别技术布局智能家居领域，未来将如何？
新品发布|| 三相整流模块N0/N1产品
盘点全球五大MEMS领军企业，都有谁？
佳歌集成灶荣获权威媒体新浪家居双料大奖！
基于Linux的中频磁场测量系统
细节决定成败，PCB layout工程师的7个好习惯
场效应管的检测与测量
4美元的微控制器Pico与RP2040芯片
LCD液晶拼接与DLP背投及PDP等离子技术对比
面向制造的线束设计分析(DFM分析)