변경 데이터 검색을 위한 함수 만들기

아티클
07/12/2024

적용 대상: SQL Server Azure Data Factory의 SSIS Integration Runtime

변경 데이터의 증분 로드를 수행하는 Integration Services 패키지에 대한 제어 흐름을 완료한 후 다음 작업은 변경 데이터를 검색하는 TVF(테이블 반환 함수)를 만드는 것입니다. 이 함수는 첫 번째 증분 로드 전에 한 번만 만들어야 합니다.

참고 항목

변경 데이터를 검색하는 함수를 만드는 것은 변경 데이터의 증분 로드를 수행하는 패키지를 만드는 프로세스의 두 번째 단계입니다. 이 패키지를 만들기 위한 전체 프로세스에 대한 설명은 변경 데이터 캡처(SSIS)를 참조하세요.

CDC(변경 데이터 캡처) 함수에 대한 디자인 고려 사항

변경 데이터를 검색하기 위해 패키지의 데이터 흐름에 있는 원본 구성 요소는 다음 변경 데이터 캡처 쿼리 함수 중 하나를 호출합니다.

cdc.fn_cdc_get_net_changes_<capture_instance> 이 쿼리의 경우 각 업데이트에 대해 반환된 단일 행에는 변경된 각 행의 최종 상태가 포함됩니다. 대부분의 경우 순 변경 내용에 대해 쿼리에서 반환하는 데이터만 필요합니다. 자세한 내용은 cdc.fn_cdc_get_net_changes_<capture_instance>(Transact-SQL)를 참조하세요.
cdc.fn_cdc_get_all_changes_<capture_instance> 이 쿼리는 캡처 간격 동안 각 행에 발생한 모든 변경 내용을 반환합니다. 자세한 내용은 cdc.fn_cdc_get_all_changes_<capture_instance>(Transact-SQL)를 참조하세요.

그런 다음 원본 구성 요소는 함수에서 반환한 결과를 다운스트림 변환 및 대상으로 전달하여 변경 데이터를 최종 대상에 적용합니다.

그러나 Integration Services 원본 구성 요소는 이러한 변경 데이터 캡처 함수를 직접 호출할 수 없습니다. Integration Services 원본 구성 요소에는 쿼리가 반환하는 열에 대한 메타데이터가 필요합니다. 변경 데이터 캡처 함수는 출력 테이블의 열을 정의하지 않습니다. 따라서 이러한 함수는 Integration Services 원본 구성 요소에 대한 충분한 메타데이터를 반환하지 않습니다.

대신 이러한 종류의 함수는 RETURNS 절에서 출력 테이블의 열을 명시적으로 정의하므로 테이블 반환 래퍼 함수를 사용합니다. 열의 이 명시적 정의는 Integration Services 원본 구성 요소에 필요한 메타데이터를 제공합니다. 변경 데이터를 검색하려는 각 테이블에 대해 이 함수를 만들어야 합니다.

변경 데이터 캡처 쿼리 함수를 호출하는 테이블 반환 래퍼 함수를 만드는 두 가지 옵션이 있습니다.

sys.sp_cdc_generate_wrapper_function 시스템 저장 프로시저를 호출하여 테이블 반환 함수를 만들 수 있습니다.
이 항목의 지침 및 예제를 사용하여 고유한 테이블 반환 함수를 작성할 수 있습니다.

저장 프로시저를 호출하여 테이블 반환 함수 만들기

필요한 테이블 반환 함수를 만드는 가장 빠르고 쉬운 방법은 sys.sp_cdc_generate_wrapper_function 시스템 저장 프로시저를 호출하는 것입니다. 이 저장 프로시저는 Integration Services 원본 구성 요소의 요구 사항을 충족하도록 특별히 설계된 래퍼 함수를 만드는 스크립트를 생성합니다.

Important

sys.sp_cdc_generate_wrapper_function 시스템 저장 프로시저는 래퍼 함수를 직접 만들지 않습니다. 대신 저장 프로시저는 래퍼 함수에 대한 CREATE 스크립트를 생성합니다. 개발자는 증분 로드 패키지가 래퍼 함수를 호출하기 전에 저장 프로시저에서 생성하는 CREATE 스크립트를 실행해야 합니다.

이 시스템 저장 프로시저를 사용하는 방법을 이해하려면 프로시저가 수행하는 작업, 프로시저에서 생성하는 스크립트 및 스크립트가 만드는 래퍼 함수를 이해해야 합니다.

저장 프로시저 이해 및 사용

sys.sp_cdc_generate_wrapper_function 시스템 저장 프로시저는 Integration Services 패키지에서 사용할 래퍼 함수를 만드는 스크립트를 생성합니다.

다음은 저장 프로시저 정의의 처음 몇 줄입니다.

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
)

저장 프로시저의 모든 매개 변수는 선택 사항입니다. 매개 변수에 대한 값을 제공하지 않고 저장 프로시저를 호출하는 경우 저장 프로시저는 액세스 권한이 있는 모든 캡처 인스턴스에 대한 래퍼 함수를 만듭니다.

참고 항목

이 저장 프로시저의 구문과 해당 매개 변수에 대한 자세한 내용은 sys.sp_cdc_generate_wrapper_function(Transact-SQL)을 참조하세요.

저장 프로시저는 항상 각 캡처 인스턴스의 모든 변경 내용을 반환하는 래퍼 함수를 생성합니다. 캡처 인스턴스를 만들 때 @supports_net_changes 매개 변수가 설정된 경우에는 각 해당 캡처 인스턴스의 순 변경을 반환하는 래퍼 함수도 생성됩니다.

저장 프로시저는 두 개의 열이 있는 결과 집합을 반환합니다.

저장 프로시저에서 생성한 래퍼 함수의 이름. 이 저장 프로시저는 캡처 인스턴스 이름의 이름에서 함수 이름을 파생합니다. (함수 이름은 'fn_all_changes_'이고 그 뒤에 캡처 인스턴스 이름이 옵니다. net changes 함수에 사용되는 접두사는 만들어진 경우 'fn_net_changes_'입니다.)
래퍼 함수에 대한 CREATE 문입니다.

저장 프로시저에서 만든 스크립트 이해 및 사용

일반적으로 개발자는 INSERT...EXEC 문을 사용하여 sys.sp_cdc_generate_wrapper_function 저장 프로시저를 호출하고 저장 프로시저에서 만든 스크립트를 임시 테이블에 저장합니다. 그런 다음 각 스크립트를 개별적으로 선택하고 실행하여 해당 래퍼 함수를 만들 수 있습니다. 그러나 개발자는 다음 샘플 코드와 같이 하나의 SQL 명령 집합을 사용하여 모든 CREATE 스크립트를 실행할 수도 있습니다.

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

저장 프로시저에서 만든 함수 이해 및 사용

캡처된 변경 데이터 시간대를 체계적으로 탐색하기 위해 생성된 래퍼 함수는 한 간격의 @end_time 매개 변수가 후속 간격의 @start_time 매개 변수가 될 것으로 예상합니다. 이 규칙을 따르는 경우 생성된 래퍼 함수는 다음 작업을 수행할 수 있습니다.

날짜/시간 값을 내부적으로 사용되는 LSN 값에 매핑합니다.
데이터가 손실되거나 반복되지 않도록 합니다.

변경 테이블의 모든 행에 대한 쿼리를 더 간단하게 만들기 위해 생성된 래퍼 함수는 다음 규칙도 지원합니다.

@start_time 매개 변수가 null이면 래퍼 함수는 캡처 인스턴스에서 가장 낮은 LSN 값을 쿼리의 하한으로 사용합니다.
@end_time 매개 변수가 null이면 래퍼 함수는 캡처 인스턴스에서 가장 높은 LSN 값을 쿼리의 상한으로 사용합니다.
@start_time 또는 @end_time 매개 변수 값이 가장 낮은 LSN 또는 가장 높은 LSN의 시간을 초과하면 생성된 래퍼 함수의 실행은 오류 313 Msg 313, Level 16, State 3, Line 1 An insufficient number of arguments were supplied for the procedure or function에서 반환됩니다. 이 오류는 개발자가 처리해야 합니다.

대부분의 사용자는 sys.sp_cdc_generate_wrapper_function 시스템 저장 프로시저에서 만드는 래퍼 함수를 수정하지 않고 그대로 사용할 수 있습니다. 그러나 래퍼 함수를 사용자 지정하려면 스크립트를 실행하기 전에 CREATE 스크립트를 사용자 지정해야 합니다.

패키지가 래퍼 함수를 호출할 때 패키지는 세 개의 매개 변수에 대한 값을 제공해야 합니다. 이러한 세 가지 매개 변수는 변경 데이터 캡처 함수에서 사용하는 세 가지 매개 변수와 비슷합니다. 이러한 세 가지 매개 변수는 다음과 같습니다.

간격의 시작 날짜/시간 값 및 종료 날짜/시간 값. 래퍼 함수는 날짜/시간 값을 쿼리 간격의 끝점으로 사용하지만 변경 데이터 캡처 함수는 두 개의 LSN 값을 끝점으로 사용합니다.
행 필터입니다. 래퍼 함수와 변경 데이터 캡처 함수의 @row_filter_option 매개 변수는 서로 동일합니다. 자세한 내용은 cdc.fn_cdc_get_all_changes_<capture_instance>(Transact-SQL) 및 cdc.fn_cdc_get_net_changes_<capture_instance>(Transact-SQL)를 참조하세요.

래퍼 함수에서 반환하는 결과 집합에는 다음과 같은 데이터가 포함됩니다.

변경 데이터의 요청된 모든 열입니다.
행과 연결된 작업을 식별하기 위해 1자 또는 2자 필드를 사용하는 __CDC_OPERATION 열입니다. 이 필드에서 유효한 값은 다음과 같습니다. 'I'는 삽입, 'D'는 삭제, 'UO'는 이전 값 업데이트 및 'UN'은 새 값 업데이트입니다.
요청 시 작업 코드 뒤에 @update_flag_list 매개 변수에 지정된 순서대로 비트 열로 표시되는 업데이트 플래그. 이러한 열의 이름은 연결된 열 이름에 '_uflag'를 추가하여 지정됩니다.

패키지가 모든 변경 내용을 쿼리하는 래퍼 함수를 호출하는 경우 래퍼 함수는 열, __CDC_STARTLSN 및 __CDC_SEQVAL도 반환합니다. 이러한 두 열은 각각 결과 집합의 첫 번째 및 두 번째 열이 됩니다. 래퍼 함수는 이러한 두 열을 기반으로 결과 집합을 정렬합니다.

고유한 테이블 반환 함수 작성

SQL Server Management Studio를 사용하여 변경 데이터 캡처 쿼리 함수를 호출하는 고유한 테이블 반환 래퍼 함수를 작성하고 테이블 반환 래퍼 함수를 SQL Server에 저장할 수도 있습니다. TRANSACT-SQL 함수를 만드는 방법에 대한 자세한 내용은 CREATE FUNCTION(Transact-SQL)을 참조하세요.

다음 예제에서는 지정된 변경 간격에 대해 Customer 테이블에서 변경 내용을 검색하는 테이블 반환 함수를 정의합니다. 이 함수는 변경 데이터 캡처 함수를 사용하여 변경 테이블이 내부적으로 사용하는 이진 LSN(로그 시퀀스 번호) 값에 datetime 값을 매핑합니다. 또한 이 함수는 다음과 같은 몇 가지 특수 상황을 처리합니다.

시작 시간에 대해 Null 값이 전달되는 경우 이 함수는 사용할 수 있는 가장 오래된 값을 사용합니다.
종료 시간에 대해 Null 값이 전달되는 경우 이 함수는 사용할 수 있는 최근 값을 사용합니다.
일반적으로 선택한 간격에 대한 레코드가 없음을 나타내는 시작 LSN이 끝 LSN과 같으면 이 함수는 종료됩니다.

변경 데이터를 쿼리하는 테이블 반환 함수의 예

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

변경 데이터를 사용하여 추가 메타데이터 검색

위에 나와 있는 사용자가 만든 테이블 반환 함수는 __$operation 열을 사용하지만 cdc.fn_cdc_get_net_changes_<capture_instance> 함수는 각 변경 행의 메타데이터 열 4개를 반환합니다. 데이터 흐름에서 이러한 값을 사용하려는 경우 테이블 반환 래퍼 함수에서 추가 열로 반환할 수 있습니다.

열 이름	데이터 형식	설명
__$start_lsn	binary(10)	변경에 대한 커밋 트랜잭션과 연결된 LSN입니다. 동일한 트랜잭션에서 커밋된 모든 변경 사항은 동일한 커밋 LSN을 공유합니다. 예를 들어 원본 테이블의 업데이트 작업에서 두 개의 서로 다른 행을 수정하는 경우 변경 테이블에는 각각 동일한 __$start_lsn 값을 가진 4개의 행(이전 값이 있는 행 2개, 새 값이 있는 행 2개)이 포함됩니다.
__$seqval	binary(10)	트랜잭션의 행 변경 내용을 정렬하는 데 사용되는 시퀀스 값입니다.
__$operation	int	변경과 관련된 DML(데이터 조작 언어) 작업입니다. 다음 중 하나일 수 있습니다. 1 = 삭제 2 = 삽입 3 = 업데이트(업데이트 작업 전 값) 4 = 업데이트(업데이트 작업 후 값)
__$update_mask	varbinary(128)	변경된 열을 식별하는 변경 테이블의 열 서수를 기준으로 하는 비트 마스크입니다. 변경된 열을 확인해야 하는 경우 이 값을 검사할 수 있습니다.
<captured source table columns>	다양함	함수에서 반환되는 나머지 열은 캡처 인스턴스 생성 시 캡처된 열로 식별된 원본 테이블의 열입니다. 캡처된 열 목록에 원래 지정된 열이 없으면 원본 테이블의 모든 열이 반환됩니다.

자세한 내용은 cdc.fn_cdc_get_net_changes_<capture_instance>(Transact-SQL)를 참조하세요.

이후 수행할 단계

변경 데이터를 쿼리하는 테이블 반환 함수를 만든 후 다음 단계는 패키지의 데이터 흐름 디자인을 시작하는 것입니다.

다음 항목: 변경 데이터 검색 및 이해

다음을 통해 공유