Início / dba / Perguntas / 25793
Accepted
codingbadger
Asked: 2012-10-12 02:40:00 +0800 CST

Conselhos sobre como abordar a limpeza e importação de dados

  • 772

Estou importando e limpando dados de uma fonte de dados que possui registros como este:

Dados de origem

Os dados precisam ser classificados e reorganizados com base no tipo de evento.

O esquema de destino seria algo como isto:

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
)

Para cada tipo de evento, e pode haver centenas de tipos de eventos, existem regras sobre quais dados existem em cada uma das colcolunas.

Portanto, para o tipo de evento 1234, Col0tem o mapeamento para a Categoriespesquisa e Col1o mapeamento para a Companiespesquisa.

No entanto, para Event Type 5000, Col0contém o mapeamento para a Companiespesquisa e Col1contém o mapeamento para Locations.

Além disso, há situações em que haverá valores nulos para todas as Colcolunas, o que significa que preciso mapeá-los para valores padrão de categoria, empresa etc.

Preciso criar um processo de importação que possa manipular as regras existentes e as novas regras em potencial. A importação processará aproximadamente 2,5 milhões de linhas por dia e aumentará.

Qual seria uma boa forma de implementar esse tipo de importação. Nada é imutável aqui, além dos dados de origem, por isso estou mais do que disposto a considerar a ideia de alterar o esquema, etc.

O objetivo final é transformar os dados de origem para que possam ser facilmente usados ​​para fins de relatório.

ssis sql-server-2012

2 respostas

  1. Best Answer

    Acho que o maior desafio será fazer as transformações de tipo de evento com eficiência, então vou me concentrar nisso e ignorar completamente o esquema.

    Meu primeiro pensamento (leia-se: na verdade, nunca tentei isso) é usar SQL dinâmico para criar uma SELECTinstrução que fará a transformação de dados de forma que os valores terminem em suas colunas corretas (ou seja, Col0--> CompanyIdou qualquer outra coisa). Isso se resume a uma tabela de mapeamento que consiste no tipo de evento, na coluna de origem, na coluna de destino e em um valor padrão, se necessário.

    Uma vez que você tenha a tabela de mapeamento (ou table s , dependendo de quão complexa ela realmente é), você pode usá-la para construir dinamicamente uma instrução SQL no formato:

    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

    Crie a instrução como parte do processo ETL. Se você precisar adicionar um novo tipo de evento, basta adicionar os mapeamentos de coluna necessários à tabela de mapeamento e eles serão manipulados na próxima vez que o processo ETL for executado.

    Depois que os dados de origem estiverem nesse tipo de estado, o restante do processo de importação deve ser bastante padrão.

    • 2

  2. OK, então, depois de um pouco de pôquer, finalmente cheguei a esta solução.

    Eu criei uma tabela de mapeamento como esta:

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

    E então, para cada tipo de evento, preencha a tabela de mapeamento com os valores relevantes.

    Por exemplo, se a categoria estivesse presente, o IsCategoryPresentvalor seria 1, o CategoryLocationvalor seria Col2e o CategoryDefaultvalor seria 1.

    O valor CategoryLocation corresponde à coluna na tabela de origem e o valor CategoryDefault corresponde ao valor padrão usado na nova tabela de pesquisa.

    Se a categoria não estiver presente, o IsCategoryPresentvalor será 0, o CategoryLocationvalor será SourceTableIde o CategoryDefaultvalor será 1. Eu tenho que definir o local para uma coluna válida no SourceTable ou o SQL Server gera um erro.

    O SQL que tenho agora é este procedimento armazenado (que executo para cada tipo de evento na tabela de mapeamento).

    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

    Estou ingressando nas tabelas de pesquisa e, em seguida, usando uma caseinstrução junto com o SQL dinâmico para determinar qual coluna usar. Portanto, se um valor estiver presente, escolho a coluna da tabela de origem. Se um valor não estiver presente, eu uso um valor padrão que garante que eu sempre obtenha um valor FK para inserir em minha tabela de destino.

    Você também notará que eu também tenho esse cheque em minha junção

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

    Isso ocorre porque descobri que algumas colunas não eram valores nulos, mas em branco. O que me causou um pouco de dor também.

    Eu tenho executado isso por alguns dias e parece funcionar muito bem.

    Dito isto, qualquer comentário seria muito apreciado!

    • 1

relate perguntas