主页 / dba / 问题 / 25793
Accepted
codingbadger
Asked: 2012-10-12 02:40:00 +0800 CST

关于如何处理数据清理和导入的建议

  • 772

我正在从具有如下记录的数据源导入和清理数据：

源数据

数据需要根据事件类型进行排序和重新排列。

目标架构将是这样的：

Create Table dbo.Destination
(
DestinationId int not Null primary key clustered,
EventType int not null,
fkyCustomerId int not null,
         Constraint FK_Destination_Customers References dbo.Customers (pkyCustomerId),
fkyCategoryId int not null
         Constraint FK_Destination_Categories References dbo.Categories (pkyCategoryId),
fkyCompanyId int not null
         Constraint FK_Destination_Companies References dbo.Companies(pkyCompanyId),
fkyLocationId int not null
        Constraint FK_Destination_Locations References dbo.Locations(pkyLocationId),
Amount money not null
        Constraint DF_Destination_Amount Default(0)
)
-- Lookup table
Create Table dbo.Categories
(
pkyCategoryId int not null primary key clustered,
SourceCategoryId char(10) Not Null,
CategoryName varchar(50) Not Null
)
-- Lookup table
Create Table dbo.Companies
(
pkyCompanyId int not null primary key clustered,
SourceCompanyId varchar(10) Not null,
CompanyName varchar(50) Not Null
)
-- Lookup Table
Create Table dbo.Locations
(
pkyLocationId int not null primary key clustered,
SourceLocationId varchar(10) Not Null,
LocationName varchar(50) Not Null
)

col对于每个事件类型，可能有数百个事件类型，每个列中存在哪些数据都有规则。

因此，对于事件类型 1234，Col0具有Categories查找Col1映射和查找映射Companies。

但是，对于事件类型 5000，Col0包含Companies查找的映射并Col1包含 的映射Locations。

此外，有些情况下所有列都会有空值，Col这意味着我需要将这些映射到默认类别、公司等值。

我需要设计一个可以处理现有规则和潜在新规则的导入流程。导入每天将处理大约 250 万行并且还在增加。

什么是实施这种导入的好方法。除了源数据之外，这里没有什么是一成不变的，所以我非常愿意接受更改模式等的想法。

最终目标是转换源数据，以便可以轻松地将其用于报告目的。

ssis sql-server-2012

2 个回答

  1. Best Answer

    我认为最大的挑战是有效地进行事件类型转换，所以我将专注于此并完全忽略架构。

    我的第一个想法（阅读：我从来没有真正尝试过）是使用动态 SQL 来构建一个SELECT语句，该语句将进行数据转换，以便值最终出现在它们正确的列中（即Col0-->CompanyId或其他）。这归结为一个由事件类型、源列、目标列和默认值（如果需要）组成的映射表。

    一旦你有了映射表（或表s，具体取决于它的实际复杂程度），你就可以使用它来动态构建以下形式的 SQL 语句：

    SELECT
    (
        CASE EventType
            WHEN 1234 THEN COALESCE(Col0, 'DefaultCategory')
        END
    ) AS Category,
    (
        CASE EventType
            WHEN 1234 THEN COALESCE(Col1, 'DefaultCompany')
            WHEN 5000 THEN COALESCE(Col0, 'DefaultCompany')
        END
    ) AS Company,
    (
        CASE EventType
            WHEN 5000 THEN COALESCE(Col1, 'DefaultLocation')
        END
    ) AS Location
    FROM RawSourceTable

    将语句构建为 ETL 过程的一部分。如果您需要添加新的事件类型，只需将所需的列映射添加到映射表中，它们将在下次 ETL 过程运行时处理。

    一旦源数据处于这种状态，导入过程的其余部分应该是非常标准的。

    • 2

  2. 好吧，经过一番拼凑，我终于想出了这个解决方案。

    我创建了一个这样的映射表：

    Create Table dbo.MappingTable
(
EventTypeId int
IsCompanyPresent bit,
CompanyLocation varchar(50),
CompanyDefault varchar(50),
IsCategoryPresent bit,
CategoryLocation varchar(50),
CategoryDefault varchar(50),
...
)

    然后为每个事件类型填充具有相关值的映射表。

    例如，如果存在类别，则IsCategoryPresent值为1，CategoryLocation值为Col2，CategoryDefault值为1。

    CategoryLocation 值对应于源表中的列，CategoryDe​​fault 值对应于新查找表中使用的默认值。

    如果类别不存在，则IsCategoryPresent值为0，CategoryLocation值为SourceTableId，CategoryDefault值为1。我必须将位置设置为 SourceTable 中的有效列，否则 SQL Server 会引发错误。

    我现在拥有的 SQL 是这个存储过程（我为映射表中的每个事件类型执行）大约 2.5 - 3 百万行的加载时间不到 1 分钟 - 我对此非常满意。

    Create Proc [dbo].[XXXXXXX]

@EventType int

As Begin

Declare @Sql varchar(max)

Select @sql = 'Insert Into dbo.Destination
                Select  re.SourceTableId,
                        et.pkyEventTypeId,
                        c.pkyCustomerId,
                        bci.CompanyId,
                        rec.pkyCategoryId,
                        s.pkyLocationId,
                        Case 
                        When rem.IsAmountPresent = 0 Or Len(IsNull(re.' + rem.AmountLocation + ', space(0))) = 0 Then rem.AmountDefault 
                        Else re.' + rem.AmountLocation + '
                        End as [Amount]

                From dbo.SourceTable re
                Left Join dbo.MappingTable rem on re.Event_Type = rem.EventType
                Join dbo.EventTypes et on rem.EventType = et.EventTypeId

                Join dbo.Companies bci on bci.SourceCompanyId =  Case 
                                                                        When rem.IsCompanyPresent = 0 Or Len(IsNull(re.' + rem.CompanyLocation + ', space(0))) = 0 Then rem.CompanyDefault 
                                                                        Else  re.' + rem.CompanyLocation + ' 
                                                                        End

                Join dbo.Categories rec on rec.SourceCategoryId =   Case 
                                                                        When rem.IsCategoryPresent = 0 Or Len(IsNull(re.' + rem.CategoryLocation + ', space(0))) = 0 Then rem.CategoryDefault 
                                                                        Else  re.' + rem.CategoryLocation + ' 
                                                                        End
                Left Join dbo.Customers c on c.CustomerId = Case 
                                                        When rem.IsCustomerIdPresent = 0 Or Len(IsNull(re.' + rem.CustomerIdLocation + ', space(0))) = 0 Then rem.CustomerDefault
                                                        Else re.' + rem.CustomerIdLocation + '
                                                        End

                Join dbo.Locations s on s.SourceLocationId = Case
                                                        When rem.IsLocationPresent = 0 Or Len(IsNull(re.' + rem.Location + ', space(0))) = 0 Then rem.LocationDefault
                                                        Else re.' + rem.Location + '
                                                        End


                Where rem.EventType = ' + Cast(rem.EventType as Varchar)

From dbo.SourceTable re
Join dbo.MappingTable rem on re.Event_Type = rem.EventType
Where rem.EventType = @EventType

Exec (@sql)

End

    我正在加入查找表，然后使用一条case语句和动态 SQL 来确定要使用的列。因此，如果存在一个值，那么我会从源表中挑选出该列。如果值不存在，那么我将使用默认值，以确保我始终获得 FK 值以插入到我的目标表中。

    您还会注意到我的加入中也有这张支票

    Len(IsNull(re.' + rem.CompanyLocation + ', space(0))) = 0

    这是因为我发现有些列不是空值而是空值。这也让我有点悲伤。

    我已经运行了几天，它似乎工作得很好。

    话虽如此，如有任何意见，我们将不胜感激！

    • 1

相关问题