A importação utilizando CDC permite criar uma carga de dados incremental que se adéqua bem a cenários onde o sistema de origem não consegue ou não possui uma forma específica para evidenciar quais registros precisam ser atualizados, normalmente em sistemas que manipulam fatos que podem ser alterados de forma retroativa em qualquer momento do seu ciclo de vida. Nestes casos, as cargas incrementais precisam necessariamente ser feitas analisando toda a base de origem para uma atualização consistente da base de destino.
O recurso CDC é uma alternativa para este cenário, onde o volume de dados impede uma carga full em um tempo razoável.
Change Data Capture um recurso nativo do SQL Server que monitora os inserts, updates e deletes salvos no arquivo de log do banco de dados.
Para implementar uma estratégia de importação incremental existem diversas abordagens podem ser usadas com seus prós em contras:
- incluir uma coluna de data de alteração para controlar na fonte o que deve ser incluído ou não. Isso não funciona bem para bancos legados que não podem ser alterados.
- criar tabelas espelhos e popular com triggers e depois usar estas tabelas para popular o destino
- fazer querys que comparam todos os dados da fonte com todos os dados de destino. As vezes muito oneroso
- Carga/Descarga Full: apagar tudo no destino e atualizar com tudo da origem. Para bancos maiores ou para sistemas que precisam de intervalos de atualização pequenos pode ser muito oneroso
Change Data Capture
Benefícios:
- baixo impacto: não precisa criar colunas adicionais, triggers e fazer querys longas
- Pouco overhead: o processo é assíncrono e não afeta o banco
- Granular: configurado sob demanda tabela por tabela
- SQL nativo
1-Habilitar Feature no Banco
Ao habilitar, o sql cria um esquema "cdc" para armazenar as tabelas espelho.
As tabelas espelho são réplicas das tabelas monitoradas, com algumas colunas adicionais com metadados das operações realizadas.
Para o log de alteração ser monitorado corretamente, deve ser habilitado o serviço SQL Agent.
As tabelas espelho são réplicas das tabelas monitoradas, com algumas colunas adicionais com metadados das operações realizadas.
Para o log de alteração ser monitorado corretamente, deve ser habilitado o serviço SQL Agent.
2-Habilitar CDC nas Tabelas
* capture_instance HumanResources_Employee' é uma tabela sombra/espelho que armazena as alterações
* essa tabela tem a mesma estrutura, mais alguns campos adicionais como tipo da operação realizada (insert, delete, update). Esses metadados serão usados pelos componentes do SSIS para tratar a importação
* o SQL busca os dados alterados do log do banco de forma assíncrona nos momentos de menor atividade do banco, isso faz com que o processo seja transparente para o banco.
Os componentes do SSIS fazem a leitura das tabelas sombra também chamadas de "Capture_Instances"
Para consultar um tabela espelho
SELECT * FROM cdc.HumanResources_Employee_CT;
Entre os metadados, esta é a coluna que mapeia a operação DML realizada
__$operation column
1 = delete,
2 = insert,
3 = update [valor anterior],
4 = update [novo valor],
and 5 = merge
As operações de update armazenam a versão atual e a nova versão do registro, são 2 linhas para cada alteração.
3-Alterando um valor da tabela monitorada
--Atualizações
UPDATE HumanResources.Employee
SET HireDate = DATEADD(day, 1, HireDate)
WHERE [BusinessEntityID] IN (1, 2, 3);
Usando CDC no SSIS
O SSIS utiliza os seguintes componentes:
CDC Control Task: para configurar o fluxo de dados marcando o início e o fim do processamento. Existem várias opções de configuração para este componente, o mais importante é entender qual tipo de operação utilizar e em qual ponto no fluxo de dados
- Mark Initial Load Start: informa que será feita a carga inicial full
- Mark Initial Load End: informa que a carga inicial full foi concluida
- Mark CDC Start: Marca o início
- Get Processing Range: marca o início de uma nova carga incremental, a partir da marca do fim da anterior
- Mark Processed Range: marca o fim do processamento incremental
Ao configurar o CDC Control, será criada uma tabela que controla o estado das mudanças
CDC Source: data flow task que se conecta às tabelas espelho para importar as alterações CDC
CDC Spliter: cria data paths para cada tipo de operação capturada (insert, delete, updade)
Carga Inicial
- criar a conexão,a tabela CDC state e a variável
Na primeira carga usar Mark Initial Load Start e Mark Initial Load End
Cargas Incrementais
Após rodar a carga full apenas uma vez, a carga incremental entra em ação.
A primeira atividade é criar um etapa CDC Control com a opção Get Processing Range para marcar o início da carga incremental.
Na mesma tela são criadas a fonte de dados ADO, a variável do tipo string e uma tabela de states, tudo para apoiar o processo CDC.
Com a etapa anterior configurada, a etapa de extração pode irá ser feita. Inclua um data flow task, mas fontes de dados, inclua CDC Source para extrair da tabela sombra.
Na configuração da fonte, marcar a tabela que esta sendo monitorada e a variável.
Com a fonte configurada, conecte com um CDC Split para identificar os tipos de operações.
Com as saídas identificadas, basta enviar para o destino os registros novos, os atualizados podem ser usados em um comando UPDATE na base de destino e os registros do caminho DELETE podem opcionalmente ser usado para deletar no destino, ou atualizar alguma coluna de controle.
Muito Obrigado pelas dicas
ResponderExcluir