- PVSM.RU - http://www.pvsm.ru -

Интеграция PostgreSQL с MS SQL Server для тех, кто желает побыстрее и поглубже

Интеграция PostgreSQL с MS SQL Server для тех, кто желает побыстрее и поглубже - 1 Недавно на хабре уже было опубликовано описание интеграции [1] PostgreSQL и MSSQL. Но, деталей там категорически не хватало. Посему, цели сей публикации следующие:

  • расширить и углубить общедоступную информацию о FDW для MSSQL называемом tds_fdw [2]: рассказать о разнице в мажорных версиях и описать базовые проблемы совместимости;
  • рассказать о возможностях оптимизации запросов, использующих внешние таблицы;
  • затронуть тему кэширования внешних данных в материализованных представлениях;
  • сказать пару слов об экзотических подходах к интеграции PostgreSQL и MSSQL.

Установка и настройка TDS FDW

Ребята из PostgresPro уже достаточно сказали [1] об этом процессе, повторяться не буду. Оставлю лишь несколько ссылок на официальную документацию PostgreSQL и на примеры из tds_fdw:

И ещё один момент: пожалуйста, не делайте как указано в инструкции по установке tds_fdw [10]

sudo make USE_PGXS=1 install

Спасите котиков [11], соберите deb-пакет и радуйтесь жизни:

sudo USE_PGXS=1 checkinstall

Различия между мажорными версиями TDS FDW

На текущий момент существует две актуальные версии FDW'шки: стабильная 1.0.7 и 2.0.0-alpha, которая, по сути, является master-веткой и в которой происходит всё самое интересное. Вот небольшой список их различий:

  • в 2.0.0 наконец-то появилась поддержка pushdown для условий в блоке WHERE, относящихся непосредственно к внешней таблице; однако он пока плохо работает при использовании опции query внутри объявления foreign_table;
  • появилась поддержка версии tds 7.4 (ниже опишу почему это нужно и важно);
  • есть некоторые проблемы с работой DISTINCT по внешней таблице (вот issue на GitHub'е [12]), хотя доподлинно не известно: у меня ли руки не оттуда растут или баг довольно хитрый и проявляется только при определённом стечении обстоятельств.

Подводные камни совместимости

До недавнего времени, tds_fdw не работал с версией tds выше 7.3. Но в ходе написания сей статьи поддержку версии 7.4 пришлось изыскать [13]. Поэтому теперь, начиная с коммита 3a803c [14], tds_fdw поддерживает все актуальные версии tds.

Почему же поддержка этой версии так важна? Лично для меня это важно из-за необходимости работать с MSSQL 2012. Если коротко: в Ruby on Rails для подключения к MSSQL используется библиотека activerecord-sqlserver-adapter [15], которая, в свою очередь, использует tiny_tds [16], который использует FreeTDS [17], который умеет общаться с MSSQL. Вся беда в том, что для RoR 3 и соответствующих мажорных версий библиотек, использование версии tds 7.1 прибито гвоздями и изменять её через конфиг можно только в 4+ версии. При этом версия 7.1 замечательно работает с MSSQL 2008, но при общении с MSSQL 2012 появляются следующие ошибки:

  • DB-Library error: DB #: 20017, DB Msg: Unexpected EOF from the server
  • ActiveRecord::LostConnection: TinyTds::Error: closed connection: ...
  • TinyTds::Error: Adaptive Server connection failed
  • и им подобные.

Их хотелось избежать перейдя на использование FDW, так как обновить RoR — это категорически дольше и дороже. Но tds_fdw не поддерживал нужной версии и пришлось с этим что-то делать.

Что же касается ошибок, то все они появляются рандомно и произрастают из-за одного и того же места; вносят некоторое «разнообразие» в приложение, заставляя его отваливаться в случайных местах в случайное время. Лечится же всё это безобразие только использованием правильной версии tds. Для MSSQL 2012 это tds 7.4.

Здесь же первая засада: поддержка версии tds 7.4 реализована во FreeTDS начиная с версии 0.95. Но из коробки в Ubuntu 14.04 и 16.04 идут версии 0.91-5 [18] и 0.91-6.1build1 [19] соответственно. И получить более новую версию FreeTDS можно двумя способами:

  1. собрать FreeTDS из исходников [20];
  2. воспользоваться альтернативным PPA [21] с версией FreeTDS 1.00.

Во втором случае есть один нюанс: в указанном репозитории есть пакет только для Ubuntu 14.04 (которая trusty). Для 16.04 (которая xenial), там ничего нет. Но, в целом, ничего фатального и если в 16.04 поправить /etc/apt/sources.list.d/jamiewillis-freetds-trusty.list на что-нибудь вот такое

deb http://ppa.launchpad.net/jamiewillis/freetds/ubuntu trusty main

То можно будет ставить пакет и в последней Ubuntu (и таки да, он работает без проблем).

Если же у вас CentOS, то под неё можно легко найти [22] FreeTDS до версии 0.95 включительно. Всё что старше придётся собирать из исходников.

Временное решение проблемы совместимости

Если ошибка под номером 20017 и её производные очень сильно докучают, а возможности заиспользовать необходимую версию tds нет, то можно обработать исключение, выбрасываемое PostgreSQL и перезапустить блок/метод/etc, обращающийся к MSSQL через FDW. В моём случае для RoR приложения это выглядело так:

def retry_mssql_operation(tries = 5)
  begin
    yield
  rescue ActiveRecord::StatementInvalid => e
    if e.message =~ /^PG::FdwUnableToCreateExecution/ && tries > 0
      tries -= 1
      retry
    else
      raise
    end
  end
end

На первое время спасает, но для долговременного решения категорически не подходит.

Немного о pushdown и о том, как работает FDW «на пальцах»

Прежде чем перейти к вопросам оптимизации запросов к внешней БД хотелось бы сказать несколько слов о pushdown. Почему-то описание сего механизма не востребовано в русскоязычных ресурсах (либо я не знаком с его правильным переводом, а трицепсовый жим вниз на блоке [23] это явно не из той оперы). Поэтому хочется коротко рассказать о нём.

В простейшем случае, когда мы в PG выполняем запрос вида

SELECT column_name FROM foreign_table WHERE column_id = 42;

Фактически в БД происходин следующее:

  1. из ассоциированой с foreign_table таблицы (или не таблицы), находящейся на стороннем сервере, извлекается всё содержимое в postgres;
  2. затем, полученные данные фильтруются на основании условий из WHERE.

Не шибко эффективная схема, особенно если из таблицы с несколькими миллионами строк хочется получить всего лишь одну. И вот здесь появляется pushdown. Сей механизм позволяет уменьшить количество строк, которые мы получаем от удалённого сервера. Делается это посредством конструирования запроса к внешней БД с учётом того, что мы хотим на стороне PG, то есть с учётом того что указано в WHERE, JOIN, ORDER и др. Иначе говоря, FDW разбирает исходный запрос в PotsgreSQL, выбрать из него то, что может понять удалённое хранилище данных и собрать новый запрос, сообразно этим условиям. Отсюда вытекает очевидное следствие: pushdown применим не для всех FDW (например, для file_fdw [24] pushdown почти бесполезен, а вот для postgres_fdw [25] или tds_fdw — совсем наоборот).

Итого: pushdown — это круто, он позволяет использовать механизмы внешнего хранилища данных, уменьшает объём данных, циркулирующих между PG и внешним хранилищем, тем самым ускоряя выполнение запросов, но, при этом, он является отдельным механизмом, поэтому его нужно реализовывать, поддерживать и это довольно нетривиальная задача.

Ускорение запросов

С установкой, настройкой и матчастью разобрались. Теперь приступим к описанию того, как можно побыстрее извлечь данные из MSSQL.

Pushdown

Пригодится такой подход в случае простых запросов, не обременённых различными JOIN и прочими SQL-ухищрениями. В последней версии tds_fdw (на текущий момент это 2.0.0-alpha) появилась поддержка простейшего pushdown для WHERE.

Для примера рассмотрим таблицу simple_table из БД MSSQL. В этой таблице есть два поля: id и data. Определение внешней таблицы для неё будет следующим:

CREATE FOREIGN TABLE mssql_table (
    id integer,
    custom_data varchar OPTIONS (column_name 'data'))
    SERVER mssql_svr
    OPTIONS (schema_name 'dbo',
             table_name 'simple_table',
             row_estimate_method 'showplan_all',
             match_column_names '1');

В данном случае, первый столбец имеет одинаковое название в PostgreSQL и в MSSQL: id. У второго столбца различные имена в PG и в MSSQL, поэтому здесь нужна опция column_name. Сей параметр явно задаёт отображение столбцов из PostgreSQL на столбцы в MSSQL. Так же, в конце указан параметр match_column_name, который отвечает за неявный мапинг названий колонок по именам, то есть, благодаря ему, мапается столбец id.

Всё, теперь если выполнить запрос

SELECT custom_data FROM mssql_table WHERE id = 42;

FDW должен обработать условие, указанное в WHERE и собрать правильный запрос в MSSQL. Например такой:

SELECT data FROM simple_table WHERE id = 42;

В случае tds_fdw версии 1.0.7 и ниже запрос в MSSQL будет другим:

SELECT id, data FROM simple_table;

Ещё раз повторюсь: pushdown, на текущий момент, работает только для WHERE; для JOIN, ORDER и прочих функций типа MAX, LOWER и др. он не взлетит.

И ещё одно: как же узнать, какой фактически запрос выполнился на стороне MSSQL? При использовании FDW для, например, MySQL, в explain появляется вот такая строка:

Remote query: SELECT `id`, `customer_id`, `order_date` FROM `data`.`orders`

И это удобно. В tds_fdw такого пока нет и нужно идти более длинным путём через логи FreeTDS. По умолчанию, во FreeTDS логи отключены, но это легко исправить покопавшись в /etc/freetds/freetds.conf. Там можно найти вот такие строки:

;       dump file = /tmp/freetds.log
;       debug flags = 0xffff

У которых нужно убрать точку с запятой в начале.

Теперь для любого запроса в MSSQL из PG, FreeTDS будет логировать всё что сможет. Это замедлит выполнение все внешних запросов и может наплодить кучу логов (в моём случае обычный SELECT сделал лог в ~300Мб, а JOIN еле ужодился в ~1.5Гб). Но зато в логих будет видно что фактически выполнилось в MSSQL. К тому же, объём логов можно уменьшить, поигравшись с `debug flags`. Подробнее о логировании во FreeTDS написать здесь [26], а детали о `debug flags` лежат вот тут [27].

Materialized view

Материализованное представление [28] (далее MV) — это обычное представление + таблица с данными. Сей подход поможет в случае сложных запросов с джойнами внешних и внутренних таблиц, с функциями, преферансом и куртизанками.

Профит от MV следующий: оно являются «родным» объектом для PG, то есть MV замечательно взаимодействует с остальными частями PostgreSQL и оно может быть проиндексировано и проанализировано независимо от источника данных, который его наполнил. Минусы тоже есть: MV нужно обновлять. Обновлять можно по внутренним триггерам, по внешним событиям, можно полностью пересоздавать и тд. Но, в любом случае, MV порождает отставание PG от первоисточника данных.

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

CREATE MATERIALIZED VIEW materialized_mssql_table AS
    SELECT id, custom_data
    FROM mssql_table;

Теперь все данные из MSSQL есть в PostgreSQL, а значит их можно индексировать как вздумается (B-tree [29], GIN и GiST [30] и др.), для них становится доступна статистика [31], можно увидеть детали о плане выполнения запроса и ещё много чего приятного из PG.

Обновить MV можно через стандартные INCERT/UPDATE/DELETE команды, либо просто пересоздать всё содержимое при помощи

REFRESH MATERIALIZED VIEW CONCURRENTLY materialized_mssql_table;

Опция CONCURRENTLY позволяет обновить MV не блокируя конкурирующие запросы на чтение, но требует больше времени и ресурсов. Так же, для возможности использования CONCURRENTLY целевое MV должно удовлетворять некоторым требованиям. Их можно найти на соответствующей странице документации [32].

Экзотический подход

Честно говоря, чёрт знает, может ли взлететь этот подход, быть может уважаемая публика расскажет что-нибудь интересное на этот счёт. В любом случае, считаю что нужно о нём сказать, так как на профильных ресурсах на большинство вопросов по интеграции двух БД отвечают «используйте FDW» и никакого разнообразия не предвидится, даже если вы желаете странного.

Итак, когда это может понадобиться: в случае, если все вышеописанные варианты не помогли в силу различных ограничений. Например:

  • неприлично большой объём внешней БД и невозможность её клонирования в PG;
  • строгие требования к быстродействию и наличие оптимального запроса во внешнюю БД;
  • желание выполнять параметризованный запрос, то есть аналог опции query для FDW, только с динамическим параметром, например хочется использовать полнотекстовый поиск на стороне MSSQL через функцию CONTAINS [33];
  • ещё что-нибудь необычное.

Чем пользоваться: dbi-link [34] или dblink-tds [35]. Сие есть аналоги dblink'а [36] но с поддержкой нескольких СУБД: PostgreSQL, MySQL, MSSQL Server и Oracle в случае dbi-link и просто TDS'а в случае dblink-tds.

Как видится механика работы: как некий узкоспециализированный аналог FDW в виде функции PG, которая собирает внутри себя нужный запрос исходя из переданных аргументов, выполняет его во внешней БД через вышеуказанные инструменты, получает данные, обрабатывает их и возвращает их в PG как pipeline-функция [37]. То есть, гипотетически, можно выполнить именно тот запрос, который хочется и представить его результат в виде, удобоваримом для последующей обработки в PG.

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

Заключение

На текущий момент есть единственное простое и рабочее решение для стыковки PostgreSQL и MSSQL. Это tds_fdw [2]. У него есть много недоработок, но проект развивается, баги чинятся, фичи выкатываются и это здорово. Поэтому tds_fdw может решить бо́льшую часть проблем, связанных с извлечением данных из MSSQL через PG. Тем же, кто хочется побыстрее, пооптимальнее и с куртизанками поможет PostgreSQL и его богатый арсенал инструментов по оптимизации. А те, кто желает очень странного и хочет делать всё внутри БД с минимумом внешних сервисов придётся туго. Инструментарий древний, документации нет, поддержки нет, населена роботами и кроме чтения исходников ничего не поможет.

Автор: Loriowar

Источник [38]


Сайт-источник PVSM.RU: http://www.pvsm.ru

Путь до страницы источника: http://www.pvsm.ru/postgresql/200099

Ссылки в тексте:

[1] описание интеграции: https://habrahabr.ru/company/postgrespro/blog/309490/

[2] tds_fdw: https://github.com/tds-fdw/tds_fdw

[3] офф.документация: https://www.postgresql.org/docs/current/static/sql-createextension.html

[4] офф.документация: https://www.postgresql.org/docs/9.5/static/sql-createserver.html"

[5] пример из tds_fdw: https://github.com/tds-fdw/tds_fdw/blob/master/ForeignServerCreation.md

[6] офф.документация: https://www.postgresql.org/docs/9.5/static/sql-createusermapping.html"

[7] пример из tds_fdw: https://github.com/tds-fdw/tds_fdw/blob/master/UserMappingCreation.md

[8] офф.документация: https://www.postgresql.org/docs/9.5/static/sql-createforeigntable.html"

[9] пример из tds_fdw: https://github.com/tds-fdw/tds_fdw/blob/master/ForeignTableCreation.md

[10] инструкции по установке tds_fdw: https://github.com/tds-fdw/tds_fdw/blob/master/InstallUbuntu.md

[11] Спасите котиков: https://habrahabr.ru/post/130868/

[12] вот issue на GitHub'е: https://github.com/tds-fdw/tds_fdw/issues/99

[13] изыскать: https://github.com/tds-fdw/tds_fdw/issues/100

[14] 3a803c: https://github.com/tds-fdw/tds_fdw/commit/3a803c5840b8cf69e1a725fb4377d0f95a53a4e9

[15] activerecord-sqlserver-adapter: http://activerecord-sqlserver-adapter

[16] tiny_tds: https://github.com/rails-sqlserver/tiny_tds

[17] FreeTDS: http://www.freetds.org/

[18] 0.91-5: http://packages.ubuntu.com/trusty/freetds-common

[19] 0.91-6.1build1: http://packages.ubuntu.com/xenial/freetds-common

[20] из исходников: https://github.com/FreeTDS/freetds

[21] альтернативным PPA: https://launchpad.net/~jamiewillis/+archive/ubuntu/freetds

[22] легко найти: https://rpmfind.net/linux/rpm2html/search.php?query=freetds

[23] трицепсовый жим вниз на блоке: http://www.multitran.ru/c/m.exe?l1=1&l2=2&s=pushdown

[24] file_fdw: https://www.postgresql.org/docs/9.5/static/file-fdw.html

[25] postgres_fdw: https://www.postgresql.org/docs/9.5/static/postgres-fdw.html

[26] здесь: http://www.freetds.org/userguide/logging.htm

[27] вот тут: http://www.freetds.org/userguide/freetdsconf.htm#TAB.FREETDS.CONF.DEBUGFLAGS

[28] Материализованное представление: https://www.postgresql.org/docs/current/static/sql-creatematerializedview.html

[29] B-tree: https://ru.wikipedia.org/wiki/B-%D0%B4%D0%B5%D1%80%D0%B5%D0%B2%D0%BE

[30] GIN и GiST: https://www.postgresql.org/docs/current/static/textsearch-indexes.html

[31] статистика: https://www.postgresql.org/docs/current/static/monitoring-stats.html

[32] соответствующей странице документации: https://www.postgresql.org/docs/9.5/static/sql-refreshmaterializedview.html

[33] CONTAINS: https://msdn.microsoft.com/ru-ru/library/ms187787.aspx

[34] dbi-link: http://pgfoundry.org/projects/dbi-link/

[35] dblink-tds: http://dblink-tds.projects.pgfoundry.org/

[36] dblink'а: https://www.postgresql.org/docs/9.5/static/dblink.html

[37] pipeline-функция: http://manojadinesh.blogspot.ru/2011/11/pipelined-in-oracle-as-well-in.html

[38] Источник: https://habrahabr.ru/post/312090/?utm_source=habrahabr&utm_medium=rss&utm_campaign=best