Создание функции для получения информации об изменениях

Статья
05/30/2023

Область применения: среда выполнения интеграции SSIS SQL Server в Фабрика данных Azure

После завершения потока управления для пакета служб Integration Services, выполняющего добавочную нагрузку данных об изменениях, следующая задача — создать табличную функцию (TVF), которая извлекает измененные данные. Создавать эту функцию необходимо только один раз — перед первой добавочной загрузкой.

Примечание.

Создание функции для получения измененных данных — второй этап процесса формирования пакета, который выполняет добавочную загрузку измененных данных. Общее описание процесса формирования этого пакета см. в разделе Система отслеживания измененных данных (службы SSIS).

Вопросы проектирования функций для отслеживания измененных данных

Чтобы получить измененные данные, исходный компонент в потоке данных пакета вызывает одну из следующих функций запроса системы отслеживания измененных данных.

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.

Вместо этого используется возвращающая табличное значение функция-оболочка, так как в предложениях RETURN функций этого типа явным образом определяются столбцы выходных таблиц. Это явное определение столбцов предоставляет метаданные, необходимые исходному компоненту служб Integration Services. Эту функцию нужно создавать для каждой таблицы, для которой необходимо получить измененные данные.

Существует два способа создания возвращающей табличное значение функции-оболочки, которая вызывает функцию запроса системы отслеживания измененных данных.

Чтобы создать функции с табличным значением, можно вызвать системную хранимую процедуру sys.sp_cdc_generate_wrapper_function.
Можно написать собственную возвращающую табличное значение функцию на основе рекомендаций и примера данного раздела.

Вызов хранимой процедуры для создания функции с табличным значением

Самый быстрый и простой способ создания функций с табличным значением — вызов системной хранимой процедуры sys.sp_cdc_generate_wrapper_function. Эта хранимая процедура создает сценарии для создания функций оболочки, предназначенных специально для удовлетворения потребностей исходного компонента служб Integration Services.

Внимание

Системная хранимая процедура 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_" и имени экземпляра для сбора данных. Если создана функция получения итоговых изменений, она имеет префикс "fn_net_changes_".)
Инструкция CREATE для функции-оболочки.

Основные сведения о скриптах, созданных хранимой процедурой, и их использование

Обычно для вызова хранимой процедуры sys.sp_cdc_generate_wrapper_function используется инструкция INSERT...EXEC, а созданные процедурой скрипты сохраняются во временной таблице. Затем каждый скрипт можно выбрать отдельно и создать с его помощью соответствующую функцию-оболочку. Однако разработчик также может воспользоваться набором команд 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, то выполнение созданных функций-оболочек приведет к ошибке 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).

Результирующий набор, возвращаемый функциями-оболочками, включает следующие данные:

Все запрошенные столбцы информации об изменениях.
Столбец с именем __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 данные об изменениях за указанный период изменений. Эта функция применяет систему отслеживания измененных данных для сопоставления значений datetime с двоичными значениями регистрационных номеров транзакций в журнале (номеров LSN), которые используются внутренними механизмами таблиц изменений. Кроме того, эта функция обрабатывает некоторые особые ситуации.

Если для времени начала передается значение 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> возвращает четыре столбца с метаданными для каждой строки изменений. Если нужно использовать эти значения в потоке данных, можете возвратить их как дополнительные столбцы с помощью возвращающей табличное значение функции оболочки.

Имя столбца	Тип данных	Description
__$start_lsn	binary(10)	Номер LSN, связанный с фиксацией транзакции изменения. Все изменения, зафиксированные в одной транзакции, имеют общий номер LSN фиксации. Например, если операция обновления в исходной таблице изменяет две различные строки, таблица изменений будет содержать четыре строки (две со старыми значениями и две с новыми), каждая с одним и тем же значением __$start_lsn .
__$seqval	binary(10)	Значение последовательности, используемое для упорядочивания изменений строк в пределах транзакции.
__$operation	int	Операция языка обработки данных (DML), связанная с изменением. Может применяться один из перечисленных ниже типов. 1 = удаление 2 = вставка 3 = обновление (Значения перед операцией обновления.) 4 = обновление (Значения после операции обновления.)
__$update_mask	varbinary(128)	Битовая маска, основанная на порядковых номерах столбцов в таблице изменений, идентифицирующих эти измененные столбцы. Это значение можно проанализировать, если необходимо установить, какие столбцы были изменены.
<отслеживаемые столбцы исходной таблицы>	Различается	Остальные столбцы, возвращенные функцией, — это столбцы из исходной таблицы, определенные как отслеживаемые при создании экземпляра отслеживания. Если в списке отслеживаемых столбцов первоначально не было указано ни одного столбца, возвращаются все столбцы исходной таблицы.

Дополнительные сведения см. в статье cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

Следующий шаг

После создания возвращающей табличное значение функции, запрашивающей измененные данные, начинается проектирование потока данных в пакете.

Следующий раздел: получение и понимание измененных данных

Поделиться через