company_banner

Интегрируем 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 Table Loader. В некоторых местах из-за переноса таблиц на Greenplum пришлось править job’ы: стандартный трансформ «Lookup», например, работает неэффективно, если обе входные таблицы лежат в БД.

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

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

    Comments 8

      0
      Отличная статья. На недавнем форуме SAS в Москве слышал об этой теме, очень интересно.
      Я правильно понимаю, что интернет банк использует аналитические данные SAS? Может быть и SAS BI тоже?
        0
        Спасибо! Да, графики в интернет-банке строятся на данных DWH. Если я правильно понял вопрос про SAS BI, то нет, интернет-банк его не использует.
        0
        При практическом применении DIS множество трансформаций кажутся жутко неудобными (кроме милейших Extract и SQL Join). Вечно так и подрывает накодить User-written Code.
        Скажите, а Вы используете LSF, если у Вас бывает такое, что один и тот же процесс запускают несколько пользователей?
          0
          Еще Append и Lookup вполне юзабельны. А так да, без User Written Code не обойтись.
          LSF не используем, у нас самописный планировщик. На prod'е пользователи процессы не запускают — все автоматизировано, так что один и тот же процесс несколькими пользователями не запускается. Ну а в случае каких-то проблем, чинит обычно кто-то один, так что проблем не возникает.
          0
          >Передача данных из Greenplum в SAS всегда идет через master node и ограничена скоростью работы драйвера

          На самом деле, в Greenplum есть Writable External Tables, то есть выгружать данные из Greenplum можно с той же скоростью, что и загружать. Вопрос в том, что SAS/Access for Greenplum пока не умеет их использовать, но обертку написать вполне можно
            0
            По факту скорость выгрузки во writable external table существенно меньше скорости загрузки. (Множество сегментов могут параллельно записывать данные, а один ETL хост с такой скоростью принимать их не может). Возможно, использование большого числа gpfdist улучшило бы ситуацию, но есть подозрение, что полученный выигрыш все равно бы съела загрузка из текстовых файлов в SAS.
              0
              Если gpfdist смотрит на RAM-диск и Greenplum подключен по 10GbE, то использование writable external table даст скорость записи в 10Gbit/sec, реально около 1100 MB/sec. Дальше задача SAS по чтению результата из delimited-файла, но в итоге среднюю скорость в ~200MB/sec получить можно
              Использование большего числа gpfdist должно подкрепляться архитектурой сети, иначе будут те же 10 Gbit/sec, хоть 1 gpfdist, хоть 10
                0
                У нас как раз gpfdist смотрит на RAM-диск и ETL-хост подключен в interconnect сеть Greenplum по 10GbE. Но вот скорость выгрузки в текстовый файл при этом составляет ~ 30MB/s. Есть правда одно но — ETL-хост у нас виртуальный. Но это все же не должно давать падение скорости на порядок. Ваши данные о скорости — это из личного опыта?

          Only users with full accounts can post comments. Log in, please.