ETL培训详解ppt课件

ETL介绍,提纲,KDSP平台概述ETL架构原理ETL介绍ETL工具Kettle介绍Kettle使用及练习Kettle场景实例,KDSP金蝶决策支持平台架构图,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,常用环节介绍,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,常用环节介绍,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设计的数据转换任务，并能监控任务的执行状态和结果，以保证任务策略能够正确、稳定运行，管理员可以通过日志信息发现错误及异常。,Kettle使用及练习Kettle定时任务,Kitchen是一个作业执行引擎，是一个命令行执行工具，是用来执行作业的。,Kettle使用及练习Kettle如何设置定时任务,如何设置定时任务1.当job是存放在资源库（一般资源库都使用数据库）中时，使用Kitchen.bat执行job时，需使用如下的命令行：Kitchen/rep“资源库名”/dir“目录文件名”/job“job名”/useradmin/passadmin/level=Basiclogfile=D:ETL_LOGday_basic.logD:ETL_LOGday_run.log2.当job没有存放在资源库而存放在文件系统时，使用Kitchen.bat执行job时，需使用如下的命令行：Kitchen.bat/norep/fileuser-transfer-job.kjb在Job下的start模块，设置数据转换任务的执行时间、循环次数，可以每日，每周等方式进行定时，（例如：每隔一分钟执行一次数据转换任务进行数据同步）等参数即可3.可以使用命令行执行job后，就可以使用windows或linux的任务调度来定时执行任务了,Kettle场景实例全量抽取,基础维表抽取A.简单基础维表场景实例,Kettle场景实例全量抽取,基础维表抽取A.组织维表场景实例组织单元,Kettle场景实例全量抽取,基础维表抽取A.组织维表场景实例分析组织,Kettle场景实例全量抽取,事实表抽取A.资产负债实例,Kettle场景实例全量抽取,事实表抽取A.成本指标实例,Kettle场景实例增量抽取,增量抽取原理,Kettle场景实例增量抽取,增量更新按照数据种类的不同大概可以分成：1.只增加，不更新，2.只更新，不增加3.即增加也更新4.有删除，有增加，有更新将源库的时间戳抽取到目标表中，每次抽取数据的时候，先读取这个目标数据库表的时间戳的最大值，把这个值当作参数传给原数据库的相应表，根据这个时间戳来做限定条件来抽取数据，抽取之后同样要保留这个时间戳，同时使用Kettle的Update步骤实现新增和更新功能。,Kettle场景实例增量抽取,业务数据抽取,Kettle场景实例增量抽取,业务数据抽取凭证抽取,Kettle场景实例增量抽取,业务数据抽取凭证抽取节点说明：它冻结所有的输出，直到从上一步骤来的最后一行数据到达,最后一行数据将发送到下一步。执行SQL脚本，可在转换初始化的时候执行，或者在步骤的每一个输入行执行。SQL脚本中使用参数,Kettle场景实例增量抽取,设置变量，允许