批量数据处理经验分享.pptVIP

China Construction Bank. | 1 批量数据处理经验分享 中国建设银行 2011年2月 目录 China Construction Bank. | 2 CRM数据线结构 批量数据处理经验分享 CRM数据线结构 China Construction Bank. | 3 外部系统 ODS ETL 处理模块 OCRM ACRM TPS WMSS WPPS CRM数据线数据类型-每日基础数据 China Construction Bank. | 4 CCBS：活期、定期、银行卡、实物黄金账户每日增量 国际卡：贷记卡每日增量 证券：证券账户、持有证券、签约每日增量；成交每日全量 A+P：贷款账户每日增量 理财系统：账户每日增量 银保通：保单、签约每日增量 CTS：资金账户每日增量 ECIF：客户、1560、联系方式、渠道签约每日增量 账户和客户每日增量包括最新的时点状态 ETL模块在逻辑上积累这些数据的全量（ACRM数据库中） CRM数据线数据类型-每月汇总数据 China Construction Bank. | 5 CCBS：活期、定期、银行卡、实物黄金账户全量 国际卡：贷记卡全量 证券：持有证券每月全量 A+P：贷款账户每月全量 理财系统：账户每月全量 银保通：保单每月全量 CTS：资金账户每月全量 ECIF：客户汇总每月全量 客户汇总数据包括客户当月的AUM、贡献、总负债等 账户汇总数据包括月末余额，月均余额、当月累计交易情况等 CRM数据线处理类型 China Construction Bank. | 6 直传： 项目组直接和ODS确定接口，数据送到ETL模块后，通过DPTF平台转发。 目前OCRM、ACRM、WPPS、ICSP、ESME、TPS、OCRM房金、WMSS、 CCOB、PBCS都使用到这种方式。 加工： ETL模块对数据做加工，然后通过DPTF平台发送。目前OCRM、WPPS、 WMSS、TPS、ECIF使用这种方式。加工规则主要包括： 1、拼接字段，增加信息（增加ECIF客户号，或拼接几个档的数据） 2、过滤记录（滤出某个客户群体的数据） 3、数据质量完善（处理不同步的情况，或全量修复） 4、代码转换 5、汇总（ETL模块汇总数据，共享给其它系统如ECIF、OCRM） 目录 China Construction Bank. | 7 CRM数据线结构 批量数据处理经验分享 批量处理结构设计 datastage常用经验 大数据量操作 批量处理结构设计-批量处理方式的选择 China Construction Bank. | 8 我中心目前常用的批量数据处理方式包括三类： 通过ETL工具（主要是datastage）进行批量数据处理，中 心的大部分系统均通过这种方式进行处理，如OCRM、 ACRM批量线等； 通过SQL和sh脚本进行批量数据处理，使用这种方式的系 统较少，ACRM部分应用使用PLSQL，如营销清单处理； 编写C/java代码进行批量数据处理，主要是ECIF系统； 批量处理结构设计-批量处理方式的选择-datastage China Construction Bank. | 9 datastage优点： 并行数据处理效率高，特别是文件处理，可以达到 平面文件处理效率的10倍以上，而且可以在同一个任 务中做灵活的并发设置。其提供的join、sum、sort、 group等节点，可以有效的降低数据库的压力； 对于复杂的ETL应用，开发简单，而且提供任务调度 、事件触发等功能，在批量处理环境复杂的项目中有 优势； 批量处理结构设计-批量处理方式的选择-datastage China Construction Bank. | 10 datastage缺点： 管理和维护的工作量较大。一旦使用datastage，必须在运行期间投入人力进行运维，主要 体现在几方面： 运维的大部分操作需要使用工具的client完成，命令行能力较差，因此运维难以实现自 动化。例如日志一般情况下只能通过工具查看，很难导出；运行监控只能在工具中进行 ；错误查询时只能一个个stage的打开查看设置，无法搜索和批量操作等； Datastage的任务运行模式，使程序的错误处理很难做到自动，因为任务（job）自身不 能定位错误发生在哪里，发生的规模有多大，因此当发生错误时最好的方式是停止程序 并人为干涉，这一点和自己编写程序的ETL处理流程有很大差别； 版本管理不方便； 资源的占用大，一个parallel job，如果stage较多，在运行是可能生成几十到上百的osh进程 ，cpu和io的占用会比较大。datastage对文件处理的高效是以大cpu占用，大io占用为代价的 ； Datastage工具本身没有提供包装业务逻辑的手段，其结果是完成一个操作的很多业务逻辑 ，被分散到了不同的stage中（甚至分散在不同的job中），在这种情况下，如果没有统一的规 范，会造成开发出来的代码比较“乱”，除了开发者外其它人比较难以维护； 批量处理结构设计-批量处理方式的选择-datastage China Construction Bank. | 11 datastage适用场景： 如果处理的数据量不大，或者没有处理效率的要求，使用datastage的 意义并不大，如果一定需要减少ETL开发的工作量，可以考虑使用server job，而不要使用parallel job，前者相对简单，维护的工作量也较小； 如果ETL服务器和其它应用共享（如ETL服务器同时也是J2EE应用服务 器或数据库服务器），不建议使用datastage，因为当数据处理繁忙时其它 应用可能会失去响应； 因为datastage的文件处理高效，所以比较适合大量文件操作的批量数 据处理（或从多种不同的数据源取数，如从文件、不同的数据库）。相反 ，如果在同一个数据库中，从某些数据加工成另外一些数据，datastage的 优势不一定能发挥出来，这种情况下使用存储过程可能更合适； 批量处理结构设计-批量处理方式的选择-SQL和sh China Construction Bank. | 12 Sql和sh的优点： 开发和部署相对简单； 可以完全不需要批量处理服务器，直接在DB上进行批量处理 ； 如果使用oracle的PLSQL开发存储过程，效率比较高，可以 使用数据库的并发机制； 一般在这种开发模式下，具体的业务逻辑在SQL或存储过程 中实现，sh提供调度、触发、或定时的机制，比较容易分离业 务逻辑，维护工作量较小； 批量处理结构设计-批量处理方式的选择-SQL和sh China Construction Bank. | 13 Sql和sh的缺点和适用范围： 这种批量数据处理方式的适用范围较小，不适合大量复杂 的批量数据处理环境，也不适合有大量文件操作的批量数据处 理。 因此这种模式最适合的场景是在同一个数据库中，通过一 部分数据加工出另外一部分数据（计算）。 批量处理结构设计-批量处理方式的选择-C/java程序开发 China Construction Bank. | 14 通过C/java程序进行批量处理： 目前，我中心ECIF系统使用的是这种方式，此方式的优 点很明显，可以做各种灵活的控制，可以很容易的解决 datastage在运行维护、错误处理、版本管理、资源占用上的 问题，也可以灵活的在DB和ETL服务器间分配资源，但其缺点 就是开发工作量和开发难度都较大。 因此，如果一个项目主要做批量数据处理，或批量处理 任务较少且需要高稳定性，可以考虑使用这种方式。 批量处理结构设计-数据库和AP的负载分配 China Construction Bank. | 15 目前中心的大部分系统都申请了单独的批量处理服务器，在使用ETL 服务器和DB服务器配合工作的过程中（特别是大数据量的处理），需要特 别注意批量程序开发中的负载分配问题。 ETL服务器 DB ETL程序SQL 批量处理结构设计-数据库和AP的负载分配 China Construction Bank. | 16 如果使用datastage做批量数据处理，很多应用喜欢在数据库源上写复 杂的sql，而在job层面使用的stage很少。这种方式，计算的压力实际上是 由数据库承担的，ETL服务器上只负责job的运行，压力很小，最终现象是 数据库繁忙，而ETL服务器总是很闲。实际上datastage提供大量的计算工 具代替数据库计算，如sum，join，group、distinct等操作，合理的使用这 些stage，可以有效的分担数据库压力。如果数据库需要支持联机类应用， 一种比较合理的批量处理方式是datastage只通过最简单的select语句从数 据库中读取数据，复杂的计算通过各种功能stage完成，结果生成文件，在 数据库不繁忙时（如晚上），再使用loader或merge批量修改数据库。 如果使用sql或存储过程，开发者必须意识到，ETL服务器只负责任 务的调度，所有的批量处理压力都在数据库服务器上。因此，如果采用这 种方式，除开调度程序运行和生成文件的问题，甚至不需要独立的ETL服 务器。 一般来说，自己开发C/java程序是最容易实现负载分配的，可以合理 的把业务逻辑分担在数据库和ETL服务器上，这种方式从理论上说是效率 相对较低的，但同时是最稳定，最不容易出问题的批量数据处理方式。 批量处理结构设计-批量处理调度 China Construction Bank. | 17 批量数据处理的调度往往是事件触发的，事件类型最常 用的是数据就绪和定时两种。 目前OCRM、ACRM系统的ETL模块使用的调度是taskctl ，这是OCRM开发的一套公共调度程序，功能强大、稳定而且 操作相对简单，可以调度datastage job，sh、sql、exec code 等，并可以分为并行、串行调度，缺点是对于调度中的资源监 控较欠缺。如果系统没有使用多ETL服务器，并在服务器间做 任务分配，建议都可以使用taskctl。 Datastage自身也提供调度方式，但datastage的调度实 际上是通过crontab实现，功能较少，而且操作较复杂，不推 荐使用。 也可以通过crontab调度，但对于规模较大，处理复杂的 批量任务，必须存在一个统一的调度程序，否则当出现错误时 ，维护的工作会非常大。 批量处理结构设计-时序问题 China Construction Bank. | 18 证券系统 每日流水 （交易账号） PPSS营销反馈 (客户号) OCRM销售台账 (客户号) 全量1560 (客户号 交易账号) 案例：匹配客户号 批量处理结构设计-时序问题 China Construction Bank. | 19 未匹配的历史 购买流水 每日流水 PPSS营销反馈 OCRM销售台账 全量1560 建立补充机制后 ECIF 批量处理结构设计-积累全量问题 China Construction Bank. | 20 OCRM VIPNOR ODS VIP 全量 批量处理结构设计-积累全量问题 China Construction Bank. | 21 OCRM ETL模块 VIP清单 全量 VIP数据 ODS/DW 全量 VIP数据 VIP清单 批量处理结构设计-星型模型和缓慢变化维 China Construction Bank. | 22 常见的ETL需求有两类： 数据的抽取、转换、加载 数据汇总（报表计算） 对于第二类需求，常用星型模型和缓慢变化维 批量处理结构设计-星型模型 China Construction Bank. | 23 批量处理结构设计-缓慢变化维 China Construction Bank. | 24 总行 上海四川 浦东岷江 机构存量 人数 月新增 人数 浦东100 上海100 机构存量 人数 月新增 人数 岷江100 四川100 2011-1月数据状态 批量处理结构设计-缓慢变化维 China Construction Bank. | 25 总行 上海四川 浦东岷江 机构存量 人数 月新增 人数 上海00/-10 机构存量 人数 月新增 人数 岷江100 浦东100 四川200/10 2011-2月数据状态 批量处理结构设计-缓慢变化维 China Construction Bank. | 26 总行 上海四川 浦东2岷江 机构存量 人数 月新增 人数 浦东 1 0-10 上海0-10 机构存量 人数 月新增 人数 岷江100 浦东 2 1010 四川2010 2011-2月数据状态 浦东1 批量处理结构设计-缓慢变化维 China Construction Bank. | 27 总行 1900-1-1- 9999-12-31 上海 1900-1-1 9999-12-31 四川 1900-1-1 9999-12-31 浦东2 2011-2-1 9999-12-31 岷江 1900-1-1 9999-12-31 浦东1 1900-1-1 2011-2-1 目录 China Construction Bank. | 28 CRM数据线结构 批量数据处理经验分享 批量处理结构设计 datastage常用经验 大数据量操作 Datastage常有经验-开发规范-结构规范 China Construction Bank. | 29 结构规范: 描述一个大的批量处理过程应该如何实现。 在ACRM系统中，任何一个业务功能都被分拆成TR（数据处理）、LD （数据加载）两个过程，这两个过程可能包含多个job，因此用GE来进行统一的 调度管理，这样每个GE就是一个独立的业务功能实现单元，它又依靠SE来进行 集成，变成规模更大的功能包，最终，通过SH来包装SE，供调度程序使用。 同时，这个规范还规定了每一层采用的技术，如： TR使用parallel job LD使用server job，嵌入basic代码，用merge脚本入库 GE使用server job，嵌入basic代码进行调度 SE使用seq job SH使用server job，嵌入basic代码进行调度 任何批量处理的开发都应该制定类似的规范，来禁止开发人员随意的 选择开发模式。 Datastage常有经验-开发规范-代码规范 China Construction Bank. | 30 代码规范： Datastage软件本身并不规定细节的业务逻辑在什么地方实现。比如当 需要对一批数据做代码转换时，转换动作即可以在读取数据表的sql中实现，也 可以在sql中仅进行读取，转换放在后续的transformer中实现。这种开发方式如 果不加以规范，会给维护造成很大的麻烦。 因此，对于使用datastage做批量数据处理，都建议对业务逻辑实现的 位置做适当的规范，如： 一个数据源只能对应一张数据表； 数据源的sql中不要进行复杂的处理，仅作简单的where过滤； 表的关联可以用join stage来实现，不要在数据源的sql中做关联； 汇总动作使用aggregator stage来实现，不要在数据源的sql中做group ； 复杂的转换逻辑统一在transformer中实现； Transformer单入口单出口； 除了上述代码上的规范外，还需要规范stage命名，注释信息的编写等 。 Datastage常有经验-开发规范-过程规范 China Construction Bank. | 31 过程管理: 使用datastage进行开发时，一般都是设计人员维护mapping， 开发人员根据mapping进行程序开发。 ODS系统在进行开发过程管理时，采用如下的一种方法： 设计人员不仅要设计mapping，而且会设计job，具体方法是设 计人员根据mapping，做一个模板job，这个job中只有每个stage及其数 据流关系，没有具体的字段等信息；开发人员再根据mapping和这个半 成本的job去做细化。这样做的好处是由设计人员来控制job的形态，工 作量并不大，但重要的业务逻辑控制在了少量设计人员手中。 Datastage常有经验-使用注释 China Construction Bank. | 32 Datastage job提供注释，但目前开发中使用的较少，比较良好的注释方式 如下： Datastage常有经验-版本管理 China Construction Bank. | 33 Datastage提供版本管理工具，但使用方式比较复杂 ，难以满足我行版本管理的要求。 目前常见的方式是每个job单独创建一个dsx包，放 在CC上作为文件管理。可以通过自己编写的工具程序，把 一个大的dsx包切分成小的dsx文件。 Datastage常有经验-并行处理-灵活使用分区 China Construction Bank. | 34 分区并行处理是datastage paralleljob最大的特点 ，可以极大的提高性能。为了实现高效的并行处理，最 好在系统上做如下设计： 建立多个文件系统，最好分布在不同的存储上； 通过修改default.apt文件，或建立新的apt文件，把不 同的并行节点使用的缓冲和dataset分布在不同的文件系 统上； 这样，当datastage生成dataset时，会写入不同 的文件系统，IO会比较好；同时所有并行节点产生的缓 冲（scratch目录），将使用不同的文件系统。 Datastage常有经验-并行处理-使用dataset China Construction Bank. | 35 Dataset是datastage最大的特色，采用分区方式存放数据，读写效率都非常高 。使用datastage进行数据计算的方式区分于在数据库中用sql或存储过程进行计算的关 键点也在于dataset。 如果一个大的sql在数据库中运行，因为某种原因报错，必须要完全重新运行 一次，无法使用中间的计算结果，除非把这个sql拆分，然后保留中间计算结果在表或 文件中，无论哪种方式必然造成效率的降低。 Datastage在做相同的计算时，任何中间结果都可以保留在dataset中，dataset 生成的速度很快（并行写入），而且从dataset中读取这些数据的效率要大大优于从数 据库中读取，因此这种计算工作的拆分不会造成太大的效率降低。 因此，如果充分利用dataset的性能优势，datastage的批量数据处理和用sql和 存储过程有很大差异。Datastage可以在系统不繁忙时预先生成大量的dataset数据，加 工成应用可以直接使用的格式并排好序，然后等待“关键数据”的就绪，一旦“关键数据” 就绪，就从dataset中高效的读取数据，并和“关键数据”做关联、汇总等计算，最后生 成结果，这样，从“关键数据”就绪到加工出结果，时间会大大缩短。 ACRM系统和OCRM系统做的“大海捞针”动作，每日都在10亿左右的1560中 找到所有的VIP客户的账户，并与OCRM提供的全量账户比对并发现差别，采用此方式 后，能保证在几个小时内完成，大大提高了效率。 Datastage常有经验-并行处理-使用并行节点 China Construction Bank. | 36 Parallel stage可以极大的提高数据处理效率，但同时系统资 源占用也比较厉害。要注意的问题主要有： 所有并行节点必须采用相同的分区方式，如果分区方式有差异会 造成结果数据错误，而且没有任何提示； 分区的键值的数据结构必须完全相同，即使都是varchar，长度也 必须一样，否则有可能出现关联不到的情况； Datastage常有经验-修改数据库 China Construction Bank. | 37 不建议使用datastage的insert、update、 update_or_insert、delete等方式修改数据库，这样做有两个 问题，一是效率太差，二是如果数据中重复数据，在并行处 理中容易锁表。如果必须要这样做，最好严格控制提交事务 的记录数，极端情况下每条记录都提交事务。 建议的处理方式是对要修改数据库的数据生成文件， 然后用sqlloader或merge方式导入数据库中，但对于联机应 用频繁的数据库必须选择合适的时间。 Datastage常有经验-空值处理 China Construction Bank. | 38 空值是Datastage处理较复杂的问题，很多datastage 函数，都不能处理空值，如果把数据不加判断的传给这些函 数，会造成警告，当警告积累过多就会导致程序abort。 一般在transform中，调用函数的方式为： if isnull(xxx) then setnull() else dosomething() 还有一种比较好的方式是在从数据源读出数据时， 对所有字段都设置为notnull，把所有的null都转换成空串。 Datastage常有经验-日志清理 China Construction Bank. | 39 Datastage的日志会占用安装程序路径的空间，因此 最好设置定时清理（可以在administrator中设置），否则运 行时间过长会造成空间紧张，同时打开日志的时间会非常长 ，难以维护。 另外，如果日志中有警告，必须认真对待，因为警告 往往是程序不健壮的地方。当数据都正确时没有问题，但当 数据大量出问题时，如果设置了警告条数是unlimit，那么每 条记录都在报警告，文件系统空间可能会立刻达到100%。 Datastage常有经验-数据预处理 China Construction Bank. | 40 Datastage允许job运行前后执行某些特定的动作，但 如果存在好的调度方式，不建议采用这种开发方式，而应该 通过调度来执行这些特定的动作。因为批量调度应该管理所 有的任务，这样便于后期的维护，让一个job来处理和调度相 关的任务，时间长了后会带来运维的难度。 某些从数据库中读取数据的stage，在运行前后也可 以执行一些命令（open command和close command），此 时请注意命令是在所有节点上都会执行，因此如果该stage是 并行处理的，最好不要执行重建表之类的操作，因为这种操 作会被每个节点都执行，会造成处理出错。 目录 China Construction Bank. | 41 CRM数据线结构 批量数据处理经验分享 批量处理结构设计 datastage常用经验 大数据量操作 大数据量操作经验-数据删除 China Construction Bank. | 42 在生产数据库中一定不能直接用delete命令删除大量 的数据，这样操作可能导致锁资源耗尽而回滚，如果回滚段 不够会使数据库挂起。 因此在处理大数据量删除时，一般要采用特殊的方式 ： 如果删除数据量不是非常大（在几万到几十万间）， 可以手工卸出要删除数据的key，然后用sh生成对每个key生 成一条删除语句并提交。这时删除命令实际上是删除一条提 交一条，速度虽然比较慢，但非常稳妥； 如果删除的数据量达到百万甚至千万，可以考虑把表 中不需要删除的数据备份到文件或其他表中，然后truncate 源表，并把备份的数据重新导回源表； 大数据量操作经验-联机状态下批量修改 China Construction Bank. | 43 如果需要大批量修改一张数据表中的数据，但该表 频繁被其他应用做select访问，为了不影响应用，可以采用 下面的操作方式： 建立另外一张备份表，从源表复制数据，然后在备份 表上做批量修改操作，最后直接对源表和备份表rename互换 名称。 大数据量操作经验-大数据量加载 China Construction Bank. | 44 如果需要大批量数据插入数据库，可以先通过ETL程 序加工出需要装入数据库的数据文件，再使用oracle的 sqlloader等工具一次性导入数据库中。 这种方式相对于insert的优点在于最终修改数据库操 作集中在很短的时间内，而且效率远远高于insert。 大数据量操作经验-大数据量修改（或新增） China Construction Bank. | 45 如果需要大批量数据修改数据库表（或使用insert_or_update方式），可以先通过 ETL程序加工出需要装入数据库的数据文件，再使用merge语句一次性导修改数据库中。 这种方式相对于update的优点在于修改数据库操作集中进行，而且效率较高，同 时merge语句可以按照分区进行，效率会更高，如下例： merge into/*+(use_hash(a b)*/ dw.CUST_ARGMT_RLSP partition(#PARTITION_ID_DW) a using etl.CUST_ARGMT_RLSP partition(#PARTITION_ID_ETL| ) b on (a.ARRANGEMENT_TYPE = b.ARRANGEMENT_TYPE and a.ARRANGEMENT_KEY = b.ARRANGEMENT_KEY and a.PRIN_ARR_TYPE = b.PRIN_ARR_TYPE) when matched the