10.6.4. Преобразования Data Flow Task

Преобразования (transformations) Data Flow Task в SSIS SQL Server 2005, особенности применения преобразований

Одна из самых ценных возможностей Data Flow Task — это преобразования (transformations). Преобразования — это элементы задачи Data Flow Task, которые предназначены для выполнения каких-либо действий с данными в ходе их перемещения с источника в место назначения. Работа со всеми преобразованиями выглядит одинаково: необходимо перетащить этот элемент из раздела Transformations (Преобразования) в Toolbox на свободное место на вкладке Data Flow в SSIS Designer, а затем соединить преобразование стрелками с источниками, назначениями или другими преобразованиями. После этого нужно будет настроить свойства данного преобразования.

Обратите также внимание, что в преобразованиях нужно указывать не все столбцы источника, а только те, с которыми действительно производятся какие-то преобразования. Значения остальных столбцов просто "пройдут сквозь" преобразование и станут доступны для назначения столбцам источника в исходном виде. Чаще всего преобразования просто добавляют новые столбцы, которые можно использовать в назначении (они появятся на вкладке Mappings).

В Data Flow Task предусмотрено много типов преобразований (если быть точным, то 28). Подробное рассмотрение каждого из них потребовало бы слишком много места. Поэтому в этом разделе будет приведена только краткая характеристика каждого преобразования, чтобы можно было составить представление о том, в каких ситуациях оно подойдет наилучшим образом.

q      Aggregate (Агрегат) — это преобразование предназначено для расчета итоговых значений по столбцам перекачиваемых записей. Можно посчитать сумму, среднее значение, минимум и максимум, общее количество значений и т. п. Это преобразование действует аналогично оператору GROUP BY в запросе SELECT в Transact-SQL. Вы выбираете столбцы на источнике, по которым будет проводиться группировка, и в результате этого преобразования на выходе в ваше распоряжение предоставляются дополнительные столбцы с итогами по группам.

Этим преобразованием нужно пользоваться очень осторожно. Оно не переносит расчет агрегатов на сервер (на SQL Server, например, преобразование отправит запрос вида SELECT * FROM HumanResources.vEmployee), а скачивает все записи в оперативную память и уже по ним считает итоги. Если записей у вас много, то оперативной памяти может просто не хватить. Кроме того, появляется искушение использовать SSIS для генерации отчетов (обычно в отчетах как раз и нужны итоги), что неправильно. Для создания отчетов и работы с ними предназначен другой компонент SQL Server 2005 — Reporting Services.

q      Audit (Аудит) — это преобразование позволяет использовать в вашем пакете значения системных переменных (например, имя пользователя, который запустил пакет, имя компьютера, имя пакета и т. п.). Фактически у вас появляется новый источник данных, столбцы которого соответствуют системным переменным.

q      Character Map (Карта символов) — это преобразование позволяет применить к значениям, получаемым с источника, строковые функции, например, перевод в верхний и нижний регистр или перестановка символов в строковом значении в обратном порядке. Остальные преобразования относятся к восточным языкам. Для каждого столбца можно указать в свойствах этого преобразования два назначения: In-place upgrade (меняться будет значения того же столбца) и New column (результаты преобразования будут помещены в новый столбец, а старый столбец останется в неприкосновенности).

q      Copy Column (Копирование столбца) — это самый простой тип преобразования. Он используется для того, чтобы "размножить" существующий столбец, сделав из него два (с одинаковыми значениями). Затем такой дополнительный столбец можно использовать для других преобразований.

q      Conditional Split (Условное разделение) — этот тип преобразования более всего похож на оператор SELECT CASE. Для него можно назначить несколько пунктов назначения. Это преобразование проверяет каждую запись источника на соответствие набору определенных в нем условий (для которых устанавливается очередность проверки), и результат записывается в соответствующее назначение в зависимости от того, какое именно условие вернуло для данной записи значение TRUE. Можно установить также назначение по умолчанию: данные будут записываться в это назначение, если проверка всех условий вернула FALSE.

q      Data Conversion (Преобразование типов данных) — одно из самых простых преобразований. Позволяет в процессе перекачки данных изменять тип данных. Обратите внимание, что это преобразование просто добавляет новые столбцы, которые можно будет использовать в назначении. Столбцы в исходном виде также остаются в вашем распоряжении.

q      Data Mining Query (Запрос к модели добычи данных) — это очень экзотический тип преобразования. Он позволяет для каждой записи на источнике выполнить запрос на языке DMX к модели добычи данных на Analysis Services. При этом значения из источника можно использовать как параметры, передаваемые этому запросу. Результаты запроса можно использовать в назначении.

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

q      Export Column (Экспортировать столбец) и Import Column (Импортировать столбец) — эти два преобразования предназначены для работы с данными BLOB (большими двоичными типами данных). На SQL Server к ним относятся столбцы text, ntext и image. Для преобразования Export Column необходимы два столбца: с данными BLOB и с обычными строковыми значениями. В столбце со строковыми значениями должны находится имена файлов. Это преобразование запишет двоичные данные для каждой записи в файл на диске с именем, которому будет соответствовать название текстового столбца. Наиболее очевидное использование этого преобразования — экспорт изображений из базы данных в файлы на диске.

Преобразование Import Column предназначено для обратной задачи — импорт двоичных данных из файлов на диске в базу данных. В столбце источника должны быть указаны имена файлов с путями. Для каждой записи источника будет открываться соответствующий файл и записываться в столбец назначения.

q      Fuzzy Grouping (Нечеткая группировка) — это достаточно сложное преобразование. В нем используются элементы искусственного интеллекта для группировки записей на источнике. В свойствах этого преобразования вам нужно будет выбрать один или несколько столбцов источника, для которых будет проводиться группировка, допустимые строковые преобразования и уровень "похожести" для группировки (от 0,00 до 0,99). Записи на источнике останутся в неприкосновенности, но для записи в назначение можно будет использовать в добавление к исходным столбцам источника еще три дополнительных столбца:

·                _key_in — уникальный идентификатор записи источника;

·                _key_out — идентификатор группы, к которой преобразование "решило" отнести эту запись;

·                _score — счет, значение от 0 до 0,99, которое отражает степень похожести данной записи на "идеальную" (canonical) для данной группы.

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

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

q      Lookup (Обращение к внешнему источнику) — очень удобное во многих ситуациях преобразование. Для каждой записи из источника будет выполнен специальный запрос к внешнему источнику данных (при этом значения из первого источника будут использоваться как параметры внешнего запроса). Затем данные из возвращаемого запроса можно использовать вместе со столбцами источника в назначении. Например, представьте себе такую ситуацию: в таблице на SQL Server у вас находятся номера продуктов, а расшифровка этих номеров находится, например, в файлах FoxPro. При помощи этого преобразования вы можете получить расшифровку для каждого продукта и записать ее вместе с другими столбцами в назначение.

q      Fuzzy Lookup (Нечеткое обращение к внешнему источнику) — это так называемые нечеткие сравнения. Если в обычном преобразовании Lookup просмотр производится в соответствии с четкой логикой запросов SQL, то в Fuzzy Lookup, как и в Fuzzy Grouping, вы можете задать "уровень похожести" от 0 до 0,99. Соответственно, из внешнего источника данных будут возвращаться не только совпадающие значения, но и "похожие" на значения с источника.

q      Merge (Слияние) и Union All (Полное объединение) — эти преобразования позволяют поместить одинаковые по формату данные из нескольких источников в одно назначение. Например, если филиалы передают вам отчеты о проведенных операциях в виде макетных текстовых файлов (в терминологии SQL Server — flat files, плоские файлы), то преобразования Merge и Union All позволят в ходе одной операции поместить данные из всех текстовых файлов в таблицу SQL Server. Отличие между ними заключается в том, что преобразование типа Merge перед загрузкой данных в назначение вначале получает их из всех источников и сортирует их, а Union All не выполняет сортировку и загружает данные последовательно в том порядке, как они поступили из источников.

q      Merge Join (Соединение слиянием) — это преобразование, которое обеспечивает объединение данных из разных таблиц по ключу. Обратите внимание на следующие моменты:

·                если речь идет о соединении данных из двух таблиц на одном источнике данных (например, в базе данных SQL Server), то намного проще будет просто указать в качестве источника запрос, а объединение (т. е. оператор JOIN) использовать в самом этом запросе. SQL Server выполнит такое соединение быстрее и эффективнее, чем пакет SSIS. Поэтому такое преобразование имеет смысл использовать только тогда, когда вам нужно объединять данные из таблиц на разных источниках данных;

·                альтернативой этому преобразованию может стать преобразование Lookup (точно так же, как запрос с вложенным подзапросом может заменить соединения). При использовании преобразования Merge Join вы проигрываете в гибкости, но можете выиграть в производительности тогда, когда обе таблицы, информация из которых соединяется, отсортированы по ключу, по которому производится соединение.

В этом преобразовании вы можете использовать два источника данных. Если на одном источнике информация не отсортирована, то рекомендуется перед этим преобразованием настроить преобразование типа Sort.

Соединения могут быть такими же, как и в запросах Transact-SQL: правые, левые и внутренние.

q      Multicast (Множественная передача) — это преобразование просто распараллеливает поток входящих данных. В нем используется один источник и множество назначений. Во все назначения будет записана одинаковая информация. Можно использовать такое преобразование в качестве элементарной системы репликации, а можно организовывать одинаковые потоки данных из одного источника, чтобы передать их другим преобразованиям и затем опять слить вместе.

q      OLE DB Command (Команда OLE DB) — это преобразование позволяет использовать команду SQL для каждой записи, которая передается с источника. При этом в команде SQL информацию из текущей записи можно использовать в качестве значений параметров. Например, преобразование OLE DB Command можно использовать в ситуации, когда каждой записи на источнике соответствует, например, информация о заказе, которую нужно удалить из другой таблицы. В этом случае при помощи этого преобразования можно определять команду DELETE и использовать для нее в качестве параметров значения, поступающие с источника.

Это преобразование использует один источник (только типа OLE DB Source) и одно назначение (только типа OLE DB Destination).

q      Percentage Sampling (Процентная выборка) — это очень интересное преобразование. Оно принимает данные из одного источника и передает их в два назначения. В одно назначение, которое называется невыбранный вывод выборки (sampling unselected output), будут передаваться данные в том же виде, в котором они поступили с источника, а в другое, которое называется выбранный вывод выборки (sampling selected output), поступит случайная выборка значений с источника. При этом вы можете определить процент значений, который попадет в выборку. Но следует учесть, что большой точностью в подсчете процентов это преобразование не отличается — например, если попросить его взять 10% от 290 записей, то получится 35 записей.

Если вас интересует только выборка, то вы можете вообще не определять для этого преобразования невыбранный вывод выборки.

Основное назначение этого преобразования — тестирование пакетов, когда тестировать их на всем массиве записей получается очень долго.

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

Таблица 10.1. Исходная таблица с информацией о заказах

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

Таблица 10.2. Таблица после применения преобразования Pivot

Настройка этого преобразования не очевидна. Вначале на вкладке Input Columns (Столбцы ввода) свойств преобразования вам потребуется выбрать нужные столбцы источника, а затем перейти на вкладку Input and Output Properties (Свойства ввода и вывода) и для столбцов источника настроить значение свойства PivotUsage. Для этого свойства используется одно из четырех значений, которое определяет, какая роль в преобразовании предназначается данному столбцу. Затем на вкладке Output Columns (Столбцы вывода) нужно создать столбцы, которые будут передаваться назначению, и привязать их к столбцам источника при помощи свойства PivotKeyValue.

Существует также преобразование, которое выполняет обратную операцию и производит нормализацию таблицы. Оно называется Unpivot (Обратная смена).

q      Row Count (Счетчик строк) — это преобразование просто считает количество строк источника, которое прошло через него, и сохраняет это значение в переменной пакета (перед настройкой этого преобразования переменная должна быть уже создана). Это преобразование обычно применяется при создании пользовательского отчета по результатам работы пакета.

q      Row Sampling (Выборка строк) — это преобразование очень похоже на Percentage Sampling. Оно также случайным образом выбирает из всех записей источника некоторое их количество. Однако в отличие от Percentage Sampling, в котором выбирался примерный процент для записей, попадающих в выборку, здесь указывается не процент, а точное количество. Поэтому Microsoft рекомендует использовать это преобразование, например, для лотерей. Если вам нужно явно выбрать какие-то значения вместо случайных (например, для тестирования, или вы хотите повлиять на результаты лотереи), то в вашем распоряжении — параметр Seed.

Точно так же, как и Percentage Sampling, источник у этого преобразования может быть только один, а вот назначений — два (для полного набора записей и для выборки).

q      Script Component (Компонент скрипта) — это, безусловно, самое мощное преобразование. Его можно использовать не только как преобразование, но и как источник или назначение данных. Фактически этим преобразованием можно заменить все остальные преобразования. Если вам не хватает функциональности других преобразований, то опять-таки можно использовать преобразование Script Component. В этом преобразовании вы можете использовать свой собственный программный код для выполнения каких-то операций с данными, которые передаются с источника. Несмотря на слово Script в названии преобразования, создавать программный код можно только на Visual Basic.NET. Но при этом вам будут доступны все функции Visual Basic.NET, все сборки и пространства имен .NET, а при помощи стандартных средств Visual Studio и обычные COM-компоненты.

Расплата за функциональность — резкое падение производительности: программный код выполняется для каждой записи, которая приходит с источника.

Настройка этого преобразования не вполне очевидна. При размещении его в пакете нужно выбрать: будет ли Script Component выступать в роли источника, назначения или преобразования. Если вы выбрали для него роль преобразования, то его нужно разместить, как и другие преобразования, между источником и назначением (рис. 10.7).

Рис. 10.7. Настройка преобразования Script Component

Предположим, что вы разместили этот компонент в вашем экспериментальном пакете, который занимается передачей данных из SQL Server в файл Excel, как показано на рисунке, и выбрали для него роль преобразования (т. е. в окне Select Script Component Type (Выбрать тип компонента скрипта), которое открывается при перетаскивании этого объекта из Toolbox, установили переключатель в положение Transformation (Преобразование)). Для демонстрации произведем очень простую операцию: выведем в окно сообщения информацию о каждом сотруднике (его имени и фамилии). Для этого вам нужно выполнить следующие действия:

1.     Открыть свойства преобразования Script Component (это можно сделать, щелкнув по нему правой кнопкой мыши и в контекстном меню выбрав команду Edit).

2.     На вкладке Input Columns свойств преобразования нужно установить флажки напротив столбцов FirstName и LastName.

3.     На вкладке Inputs and Outputs (Вводы и выводы) для вас уже будет заготовлены один источник и одно назначение. При желании можно добавить дополнительные назначения (т. е. для этого преобразования можно будет использовать не одно, а несколько исходящих зеленых стрелок). В данном случае оставьте на этой вкладке значения по умолчанию.

4.     Основная работа производится в окне редактора кода. Чтобы его открыть, нужно перейти на вкладку Script (Скрипт) и нажать кнопку Design Script (Спроектировать скрипт). Откроется окно Visual Studio for Application.

5.     В этом окне вам будет предложено ввести свой код для класса ScriptMain (точнее, для событийной процедуры Input0_ProcessInputRow(), определенной в этом классе). Вводить код рекомендуется на месте комментария с пометкой "Add Your Code Here" (Добавьте сюда ваш код). Вариант этой процедуры может быть таким:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    MsgBox(Row.FirstName.ToString & " " & Row.LastName.ToString)

End Sub

Далее вам осталось только закрыть все окна с сохранением внесенных изменений и запустить пакет на выполнение. Если вам не хочется закрывать окна сообщений 290 раз, то можно воспользоваться командой Debug | Stop debugging (Отладка | Остановить отладку) из меню SSIS Designer.

Для того чтобы использовать более функциональные варианты этого преобразования, необходимо знакомство с объектной моделью пакетов SSIS. К сожалению, справки по ней в документации по SQL Server 2005 нет. Вам придется обращаться к справке в MSDN (поставляемой с Visual Studio 2005 или на сайте www.microsoft.com/msdn).

q      Slowly Changing Dimension Transformation (Преобразование медленно изменяющегося измерения) — это экзотическое преобразование предназначено для внесения изменений в измерения кубов Analysis Services на основе информации из реляционного источника данных.

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

Вся сортировка производится в оперативной памяти того компьютера, на котором работает пакет, поэтому с этим преобразованием нужно быть осторожным. По возможности то сортировку лучше выполнять на сервере баз данных, с которого поступают записи, указав в качестве источника запрос с оператором ORDER BY. Это преобразование лучше всего использовать только в тех ситуациях, когда источник не поддерживает сортировку (например, когда в качестве источника выступают текстовые файлы).

q      Term Extraction (Извлечение термина) и Term Lookup (Извлечение термина из внешнего источника) — это сложные преобразования, использующие элементы полнотекстового поиска. Они могут работать только для источников на английском языке. Term Extraction извлекает из источника существительные и словосочетания с существительными (в зависимости от выбранного режима) и записывает их в назначение. Term Lookup позволяет также обратиться к еще одному источнику (он называется справочной таблицей (reference table)), и определить, сколько найдено между двумя источниками совпадений. Затем сумма совпадений вместе с извлеченными существительными и словосочетаниями записывается в назначение.

Получить учебные материалы по этому курсу