ETL培训详解

ETL介绍提纲•KDSP平台概述•ETL架构原理•ETL介绍•ETL工具—Kettle介绍•Kettle使用及练习•Kettle场景实例KDSP—金蝶决策支持平台架构图ETL过程中心数据仓库ERPCRMSCM…ETL过程数据集市数据集市数据集市OLAP分析报表仪表板即席查询…展现平台主数据管理元数据管理决策人员管理人员操作人员数据获取数据存储数据展现ETL过程应用1应用2应用N…暂存区管控中心数据中心后台数据中心前台ETL过程ETL系统BI工具数据服务平台ETL架构原理ETL介绍•ETL模型主要描述了从各个数据源中抽取数据、转换数据并加载到数据仓库的各个环节及流程，主要功能有数据抽取规则、数据抽取频率、数据校验、数据转换规则、数据质量检查、错误处理、记录ETL日志、调度任务设置等。1.从哪里抽取什么样的数据，即抽取规则。要支持增量抽取，即每次抽取只抽取上次抽取后变化的数据。在复杂情况下，还需要检查上次抽取后修改或者删除的数据，并依据数据安全策略进行相应的处理；2.数据抽取频率即什么时间抽取，即抽取时间设置，确定每天晚上12点抽取，或者每1小时正点时抽取1次，等等；3.数据校验，确定每个抽取的数据是否是有效的，是否是没有缺陷的，是否需要补充内容等；4.数据转换规则，即源数据怎样转化成需要的数据的，经过什么样的计算、拆分、合并等等；本数据转换完后，需要触发哪些数据的ETL过程；5.数据质量检查，可以采用对账等方式对转换完后的数据进行统一检查，保证数据的抽取质量；6.错误处理，如果转换过程中出现错误，需要进行统一的、相应的处理，给出明确的业务描述，记录错误日志，并发到系统信息中心；7.记录ETL日志，包括转换的时间，数据源是哪个，转化的数据种类，转换的源数据是哪些，对应的目标数据是哪些，等等。ETL介绍•ETL大致分为两部分设计期：ETL过程，Extract即是从业务数据库中抽取数据,Transform即是根据业务逻辑规则对数据进行加工的过程，Load即是把数据加载到数据仓库的过程(Extract-Transform-Load)，关键就在T的处理上，这个过程的实现，可以用可以用perl、shell、存储过程等来实现，也可以用类似Kettle等ETL工具实现运行期：ETL的调度过程，所谓调度，就是执行定时任务，对以上脚本、job的调度，主要是这其中的依赖如何配置的问题，还有就是对于日增、日全、月增、月全等数据如何加载。ETL工具—Kettle介绍•开源ETL产品中，Kettle(PentahoDataIntegration)反映最好，开源社区较为完善，技术支持方面也优于其它开源产品。•Kettle是一款国外开源的etl工具，纯java编写，绿色无需安装，数据抽取高效稳定。Kettle中有两种脚本文件，transformation和job，transformation完成针对数据的基础转换，job则完成整个工作流的控制。•Kettle功能非常丰富,主要包括转换、作业、资源库、日志、权限、分区、集群、性能监控等模块。Kettle转换功能全，使用简洁。作业项丰富，流程合理，java代码，支持的数据源范围广，所以，跨平台性较好。ETL工具—Kettle元数据介绍•元数据的定义，就是描述数据的数据，元数据是ETL项目组面对的一个非常重要的主题，对于整个数据仓库项目也是非常重要的一部分。在kettle的元数据存储在资源库（repository）中，资源库保存在数据库中。下面按照不同的类别介绍。ETL工具—Kettle元数据介绍ETL工具—Kettle资源库介绍•资源库用来保存连接信息和转换信息。用户通过图形界面创建的的数据库连接和数据转换任务可以保存在资源库中。资源库可以是各种常见的数据库，用户通过用户名/密码来访问资源库中的资源，默认的用户名/密码是admin/admin。资源库并不是必须的，如果没有资源库，用户还可以把转换任务保存在xml文件中。资源库可以使多用户共享转换任务，转换任务在资源库中是以文件夹形式分组管理的，用户可以自定义文件夹名称。有了资源库,就可以将transformation/job保存在数据库里,这样方便共享。ETL工具—Kettle转换作业介绍•Kettle以任务的方式存在，每个任务就是一个转换流程，以流程图的方式表现，支持各种流程处理模式，包括条件跳转、分支、循环等等，每一个节点就是业务处理单元。业务处理单元可以是SQL语句，也可以是存储过程，还可以是Java程序等等。•Kettle中有两种脚本文件，transformation和job，transformation完成针对数据的基础转换，job则完成整个工作流的控制。主要描述了从各个数据源中抽取数据、转换数据并加载到数据仓库的各个环节及流程。主要功能有数据校验、数据转换规则、数据质量检查、Bug调试错误处理，定时功能、日志跟踪等。Kettle使用及练习—安装部署•Kettle的下载可以在网站下载。要求jdk.5以上版本，设置JAVA_HOME环境变量，.kettle不需要安装,直接解压。运行spoon.bat即能看到kettle的欢迎界面，选择没有资源库，打开kettle主界面，在左边的Maintree,双击transaction，配置DBConnection，点击test,提示成功则表示DB已经配置好了。•注：1.Kettle支持跨平台使用，Spoon.bat是在windows平台运行，Spoon.sh是在Linux、AppleOSX、Solaris平台运行。2.spoon.bat文件中的setOPT=-Xmx256m改成setOPT=-Xmx512m，或者更大也可以，否则会在抽取过程中，可能会出现内存溢出的异常Kettle使用及练习—资源库创建及导入•创建资源库如果用户需要创建一个资源库，在资源库的登录窗口（PDI启动时的第一个窗口）中有【新建】按钮，点击该按钮弹出新建资源库窗口，在该窗口中选择一个数据库连接，如果没有事先定义的数据库连接，则还要点击【新建】按钮，来创建一个数据库连接。选择数据库连接后，要为该资源库命名，作为这个资源库的唯一标志，最后选择【创建或更新】按钮来创建这个资源库。•导入资源库找到【资源库】菜单，选择【探索资源库】，在【探索资源库】的界面上，点击左上角的【文件】菜单，选择【把资源库从XML文件导入】，找到转换或作业存放的路径，导入。Kettle使用及练习—Transformation•转换,点击页面左上角的文件，创建一个新的transformation，点击保存到本地路径，例如保存到D:/etltest下，保存文件名为EtltestTrans，kettle默认transformation文件保存后后缀名为ktr.•转换菜单介绍：A主对象树：MainTree菜单列出的是一个transformation中基本的属性，可以通过各个节点来查看。DB连接：显示当前transformation中的数据库连接，每一个transformation的数据库连接都需要单独配置。Steps：一个transformation中应用到的环节列表Hops：一个transformation中应用到的节点连接列表Kettle使用及练习—Transformation•转换菜单介绍：B核心对象：CoreObjects菜单列出的是transformation中可以调用的环节列表，可以通过鼠标拖动的方式对环节进行添加。Input：输入环节Output：输出环节Lookup：查询环节Transform：转化环节Joins：连接环节Scripting：脚本环Kettle使用及练习—Transformation•常用环节介绍类别环节名称功能说明Input文本文件输入从本地文本文件输入数据表输入从数据库表中输入数据获取系统信息读取系统信息输入数据Output文本文件输出将处理结果输出到文本文件表输出将处理结果输出到数据库表插入/更新根据处理结果对数据库表机型插入更新，如果数据库中不存在相关记录则插入，否则为更新。会根据查询条件中字段进行判断更新根据处理结果对数据库进行更新，若需要更新的数据在数据库表中无记录，则会报错停止删除根据处理结果对数据库记录进行删除，若需要删除的数据在数据库表中无记录，则会报错停止Lookup数据库查询根据设定的查询条件，对目标表进行查询，返回需要的结果字段流查询将目标表读取到内存，通过查询条件对内存中数据集进行查询调用DB存储过程调用数据库存储过程Transform字段选择选择需要的字段，过滤掉不要的字段，也可做数据库字段对应过滤记录根据条件对记录进行分类排序记录将数据根据某以条件，进行排序空操作无操作增加常量增加需要的常量字段ScriptingModifiedJavaScriptValue扩展功能，编写JavaScript脚本，对数据进行相应处理Mapping映射（子转换）数据映射JobSatVariables设置环境变量GetVariables获取环境变量Kettle使用及练习—Job•Job包，作为嵌套作业使用,点击页面左上角的文件，创建一个新的job，点击保存到本地路径，例如保存到D:/etltest下，保存文件名为EtltestJob，kettle默认job文件保存后后缀名为kjb•任务菜单介绍：A主对象树：MainTree菜单列出的是一个Job中基本的属性，可以通过各个节点来查看。DB连接：显示当前Job中的数据库连接，每一个Job的数据库连接都需要单独配置。Kettle使用及练习—Job•任务菜单介绍：B核心对象：Jobentries：一个Job中引用的环节列表Jobentries菜单列出的是Job中可以调用的环节列表，可以通过鼠标拖动的方式对环节进行添加。每一个环节可以通过鼠标拖动来将环节添加到主窗口中。并可通过shift+鼠标拖动，实现环节之间的连接。Kettle使用及练习—JOB•常用环节介绍类别环节名称功能说明JobentriesSTART开始DUMMY结束Transformation引用Transformation流程Job引用Job流程Shell调用Shell脚本SQL执行sql语句FTP通过FTP下载Tableexists检查目标表是否存在，返回布尔值Fileexists检查文件是否存在，返回布尔值Javascript执行JavaScript脚本Createfile创建文件Deletefile删除文件Waitforfile等待文件，文件出现后继续下一个环节FileCompare文件比较，返回布尔值Waitfor等待时间，设定一段时间，kettle流程处于等待状态Zipfile压缩文件为ZIP包Kettle使用及练习—Job实践练习•点击左侧的【核心对象树】，点击【通用】，选中【START】，点击左侧的【核心对象树】，点击【通用】，选中【Transformation】，连接两节点，按住shift键，从“开始”节点开始，拖动鼠标到“Transformation”节点后，释放鼠标。•转换文件是一个绝对路径，我们需要把它改成相对路径，为了方便移植，就需要把“file:///D:/ETLTEST”改成“${Internal.Job.Filename.Directory}”Kettle使用及练习—Job实践练习注：Job非常灵活，可以Job中调转换，也可以Job中调小Job，多个Job，Job和Transformation任意组合。如上图，Job和Transformation混合执行。但注意在项目的运用中注意JOB定制的规范性。Kettle使用及练习—Kettle定时任务•Kitchen调度功能，Kettle中的Kitchen(作业执行器)，是一个作业执行引擎，用来执行作业。用来定时执行Kettle的数据转换任务的软件模块，周期性的运行，使得各个集成模块可以实时、准确、高效的从子系统的数据库中取得需要的数据。其中的任务时间排程，可以用来定时执行由Spoon设计的数据转换任务，并