介绍

我正在开发一个大数据仓库。我们拥有来自具有不同数据库环境和体系结构的多个事务系统的数据。COBOL平面文件，Oracle，SQL服务器是主要的数据源。每夜进程从所有数据源导入每日事务数据（增量更改），并组合形成单个客户视图数据库。

通过比较Surname，Firstname，DOB，Sex，前两个地址行的第一个字符，ETL过程可以从所有事务系统中找到Common客户——根据上述条件字段创建HASHBYTE校验和值，并在Checksum字段上使用GRUOP BYE。因此，ETL过程创建一个重复数据删除的Customer表。

然后使用最新的联系人详细信息（电话，移动电话，电子邮件，地址等）更新新的Staging Customer表。此外，Customer表经过某些数据标准化和验证。例如，将所有电话号码设为国际格式，验证电子邮件地址，删除不良数据等。

每个唯一的客户(Customer)都会获得一个客户(Customer)ID（CID）。在下一个ETL过程中，CID可以合并或解除合并作为事务系统上的更改的一部分。我在本文中仅描述了Customer表，但是ETL过程基于CID创建了大量的其他表（例如，PRODUCT）。

最后，我必须将Staging Customer表与生产实时客户(Live Customer)表同步，因为Web和Mobile API都是实时的，所以在白天也要同步，即使是午夜也要同步。由于Customer表具有数百万条记录和CID合并的可能性，因此从staging Customer表更新Live Customer表并不容易，而且成本不低。此过程需要很长时间，并导致其他应用程序运行超时。要更新实时表格，请按以下步骤操作：

锁定客户(Customer)表

在ETL过程中加载在实时表上创建的任何新客户

截断实时(live)客户表

从Staging Customer表加载数据

重新构建所有索引

解锁客户(Customer)表

现在问题是上面的步骤需要将近5-10分钟，而其他Web和移动应用程序会因超时而受到影响。我已使用临时表（DROP和RENAME）解决了该问题。系统工作了，只有一秒钟锁定客户表！

步骤：

在Production数据库上创建临时Customer表。

从暂存区域将数据加载到临时表。

在临时表上创建必需的索引（在我的例子中也是一个全文索引）

在ETL过程中将在活动表上创建的任何新客户加载到临时表。

DROP 实时(live)Customer表

RENAME临时表生存客户表

使用代码

代码在生产数据库上创建tmp_Customer并从临时(staging)表加载数据。下一步创建所有必要的索引。从实时客户表中收集任何缺少的客户详细信息——在ETL过程中，在Production 数据库中创建Customers。最后DROP 实时(live)customer表和RENAME临时表生活。代码仅显示Customer表如何在Production 中刷新。您可以重复所有其他表的代码。在继续之前备份必要的表。

-- Microsoft SQL Server 2014 (SP2-CU16) (KB4482967) - 12.0.5626.1 (X64)-- Author: Prasannakumaran SarasijanayananIF OBJECT_ID('dbo.tmp_Customer', 'U') IS NOT NULL DROP TABLE dbo.tmp_Customer;　CREATE TABLE [dbo].[tmp_Customer]([CID] [bigint] NOT NULL,[FirstName] [varchar](50) NULL,[SurName] [varchar](50) NULL,[Address1] [varchar](50) NULL,[Address2] [varchar](50) NULL,[Address3] [varchar](50) NULL,[Address4] [varchar](50) NULL,[Address5] [varchar](50) NULL,[Title] [varchar](10) NULL,[Sex] [varchar](1) NULL,[DOB] [varchar](12) NULL,[Fulladdress] [varchar](200) NULL,[email] [varchar](100) NULL,[Mobile] [varchar](20) NULL,[Fullname] [varchar](100) NULL-- Other columns are purposely removedPRIMARY KEY CLUSTERED([CID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]　--Load data from ETL staging areainsert into tmp_customer( CID, FirstName, SurName, Address1, Address2, Address3, Address4, Title, Sex, DOB, Fulladdress, email, Mobile, Fullname)select CID, FirstName, SurName, Address1, Address2, Address3, Address4, Title, Sex, DOB, Fulladdress, email, Mobile, Fullname from CCF--========================= Creating Indices ==================================--MobileCREATE NONCLUSTERED INDEX [NonClusteredIndex-Mobile] ON [dbo].[tmp_Customer]([Mobile] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)　--CREATE FULLTEXT CATALOG Customer_FullText WITH ACCENT_SENSITIVITY = OFFdeclare @sql varchar(max)Declare @CONSTRAINT_NAME varchar (256)SELECT @CONSTRAINT_NAME=KU.CONSTRAINT_NAMEFROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TCINNER JOININFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KUON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' ANDTC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME ANDKU.table_name='tmp_customer'ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION--BUILD Dynamic SQLset @sql='CREATE FULLTEXT INDEX ON tmp_customer(EmailLanguage 1033,FullnameLanguage 1033,FulladdressLanguage 1033)KEY INDEX ' + @CONSTRAINT_NAME + 'ON Customer_FullTextWITH STOPLIST = OFF-- SEARCH PROPERTY LIST = OFF'EXEC (@sql)--insert Customer data created during ETLINSERT INTO tmp_Customer( CID, FirstName, SurName, Address1, Address2, Address3, Address4, Address5, Title, Sex, DOB, Fulladdress, email, Mobile, Fullname)SELECT CID, FirstName, SurName, Address1, Address2, Address3, Address4, Address5, Title, Sex, DOB, Fulladdress, email, Mobile, FullnameFROM Customer P WHERE NOT EXISTS (SELECT NULL FROM tmp_Customer F WHERE P.CID = F.CID)--Finally Drop and Rename tableIF OBJECT_ID('dbo.Customer', 'U') IS NOT NULL DROP TABLE dbo.Customer;EXEC sp_rename 'tmp_customer', 'Customer'

原文地址：

转载地址：http://kszhj.baihongyu.com/

你可能感兴趣的文章