Преобразование Merge Join и советы по сортировке в SSIS
В этой статье мы рассмотрим задачу преобразования “Соединение слиянием” (Merge Join). Вы получите небольшой совет, который устранит необходимость добавления задачи преобразования SORT в задачу потока данных.
Изолируем наши результаты по одному сотруднику в базе данных AdventureWorks и объединим две таблицы. Добавляем третью таблицу и получаем следующие три таблицы:
- История отдела (Employee Department History)
- Сотрудник (Person)
- Отдел (Department)
Вот как это выглядело бы через SSMS с T-SQL:
Давайте посмотрим, как мы можем имитировать этот набор результатов в SSIS без использования T-SQL для объединения трех таблиц. Следует иметь ввиду, что не всегда будет лучшим вариантом не использовать T-SQL вместо отдельных задач в SSIS. Иногда легче написать логику соединения непосредственно в вашей задаче источника потока данных. Однако это для демонстрационных целей.
Допустим, вы получили запрос на извлечение набора результатов, упорядочение набора результатов и его загрузку в другое место. Вот как выглядит ваш пакет в SSIS с помощью задачи “соединения слиянием”:
Вот наши результаты:
Обратите внимание, мы использовали задачу преобразования SORT в приведенном выше примере. Это необходимо для того, чтобы описать, что должно происходить поэтапно:
- Извлекаем данные из таблиц История отдела и Сотрудник
- Сортируем каждый набор результатов
- Объединяем два результата в один, используя внутреннюю логику соединения
- Извлекаем данные из таблицы Отдел
- Сортируем первый набор объединенных результатов и набор результатов из таблицы Отдел
- Делаем слияние набора объединенных результатов, полученных из таблиц Сотрудник и История отдела с таблицей Отдел.
Давайте поговорим о лучшей практике для этого примера. Здесь используется преобразование типа Sort. Поскольку нам нужен упорядоченный набор результатов для запроса, мы используем преобразование “слияния” вместо преобразования ”полного объединения” (Union All). Кроме того, мы использовали задачу преобразования Sort, которая в данном случае может сильно повлиять на производительность пакета SSIS, особенно если ваши результирующие наборы больше, чем те, которые мы собираемся извлечь из базы данных AdventureWorks.
Лучшим использованием является приведение упорядоченного набора результатов в исходный код, а затем объединение ваших наборов записей. Перейдем к упорядочению вашего набора результатов в источнике и настройке источника, чтобы определить отсортированный столбец для объединения ваших наборов записей.
Сначала мы открываем задачу и добавляем оператора ORDER BY к нашему источнику.
Далее закрываем исходную задачу, щелкаем правой кнопкой мыши по той же исходной задаче и выбираем опцию Show Advanced Editor.
В Advanced Editor есть две спецификации, которые необходимо определить для выполнения этой работы:
- Перейдите на вкладку Input and Output Properties
- Нажмите на OLE DB Source Outputs
- Измените параметр IsSorted на "True"
- Перейдите в OLE DB Output к столбцам вывода Output columns.
- Нажмите на столбцы, которые вы использовали в условии ORDER BY.
- Измените параметр SortKeyPosition с «0» на «1».
Результаты должны выглядеть следующим образом:
Далее вы можете удалить каждую задачу сортировки, которая непосредственно следует за OLE DB Source task, повторив описанные выше шаги, чтобы перенастроить каждый редактор исходного кода. Теперь задача потока данных должна выглядеть так:
Мы получаем те же результаты:
Если вам интересно, почему мы избавились от всех задач типа Sort, кроме тех, которые следуют за первым объединением-слиянием, то для того есть две причины. Второе соединение сделано на DepartmentID, и, самое главное - задача преобразования слияния не считается задачей источника потока данных и не имеет функциональности для определения отсортированного порядка.
В заключении следует отметить, что задача преобразования Merge Join может использоваться для объединения столбцов из двух разных таблиц с использованием оператора Join, аналогичному Joins, который может использоваться в T-SQL. Мы рассмотрели пошаговое разбиение реализации задачи преобразования “соединение слиянием”, а также некоторые советы по использованию и передовую практику в отношении задачи типа Sort в SSIS.