Інтеграція PostgreSQL з MS SQL Server для тих, хто бажає швидше і глибше

Нещодавно на хабре вже було опубліковано опис інтеграції PostgreSQL та ms sql. Але, деталей там категорично не вистачало. Тому, мети цього публікації наступні:
  • розширити і поглибити загальнодоступну інформацію про FDW для MSSQL званому tds_fdw: розповісти про різницю в мажорних версіях і описати базові проблеми сумісності;
  • розповісти про можливості оптимізації запитів, що використовують зовнішні таблиці;
  • зачепити тему кешування зовнішніх даних в матеріалізованих представлень;
  • сказати пару слів про екзотичних підходи до інтеграції PostgreSQL та ms sql.


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

Хлопці з PostgresPro вже досить сказали про цьому процесі, повторюватися не буду. Залишу лише кілька посилань на офіційну документацію PostgreSQL і на приклади з tds_fdw:
І ще один момент: будь ласка, не робіть як зазначено в інструкції з встановлення tds_fdw
sudo make USE_PGXS=1 install

Врятуйте котиків, зберіть 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'е), хоча достеменно не відомо: у мене руки не звідти ростуть або баг досить хитрий і проявляється тільки при певному збігу обставин.


Підводні камені сумісності

До недавнього часу, tds_fdw не працював з версією tds вище 7.3. Але в ході написання цього статті підтримку версії 7.4 довелося знайти. Тому тепер, починаючи з коміта 3a803c, tds_fdw підтримує всі актуальні версії tds.

Чому ж підтримка цієї версії так важлива? Особисто для мене це важливо через необхідність працювати з MSSQL 2012. Якщо коротко: в Ruby on Rails для підключення до MSSQL використовується бібліотека activerecord-sqlserver-adapter, яка, в свою чергу, використовує tiny_tds, який використовує FreeTDS, який вміє спілкуватися з 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: connection closed: ...
     
    
  • 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 і 0.91-6.1build1 відповідно. І отримати більш нову версію FreeTDS можна двома способами:
  1. зібрати FreeTDS з исходников;
  2. скористатися альтернативним PPA з версією FreeTDS 1.00.
У другому випадку є один нюанс: у зазначеному репозиторії є пакет тільки для Ubuntu 14.04 (яка trusty). Для 16.04 (яка xenial), там нічого немає. Але, в цілому, нічого фатального і якщо в 16.04 поправити /etc/apt/sources.list.d/jamiewillis-freetds-вірного.list на що-небудь отаке
deb http://ppa.launchpad.net/jamiewillis/freetds/ubuntu trusty main

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

Якщо ж у вас CentOS, то під неї можна легко знайти 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. Чомусь опис цього механізму не затребуване в російськомовних ресурсах (або я не знайомий з його правильним перекладом, а трицепсовий жим вниз на блоці це явно не з тієї опери). Тому хочеться коротко розповісти про нього.

У найпростішому випадку, коли ми в 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 pushdown майже марний, а ось для postgres_fdw або tds_fdw — зовсім навпаки).

Загальна: pushdown — це круто, він дозволяє використовувати механізми зовнішнього сховища даних, зменшує обсяг даних, що циркулюють між PG і зовнішнім сховищем, тим самим прискорюючи виконання запитів, але, при цьому, він є окремим механізмом, тому його потрібно реалізовувати, підтримувати і це досить нетривіальне завдання.

Прискорення запитів

З установкою, налаштуванням і матчастью розібралися. Тепер приступимо до опису того, як можна швидше витягти дані з MSSQL.

Pushdown
Стане в нагоді такий підхід у випадку простих запитів, не обтяжених різними JOIN та іншими SQL-хитрощами. В останній версії tds_fdw (на поточний момент це 2.0.0-alpha) з'явилася підтримка найпростішого pushdown для WHERE.

Для прикладу розглянемо таблицю simple_table з БД ms sql. У цій таблиці є два поля: 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 написати тут, а деталі про `debug flags` лежать ось тут.

Materialized view
Матеріалізоване уявлення (далі MV) — це звичайне уявлення + таблиця з даними. Цей підхід допоможе у випадку складних запитів з джойнами зовнішніх і внутрішніх таблиць, з функціями, преферансом і куртизанками.

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

Для вищеописаної зовнішньої таблиці MV можна створити таким чином:
CREATE MATERIALIZED VIEW AS materialized_mssql_table
SELECT id, custom_data
FROM mssql_table;

Тепер всі дані з MSSQL є в PostgreSQL, а значить їх можна індексувати як заманеться (B-tree, GIN і GiST та ін), для них стає доступна статистика, можна побачити деталі про план виконання запиту і ще багато чого приємного з PG.

Оновити MV можна через стандартні INCERT/UPDATE/DELETE команди, або просто відновити весь вміст за допомогою
REFRESH MATERIALIZED VIEW CONCURRENTLY materialized_mssql_table;

Опція CONCURRENTLY дозволяє оновити MV не блокуючи конкуруючі запити на читання, але вимагає більше часу і ресурсів. Так само, для можливості використання CONCURRENTLY цільове MV повинно відповідати деяким вимогам. Їх можна знайти на відповідній сторінці документації.

Екзотичний підхід
Чесно кажучи, чорт знає, може злетіти цей підхід, можливо шановна публіка розповість що-небудь цікаве на цей рахунок. У будь-якому випадку, вважаю що потрібно про нього сказати, так як на профільних ресурсах на більшість питань по інтеграції двох БД відповідають «використовуйте FDW» і ніякого різноманіття не передбачається, навіть якщо ви бажаєте дивного.

Отже, коли це може знадобитися у випадку, якщо всі вищеописані варіанти не допомогли в силу різних обмежень. Наприклад:
  • непристойно великий обсяг зовнішньої БД і неможливість її клонування в PG;
  • суворі вимоги до швидкодії та наявність оптимального запиту в зовнішню БД;
  • бажання виконувати параметризований запит, тобто аналог опції query FDW, тільки з динамічним параметром, наприклад хочеться використовувати повнотекстовий пошук на стороні MSSQL через функцію CONTAINS;
  • ще що-небудь незвичайне.
Як користуватися: dbi-link або dblink-tds. Це є аналоги dblink'а але з підтримкою кількох СУБД: PostgreSQL, MySQL, MSSQL Server і Oracle у разі dbi-link і просто TDS'а в разі dblink-tds.

Як бачиться механіка роботи: як якийсь вузькоспеціалізований аналог FDW у вигляді функції PG, яка збирає всередині себе потрібний запит виходячи з переданих аргументів, виконує його зовнішньої БД через вищезазначені інструменти, отримує дані, обробляє їх і повертає їх у PG pipeline-функция. Тобто, гіпотетично, можна виконати саме той запит, який хочеться і представити його результат у вигляді, удобоваримом для подальшої обробки в PG.

Все вищесказане є чистими теоретичними міркуваннями. Якщо у вас є реальний досвід використання цих чи подібних інструментів, будь ласка, поділіться своїми знаннями зі світом.

Висновок

На поточний момент є єдине просте і робоче рішення для стикування PostgreSQL та ms sql. Це tds_fdw. У нього є багато недоробок, але проект розвивається, баги чиняться, фічі викочуються і це здорово. Тому tds_fdw може вирішити більшу частину проблем, пов'язаних з отриманням даних з ms sql через PG. Тим же, хто хочеться швидше, пооптимальнее і з куртизанками допоможе PostgreSQL і його багатий арсенал інструментів щодо оптимізації. А ті, хто бажає дуже дивного і хоче робити все всередині БД з мінімумом зовнішніх сервісів доведеться туго. Інструментарій древній, документації немає, підтримки немає, населена роботами і, крім читання исходников нічого не допоможе.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.