Crie a função para recuperar os dados de alteração

Aplica-se a: SQL Server SSIS Integration Runtime em Azure Data Factory

Após completar o fluxo de controlo para um pacote de Serviços de Integração que realiza uma carga incremental de dados de alteração, a tarefa seguinte é criar uma função de valores de tabela (TVF) que recupere os dados de alteração. Só tens de criar esta função uma vez antes da primeira carga incremental.

Considerações de conceção para funções de captura de dados de alteração (CDC)

Para recuperar dados de alteração, um componente de origem no fluxo de dados do pacote chama uma das seguintes funções de consulta de captura de dados de alteração:

• cdc.fn_cdc_get_net_changes_<capture_instance> Para esta consulta, a única linha devolvida para cada atualização contém o estado final de cada linha alterada. Na maioria dos casos, só precisa dos dados devolvidos por uma consulta para alterações líquidas. Para mais informações, consulte cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

• cdc.fn_cdc_get_all_changes_<capture_instance> Esta consulta devolve todas as alterações que ocorreram em cada linha durante o intervalo de captura. Para mais informações, consulte cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL).

O componente de origem então recolhe os resultados devolvidos pela função e passa-os para transformações e destinos a jusante, que aplicam os dados de alteração ao destino final.

No entanto, um componente de origem dos Serviços de Integração não pode chamar diretamente estas funções de captura de dados de alterações. Um componente fonte de Serviços de Integração requer metadados sobre as colunas que a consulta devolve. As funções de captura de dados de alteração não definem as colunas da sua tabela de saída. Assim, estas funções não retornam metadados suficientes para um componente fonte dos Serviços de Integração.

Em vez disso, usas uma função wrapper com valores de tabela porque este tipo de função define explicitamente as colunas da sua tabela de saída na sua cláusula RETURNS. Esta definição explícita de colunas fornece os metadados que um componente fonte dos Serviços de Integração necessita. Tens de criar esta função para cada tabela para a qual queres recuperar os dados de alteração.

Dispõe de duas opções para criar a função encapsuladora com valor de tabela que chama a função de consulta de captura de dados de alterações:

• Podes chamar o sys.sp_cdc_generate_wrapper_function procedimento armazenado do sistema para criar as funções de valores de tabela para ti.

• Pode escrever a sua própria função de valores em tabela usando as diretrizes e o exemplo deste tópico.

Chamar um procedimento armazenado para criar a função com valores de tabela

A forma mais rápida e fácil de criar as funções com valores de tabela de que precisa é chamar o sys.sp_cdc_generate_wrapper_function procedimento armazenado do sistema. Este procedimento armazenado gera scripts para criar funções wrapper que são especificamente concebidas para satisfazer as necessidades de um componente fonte de Serviços de Integração.

Para compreender como utilizar este procedimento armazenado do sistema, deve compreender o que o procedimento faz, que scripts o procedimento gera e que funções de encapsulamento os scripts criam.

Compreender e utilizar o procedimento armazenado

O sys.sp_cdc_generate_wrapper_function procedimento armazenado do sistema gera scripts para criar funções envolventes para uso por pacotes de Serviços de Integração.

Aqui estão as primeiras linhas da definição do procedimento armazenado:

CREATE PROCEDURE sys.sp_cdc_generate_wrapper_function
(
@capture_instance sysname = null
@closed_high_end_point bit = 1,
@column_list = null,
@update_flag_list = null
)

Todos os parâmetros para o procedimento armazenado são opcionais. Se chamar o procedimento armazenado sem fornecer valores para nenhum dos parâmetros, o procedimento armazenado cria funções de encapsulamento para todas as instâncias de captura a que tem acesso.

O procedimento armazenado gera sempre uma função wrapper para devolver todas as alterações de cada instância de captura. Se o parâmetro @supports_net_changes tiver sido definido quando a instância de captura foi criada, o procedimento armazenado também gera uma função de encapsulamento para retornar as alterações líquidas de cada instância de captura à qual se aplique.

O procedimento armazenado devolve um conjunto de resultados com duas colunas:

• O nome da função wrapper que o procedimento armazenado gerou. Este procedimento armazenado deriva o nome da função a partir do nome da instância de captura. (O nome da função é 'fn_all_changes_' seguido do nome da instância de captura. O prefixo usado para a função de alterações líquidas, se for criada, é 'fn_net_changes_'.)

• A instrução CREATE para a função wrapper.

Compreender e utilizar os scripts criados pelo procedimento armazenado

Normalmente, um desenvolvedor usaria um INSERT... EXEC para chamar o sys.sp_cdc_generate_wrapper_function procedimento armazenado e guardar os scripts que o procedimento armazenado cria numa tabela temporária. Cada script podia então ser selecionado individualmente e executado para criar a função wrapper correspondente. No entanto, um programador também poderia usar um conjunto de comandos SQL para executar todos os scripts CREATE, como mostrado no seguinte código de exemplo:

create table #wrapper_functions  
      (function_name sysname, create_stmt nvarchar(max))  
insert into #wrapper_functions  
exec sys.sp_cdc_generate_wrapper_function  
  
declare @stmt nvarchar(max)  
declare #hfunctions cursor local fast_forward for   
      select create_stmt from #wrapper_functions  
open #hfunctions  
fetch #hfunctions into @stmt  
while (@@fetch_status <> -1)  
begin  
      exec sp_executesql @stmt  
      fetch #hfunctions into @stmt  
end  
close #hfunctions  
deallocate #hfunctions  

Compreender e utilizar as funções criadas pelo procedimento armazenado

Para percorrer sistematicamente a linha temporal dos dados de alteração capturados, as funções wrapper geradas esperam que o parâmetro @end_time para um intervalo seja o parâmetro @start_time para o intervalo seguinte. Quando esta convenção é seguida, as funções de envolvimento geradas podem realizar as seguintes tarefas:

• Mapeie os valores de data/hora para os valores LSN usados internamente.

• Certifique-se de que nenhum dado é perdido ou repetido.

Para simplificar a consulta de todas as linhas de uma tabela de alterações, as funções encapsuladoras geradas também suportam as seguintes convenções:

• Se o @start_time parâmetro for nulo, as funções wrapper usam o valor LSN mais baixo na instância de captura como limite inferior da consulta.

• Se o @end_time parâmetro for nulo, as funções do wrapper usam o valor LSN mais alto na instância de captura como limite superior da consulta.

• Se o valor do parâmetro @start_time ou @end_time estiver fora do intervalo entre o tempo do LSN mais baixo e o do LSN mais alto, a execução das funções wrapper geradas resultará no erro 313: Msg 313, Level 16, State 3, Line 1 An insufficient number of arguments were supplied for the procedure or function. Este erro deve ser tratado pelo desenvolvedor.

A maioria dos utilizadores deve ser capaz de usar as funções de wrapper que o sys.sp_cdc_generate_wrapper_function procedimento armazenado do sistema cria sem modificações. No entanto, para personalizar as funções do wrapper, tem de personalizar os scripts CREATE antes de executar os scripts.

Quando o seu pacote chama as funções do wrapper, o pacote deve fornecer valores para três parâmetros. Estes três parâmetros são semelhantes aos três parâmetros que as funções de captura de dados de alteração utilizam. Estes três parâmetros são os seguintes:

• O valor da data/hora de início e o valor da data/hora de fim para o intervalo. Enquanto as funções wrapper usam valores data/hora como pontos finais para o intervalo de consulta, as funções de captura de dados de alteração usam dois valores LSN como pontos finais.

• O filtro de linha. Tanto para as funções de encapsulamento como para as funções de captura de dados de alterações, o parâmetro @row_filter_option é o mesmo. Para mais informações, consulte cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL) e cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

O conjunto de resultados devolvido pelas funções do wrapper inclui os seguintes dados:

• Todas as colunas solicitadas de dados de alteração.

• Uma coluna chamada __CDC_OPERATION que utiliza um campo de um ou dois caracteres para identificar a operação associada à linha. Os valores válidos para este campo são os seguintes: 'I' para inserir, 'D' para eliminar, 'UO' para atualizar valores antigos e 'UN' para atualizar valores novos.

• Sinalizadores de atualização, quando os pedir, que aparecem sob a forma de colunas de bits após o código de operação e pela ordem especificada no parâmetro @update_flag_list. Estas colunas são nomeadas acrescentando '_uflag' ao nome da coluna associada.

Se o seu pacote chamar uma função wrapper que consulta todas as alterações, a função wrapper também devolve as colunas, __CDC_STARTLSN e __CDC_SEQVAL. Estas duas colunas tornam-se, respetivamente, a primeira e a segunda colunas do conjunto de resultados. A função wrapper também ordena o conjunto de resultados com base nestas duas colunas.

Escrever a sua própria função de valor de tabela

Também pode utilizar o SQL Server Management Studio para escrever a sua própria função de encapsulamento com valor de tabela que invoque a função de consulta de Change Data Capture e armazenar essa função no SQL Server. Para mais informações sobre como criar uma função Transact-SQL, veja CREATE FUNCTION (Transact-SQL).

O exemplo seguinte define uma função com valores de tabela que recupera alterações de uma tabela Customer para o intervalo de alteração especificado. Esta função utiliza funções de captura de dados de alteração para mapear os valores de data-hora para os valores binários de número de sequência logarítmica (LSN) que as tabelas de alteração usam internamente. Esta função também gere várias condições especiais:

• Quando um valor nulo é passado para a hora de início, esta função utiliza o valor mais antigo disponível.

• Quando um valor nulo é passado para o tempo final, esta função utiliza o valor disponível mais recente.

• Quando o LSN inicial é igual ao LSN final, o que normalmente indica que não há registos para o intervalo selecionado, esta função encerra-se.

Exemplo de uma função de valor de tabela que consulta dados de alteração

CREATE function CDCSample.uf_Customer (  
     @start_time datetime  
    ,@end_time datetime  
)  
returns @Customer table (  
     CustomerID int  
    ,TerritoryID int  
    ,CustomerType nchar(1)  
    ,rowguid uniqueidentifier  
    ,ModifiedDate datetime  
    ,CDC_OPERATION varchar(1)  
) as  
begin  
    declare @from_lsn binary(10), @to_lsn binary(10)  
  
    if (@start_time is null)  
        select @from_lsn = sys.fn_cdc_get_min_lsn('Customer')  
    else  
        select @from_lsn = sys.fn_cdc_increment_lsn(sys.fn_cdc_map_time_to_lsn('largest less than or equal',@start_time))  
  
    if (@end_time is null)  
        select @to_lsn = sys.fn_cdc_get_max_lsn()  
    else  
        select @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@end_time)  
  
    if (@from_lsn = sys.fn_cdc_increment_lsn(@to_lsn))  
        return  
  
    -- Query for change data  
    insert into @Customer  
    select   
        CustomerID,      
        TerritoryID,   
        CustomerType,   
        rowguid,   
        ModifiedDate,   
        case __$operation  
                when 1 then 'D'  
                when 2 then 'I'  
                when 4 then 'U'  
                else null  
         end as CDC_OPERATION  
    from   
        cdc.fn_cdc_get_net_changes_Customer(@from_lsn, @to_lsn, 'all')  
  
    return  
end   
go  
  

Recuperação de metadados adicionais com os dados alterados

Embora a função criada pelo utilizador com valores de tabela mostrada anteriormente use apenas a coluna __$operation , a cdc.fn_cdc_get_net_changes_<capture_instance> função devolve quatro colunas de metadados para cada linha de alteração. Se quiser usar estes valores no seu fluxo de dados, pode devolvê-los como colunas adicionais da função de wrapper com valores de tabela.

Para mais informações, consulte cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

Próximo Passo

Depois de criar a função de valores de tabela que consulta dados de alteração, o passo seguinte é começar a desenhar o fluxo de dados no pacote.

Próximo tópico:Recuperar e Compreender os Dados de Alteração