数据仓库化实施手册

1、文件：标准数据仓库标准化实施手册起草 部门：数据分析事业部管理 部门：数据分析事业部起草人：修订人：审核人：、汤宇航批准 人：发 布 日 期：2015 年 04 月 15 日修订页章节名称修订内容简述修订日期修订前版本号修订后版本号修订人批准人1全篇创建2015-04-15V1.0目录前言. -6-1. 硬件资源确认及环境检查. -6-1.1确认服务器硬件资源. -6-1.2检查数据库服务器环境. -8-2. 数据仓库部署. -11-2.1创建表空间及用户. -11-2.2数据仓库安装. -14-3. 数据抽取部署及运维. -14-3.1标准化 ETL 工具部署. -14-3.1.1.首次部署

2、. -14-3.1.2.部署验证. -15-3.2标准化 ETL 工具运行. -16-3.2.1.对象初始化（默认索引、数据准备区、分区调整） -16-3.2.2.作业流运行（多个作业）. -18-3.2.3.作业运行（单个作业）. -19-3.2.4.配置初始化或增量流程. -20-3.3抽取日常 -21-3.3.1.数据抽取调度（多个作业）：. -21-3.3.2.数据抽取调度（单个作业）：. -22-3.3.3.数据抽取运行（直接运行抽取过程）：. -22-3.4标准化 ETL 工具配置（需要时执行配置）. -22-3.4.1.作业配置. -23-3.4.2.作业流配置. -27-3.5

3、统计信息收集. -28-4. 部署后验证. -29-前言为实现快速实施部署，提高工作效率，降低实施成实施流程，特制订本手册1. 硬件资源确认及环境检查现场实施在进行硬件资源确认及环境检查工作前，先整备好基础环境准备情况检查表，表单内容可根据实际情况进行调整，及时将各项结果和存在的问题到表单中，并相关处理。1.1 确认服务器硬件资源1、 验证服务器连通性根据甲方提供的硬件资源列表，使用 SSH 连接方式，确认所提供的服务器是否可正常连通。2、 检查服务器主机名：主机名3、 检查服务器CPU：#cat /prouinfo prosor : 0vendor_id: Genuineel cpu fam

4、ily: 6m42mname:el(R) Xeon(R) CPU E5-2660 0 2.20GHz step7#hostnameahdsj2app014、 检查服务器内存：5、 检查服务器磁盘:说明：根据甲方提供的硬件资源列表，按以上方法检查各服务器的主机名、CPU、内存、硬盘容量是否符合前期规划的硬件配置，并到基础环境检查表中。#df k文件系统1K-块已用可用 已用% 挂载点/dev/sda3163112085371248 10111392 35% /tmpfs76677607667760% /dev/shm/dev/sda1162353 14% /boot/dev/sdb1524288

5、000 10742216 513545784 2% /weblogic#free mtotalusedfreesharedbufferscached Mem:65523-/+ buffers/cache:2371259Swap:409504095cpu MHz : 2693.880cache size: 3072 KB1.2 检查数据库服务器环境1、 检查数据库实例名及状态：此处为实例名此处为实例状说明：确认是否符合数据库参数配置规范(只需检查一个节点)，两行 Open 说明两个RAC 节点实例都是打开状态。如果只有一个或没有 Open 行说明数据库未处于打开状态。2、 检查数据库字符集：说明

6、：检查确认字符集是否与征管系统的数据库字符集一致 (只需检查一个节点)。3、 检查oracle 用户环境变量：#su - oracle#su - oracle$sqlplus / as sysdbaSQL select userenv(language) from dual; USERENV(LANGUAGE)AMERICAN_AMERICA.ZHS16GBK#su - grid$crsctl sus res -t | grep -E .db|Open ora.ahdszcfx.dbONLINEONLINEhcu19-vp02OpenONLINEONLINEhcu20-vp02Open说明：主

7、要检查 ORACLE_SID 和NLS_LANG 设置是否正确(两个节点都要检查)，如果执行如上两条命令时返回空行，则说明该环境变量未设置，请在oracle 用户的.profile（linux 系统为.bash_profile）文件中设置。4、 检查ASM 磁盘组设置：说明：主要检查是否符合前期规划的数据库参数配置要求(只需检查一个节点)；NAME：ASM 磁盘组名称；TOTAL_MB：磁盘组空间总量(MB)； FREE_MB：磁盘组剩余空间(MB)。#su - grid$sqlplus / as sysdbaSQL select name,total_mb,free_mb from v$as

8、m_diskgroup; NAMETOTAL_MBFREE_MBOCR_VOTE1536014338FRA542720487084DATA43417604225240$echo $ORACLE_SID ahdszcfx1$echo $NLS_LANGAMERICAN_AMERICA.ZHS16GBK5、 检查数据库系统全局区(SGA)和程序全局区(PGA)：说明：确认 SGA、PGA 大小是否符合数据库参数配置规范(两个节点都要检查)。6、 检查数据库连接数设置：说明：#su - oracle$sqlplus / as sysdbaSQL select value from v$paramet

9、er where name = proses; VALUE1000#su - oracle$sqlplus / as sysdba SQL sho parameter gaPGA 大小，要求为物理内存的NAMETYPEVALUE0.14 倍。lock_sgaFALSEpga_aggregate_bigeger8413M pre_page_sgaFALSEsga_max_sizebigeger25280MSGA 大小，要求为物理内存的sga_bigeger25280M0.56 倍。要求至少 1000(两个节点都要检查)。2. 数据仓库部署2.1 创建表空间及用户1、 创建表空间：创建以下两个业务

10、表空间：TS_DAT_SJZBQ、TS_DAT_SJCK创建以下临时表空间：TEMP_SJCK2、 创建用户：创建sjzbq 用户：- Create the usercreate user SJZBQidentified by sjzbqdefault tablespace TS_DAT_SJZBQtemporary tablespace TEMP_SJCKprofile DEFAULT;- Grant/Revoke role privilegesgrant connect to SJZBQ;grant dba to SJZBQ;grant resource to SJZBQ;- Grant/

11、Revoke system privilegesgrant alter any index to SJZBQ;grant alter any materialized view to SJZBQ;grant alter any table to SJZBQ;grant alter sesto SJZBQ;grant create any index to SJZBQ;grant create any materialized view to SJZBQ;grant create any procedure to SJZBQ;grant create any table to SJZBQ;gra

12、nt create database link to SJZBQ;grant create job to SJZBQ;grant create view to SJZBQ;grant delete any table to SJZBQ;grant drop any index to SJZBQ;grant drop any table to SJZBQ;grant execute any operator to SJZBQ;grant execute any procedure to SJZBQ;grant execute any program to SJZBQ;grant insert a

13、ny table to SJZBQ;grant select any sequence to SJZBQ;grant select any table to SJZBQ;grant unlimited tablespace to SJZBQ;grant update any table to SJZBQ;创建sjck 用户：- Create the usercreate user SJCKidentified by sjckdefault tablespace TS_DAT_SJCKtemporary tablespace TEMP_SJCKprofile DEFAULT;- Grant/Re

14、voke role privilegesgrant connect to SJCK;grant dba to SJCK;grant resource to SJCK;- Grant/Revoke system privilegesgrant alter any materialized view to SJCK;grant alter any table to SJCK;grant alter sesto SJCK;grant create any materialized view to SJCK;grant create any procedure to SJCK;grant create

15、 any table to SJCK;grant create database link to SJCK;grant create job to SJCK;grant create view to SJCK;grant delete any table to SJCK;grant drop any table to SJCK;grant execute any procedure to SJCK;grant execute any program to SJCK;grant insert any table to SJCK;grant select any sequence to SJCK;

16、grant select any table to SJCK;grant unlimited tablespace to SJCK;grant update any table to SJCK;2.2 数据仓库安装按照标准数据仓库安装包说明进行即可3. 数据抽取部署及运维3.1 标准化 ETL 工具部署3.1.1. 首次部署标准化ETL 工具版本步骤：从产品实施组（数据团队）出库，在 PL/SQL 工具中部署，部署的1、运行创建用户.sql2、运行创建对象.sql3、运行创建数据.Sql4、导入配置库文件ETL_REP.pde（Tools-Improt Tables-PL/SQL Develo

17、per）3.1.2. 部署验证1、确保部署的对象（过程、函数、包）都处于编译成功状态。2、标准化ETL 工具部署以后，确保下列表中的对象都存在。对象类型对象名对象说明包PKG_CTL_LOG日志功能包PKG_CTL_TOOL工具功能包PKG_ETL_QJ期间维度功能包PKG_ETL_S调度功能包PKG_ETL_TAB标准ETL 功能包序列SEQ_CTL_BATCH批号号SEQ_CTL_LOG日志IDSEQ_CTL_STEP步骤ID表T_CTL_FLOW_DEP作业依赖关系表T_CTL_FLOW_INFO作业流信息表T_CTL_FLOW_TAB工作流表T_CTL_INDEX_LIST索引生成T_

18、CTL_JOB_INFO作业信息表T_CTL_LOG_DEP作业依赖日志表3.2 标准化 ETL 工具运行3.2.1. 对象初始化（默认索引、数据准备区、分区调整）1、数据准备区初始化：首先将需要从源系统抽取的表、用户名、服务器名，配置到 T_CTL_SJZBQ_LIST 表，然后运行过程PKG_CTL_TOOL.P_CTL_SJZBQ_TABLE，自动在建立数据准备区建立对应的表及描述、主键、索引、审计字段。例子：T_CTL_LOG_FLOW作业流日志表T_CTL_LOG_S作业状态日志表T_CTL_LOG_STEP作业步骤日志表T_CTL_PERIOD周期信息表T_CTL_PROS系统信息

19、表T_CTL_SERVER服务器信息表T_ETL_COL_MAP字段信息表T_ETL_TAB_CONF表配置信息T_ETL_TAB_MAP表信息视图V_CTL_LOG_FLOW作业流日志视图V_CTL_LOG_DEP依赖日志视图V_CTL_LOG_S状态日志视图V_CTL_LOG_STEP步骤日志视图(1)、配置 T_CTL_SJZBQ_LIST(2)、运行过程在数据准备区中建立相应的表：beginpkg_ctl_tool.p_ctl_sjzbq_table;end;2、数据仓库默认索引初始化：T_CTL_INDEX_LIST 包含数据仓库中默认的索引，运行 PKG_CTL_TOOL.P_CT

20、L_DEFAULT_INDEX，自动在数据仓库生成表中的索引。命令如下：beginpkg_ctl_tool.p_ctl_default_index;end;3、数据仓库默认分区初始化：T_CTL_PART_LIST 包含数据仓库中默认的分区表，运行PKG_CTL_TOOL.P_CTL_DEFAULT_PART，自动在调整数据仓库的表为分区表。命令如下：beginpkg_ctl_tool.p_ctl_default_part;end;SOURCE_OWNERSOURCE_TABSOURCE_SERVERCREATE_FLAGENABLE_FLAGHX_DJDJ_NSRXXG3HX013.2.2.

21、 作业流运行（多个作业）作业流为多个作业的有机集合，作业之间有依赖关系，运行的方式有两种方式：正常运行、断点续跑。正常运行：PKG_ETL_TAB.P_ETL_S_FLOW（作业流调度外壳），输入作业流 ID，运行即可；作业流调度外壳会作业流的并发数量，以及作业流中的依赖关系，依次运行，如果无作业可执行或者作业执行完毕，作业流调度外壳回结束运行。例如：运行纳税信用等级的作业流，在T_CTL_FLOW_INFO 表找到纳税信用等级对应的flow_id（ETL_NSXYDJ），命令如下：beginpkg_etl_s.p_etl_s_flow(ETL_NSXYDJ,null,null);end;断点

22、续跑：PKG_ETL_TAB.P_ETL_S_FLOW（作业流调度外壳），输入作业流 ID，批次类型为 2，批次类型为指定存在的批次号，运行；作业流调度外壳回作业流的并发数量，作业流依赖日志的依赖关系（对应批次号的历史依赖），如果执行成功的作业会跳过执行，只执行历史中未运行及失败的作业。如果无作业可执行或者作业执行完毕，作业流调度外壳回结束运行。例如：运行纳税信用等级的作业流，在T_CTL_FLOW_INFO 表找到纳税信用等级对应的flow_id（ETL_NSXYDJ），在 T_CTL_LOG_FLOW 表中找到需要续跑的BATCH_ID（9867），命令如下：beginpkg_etl_s.

23、p_etl_s_flow(ETL_NSXYDJ,2,9867);end;3.2.3. 作业运行（单个作业）当出现异常问题需要单独调试时，单独执行作业运行有三种方式：作业调度外壳调用作业、标准过程、标准化 ETL过程直接运行作业调度外壳PKG_ETL_S.P_ETL_S_CALL 直接调用作业，输入 i_job_name =作业名，运行。批次号可以默认为空，但是不能重复历史存在。作业调度外壳会有两个返回值，o_succ_flag 为作业的成功标志，1 为成功，0 为失败。o_s_flag 为作业调度外壳的成功标志，1 为成功，0 为失败。PKG_ETL_S.P_ETL_S_CALL 将会直接T_

24、CTL_JOB_INFO（作业信息表）的配置信息，如全量标志、增量时间，结束后根据作业状态进行翻牌操作。先确定需要单独调试的作业名（P_SJCQ_TEST_6），运行下面：declare o_succ_flag varchar(1); o_s_flag varchar(1); I_BATCH_ID NUMBER(10);begin I_BATCH_ID := nvl(I_BATCH_ID,SEQ_CTL_BATCH.NEXTVAL); pkg_etl_s.p_etl_s_call(P_SJCQ_TEST_6,I_BATCH_ID,o_succ_flag,o_s_flag);end;标准过程可以

25、直接运行，但是需要输入增量时间、全量标志，数据开发规约定，全量标志为是，默认忽略增量时间，否则使用增量时间。在PL/SQL 中找到对应的过程，TEST，输入参数，点击运行标准化ETL过程，首先确保 T_CTL_JOB_INFO（作业信息表）中的 JOB_TYPE 为标准化ETL，再从 T_ETL_TAB_CONF 中找到对应，如果 PROC_TYPE 为时间戳处理，使用 PKG_ETL_TAB包中的P_ETL_TAB_TIME；如果 PROC_TYPE 为视图日志处理，使用PKG_ETL_TAB 包中P_ETL_TAB_VIEW；如果 PROC_TYPE 为刷新物化视图，使用 PKG_ETL_

26、TAB 包中的存的储P_ETL_TAB_REFRESH； 使用参数的规则同标准过程。先在T_ETL_TAB_CONF 找到目标表对应JOB_NAME，在从 PKG_ETL_TAB 包中找到对应的存储过程，TEST，输入参数，点击运行。3.2.4. 配置初始化或增量流程作业流外壳调度、作业外壳调度是使用到T_CTL_JOB_INFO（作业信息表）的配置信息，读取ALL_FLAG、INC_BEGIN_DATE、INC_END_DATE，数据开发规约定如果ALL_FLAG 为全量，将忽略INC_BEGIN_DATE、INC_END_DATE，否则使用 INC_BEGIN_DATE、INC_END_D

27、ATE 作为增量范围。初始化流程如下：通过下列 SQL 找出相关的作业，或者直接通过 T_CTL_JOB_INFO 找出相关的作业，将T_CTL_JOB_INFO 的字段ALL_FLAG 改为 1（此流程一般初始化才用到），运行作业流（见作业流运行章节）。select * from T_CTL_JOB_INFO t where t.job_name in(select a.job_name from T_CTL_FLOW_DEP a where a.flow_id = ETL_NSXYDJ)- ETL_NSXYDJ为纳税信用等级的作业流ID增量流程如下通过下列 SQL 找出相关的作业，或者直接

28、通过 T_CTL_JOB_INFO 找出相关的作业，确认T_CTL_JOB_INFO 的字段ALL_FLAG 是否为 0，运行作业流（见作业流运行章节）select * from T_CTL_JOB_INFO t where t.job_name in(select a.job_name from T_CTL_FLOW_DEP a where a.flow_id = ETL_NSXYDJ)- ETL_NSXYDJ为纳税信用等级的作业流ID标准过程、标准化ETL过程（PKG_ETL_TAB），手动传入参数运行。数据开发规约定如果I_ALL_FLAG 为全量，将忽略 I_BEGIN_TIME、I_

29、END_DATE，否则使用 I_BEGIN_TIME、I_END_DATE 作为增量范围。见作业流运行（多个作业）3.3 抽取日常3.3.1. 数据抽取调度（多个作业）：通过 PKG_ETL_S.P_ETL_S_FLOW 调度作业流运行，需要查看下列日志（以下 3 个日志都成功才算成功）：查看作业流日志视图V_CTL_LOG_FLOW，按表中的批次号倒序查询，最新的日志最近一次作业流运行的统计信息，flow_s最终的状态，1 为成功，0 为失败。us查看作业状态日志表T_CTL_LOG_S，按表中的日志号倒序查询，最新作业流中所有的作业运行状态、运行时间、参数。JOB 状态（0 为失败、1 为

30、成功、2 为运行、3 未启用、4 未运行、5 为超时）。查看作业步骤日志表T_CTL_LOG_STEP，按表中的步骤号倒序查询，最新作业运行的详细步骤，RUN_CODE、RUN_ERRM了失败信息。3.3.2. 数据抽取调度（单个作业）：通过 PKG_ETL_S.P_ETL_S_CALL 调度作业运行，需要查看下列日志：查看作业状态日志表T_CTL_LOG_S，按表中的日志号倒序查询，作业运行状态、运行时间、参数。JOB 状态（0 为失败、1 为成功、2 为运行、3 未启用、4 未运行、5 为超时）。查看作业步骤日志表T_CTL_LOG_STEP，按表中的步骤号倒序查询，作业运行的详细步骤，R

31、UN_CODE、RUN_ERRM了失败信息。3.3.3. 数据抽取运行（直接运行抽取过程）：直接运行标准过程、标准化 ETL过程（PKG_ETL_TAB），需要查看下列日志：查看作业步骤日志表T_CTL_LOG_STEP，按表中的步骤号倒序查询，作业运行的详细步骤，RUN_CODE、RUN_ERRM了失败信息。3.4 标准化 ETL 工具配置（需要时执行配置）如果有新增抽取时，才执行以下步骤：先配置作业信息（作业信息表或者标准化ETL 相关三张表）配置作业流信息3.配置作业之间的依赖关系 作业配置标准过程：标准过程编译成功后，只需要配置一张表（T_CTL_JOB_INFO），填写

32、说明如下：T_CTL_JOB_INFO（作业信息表）字段名字段填写说明job_id填写：号job_name填写：过程名job_type填写：1（1过程、2 标准化 etl、3 数据补差异）all_flag增量标志（0 为增量、1 为全量），如果为 1，将忽略 inc_begin_date、inc_end_dateperiod_id周期类型（1 为小时、2 为天、3 为周、4 为月、5 为季、6 为半年、7 为年、0 为最新时间），跟 inc_begin_date、 inc_end_date 的翻牌有直接关系his_date历史初始日期（使用默认值）back_offset向历史偏移周期（周期类型

33、为最新时间时，偏移为分钟，其他情况时跟周期类型一致）front_offset向未来偏移周期（周期类型为最新时间时，偏移为分钟，其他情况时跟周期类型一致）fail_loop_num使用默认值fail_exec_e使用默认值inc_begin_date使用默认值inc_end_date使用默认值标准 ETL 配置标准化 ETL 配置，通过配置源表和目标表的对应关系、源字段和目标字段的对应关系，由PKG_ETL_TAB 标准化 ETL 功能包实现数据抽取功能，需要配置四张表（T_CTL_JOB_INFO、T_ETL_TAB_CONF、T_ETL_TAB_MAP、T_ETL_COL_MA

34、P）。T_CTL_JOB_INFO，作业的基本信息，填写说明如下：T_CTL_JOB_INFO（作业信息表）字段名字段填写说明job_id填写：号job_name填写：处理环节 + 目标表名job_type填写：2（1过程、2 标准化 etl、3 数据补差异）all_flag增量标志（0 为增量、1 为全量），如果为 1，将忽略 inc_begin_date、inc_end_dateperiod_id周期类型（1 为小时、2 为天、3 为周、4 为月、5 为季、6 为半年、7 为年、0 为最新时间），跟 inc_begin_date、 inc_end_date 的翻牌有直接关系his_date

35、历史初始日期（使用默认值）back_offset向历史偏移周期（周期类型为最新时间时，偏移为分钟，PROS_ID处理环标表所属层级：具体代码见T_CTL_PROS）SERVER_ID所属服务器（源表所属服务器：具体代码见 T_CTL_SERVER），数据准备区的数据归属到来源系统enable_flag启用标记（1 为使用、0 为禁用）T_ETL_TAB_CONF，作业对应目标表的基本信息，填写说明如下：T_ETL_TAB_CONF（表配置信息）字段名字段填写说明TAB_ID表ID（跟 T_CTL_JOB_INFO 的 JOB_ID 保持一致）TAB_BATCH表批次（默认 1，如果来源不同的源

36、表，依次累加批次号）JOB_NAME作业名（跟 T_CTL_JOB_INFO 的 JOB_NAME 保持一致）PROC_TYPE处理方式（1 为时间戳处理、2 为 DSG 处理、3 为视图日志处理、4 为自定义 insert、5 刷新物化视图）_SERVER目标服务器其他情况时跟周期类型一致）front_offset向未来偏移周期（周期类型为最新时间时，偏移为分钟，其他情况时跟周期类型一致）fail_loop_num使用默认值fail_exec_e使用默认值inc_begin_date使用默认值inc_end_date使用默认值PROS_ID处理环标表所属层级：具体代码见 T_CTL_PROS

37、）SERVER_ID所属服务器（源表所属服务器：具体代码见 T_CTL_SERVER），数据准备区的数据归属到来源系统enable_flag启用标记（1 为使用、0 为禁用）T_ETL_TAB_MAP，作业对应源表的关系，填写说明如下：T_ETL_TAB_MAP（表信息）字段名字段填写说明tab_id表id（对应 T_ETL_TAB_CONF 的 tab_id）TAB_BATCH表批次（对应 T_ETL_TAB_CONF 的 TAB_BATCH）source_owner源用户source_tab源名称source_alias源别名（每个表ID、表批次的源别名唯一）source_server源服

38、务器main_flag主表标志（1 为是、0 为否，每个表ID、表批次只有一个主表，主表必须具备物化视图日志）mview_flag抽取数据完成后视图日志标志（1 为清空、0 为无操作）_OWNER目标用户_TAB目标名称RELA_COND关联条件，类似 where 表间关联、过滤条件INC_COND增量条件，类似 where 后面的增量语句CUST_QUERY自定义查询，单条 insert 且不包含 commit_H目标优化提示SOURCE_H源优化提示ENABLE_FLAG启用标记（1 为使用、0 为禁用）暂时不使用T_ETL_COL_MAP，目标表对应源表的字段关系，填写说明如下：3.4.

39、2. 作业流配置作业流信息配置T_CTL_FLOW_INFO，作业流信息，支持作业并发，填写说明如下：T_ETL_COL_MAP（字段信息）字段名字段填写说明tab_id表id（对应 T_ETL_TAB_CONF 的 tab_id）TAB_BATCH表批次（对应 T_ETL_TAB_CONF 的 TAB_BATCH）LOOP_ORDER表批次（默认唯一），支持同表不同列到目标表_col目标列source_col源列uk_col_flag唯一键标志（1 是、0 否）source_col源列cust_fun自定义函数（如 MAX）GROUP_COL_FLAG分组标志（1 是、0 否）

40、enable_flag启用标记（1 为使用、0 为禁用）source_clean_type抽取数据完成后源方式（1 无操作、2 增量删除、3 清空）uk_col唯一键列（视图日志处理模式使用）enable_flag启用标记（1 为使用、0 为禁用）暂时不使用作业依赖信息配置T_CTL_FLOW_INFO，作业流中作业之间的依赖信息，支持依赖多个作业，填写说明如下：3.5 统计信息收集在sjck 用户下执行以下命令（时间可能会比较长）beginT_ETL_COL_MAP（字段信息）字段名字段填写说明FLOW_ID作业流 IDJOB_NAME作业名DEP_JOB_NAME依赖作业名（开始作业的依赖作业名为 0）DEP_TYPE依赖类型（0 为失败依赖、1 为成功依赖、2 为正常依赖）ENABLE_FLAG启用标记（1 为使用、0 为禁用）T_ETL_COL_MAP（字段信息）字段名字段填写说明FLOW_ID作业流 IDFLOW_NAME作业流名称APP_PRODUCT应用产品(如纳税人信用等级系统)PARALLEL