SQLSERVERCDC增量数据抽取

1、SQLSERVERCDC 量数据抽取.版本历史日期版本描述作者26/04/2010SQLSERVERCDC 量数据抽取方案拟制刘建军，目录1、概述41.1、需求概述41.2、场景分析42、依赖关系53、服务方案5-设计的目标5-相关关键技术说明5-、报表服务方案11-报表服务表结构13-报表服务过程及其描述14-报表服务使用14需求概述本文档的主要目的是提出和测试报表服务实现， 来提高Q3系统的报表统计性能。该服务方案主要参考ERP报表服务方案，并结合Q3系统与SQLServer2008数据库的特点来实现，并尽量统一规划以提高该方案的兼容性。场景分析报表服务需要将要进行统计、分析的数据库某一业

2、务相关数据在一段时间内的变化（增量数据），同步到报表服务中间库，并将统计结果报表同步更新，这就需要：.报表中间库转储和引用业务库对象。.同一数据库实例上不同数据库或分布式数据库的变更数据捕捉.获得数据增量、并根据增量形成增量报表数据同步报表中间库。.或直接同步报表中间库。.业务执行的唯一性。.抽取日志的记录的记录。.增量数据日志记录清除。.同步异常记录。2、依赖关系Q3系统采用SQLServer2008数据库，并且该版本数据库提供了CDC（changedatacapture）捕获变更数据的方法，并且提供了同义词和数据库级应用锁功能，结合排序函数，为在数据库级别上实现与ERP类似的报表服务提供了

3、可能。3、服务方案设计的目标Q3的报表服务方案，要满足如下功能性和非功能性要求。业务数据增量抽取。增量数据合并（算法最优）。增量抽取数据的准确性和有效性。数据抽取执行线程并发控制唯一性和入口统一性（采用数据库级应用锁并需要进行封装，提供与ERL致的入口）。跨数据库或分布式数据库的抽取实现（链接服务器）。数据同步路径最短、同步效率最高（根据业务数据量和业务的复杂性分别采用不同的数据同步方式）。相关关键技术说明CDC（changedatacapture）:CD数据仓库常用技术手段，是当对数据库执行insert、updatedelete操作的时候，捕获变更数据的方法。CDC勺原理是每次对源表（Sou

4、rceTables）执行增删改操作时，数据库事务日志会记录该DM限作造成的变更数据，然后捕获处理过程将日志中源表的变更数据写入变更捕获表（ChangeTables）.使用步骤：.通过SQLServer配置管理器（SQLServerManagementStudio、服务）,启用数据库的SQLServerAgent代理服务。.执行存储过程，启用了数据库的CDC寺性。EXECsys.sp_cdc_enable_db.执行存储过程，对数据库中某一表启用CDC寺性。EXECsys.sp_cdc_enable_tablesource_schema=Ndbo,source_name=Ntest,role_n

5、ame=Ncdc_admin,capture_instance=Ntest_cdc1,supports_net_changes=1,index_name=NPK_test,captured_column_list=Ncol1,col2,col3,filegroup_name=NPRIMARY.系统自动在系统表下建立对应变更捕获表， 变更捕获表的命名规则是cdc.+实例名+CT.其中其中$operation代表的是执彳f的是何种DML1=delete,2=insert,3=update（更新前），4=update（更新后）.系统建立了其他多个关于CDC!数和过程，其中涉及报表相关过程与函数：sy

6、s.fn_cdc_get_max_lsn返回cdc.lsn_time_mapping系统表中start_lsn列中的最大日志序列号（LSN）。您可以使用此函数为任何捕获实例返回变更数据捕获时间线的高端点。sys.sp_cdc_cleanup_change_table清除变更数据存储过程手动清理：使用sys.sp_cdc_cleanup_change_table存储过程清除变更数据。自动清理：在启用数据库CDC寸，系统自动在SQLServerAgent中加入每日清除变更数据的作业。作业自动执行清理变更数据过程。.停用CDC存储过程用于停用CDC5例。EXECUTEsys.sp_cdc_disab

7、le_tablesource_schema=Ndbo,source_name=Ntest1,capture_instance=Ntest1_cdc1;.存储过程用于停用数据库CD值execsp_cdc_disable_dbSQLSERVER用程序级锁SQLSERVER供了数据库级应用程序锁，该应用程序锁提供了可利用SQLServer检测阻塞和死锁的机制，来锁任何想要锁住的资源的方法。存储过程使用应用锁示例：-开启事务begintran-对存储过程加应用锁EXECsp_getapplockResource=MyProcedure,LockMode=Exclusive,LockTimeOu=0-业

8、务处理过程EXECMyProcedure-对存储过程解应用锁EXECsp_releaseapplockResource=MyProcedure-事务完成commitMERG语句MERG用句可以在一个语句中根据与源表联接的结果对目标表执行多个INSERTUPDATEDELETE操作。该语句允许我们将数据源与目标表或视图联接，然后根据该联接的结果执行多项操作。典型应用是根据在另一个表中找到的差异在一个表中插入、更新或删除行来对两个表进行数据同步。可以使用MERGED句执行以下操作：使用一个语句有条件地在单个目标表中插入或更新行。如果目标表中存在相应行，则更新一个或多个列；否则，会将数据插入新行。同

9、步两个表根据与源数据的差别在目标表中插入、更新或删除行。MERGED法包括五个主要子句：MERG孑句用于指定作为插入、更新或删除操作目标的表或视图。USING子句用于指定要与目标联接的数据源。ON子句用于指定决定目标与源的匹配位置的联接条件。WHEN?句用于根据ON子句的结果指定要执行的操作。OUTPU评句针对更新、插入或删除的目标对象中的每一行返回一行。-建立两个结构一样的表，进行数据变化，实现2个表的数据同步。-目标表没有数据，而源表有数据。-目标表有部分数据，而源表有数据且跟目标表有差异。示例SQL本如下：mergetest3_bakastusing(selectcoll,col2,co

10、l3,col4fromtest3)asson(s.col3=t.col3)whenmatchedthenupdatesett.col1=s.col1,t.col2=s.col2,t.col4=s.col4whennotmatchedtheninsertvalues(col1,col2,col3,col4)whennotmatchedbySOURCEthendelete;执行结果，这两个表数据在执行MERG后数据完全一致。同义词同义词是架构范围内的对象的另一名称，即别名。通过使用同义词，应用程序可以使用同义词来引用基对象，而不必使用由两部分、三部分或四部分组成的名称。创建同义词语法示例：CREA

11、TESYNONYMbo.同义词名称FORqaflinkserver.QAFv0.8.dbo.sysuser;同义词基对象的基类型，可OBJECTPROPERTY座获取:ASBaseType链接服务器通过配置和使用链接服务器，SQLServer2008可以对远程服务器上的数据源进行数据操作。链接服务器具有以下优点:访问远程服务器。能够对企业内的异类数据源发出分布式查询、更新、命令和事务能够以相似的方式确定不同的数据源。请参见SQLSERVER治事务测试与验证.doc文档3SELECTOBJECTPROPERTYEBJECT。同义词名称),BaseType)3.3、报表服务方案方式一：对于数据基数

12、大、数据变更频繁的业务表:中间数据库CDCCDC 数据清理(JOB)/CD(JOB)/CDC C数：:据清理线程：；卢、:同步日志+蚪记录表中间数据库*【报表服务整体流程】业务表CDCCDC 数据变更表I I：获得增量数据LzLzCDCCDC 变更r r，1 1 表同义词；增量分析增量结果同步，分析汇总：I_II_I:统计结果 I II I 报表数据，：记同步日志记录获得增量数据1 1SQLServerAgentSQLServerAgent；代理执行数据清理作业；【更新频繁数据报表服务业务流程】方式二：对于数据基数小、数据变更不频繁的业务表:中间数据库：具体工作流程:分析汇总结果异常处理线程髭

13、黑 Q-Q-、同步成功I I1 1【定期清理】同步汇总结果乎匕 I:I:、-:7 7 业务数据库,【更新不频繁数据报表服务业务流程】报表服务表结构报表服务日志记录表Procedure_Namenvarchar(60)存储过程名Procedure_Statusnvarchar(1)存储过程状态Last_Run_Timedatetime最后运行时间Last_Run_Bynvarchar(50)最后执行者Run_Countint运行次数Last_End_Timedatetime最后结束时间Refer_CDC_Tnamenvarchar(60)涉及 CDC8 名称Exec_LSNbinary(10)执

14、行到最大 LSNRemarksnvarchar(200)存储过程描述CDC_Instance_Namenvarchar(60)涉及 CDC3；例名PKeybigint主键报表服务异常记录表ErrorProNamenvarchar(50)出异常的存储过程名RunTimedatetime出错运行时间ErrorNumberint发生错误的行号ErrorMessagenvarchar(200)错误的消息文本PKeybigint主键4；记同步日志记录：同步成功；分析汇1总结果If同步汇总结果报表服务过程及其描述报表服务存储过程:存储过程名参数功能Proc_ClearCDCProcName存储过程名清理

15、CD 喙更表；Proc_RunLogProcedure_Name 存储过程名记录存储过程运行时相关信息；Proc_ErrorLogErrorProName存储过程名记录存储过程运行出错的行号、内容等信息；ErrorNumberERROR_LINEErrorMessageERROR_MESSAGEProc_AppLockLockProcName存储过程名存储过程加锁；Proc_AppUnLockLockProcName存储过程名存储过程解锁；Proc_RegProc_Name存储过程名对存储过程初始信息进行注册CDC_TnameCD 嘎更表名CDC_InstanceCDCS 例名Remarks存

16、储过程标注Proc_GetLSNTnameCD 嘎更表名返回当前对于输入的 CD 嘎更表而得到的最大、最小 lsn 号lsn_minOutput当前最小 lsn 号lsn_maxOutput当前最大 lsn 号报表服务使用开发人员使用步骤：.通过SQLServer配置管理器(SQLServerManagementStudio、服务),启用数据库的SQLServerAgent代理服务或者将该服务设置为自动启动。.对业务数据库启动CDC(changedatacapture)功能。在当前数据库下执行EXECsys.sp_cdc_enable_db,该代码只需执行一次即3,确定原报表业务SQL句中涉及

17、对象范围、涉及对象数据量级别以及确定该对象是否需要启用CD嚷更数据捕捉。例如：原业务要对A,B,C表统计汇总，AB表数据量很大，C表数据量很少，但都有数据变化且变化频繁。则需要对A,B,C表均启用CDC.原业务要对A,B,C表统计汇总，AB表数据量很大并有数据变化，C表数据量很少且无数据变化，。则只需要对A,B表均启用CDC.原业务要对A,B,C表统计汇总，ABC表数据量均很小，A,B,C可能有数据变化也可能部分数据发生变化。则不需要对任何启用CDC而采用报表服务方式二手段处理4,确定主键和涉及相关统计汇总字段：确定要进行捕捉的业务表的字段列表和业务主键， 即捕捉源表中哪些字段的数据变化到变更

18、捕获表中。 （一般是针对SQL句涉及的字段进行捕捉）。5,调用存储过程对表启用CD瑞性。EXECsys.sp_cdc_enable_tablesource_schema=Ndbo,source_name=Ntest,role_name=Ncdc_admin,capture_instance=Ntest_cdc,supports_net_changes=1,index_name=NPK_test,captured_column_list=Ncol1,col2,col3,col4，filegroup_name=NPRIMARY;源表所属的架构的名称注：各参数说明如下source_schemasou

19、rce_namerole_name启用变更数据捕获的业务源表的名称控制更改数据访问的数据库角色的名用于唯一地标识源表中的行的唯一索引（而非主键引）的名称激活变更数据捕获的列名称列表用于为捕获实例创建的更改表的文件其中红色标注参数根据自己情况改变。6,调用存储过程对表启用CD瑞性后， 会在系统表下产生一个cdc架构下的对应该表的变更数据捕获表。.在Q3_REP艮表数据库下建立该变更数据捕获表的同义词，如果该表未启用CDC即没有变更捕获表，但报表服务涉及该表，则对该表直接建立同义词。.在Q3_REP艮表数据库下建立报表数据存储表。用作存储执行报表、或者增量报表数据MERGE该表。.对某业务中涉及的

20、所有表重复上述过程。过程完毕，准备工作就绪，以下步骤为存储过程内报表业务实现流程：调用存储过程 Proc_Reg,对报表业务存储过程涉及基础信息在报表服务日志记录表 Rep_RunLo呐进行注册。即记录该业务存储过程涉及 CD 似例名、CDCft 名、执行状态、执行次数等信息，如果该存储过程涉及多表，则多次调优存储过程来记录上述信息。注：如果该信息已经注册，则跳过注册步骤。调用存储过程 Proc_AppLock,以报表存储过程名称为参数，对本存储过程加锁，以保证该业务存储过程执行的唯一性。调用存储过程 Proc_ClearCDC,以报表存储过程名称为参数，清理 CD 吸更表中失效变更数据.ca

21、pture_instance命名特定于实例的跟踪对象的捕获实例名称supports_net_changes更改支持index_namecaptured_column_listfilegroup_name组指示是否对此捕获实例启用查询净调用存储过程 Proc_GetLSN,获得 CDC 变更捕获表中最大最小 LSN 即增量统计 LSN 的范围，以备下一步骤条件过滤表达式使用。同步增量统计报表结果到报表数据存储表。获得增量：第一类：例如业务SQL:selecta.col1,sum(a.col2),sum(b.col3)fromajoinbona.col1=b.col1.其中col1为业务主键，co

22、l2,col3为数值类型字段只需要将表a,b替换，selecta.col1,sum(a.col2),sum(b.col3)from(selectcol1,sum(case_$operationwhen1then-col2when2thencol2when3then-col2elsecol2end)fromawhere_$start_lsn0and$start_lsn0and$start_lsn0and_$start_lsn?）agroupbycollMERG同步数据：示例SQLJK本如下（需要将上述增量计算SQL替换【源结果集】位置，将报表数据存储表名替换到【目标表】位置）：merge目标表a

23、stusing（源结果集）asson（s.关联键值=t.关联键值）whenmatchedthenupdatesett.字段=s.字段+t.字段whennotmatchedtheninsertvalues（s.字段）;根据业务可能重复上述同步增量统计数据过程.调用存储过程 Proc_RunLog,将执行后的相关信息写入到日志表中。调用存储过程 Proc_AppUnLock,解应用程序锁.如对数据基数小且变更不频繁的业务表做报表统计，因为未使用CDC则使用步骤类似但有所区别：调用存储过程 Proc_Reg,对报表业务存储过程涉及执行状态、执行次数等基础信息在报表服务日志记录表 Rep_RunLo 呐进行注册。调用存