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

Система отслеживания измененных данных (SSIS)

  • Статья

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

В SQL Server сбор измененных данных предлагает эффективное решение для эффективного выполнения добавочных загрузок из исходных таблиц в киоски данных и хранилища данных.

Система отслеживания измененных данных

Исходные таблицы со временем изменяются. Витрины данных или хранилища данных, основанные на этих таблицах, должны отражать эти изменения. Однако процесс, предполагающий периодическое копирование моментальных снимков всего источника данных, требует слишком много времени и ресурсов. Альтернативные подходы, предусматривающие использование столбцов отметок времени, триггеров или сложных запросов, часто приводят к снижению производительности и повышению сложности системы. Необходим надежный поток измененных данных, структурированный таким образом, чтобы потребители могли легко применять его к целевым представлениям данных. Запись измененных данных в SQL Server предоставляет это решение.

Функция отслеживания измененных данных ядро СУБД записывает действия вставки, обновления и удаления, применяемые к таблицам SQL Server, и делает подробные сведения об изменениях доступными в легко потребляемом, реляционном формате. Таблицы изменений, используемые системой отслеживания измененных данных, содержат столбцы, отражающие структуру столбцов отслеживаемых исходных таблиц, а также метаданные, необходимые для понимания того, какие изменения произошли в каждой строке.

Примечание.

Система отслеживания измененных данных доступна не во всех выпусках Microsoft SQL Server. Сведения о функциях, поддерживаемых различными выпусками SQL Server, см. в статье Возможности, поддерживаемые выпусками SQL Server 2016.

Работа системы отслеживания измененных данных в службах Integration Services

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

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

Шаги создания пакета отслеживания измененных данных

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

Шаг 1. Конструирование потока управления
В потоке управления пакета необходимо определить следующие задачи.

  • Вычисление начального и конечного значения datetime для интервала изменений в исходных данных, которые необходимо получить.

    Чтобы вычислить эти значения, используйте задачи Execute SQL или выражения Служб Integration Services с функциями datetime . Затем эти конечные точки сохраняются в переменных пакета для дальнейшего использования в пакете.

    Дополнительные сведения: указание интервала измененных данных

  • Определение готовности измененных данных в выбранном временном интервале. Этот шаг необходим потому, что процесс асинхронного сбора, возможно, еще не дошел до выбранной конечной точки.

    Чтобы определить, готовы ли данные, начните с контейнера «цикл по элементам». При необходимости отложите выполнение до тех пор, пока не будут готовы данные об изменениях для выбранного интервала времени. Внутри контейнера «цикл по элементам» используйте задачу «Выполнение SQL», чтобы выполнить запрос к таблицам сопоставления времени, обслуживаемых с помощью системы отслеживания измененных данных. Затем при необходимости временно отложите выполнение пакета с помощью задачи «Скрипт», которая вызывает метод Thread.Sleep , или с помощью другой задачи «Выполнение SQL» с инструкцией WAITFOR . Можно также с помощью другой задачи «Скрипт» зарегистрировать ошибку или время ожидания.

    Дополнительные сведения: определение готовности данных об изменении

  • Подготовка строки запроса, которая будет использована для запроса измененных данных.

    Используйте задачу «Скрипт» или задачу «Выполнение SQL», чтобы собрать инструкцию SQL, которая будет использоваться для запроса изменений.

    Дополнительные сведения: подготовка к запросу к измененным данным

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

Используйте SQL Server Management Studio для разработки и сохранения запроса.

Дополнительные сведения см. в разделе Получение и интерпретация измененных данных

Шаг 3. Конструирование потока данных
В потоке данных пакета необходимо определить следующие задачи.

  • Получение измененных данных из таблиц изменений.

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

    Дополнительные сведения см. в разделе Получение и интерпретация измененных данных

  • Разбиение изменений на операции вставки, обновления и удаления для обработки.

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

    Дополнительные сведения: процесс вставки, обновления и удаления

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

    Чтобы применить изменения к назначению, используйте компонент назначения, для применения операции вставки к назначению. Кроме того, используйте преобразования «Команда OLE DB» с параметризованными инструкциями UPDATE и DELETE, чтобы применить к назначению операции обновления и удаления. Также можно применять операции обновления и удаления с помощью компонентов назначения, чтобы сохранить строки во временных таблицах. Затем используйте задачи «Выполнение SQL», чтобы выполнить операции массового обновления и массового удаления для назначения для временных таблиц.

    Дополнительные сведения см. в разделе Применение изменений в назначении

Изменения данных из нескольких таблиц

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

См. также

Запись блога SSIS Design Pattern — Incremental Load (Шаблон разработки SSIS — добавочная загрузка) на сайте sqlblog.com