Informatica学习以及遇到的问题总结-陈朋

1、深航Informatica学习总结 修改记录编写2016/3/9(深航infa学习中总结,学习中没有详细的文档，所以整理了一下学习的一些知识)框架：一、总体概述  ETL 包括 Extract 、 Transform 、 Cleaning 和 Load ，它是用于从生产库中提取数据到数据仓库中，再由具体的报表、统计、分析工具对数据仓库的内容进行分析，分析出自己所需要的数据。  其中 ETL 培训的内容是如何利用 Informatic PowerC

2、enter 把源数据库的内容提取存放到目标数据库中，四部分 Client ， Reposity Server ， Reposity Database 和 Informatic Server 每个部分实现自己的功能。  二、系统体系结构  此部分介绍了 Informatic ETL 工具包括的主要内容。  1.Client  Informatic Client 主要有五个部分。 

3、Client 可以和 Reposity 分离，通过 TCP/IP 连接，连接到远程的 Reposity Server 。  2. Reposity Manager  主要用于进行一个 Reposity 库的管理，当用户使用 Client 工具登录一个 Reposity 服务器之后，进行文件夹权限的创建，用户权限、密码的管理等。  3. Designer  主要是进行数据抽取的转换工具的设

4、计，主要是 mapping 的设计、设计源数据库的结构，目标数据库的结构，然后设计把源数据导入到目标数据库中，所需要进行的转换操作（ Transformation ）。  同一个 Reposity 的 folder 之间可以建立 shortcut 方式，多个 reposity 的 folder 之间只能做拷贝。  4. Workflow Manager  主要用于流程任务（ workflow

5、60;Task ）的设计。  进行任务流程的设计、每一个 Tast 针对一个 Session ，一个 session 针对一个 mapping ，其 中 workflow 中的 Folder 和 Designer 中的 folder 相对应的关系。一版来说，用户都是建立自己 的 Folder 。  5. Workflow Monitor  主要进行流程运行、

6、及任务运行时的流程运行情况跟踪。可以跟踪日志。包括 Session 日志和 Workflow 日志，可以设置生成日志的循环个数。分析 session 运行是对源数据库的数据抽出信息和对目标数据库的更新信息。  6. Reposity Server Application Console  主要进行 Reposity 的管理，包括 Reposity Server 的 start ， shutdown

7、 操作。进行 Reposity 库的 backup 、 restore 等操作，进行 Reposity 库级别的管理，级别较高。  7. Reposity Server  Reposity server 是为客户端服务的，客户端可以和各种 client 不在一台服务器上。关于数据抽取的设计成果转换成 为 XML 格式的源数据，都是通过 Reposity Server 存放到&#

8、160;Reposity Database Server 上的。8. Reposity Database Server  用于存放的是进行 ETL 设计的元数据。可以支持各类的数据库。方式为数据库中用户的一个表目录和用户关系即可。  Reposity Database 可以和 Reposity server 不安装在一台服务器。如果在一台机器上， server 通 过 native 方式连接到 d

9、atabase ，如果不在一台及其上，需要在 database 上安装一个 reposity agent ，用户 通过 agent ，以 native 方式连接到 reposity 数据库，然后 reposity agent 再以 tcp/ip 方式连接 到 reposity server 。  9.Informatic Server  Informatic 

10、;server 是实际执行数据抽取任务的运行环境。即 workflows 、 task 、 sessions 等。它是根据定义的 workflow 元数据库，然后在自己的实际环境中，执行数据抽取操作。  三、系统组件功能  Informatic 的功能主要体现在 Designer 和 Workflow Manager 上，其中 Designer 实现的是 对数据抽取的数据转换方式设计，以及效率设计目标等。而

11、在 Workflow Manager 中再把具体的数据转换方式应用到一个具体的工作任务中，包 括目标库、源库的选择，以及一条具体的执行任务的属性设置等等。包括任务 的 insert 、 truncate 、 delete 、 increment insert 等。  1.Designer 组件  （ 1 ）工作区  在 Designer 中，涉及到的工作区主要包括 有 Source

12、60;Analyzer 、 Warehouse Designer 、 Transformation Designer 、 Mapplet Designer 和 Mapping Designer 。  其中每个工作区的功能分别介绍：  Source Analyzer ：  Source Analyzer 的功能是实现对源数据库表的设计，可以手工的进行源数据库的设计，一般都是通过从 Flat

13、 File 或者 Relation DBMS 中导入数据库的表结构。 XLS 文件结构。  Warehouse Designer  用于设计目标数据库库的结构，可以利用手工设计，也可以利用 import 工具导入导一个 warehouse 的结构。  Transformation Designer  用户设计可重用的 transformation 组件，这里的组件，在一个 folder 

14、里面都是可以重用的，而且 以 shortcut 的方式使用。 Transformation 组件的修改，会直接反映到使用 此 transformation 的 mapping 中。  Mpplet  设计可以重用的多个组件，只要有 output 组件，可以有 input 组件，也可以没有 input 组件，实现的功能就和一个 expression 的功能类似，实现功能。   Mapping

15、0;Designer  实现的功能是设计具体进行抽取数据的 mapping ，这些 mapping 应用到一个 workflow 中，形成了 workflow 的一个 session （ task ）。  （ 2 ） 组件功能  ETL 支持的组件主要包括两类： Active 组件和 Passive 组件，其中 Active 组件是对输入记录集在输

16、出时个数有变化的组件， Passive 组件，输入的记录集，输出时记录集的个数不发生改变。  Informatic 提供的组件包括：  (1) Source Qualifier: reads data from flat file & relational sources  (2) Expression: performs row-level calculations  (3)&

17、#160;Filter: drops rows conditionally  (4) Sorter: sorts data  (5) Aggregator: performs aggregate calculations  (6) Joiner: joins heterogeneous sources  (7) Lookup: looks up values and&

18、#160;passes them to other objects  (8) Update Strategy: tags rows for insert, update, delete, reject  (9) Router: splits rows conditionally  (10) Sequence Generator: generates uniq

19、ue ID values  (11) Normalizer:izes records from relational or VSAM sources  (12) Rank: filters the top or bottom range of records  (13) Union : merges data from mult

20、iple pipelines into one pipeline  (14) Transaction Control: allows user-defined commits  (15) Stored Procedure: calls a database stored procedure  (16) External Procedure : calls c

21、ompiled code for each row  (17) Custom: calls compiled code for multiple rows  (18) Midstream XML Parser: reads XML from database table or message queue  (19) Midstream&#

22、160;XML Generator: writes XML to database table or message queue  每一类组件都有自己独特的功能和特点，每一种组件的详细功能这里不详细介绍。  2.Workflow 组件  Workflow 是对执行任务的执行进行控制，可以在执行时对执行的任务进行传参数进行传入，这样就能在执行的时候把数据库表进行动态的设置。  可以在执行的时候选择参数文件，对 mapping 、

23、 workflow 中的 Session 的参数进行传入。达到动态执行的目的。  Workflow 中定义 workflow 、 session 等任务，对这些任务的执行进行管理控制。  四、补充说明  Source Qualifier 支持同构数据源的连接，衣钩数据源的连接通过 N-1 个 jointer 组件实现。 Lookup 为 passive 组件，查出的多条记录集，要么取&#

24、160;first ，或者 last ，或者报错。 Lookup 可以时动态 / 静态 ，可以 connected/unconnect 。1. infa开发准备1. 安装客户端2. 准备工作创建表：建表语句create table SOURCE_EMPLOYEES( EMPLOYEE_ID NUMBER(6), EMPLOYEE_NAME VARCHAR2(50), EMPLOYEE_ADDRESS VARCHAR2(50), EMPLOYEE_CITY VARCHAR2(15), CREATE_DATE

25、 DATE);目标表create table TARGET_EMPLOYEES_XX( EMPLOYEE_ID NUMBER(6), EMPLOYEE_NAME VARCHAR2(50), EMPLOYEE_ADDRESS VARCHAR2(50), EMPLOYEE_CITY VARCHAR2(15), CREATE_DATE DATE);3. 配置域域名Domain_infa9网关主机41网关端口60052.Repository创建文件夹1连接数据库目的：连接存储库2创建文件夹点击工具栏的Folder Create,输入需要创建的文件夹名称点击确定完成创建如果文件夹没有

26、显示，断开重新连接一下看看3.Designer中创建映射1. 切换到designer窗口目的：切换到映射设计界面操作：点击D图标即可Designer 界面被打开，默认打开的是新建的XXX文件夹。2导入源表目的：从数据库中导入源表表结构操作：点击源图标切换至源窗口菜单：源->从数据库导入选择图标添加数据源ODBC连接选择配置导入数据,可以在Search窗口搜寻要查询的表名Properties属性：3.导入目标表目的：从数据库中导入目标表DIM_EMPLOYEES的表结构操作：点击切换到目标设计窗口菜单： 和导入源表步骤相同，选择目标表导入4.建立映射关系目的：设置从源表到目标表的转换规则1

27、 新建映射点击图标切换到mapping窗口工具栏出现mappings选项点击新建2 为映射添加源表从左侧源中把表SOURCE_EMPLOYEES拖入右侧编辑区,(会自动出现SQ_SOURCE_EMPLOYEES的SQL转换)3为映射添加目标表从左侧目标中把表DIM_EMPLOYEES拖入右侧编辑区4编辑SQ_SOURCE_EMPLOYEE可在Properties项中编辑sql语句可在ports项中点击新增字段，点击删除字段4在Workflow Designer中新建工作流1切换到Workflow Designer窗口目的：切换到工作流设计界面操作：点击W图标即可2新建session目的：创建会

28、话，用以调用映射操作：点击 Task Developer 按钮，切换到任务编辑窗口菜单“Tasks”->“Create” 输入名字创建选择刚刚做好的mapping点击ok3编辑session双击新建的session选择Mapping选项选择target文件夹下的目标表选择Properties下的truncate勾选,表示在运行时清空目标表注意SOURCES文件夹下的源表以及TARGETS文件夹下的目标表的Connections连接的数据库是否正确4新建工作流目的：新建工作流操作：点击图标 WorkFlow Designer 切换到工作流设计窗口菜单：“WorkFlows” ->“C

29、reate”输入名字 确定建立在界面会出现图标将 建立session 拖入工作流设计窗口中点击链接任务，鼠标状态改变点击启动，再点击 session连接起来5启动工作流目的：执行工作流操作：在工作流或者workflow designer窗口中右键启动工作流 5.lookup组件（增量ID）1.表源表SOURCE_EMPLOYEE目标表create table TARGET_EMPLOYEES_02_XX( EMPLOYEE_ID NUMBER(6), EMPLOYEE_NAME VARCHAR2(50), EMPLOYEE_ADDRESS VARCHAR2(50), EMPLOYEE_CITY

30、 VARCHAR2(15), CREATE_DATE DATE, BI_UPDATE_DATE DATE);导入源表，目标表，建立mapping参考上面2.Lookup组件在mapping designer中点击lookup组件图标创建一个lookup，选择target目标表可删除不必要的字段将源表的id字段拖入到lookup框中，改名为IN_EMPLOYEE_IDLOOKUP属性Condition点击新建选择employee_id=in_employee_id建立连接关系添加F(x) 组件，点击出现+选择mapping区域的空白处点击出现将lookup组件中的employee_id拖入F(x

31、)组件中双击F(x)组件添加俩字段BI_UPDATE_DATE是设置更新时间为当前时间UPDATE_FLAG是设置标志Exprossion中语句为IIF(ISNULL(EMPLOYEE_ID),I,U)添加一个Update组件，进行insert和update操作将源表的数据以及F(X)中的BI_UPDATE_DATE以及UPDATE_FLAG拖入Update组件中双击update组件点开设置Properties中属性Update Strategy Expressio设置内容为IIF(UPDATE_FLAG='I',DD_INSERT,IIF(UPDATE_FLAG='U

32、',DD_UPDATE,DD_REJECT)如果UPDATE_FLAG标志为I进行INSERT操作，如果为U,进行INSERT操作将update组件中的字段与目标表的对应字段连接起来3.快捷创建工作流选择做好的MAPPING右键点击Generate Workflow选择Workflows with a reusable session同时创建session注意后面的命名规则完成工作流的创建，运行。6.Filter Transformation 1.表源表：SOURCE_EMPLOYEE目标表：目标表create table TARGET_EMPLOYEES_03_XX( EMPLOYE

33、E_ID NUMBER(6), EMPLOYEE_NAME VARCHAR2(50), EMPLOYEE_ADDRESS VARCHAR2(50), EMPLOYEE_CITY VARCHAR2(15), CREATE_DATE DATE, BI_UPDATE_DATE DATE);2.创建Filter Transformation导入源表，目标表，创建mapping参考上创建Filter Transformation点击图标创建 Filter Transformation区域将源表的字段拖入Filter中双击filter进入属性编辑Properties > Filter Conditi

34、on编辑过滤条件如我们要的数据为2016年3月8号之后的数据IIF(CREATE_DATE>TO_DATE('2016/3/8','YYYY/MM/DD'),TRUE,FALSE)添加F(X)写一个修改时间为当前时间传入目标表2. 创建工作流运行参上SQL QUERY自定义（客户化）了Integration Service用于从来源读取数据的查询语句，替代了默认查询。自定义的查询语句覆盖custom join和source filter。USER-DEFINED JOIN指定用于连接多个来源数据的连接条件，Where子句的内容SOURSE FILTER指定

35、读取来源数据是的筛选条件7.作业：1. 员工表SZAIR_EMPLOYEES，部门表 SZAIR_DEPARTMENT2. 只取在职状况为Y的数据3. 身份证号长度异常的数据，将身份证号置为 'Default-异常'4. 将性别中 M 转化为 男 ，W 转化为 女5. 获取2014年后的数据6. 目标表命名为 W_SZAIR_EMPLOYEES_D_XXXX 其中XXXX为你们的姓名拼音，目标表字段包括EMPLOYEE_IDEMPLOYEE_NAMEEMPLOYEE_IDCARDSEXEMPLOYEE_ROLEEMPLOYEE_LEVELDEPARTMENT_NAMEDEPAR

36、TMENT_CITYHIRE_DATE7.多源数据操作1.简单操作 有两个源表，如作业，有employee表和department表，目标表数据来源这两个源表简单步骤：1.将employee源拖入mapping中,打开SQ2.在SQ的Properties属性中打开Sql Query在里面编辑sql语句，sql语句连接employee表和department表查询出目标表所需要的这两个表的字段。3. 在Ports中新增或删除字段，使得其字段与sql相对应，顺序要一致 4. 其他步骤相同2. 标准操作原sql语句：SELECT ject_num, ject_name,

37、pip.parent_project_id, (SELECT ject_name FROM sza_pa_invest_projects_all pip2 WHERE pip2.invest_project_id = pip.parent_project_id AND pip.parent_project_type = 'INVEST_PROJECT' UNION ALL SELECT ppa.NAME FROM pa_projects_all ppa WHERE ject_id = pip.parent_project_id AND pip.pa

38、rent_project_type = 'PA_PROJECT') parent_project_name, (SELECT flv.meaning FROM fnd_lookup_values_vl flv WHERE flv.lookup_type = 'SZA_PA_PROJECT_STATUS' AND flv.lookup_code = pip.status_code) status_desc, pip.status_code, pip.request_date, pip.creation_date, NULL effect_date, pip.own

39、ing_dept_code, pip.manage_dept_code, pip.resp_dept_code, sza_pa_invest_proj_pub.get_manage_dept_desc(pip.manage_dept_code) manage_dept, sza_pa_invest_proj_pub.get_owning_dept_desc(pip.owning_dept_code) owning_dept, NULL request_dtp, sza_pa_invest_proj_pub.get_invest_proj_bgt_amt(pip.invest_project_i

40、d, 'ALL', 'N', ibv.invest_budget_ver_id) total_budget_amt, PIP.INVEST_PROJECT_ID, PIP.PA_PROJECT_ID, PIP.INVEST_CLASS_CODE, PIP.ATTRIBUTE_CATEGORY, pip.attribute1, PIP.ATTRIBUTE2, PIP.ATTRIBUTE3, PIP.ATTRIBUTE4, PIP.ATTRIBUTE5, PIP.ATTRIBUTE6, PIP.ATTRIBUTE7, PIP.ATTRIBUTE8, PIP.ATTR

41、IBUTE9, PIP.ATTRIBUTE10, PIP.ATTRIBUTE11, PIP.ATTRIBUTE12, PIP.ATTRIBUTE13, PIP.ATTRIBUTE14, PIP.ATTRIBUTE15 FROM sza_pa_invest_projects_all pip, sza_pa_invest_bgt_versions ibv WHERE pip.invest_project_id = ibv.invest_project_id(+) AND ibv.version_type(+) = 'INVEST'我们在此不使用sql语句，使用infa的组件来完成S

42、tored Procedure Transformation Joiner Transformation 效果图：1.用法· 来连接两个源表的，只能连接两个，个数就为源表个数减1将两个源表拖入mapping中使用进行连接 将连接字段拖入连接，将两个源表所需的字段拖入2.用法对应sql中的：sza_pa_invest_proj_pub.get_manage_dept_desc(pip.manage_dept_code) manage_dept, sza_pa_invest_proj_pub.get_owning_dept_desc(pip.owning_dept_code) ownin

43、g_dept,sza_pa_invest_proj_pub.get_invest_proj_bgt_amt(pip.invest_project_id, 'ALL', 'N', ibv.invest_budget_ver_id) total_budget_amt,新建P(X)点击确定传入参数，返回return_value8.参数和变量1概述参数和变量可以定义在工作流、工作集或会话中。您可以使用 WordPad 或 Notepad 等文本编辑器来创建参数文件。您在参数文件中列出参数或变量及其值。参数文件可以包含以下类型的参数和变量：u Integration Se

44、rvice变量u Integration Service process变量u Workflow变量u Worklet变量u Session参数u Mapping参数和变量2参数和变量的设置1定义参数文件Mapping Parameters(参数) 和 Variables（变量）的使用 如果你在一个mapping中多次用到同一个值，又或者mapping中要用到一个在session运行才能决定的值，这时候可以使用mapping parameters或variable。Mapping参数变量的命名方式：$NAME存放参数变量的.txt文件的格式：folder_name.session_name parameter_name=value variable_name=value mapplet_name.parameter_name=value folder_name.session2_name parameter_name=value variable_name=value mapplet_name.variable_name=value folder2_n