De functie maken om de wijzigingsgegevens op te halen

Van toepassing op:SQL Server SSIS Integration Runtime in Azure Data Factory

Na het voltooien van de controlestroom voor een Integration Services-pakket dat een incrementele belasting van wijzigingsgegevens uitvoert, is de volgende taak het maken van een functie met tabelwaarde (TVF) waarmee de wijzigingsgegevens worden opgehaald. U hoeft deze functie slechts één keer te maken vóór de eerste incrementele belasting.

Ontwerpoverwegingen voor CDC-functies (Change Data Capture)

Als u wijzigingsgegevens wilt ophalen, roept een brononderdeel in de gegevensstroom van het pakket een van de volgende queryfuncties voor het vastleggen van wijzigingengegevens aan:

• <cdc.fn_cdc_get_net_changes_capture_instance> Voor deze query bevat de enkele rij die voor elke update wordt geretourneerd de uiteindelijke status van elke gewijzigde rij. In de meeste gevallen hebt u alleen de gegevens nodig die worden geretourneerd door een query voor nettowijzigingen. Zie cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL) voor meer informatie.

• <cdc.fn_cdc_get_all_changes_capture_instance> Deze query retourneert alle wijzigingen die in elke rij zijn opgetreden tijdens het opnameinterval. Zie cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL) voor meer informatie.

Het brononderdeel neemt vervolgens de resultaten die door de functie worden geretourneerd en geeft deze door aan downstreamtransformaties en bestemmingen, die de wijzigingsgegevens toepassen op de uiteindelijke bestemming.

Een Integration Services-brononderdeel kan deze functies voor het vastleggen van wijzigingen echter niet rechtstreeks aanroepen. Een Integration Services-brononderdeel vereist metagegevens over de kolommen die door de query worden geretourneerd. De functies voor het vastleggen van wijzigingengegevens definiëren niet de kolommen van de uitvoertabel. Deze functies retourneren dus onvoldoende metagegevens voor een Integration Services-brononderdeel.

In plaats daarvan gebruikt u een wrapper-functie met tabelwaarde, omdat dit type functie expliciet de kolommen van de uitvoertabel in de RETURNS-component definieert. Deze expliciete definitie van kolommen biedt de metagegevens die een Integration Services-brononderdeel nodig heeft. U moet deze functie maken voor elke tabel waarvoor u wijzigingsgegevens wilt ophalen.

U hebt twee opties voor het maken van de wrapper-functie met tabelwaarde die de queryfunctie change data capture aanroept:

• U kunt de door het sys.sp_cdc_generate_wrapper_function systeem opgeslagen procedure aanroepen om de tabelwaardefuncties voor u te maken.

• U kunt uw eigen tabelwaardefunctie schrijven met behulp van de richtlijnen en het voorbeeld in dit onderwerp.

Een opgeslagen procedure aanroepen om de tabelwaardefunctie te maken

De snelste en eenvoudigste manier om de tabelwaardefuncties te maken die u nodig hebt, is het aanroepen van de door het sys.sp_cdc_generate_wrapper_function systeem opgeslagen procedure. Met deze opgeslagen procedure worden scripts gegenereerd voor het maken van wrapperfuncties die specifiek zijn ontworpen om te voldoen aan de behoeften van een Integration Services-brononderdeel.

Als u wilt weten hoe u deze opgeslagen procedure van het systeem gebruikt, moet u begrijpen wat de procedure doet, welke scripts de procedure genereert en welke wrapperfuncties de scripts maken.

De opgeslagen procedure begrijpen en gebruiken

De sys.sp_cdc_generate_wrapper_function door het systeem opgeslagen procedure genereert scripts voor het maken van wrapperfuncties voor gebruik door Integration Services-pakketten.

Hier volgen de eerste regels van de definitie van de opgeslagen procedure:

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
)

Alle parameters voor de opgeslagen procedure zijn optioneel. Als u de opgeslagen procedure aanroept zonder waarden op te geven voor een van de parameters, maakt de opgeslagen procedure wrapperfuncties voor alle capture-exemplaren waartoe u toegang hebt.

De opgeslagen procedure genereert altijd een wrapper-functie om alle wijzigingen van elke capture-instantie te retourneren. Als de parameter @supports_net_changes is ingesteld toen het capture-exemplaar werd gemaakt, genereert de opgeslagen procedure ook een wrapper-functie om nettowijzigingen te retourneren van elke toepasselijke capture-instantie.

De opgeslagen procedure retourneert een resultatenset met twee kolommen:

• De naam van de wrapper-functie die door de opgeslagen procedure is gegenereerd. Deze stored procedure leidt de functienaam af van de naam van de capture-instantie. (De functienaam is 'fn_all_changes_' gevolgd door de naam van het capture-exemplaar. Het voorvoegsel dat wordt gebruikt voor de functie netwijzigingen, als deze wordt gemaakt, is 'fn_net_changes_'.)

• De CREATE-instructie voor de wrapper-functie.

De scripts begrijpen en gebruiken die zijn gemaakt door de opgeslagen procedure

Normaal gesproken gebruikt een ontwikkelaar een INSERT... EXEC-instructie voor het aanroepen van de sys.sp_cdc_generate_wrapper_function opgeslagen procedure en het opslaan van de scripts die door de opgeslagen procedure in een tijdelijke tabel worden gemaakt. Elk script kan vervolgens afzonderlijk worden geselecteerd en uitgevoerd om de bijbehorende wrapper-functie te maken. Een ontwikkelaar kan echter ook één set SQL-opdrachten gebruiken om alle CREATE-scripts uit te voeren, zoals wordt weergegeven in de volgende voorbeeldcode:

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  

Informatie over en het gebruik van de functies die zijn gemaakt door de opgeslagen procedure

Om de tijdlijn van vastgelegde wijzigingsgegevens systematisch te doorlopen, verwachten de gegenereerde wrapper-functies dat de parameter @end_time voor één interval de parameter @start_time voor het volgende interval is. Wanneer deze conventie wordt gevolgd, kunnen de gegenereerde wrapperfuncties de volgende taken uitvoeren:

• Wijs de datum-/tijdwaarden toe aan de LSN-waarden die intern worden gebruikt.

• Zorg ervoor dat er geen gegevens verloren gaan of herhaald worden.

Om query's voor alle rijen van een wijzigingstabel eenvoudiger te maken, ondersteunen de gegenereerde wrapperfuncties ook de volgende conventies:

• Als de @start_time parameter null is, gebruiken de wrapper-functies de laagste LSN-waarde in het capture-exemplaar als de ondergrens van de query.

• Als de @end_time parameter null is, gebruiken de wrapper-functies de hoogste LSN-waarde in het capture-exemplaar als de bovengrens van de query.

• Als de waarde van de parameter @start_time of @end_time buiten het tijdstip van de laagste of hoogste LSN valt, zal de uitvoering van de gegenereerde wrapperfuncties resulteren in fout 313: Msg 313, Level 16, State 3, Line 1 An insufficient number of arguments were supplied for the procedure or function. Deze fout moet worden verwerkt door de ontwikkelaar.

De meeste gebruikers moeten de wrapper-functies kunnen gebruiken die door de opgeslagen procedure van het sys.sp_cdc_generate_wrapper_function systeem worden gemaakt zonder dat ze hoeven te worden gewijzigd. Als u echter de wrapper-functies wilt aanpassen, moet u de CREATE-scripts aanpassen voordat u de scripts uitvoert.

Wanneer uw pakket de wrapperfuncties aanroept, moet het pakket waarden opgeven voor drie parameters. Deze drie parameters komen overeen met de drie parameters die de change data capture-functies gebruiken. Deze drie parameters zijn als volgt:

• De begindatum/tijdwaarde en de einddatum/tijdwaarde voor het interval. Terwijl de wrapper-functies datum-/tijdwaarden gebruiken als de eindpunten voor het queryinterval, gebruiken de functies voor het vastleggen van gegevens twee LSN-waarden als eindpunten.

• Het rijenfilter. Voor zowel de wrapper-functies als de functies voor het vastleggen van wijzigingsgegevens is de parameter @row_filter_option hetzelfde. Zie cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL) en cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL) voor meer informatie.

De resultatenset die door de wrapper-functies wordt geretourneerd, bevat de volgende gegevens:

• Alle aangevraagde kolommen met wijzigingsgegevens.

• Een kolom met de naam __CDC_OPERATION die gebruikmaakt van een veld met één of twee tekens om de bewerking te identificeren die aan de rij is gekoppeld. De geldige waarden voor dit veld zijn als volgt: 'I' voor invoegen, 'D' voor verwijderen, 'UO' voor het bijwerken van oude waarden en 'UN' voor het bijwerken van nieuwe waarden.

• Updatevlaggen, wanneer u deze aanvraagt, die als bitkolommen verschijnen na de opcode en in de volgorde die is opgegeven in de parameter @update_flag_list. Deze kolommen worden benoemd door '_uflag' toe te voegen aan de naam van de gekoppelde kolom.

Als uw pakket een wrapper-functie aanroept waarmee query's worden uitgevoerd op alle wijzigingen, retourneert de wrapper-functie ook de kolommen, __CDC_STARTLSN en __CDC_SEQVAL. Deze twee kolommen worden respectievelijk de eerste en tweede kolommen van de resultatenset. De wrapper-functie sorteert ook de resultatenset op basis van deze twee kolommen.

Uw eigen tabelwaardefunctie schrijven

U kunt ook SQL Server Management Studio gebruiken om uw eigen wrapperfunctie met tabelwaarde te schrijven waarmee de queryfunctie change data capture wordt aangeroepen en de wrapper-functie met tabelwaarde opslaat in SQL Server. Zie (Transact-SQL) voor meer informatie over het maken van een Transact-SQL-functieCREATE FUNCTION.

In het volgende voorbeeld wordt een tabelwaardefunctie gedefinieerd waarmee wijzigingen worden opgehaald uit een klanttabel voor het opgegeven wijzigingsinterval. Deze functie maakt gebruik van change data capture-functies om de datum/tijd-waarden toe te wijzen aan de LSN-waarden (Binary Log Sequence Number) die de wijzigingstabellen intern gebruiken. Deze functie verwerkt ook verschillende speciale voorwaarden:

• Wanneer een null-waarde wordt doorgegeven voor de begintijd, gebruikt deze functie de vroegste beschikbare waarde.

• Wanneer een null-waarde wordt doorgegeven voor de eindtijd, gebruikt deze functie de meest recente beschikbare waarde.

• Wanneer de begin-LSN gelijk is aan de eind-LSN, wat meestal aangeeft dat er geen records zijn voor het geselecteerde interval, wordt deze functie afgesloten.

Voorbeeld van een tabelwaardefunctie die wijzigingsgegevens opvraagt

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  
  

Aanvullende metagegevens ophalen met de wijzigingsgegevens

Hoewel de door de gebruiker gemaakte tabelwaardefunctie die eerder wordt weergegeven alleen de kolom __$operation gebruikt, retourneert de cdc.fn_cdc_get_net_changes_<capture_instance> functie vier kolommen met metagegevens voor elke wijzigingsrij. Als u deze waarden in uw gegevensstroom wilt gebruiken, kunt u deze retourneren als extra kolommen van de wrapper-functie met tabelwaarde.

Zie cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL) voor meer informatie.

Volgende stap

Nadat u de tabelwaardefunctie hebt gemaakt die query's uitvoert op wijzigingsgegevens, is de volgende stap het ontwerpen van de gegevensstroom in het pakket.

Volgend onderwerp:De wijzigingsgegevens ophalen en begrijpen