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

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

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

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

Примечание
Система отслеживания измененных данных доступна только в следующих выпусках SQL Server 2008: Enterprise Edition, Developer Edition и Evaluation Edition.

Дополнительные сведения см. в разделах Система отслеживания измененных данных

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

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

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

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

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

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

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

    Дополнительные сведения см. в разделах Задание интервала для отслеживания измененных данных

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Службы Integration Services предоставляют два образца, демонстрирующих использование системы отслеживания измененных данных в пакетах. Дополнительные сведения см. в следующих разделах.

• Файл Readme. Образец пакета для отслеживания измененных данных за указанный интервал

• Файл Readme. Образец пакета для отслеживания измененных данных с момента последнего запроса

Внешние ресурсы

Запись блога SSIS Design Pattern — Incremental Load (на английском языке) на сайте sqlblog.com