Интегрируем SAS и Greenplum

Введение

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

Когда мы начинали проект, в интернете было совершено невозможно найти что-нибудь, касающееся связки SAS DIS и Greenplum. Основные моменты перехода и возникшие в процессе трудности и хотелось бы осветить в этой статье.

Дополнительную сложность проекту придавало то, что надо было не строить процессы с нуля, а переделывать существующие, иначе бы сроки и стоимость проекта получились неприемлемыми. Исторически сложилось, что в качестве ETL средства мы используем SAS, и в частности SAS Data Integration Studio. Каждый ETL процесс здесь представляет собой т.н. job, в котором есть входные таблицы, логика их обработки, выходная таблица(ы). Ежедневный процесс загрузки хранилища состоит из ~800 таких job’ов. Их нам и предстояло переделать так, чтобы выиграть он переноса входных/выходных таблиц на Greenplum.

SAS/Access for Greenplum

Каким образом код, написанный на SAS Base, работает с таблицами в БД? Для различных баз есть серия продуктов SAS, называющихся обычно SAS Access for <название БД>. Такой коннектор для Greenplum представляет собой ODBC драйвер со специальной оберткой, позволяющей использовать его из SAS. Коннектор дает два способа доступа к данным:

• Схема БД определяется через SAS libname и с таблицами можно работать как с обычными SAS datasets. В этом случае SAS-код неявно транслируется в инструкции для БД, но, в случае невозможности такой трансляции (например, если используется какая-то функция SAS, отсутствующая в Greenplum), будет применена обработка запроса на стороне SAS.
• SQL pass-through. В proc sql можно писать код, который будет передан в ODBC-драйвер фактически as is.

В SAS Data Integration Studio код с использованием механизма pass-through умеет генерировать только трансформ «SQL Join», остальные генерируют обычный SAS код, который надо проверять (по логу)на предмет того, что он в итоге передает в коннектор. Встречались примеры неправильно сгенерированного кода.

Work -> Greenplum Work

По умолчанию принцип работы job’ов в SAS такой, что в качестве места для временных таблиц используется Work — создаваемая при старте сессии на диске директория, доступная только текущему владельцу процесса и удаляемая сразу по его завершении. В work’е содержатся SAS datasets, к которым применим язык SAS Base, что позволяет вести разработку ETL очень быстро. Также такое изолирование позволяет легко «чистить» место за упавшими job’ами, контролировать объемы используемого дискового пространства. При переводе job’ов на Greenplum часть (или все) промежуточные таблицы переезжали из WORK в Greenplum, и возник вопрос, куда эти таблицы складывать в БД?

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

• Автоматическая кодогенерация в SAS DI Studio не удаляет таблицы, которые больше не нужны. Потребовалась бы какая-то процедура, встраиваемая в каждый job, т.к. постоянно держать в базе полный объем вспомогательных таблиц ETL — слишком расточительно.
• Возможны конфликты имен у параллельно работающих job’ов.
• В случае каких-то проблем сложно идентифицировать, кому (какому job’у) принадлежит таблица.

В результате был выбран другой подход — воспроизвести в БД поведение SAS. Каждая сессия SAS создает в Greenplum схему, в которую складываются все промежуточные таблицы этой сессии. Некоторые детали:

• Одна такая «work» схема соответствует одной сессии SAS. На старте SAS-сессии в Greenplum создается схема, и на нее назначается SAS-библиотека.
• В имени создаваемой схемы кодируется необходимая информация, такая как путь до SAS work, хост, пользователь, процесс: %let LOAD_ETL_WRK_SCH_NAME = work_%substr(%sysfunc(pathname(work)),%sysfunc(prxmatch(/(?<=SAS_work)./,%sysfunc(pathname(work)))),12)_&SYSUSERID._srv%substr(&SYSHOSTNAME., %eval(%length(&SYSHOSTNAME.) — 1))_&SYSJOBID.;
• На крон ставим «чистильщик» временных схем Greenplum. Он для каждой «work_» схемы Greenplum проверяет наличие соответствующей ей сессии SAS и, в случае ее отсутствия, удаляет «work_» схему.

В DI Studio создается ODBC-библиотека со схемой &LOAD_ETL_WRK_SCH_NAM, в которую при необходимости назначаются промежуточные таблицы.

Передача данных между SAS и Greenplum

При такой схеме работы одним из главных вопросов становится скорость передачи данных между SAS и Greenplum. Передача данных из Greenplum в SAS всегда идет через master node и ограничена скоростью работы драйвера. Скорость зависит от ширины и составу полей выгружаемых таблиц, в среднем у нас получается порядка 50MB/s.

С загрузкой данных из SAS в Greenplum все значительно интересней. Greenplum позволяет делать bulk loading из текстовых файлов. Суть этого механизма в том, что внешний файл определяется как внешняя таблица (доступ к нему для Greenplum предоставляет специальная утилита, устанавливаемая на ETL хостах) и грузится напрямую на хосты с данными, минуя master. За счет этого скорость загрузки сильно возрастает. Со стороны SAS процесс выглядит так: таблица выгружается в csv-файл, а затем силами Greenplum этот файл затягивается в базу. Однако оказалось, что скорость этого процесса очень сильно зависит от скорости выгрузки таблицы из SAS в csv-файл. Выгрузка в файл идет со скоростью до 20-30MB/s (упирается в процессор), скорость загрузки csv в Greenplum превосходит 150 MB/s. Для больших таблиц это в итоге давало совершенно неудовлетворительную скорость загрузки. Ускорение было получено за счет разделения загружаемой таблицы на части: запускаются несколько параллельных потоков, каждый из которых работает со своим куском таблицы — выгружают его в csv и делают insert в Greenplum. Это позволило увеличить скорость загрузки данных в Greenplum до ~90-100 MB/s.

ETL primitives

Для работы в DI Studio нам пришлось переписать некоторые трансформы, т.к. стандартные генерировали код, который либо неоптимально работал, либо работал с ошибками. Речь идет о Table Loader и SCD Type2 ^[1] Table Loader. В некоторых местах из-за переноса таблиц на Greenplum пришлось править job’ы: стандартный трансформ «Lookup», например, работает неэффективно, если обе входные таблицы лежат в БД.

Вместо заключения

В статье описаны основные из задач, которые пришлось решать в процессе миграции. Многое осталось за рамками статьи. Проблема с hash join, блокировки таблиц пользовательскими запросами, партицирование и сжатие. При наличии интереса опишем это более детально в следующих постах.

Автор: koskar