Время прочтения: 5 мин.

В данной статье мы расскажем, как организовать ETL процесс между СУБД PostgreSQL, MS SQL и Oracle с помощью SQL Server Integration Services (SSIS).

PostgreSQL является целевой СУБД. На текущий момент преимущественно используются MS SQL, Oracle, Teradata, Hadoop. В связи с чем и появляется потребность в ETL механизмах, для совместного использования данных между различными СУБД.

Для организации ETL процесса необходимо выполнить следующие подготовительные шаги:

  • Получить доступ к БД на серверах MS SQL, Oracle, PostgreSQL
  • Запросить установку MS Visual Studio
  • Получить права на размещение SSIS пакетов
  • Убедиться, что установлены драйверы БД PostgreSQL Unicode (ODBC), SQLOLEDB.1 (OLEDB), Oracle Provider for OLE DB.

Организация ETL процесса.

Создание ETL процесса представлено следующими основными шагами:

  1. Создание проекта в MS Visual Studio.

В MS Visual Studio последовательно выбираем — Файл > Создать > Проект > Business Intelligence > Integration Services > Integration Services Project.

  • Создание подключения ODBC PostgreSQL.

На рабочем столе Windows последовательно выполняем — Ctrl+Esc > ODBC > 32 bit ODBC Administrator > Пользовательский DSN > Добавить > PostgreSQL Unicode > Далее заполняем поля подключения и учетные данные PostgreSQL сервера > Test > Save

  • Добавление подключения ODBC PostgreSQL в проект MS Visual Studio.

В проекте MS Visual Studio последовательно выбираем Диспетчеры подключений > Создать подключение > ODBC > Добавить > Далее выбираем ранее созданное подключение > OK

  • Добавление подключения OLE DB Oracle в проект MS Visual Studio.

В проекте MS Visual Studio последовательно выбираем — Диспетчеры подключений > Создать подключение OLE DB > Создать > Собственный поставщик OLE DB\Oracle Provider for OLE DB > Заполняем поля подключения и учетные данные Oracle сервера > Проверить соединение > ОК

  • Добавление подключения OLE DB MS SQL в проект MS Visual Studio.

В проекте MS Visual Studio последовательно выбираем — Диспетчеры подключений > Создать подключение OLE DB > Создать > Собственный поставщик OLE DB\SQL Server Native Client 11.0 > Заполняем настройки и учетные данные MS SQL сервера > Проверить соединение > ОК

  • Добавление в проект MS Visual Studio и настройка потока данных между MS SQL и PostgreSQL.

В проекте MS Visual Studio выбираем вкладку «Поток управления». В поле этого потока из вкладки «Панель элементов служб SSIS» переносится элемент «Задача потока данных».

  • Добавление источника OLE DB.

В проекте MS Visual Studio необходимо выбрать вкладку «Поток данных». В поле данного потока из вкладки «Панель элементов служб SSIS» необходимо перенести элемент «Источник OLE DB».

  • Настройка источника на подключение к MS SQL.

Нажимаем правую кнопку мыши на элементе «Источник OLE DB» и левую кнопку мыши на пункте «Изменить».

Выберем соединение OLE DB и «Режим доступа к данным». Например, существующее подключение «XXXXXX.TB40_XXXXXXXX» и «Команда SQL» соответственно.

  • Добавление назначения ODBC.

В проекте MS Visual Studio выбираем вкладку «Поток данных». В поле этого потока из вкладки «Панель элементов служб SSIS» переносим элемент «Назначение ODBC».

  • Настройка назначения на подключение к PostgreSQL.

Нажимаем правую кнопку мыши на элементе «Назначение ODBC» и левую кнопку мыши на пункте «Изменить»

Режим доступа рекомендуется установить в положение «Имя таблицы — пакеты» так как это более производительный способ вставки данных.

  • Добавление преобразования данных.

Элемент «Преобразование данных» необходим при копировании разнотипных данных между СУБД, так как могут происходить коллизии типов, кодировок, длины полей и т.д. Данный элемент позволяет управлять преобразованиями и обрабатывать ошибки.

Элемент «Преобразование данных» добавляем как и предыдущие элементы из вкладки «Панель элементов служб SSIS» .

  • Добавление в проект MS Visual Studio и настройка потока данных между Oracle и PostgreSQL.

В проекте MS Visual Studio выбираем вкладку «Поток управления». В поле этого потока из вкладки «Панель элементов служб SSIS» переносим элемент «Задача потока данных».

Новая задача потока данных заполняется аналогичным с п.5 образом. За исключением настройки источника OLE DB, который будет подключен к Oracle.

  • Ускорение вставки данных на PostgreSQL
ALTER TABLE test.» Korneev1″ SET UNLOGGED         —Отключает логирование ALTER TABLE test.» Korneev1″ SET LOGGED              —Включает логирование

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

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

  • Пример успешного запуска проекта.

Резюмируем

На текущий момент, с помощью SSIS пакетов, ETL процессы могут быть дополнены данными СУБД PostgreSQL. В данной статье рассмотрена лишь малая часть возможностей SSIS и PostgreSQL. PostgreSQL позволяет подключать NoSQL источники, а с помощью SSIS имеется возможность объединить данные большого количества СУБД или файловых источников для решения широкого спектра дата-инженерных задач.