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

Статья
05/08/2013

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

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

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

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

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

cdc.fn_cdc_get_net_changes_<отслеживаемый_экземпляр> Для этого запроса единственная строка, возвращаемая для каждой операции обновления, содержит окончательное состояние каждой измененной строки. В большинстве случаев требуются только данные, возвращаемые запросом для конечных изменений. Дополнительные сведения см. в разделе cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).
cdc.fn_cdc_get_all_changes_<отслеживаемый_экземпляр> Этот запрос возвращает все изменения в каждой строке за весь период отслеживания. Дополнительные сведения см. в разделе 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 не создает функции-оболочки напрямую. Вместо этого она создает скрипты 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 в экземпляре системы отслеживания в качестве верхней границы запроса.

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

Имя столбца	Тип данных	Описание
__$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).

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

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

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

Будьте в курсе новых возможностей cлужб Integration Services

Чтобы получить новейшую документацию, статьи, образцы и видеоматериалы корпорации Майкрософт, а также лучшие решения участников сообщества, посетите страницу служб Службы Integration Services на сайте MSDN:

Посетить страницу «Службы Integration Services» на сайте MSDN

Чтобы получать автоматические уведомления об этих обновлениях, подпишитесь на RSS-каналы, предлагаемые на этой странице.

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