MaxCompute在更新插入、直接加载、全量历史表中的数据转换实践
摘要： 2018“maxcompute开发者交流”钉钉群直播分享，由阿里云数据技术专家彬甫带来以“maxcompute数据仓库数据转换实践”为题的演讲。本文首先介绍了maxcompute的数据架构和流程，其次介绍了etl算法中的三大算法，即更新插入算法、直接加载算法、全量历史表算法，再次介绍了在oltp系统中怎样处理null值，最后对etl相关知识进行了详细地介绍。
2018“maxcompute开发者交流”钉钉群直播分享，由阿里云数据技术专家彬甫带来以“maxcompute数据仓库数据转换实践”为题的演讲。本文首先介绍了maxcompute的数据架构和流程，其次介绍了etl算法中的三大算法，即更新插入算法、直接加载算法、全量历史表算法，再次介绍了在oltp系统中怎样处理null值，最后对etl相关知识进行了详细地介绍。
数十款阿里云产品限时折扣中，赶快点击这里，领券开始云上实践吧!
直播视频回顾
ppt下载请点击
以下内容根据现场分享整理而成。
数据架构及流程
maxcompute包含临时层、基础数据层、应用层三个层次，数据上云后将数据源中的数据先传输到maxcompute里的临时层中，并将数据进行处理，接着将数据经过简单的转换传输到基础数据层，最后将数据进一步汇总到应用层进而提供服务。三个层次的具体介绍如下：
临时层：临时层包含增量数据和全量数据。
基础数据层：基础数据层的优点是可以永久性的保存数据，它包含核心模型和通用汇总，其中核心模型又包含客户、商品、事件、渠道、代码等数据。基础数据层使用数据仓库的实体、属性命名规范来创建模型表，基础数据层表可分为主表、历史表和追加表，且具有保存历史数据、高效地使用、方便的设计原则。
应用层：应用层包含数据集市，即包含客户分析、销售分析、商品库存分析。它不像基础数据层那样可以永久性的保存数据，而是仅保存需要的数据，但它像基础数据层那样适应于使用数据仓库的实体、属性命名规范来创建模型表的原则。
etl算法etl加载转换策略有m1全表覆盖、m2更新插入、m3直接加载、m4全量历史拉链、m5增量历史拉链五种策略，在etl算法中主要介绍m2更新插入（主表）算法、m3直接加载算法、m4全量历史表算法三种算法。
更新插入（主表）算法更新插入（主表）算法适用于保留最新状态表的处理。它是指根据主键（或指定字段）进行数据对比，如果目标表存在记录，则更新，否则插入数据。由于maxcompute中不支持update/delete，因此需使用full outer join实现。
在使用full outer join实现过程中，当主键（source table）为null，主键（target table full）为not null时，outer join 选取结果为不变数据；当主键（source table）为not null，主键（ target table full）为null时，outer join 选取结果为新增数据；当主键（ source table）和主键（target table full）都为not null时，outer join 选取结果为变化数据。
直接追加算法直接追加算法是指增量数据直接追加到目标表中，此算法适合流水、交易、事件、话单等增量且不修改的数据。
全量历史表算法全量历史表中必须包含开始日期（s_date）、结束日期（e_date）这两个字段，通过这两个字段历史表记录了数据的变动轨迹。开始日期（s_date）即数据开始存在的日期，初始加载时，如果业务表中没有日期字段对应，则填最小日期；结束日期（e_date）即数据失效或继续有效的日期，且初始加载时需填最大日期。
对全量历史表算法进行两加载四数据说明，两加载包含初始加载和日常加载，初始加载是指直接把全量数据加载到历史表中，其中开始日期为业务日期或最小日期，结束日期为最大日期；日常加载是指除开始日期、结束日期外的所有字段比对，通过full outer join生成新增、失效、不变三部分数据，直接从历史表中找出已经失效的数据。四数据是指当前新增数据、当前失效数据、当前不变数据、已经失效数据，当前新增数据是指开始日期为数据日期，结束日期为最大日期；当前失效数据是指开始日期不变，结束日期为数据日期；当前不变数据是指开始日期、结束日期都不变；已经失效数据像当前不变数据一样是指开始日期、结束日期都不变，但不同点在于已经失效数据的数据已经无效。以上四部分数据可直接插入到新历史表中。
在上图中，左侧是全量源数据表a，右侧是历史表当前数据b，1代表新增数据，2代表当前未变化数据，3代表当前失效数据。通过a full outer join b后生成新增数据、当前未变化数据、当前失效数据三种数据，再加上原有的历史已经失效的数据，总共四种数据构成new h。
null值处理！！！null是一个sql关键字，代表着未知的数据或值，它既不具备数据类型也不具备数据特征，任何值与null的比较都返回false，结果为空。在oltp系统中，大多数表字段都存在null。
在使用包含null值的字段做表关联或字段聚合时，可能会出现与业务人员期望不一致的
结果；因此，在数据进入数据仓库表时，建议对字段的null值进行非null的处理，但特
殊情况例外。同时，null问题属于数据库技术处理的范畴，由于null值的存在可能会运算出与业务人员需求不一致的结果，因此null值在进行sql和数据分析时需要特别注意。
etl统一的etl脚本开发etl程序从maxcompute元数据表中读取表的column schema时，可根据column schema生成统一的脚本。由于etl逻辑固定，因此可以使用etl程序生成相应的算法脚本，然后对脚本null处理部分内容进行修改即可。在安装python、安装python odps插件的前提下，将程序命名为scripts_gen.py，并设定odps配置文件、目标表名、源表名、主键字段、etl算法参数，通过参数的配置生成名为”.sql”的脚本文件。
etl任务映射在进行etl转换任务开发之前，为了方便进行任务的开发及相关进度记录，需先整理好任务之间的映射关系；在开发过程或开发完成后，为了方便对任务的统一管理维护，需要对字段级的映射及转换进行详细的文档映射记录。
etl转换任务开发-举例
如上图所示，根目录应为02_数据转换格式，dataworks任务目录结构应按主题划分子目录，存储主题表的任务脚本，且任务名称为表名。
如上图所示，在任务开发过程中，具体操作流程为点击主题目录→鼠标右键→新建任务→填写任务名称→创建任务→在出现的任务脚本中将转换脚本拷贝进行保存→在任务脚本页面使用运行或提交→测试运行进行任务测试→在右上角点击调度配置相关调度属性。
etl开发步骤etl开发步骤可分为etl脚本生成、dataworks任务创建、测试上线三大步，具体流程如下：
执行scriptsgen.py脚本生成器程序，根据etl算法输入相应的参数，生成统一的etl脚本文件，并对脚本文件null值处理部分进行修改。
在dataworks数据开发页面，创建相应的目录、任务，将相应的脚本文件sql拷贝到新建的任务中。
测试运行，然后设置调度配置，点击提交。
etl开发经过以上三大步后，任务就可以日常自动运行了。
原文链接
本文为云栖社区原创内容，未经允许不得转载。
linux驱动编写：从hello world到 LED驱动
指纹触控技术的安全性能怎么样
什么是低代码
全球最快4G LTE-Advanced网络，峰值速率达300Mb/s
全新一代Jeep指南者将于11月18日广州车展国产正式亮相,预售价17万-22万元之间
MaxCompute在更新插入、直接加载、全量历史表中的数据转换实践
一种透传的以太网转wifi的方案介绍
四大趋势推动机器视觉应用发展和对行业未来的影响
深度解读汽车电动助力转向系统扭矩传感器技术
云计算存储虚拟化技术三个层次上的实现
人工智能技术应用领域
通过几个方面来简单分析当下的传感器发展
djay Pro 2 加入人工智能算法，从此 DJ 的工作更加轻松
这么有颜值的机器人跟你成家，你愿意吗
分享一下用三极管搭恒流源的几种形式
安徽电信携手华为打造基于IPRAN的政企精品网络
vps服务技术的5大优势
M8法兰插座3芯4P防水连接器
负反馈电路案例
3D建模三维扫描钢琴乐器扫描复刻逆向工程设计服务
2018“maxcompute开发者交流”钉钉群直播分享，由阿里云数据技术专家彬甫带来以“maxcompute数据仓库数据转换实践”为题的演讲。本文首先介绍了maxcompute的数据架构和流程，其次介绍了etl算法中的三大算法，即更新插入算法、直接加载算法、全量历史表算法，再次介绍了在oltp系统中怎样处理null值，最后对etl相关知识进行了详细地介绍。
数十款阿里云产品限时折扣中，赶快点击这里，领券开始云上实践吧!
直播视频回顾
ppt下载请点击
以下内容根据现场分享整理而成。
数据架构及流程
maxcompute包含临时层、基础数据层、应用层三个层次，数据上云后将数据源中的数据先传输到maxcompute里的临时层中，并将数据进行处理，接着将数据经过简单的转换传输到基础数据层，最后将数据进一步汇总到应用层进而提供服务。三个层次的具体介绍如下：
临时层：临时层包含增量数据和全量数据。
基础数据层：基础数据层的优点是可以永久性的保存数据，它包含核心模型和通用汇总，其中核心模型又包含客户、商品、事件、渠道、代码等数据。基础数据层使用数据仓库的实体、属性命名规范来创建模型表，基础数据层表可分为主表、历史表和追加表，且具有保存历史数据、高效地使用、方便的设计原则。
应用层：应用层包含数据集市，即包含客户分析、销售分析、商品库存分析。它不像基础数据层那样可以永久性的保存数据，而是仅保存需要的数据，但它像基础数据层那样适应于使用数据仓库的实体、属性命名规范来创建模型表的原则。
etl算法etl加载转换策略有m1全表覆盖、m2更新插入、m3直接加载、m4全量历史拉链、m5增量历史拉链五种策略，在etl算法中主要介绍m2更新插入（主表）算法、m3直接加载算法、m4全量历史表算法三种算法。
更新插入（主表）算法更新插入（主表）算法适用于保留最新状态表的处理。它是指根据主键（或指定字段）进行数据对比，如果目标表存在记录，则更新，否则插入数据。由于maxcompute中不支持update/delete，因此需使用full outer join实现。
在使用full outer join实现过程中，当主键（source table）为null，主键（target table full）为not null时，outer join 选取结果为不变数据；当主键（source table）为not null，主键（ target table full）为null时，outer join 选取结果为新增数据；当主键（ source table）和主键（target table full）都为not null时，outer join 选取结果为变化数据。
直接追加算法直接追加算法是指增量数据直接追加到目标表中，此算法适合流水、交易、事件、话单等增量且不修改的数据。
全量历史表算法全量历史表中必须包含开始日期（s_date）、结束日期（e_date）这两个字段，通过这两个字段历史表记录了数据的变动轨迹。开始日期（s_date）即数据开始存在的日期，初始加载时，如果业务表中没有日期字段对应，则填最小日期；结束日期（e_date）即数据失效或继续有效的日期，且初始加载时需填最大日期。
对全量历史表算法进行两加载四数据说明，两加载包含初始加载和日常加载，初始加载是指直接把全量数据加载到历史表中，其中开始日期为业务日期或最小日期，结束日期为最大日期；日常加载是指除开始日期、结束日期外的所有字段比对，通过full outer join生成新增、失效、不变三部分数据，直接从历史表中找出已经失效的数据。四数据是指当前新增数据、当前失效数据、当前不变数据、已经失效数据，当前新增数据是指开始日期为数据日期，结束日期为最大日期；当前失效数据是指开始日期不变，结束日期为数据日期；当前不变数据是指开始日期、结束日期都不变；已经失效数据像当前不变数据一样是指开始日期、结束日期都不变，但不同点在于已经失效数据的数据已经无效。以上四部分数据可直接插入到新历史表中。
在上图中，左侧是全量源数据表a，右侧是历史表当前数据b，1代表新增数据，2代表当前未变化数据，3代表当前失效数据。通过a full outer join b后生成新增数据、当前未变化数据、当前失效数据三种数据，再加上原有的历史已经失效的数据，总共四种数据构成new h。
null值处理！！！null是一个sql关键字，代表着未知的数据或值，它既不具备数据类型也不具备数据特征，任何值与null的比较都返回false，结果为空。在oltp系统中，大多数表字段都存在null。
在使用包含null值的字段做表关联或字段聚合时，可能会出现与业务人员期望不一致的
结果；因此，在数据进入数据仓库表时，建议对字段的null值进行非null的处理，但特
殊情况例外。同时，null问题属于数据库技术处理的范畴，由于null值的存在可能会运算出与业务人员需求不一致的结果，因此null值在进行sql和数据分析时需要特别注意。
etl统一的etl脚本开发etl程序从maxcompute元数据表中读取表的column schema时，可根据column schema生成统一的脚本。由于etl逻辑固定，因此可以使用etl程序生成相应的算法脚本，然后对脚本null处理部分内容进行修改即可。在安装python、安装python odps插件的前提下，将程序命名为scripts_gen.py，并设定odps配置文件、目标表名、源表名、主键字段、etl算法参数，通过参数的配置生成名为”.sql”的脚本文件。
etl任务映射在进行etl转换任务开发之前，为了方便进行任务的开发及相关进度记录，需先整理好任务之间的映射关系；在开发过程或开发完成后，为了方便对任务的统一管理维护，需要对字段级的映射及转换进行详细的文档映射记录。
etl转换任务开发-举例
如上图所示，根目录应为02_数据转换格式，dataworks任务目录结构应按主题划分子目录，存储主题表的任务脚本，且任务名称为表名。
如上图所示，在任务开发过程中，具体操作流程为点击主题目录→鼠标右键→新建任务→填写任务名称→创建任务→在出现的任务脚本中将转换脚本拷贝进行保存→在任务脚本页面使用运行或提交→测试运行进行任务测试→在右上角点击调度配置相关调度属性。
etl开发步骤etl开发步骤可分为etl脚本生成、dataworks任务创建、测试上线三大步，具体流程如下：
执行scriptsgen.py脚本生成器程序，根据etl算法输入相应的参数，生成统一的etl脚本文件，并对脚本文件null值处理部分进行修改。
在dataworks数据开发页面，创建相应的目录、任务，将相应的脚本文件sql拷贝到新建的任务中。
测试运行，然后设置调度配置，点击提交。
etl开发经过以上三大步后，任务就可以日常自动运行了。
原文链接
本文为云栖社区原创内容，未经允许不得转载。
linux驱动编写：从hello world到 LED驱动
指纹触控技术的安全性能怎么样
什么是低代码
全球最快4G LTE-Advanced网络，峰值速率达300Mb/s
全新一代Jeep指南者将于11月18日广州车展国产正式亮相,预售价17万-22万元之间
MaxCompute在更新插入、直接加载、全量历史表中的数据转换实践
一种透传的以太网转wifi的方案介绍
四大趋势推动机器视觉应用发展和对行业未来的影响
深度解读汽车电动助力转向系统扭矩传感器技术
云计算存储虚拟化技术三个层次上的实现
人工智能技术应用领域
通过几个方面来简单分析当下的传感器发展
djay Pro 2 加入人工智能算法，从此 DJ 的工作更加轻松
这么有颜值的机器人跟你成家，你愿意吗
分享一下用三极管搭恒流源的几种形式
安徽电信携手华为打造基于IPRAN的政企精品网络
vps服务技术的5大优势
M8法兰插座3芯4P防水连接器
负反馈电路案例
3D建模三维扫描钢琴乐器扫描复刻逆向工程设计服务