Перейти к основному содержимому

tds_fdw. Оболочка внешних данных MSSQL, Sybase

Версия: 2.0.2.

В исходном дистрибутиве установлено по умолчанию: нет.

Связанные компоненты: драйвер freetds версии не ниже 1.1.20.

Схема размещения: ext.

Модуль представляет собой оболочку внешних данных PostgreSQL для подключения к базам данных Sybase и Microsoft SQL server, использующим протокол табличного потока данных TDS.

Для работы внешней оболочки данных tds_fdw требуется библиотека, реализующая интерфейс DB-Library, например, FreeTDS. Работа расширения была протестирована с FreeTDS (не с проприетарными реализациями DB-Library).

Расширение tds_fdw позволяет работать с таблицами базы данных MSSQL как с собственными таблицами Pangolin. Для подключения к сторонним базам данных Microsoft SQL Server используется драйвер freetds, который должен быть установлен на сервере с СУБД.

Оригинальное решение предполагает обязательное сохранение логина и пароля от сторонней БД при создании сопоставления пользователей, поэтому расширение для Pangolin дорабатывается с целью использовать для хранения пароля в шифрованном хранилище pg_auth_config.

Параметры​

ПараметрОписание
tds_fdw.show_before_row_memory_statsВывод статистики контекста памяти в лог PostgreSQL перед извлечением каждой строки
tds_fdw.show_after_row_memory_statsВывод статистики контекста памяти в лог PostgreSQL после извлечения каждой строки
tds_fdw.show_finished_memory_statsВывод статистики контекста памяти в лог PostgreSQL после завершения транзакции

Для установки параметра используйте команду SET:

SET tds_fdw.show_finished_memory_stats=1;

Foreign server​

Параметры сервера внешних данных:

ПараметрОбязательныйОписаниеСсылки на документацию
servernameДаИмя сервера, IP-адрес или fqdn внешнего сервера или DSN, указанный в файле freetds.conf; в опции DSN можно передать через запятую список серверов, при этом указанные в списке серверы будут последовательно применяться до первого успешного подключения; используется для автоматического переключения на другой сервер в случае неисправности;
Значение по умолчанию – 127.0.0.1		FreeTDS name lookup
portНетПорт стороннего сервера; параметр опциональный. Можно не указывать порт в этом параметре, а указать в файле freetds.conf, если в параметре servername указан DSN
databaseНетВнешняя база данных для подключения
dbuseНетПодключаться напрямую к БД, если параметр dbuse = 0 (значение по умолчанию);
Если dbuse не равен 0, tds_fdw будет подключаться к базе данных по умолчанию, затем выберет базу данных вызовом функции dbuse() из библиотеки DB-Library
languageНетЯзык, используемый в сообщениях, и локаль, используемая для форматирования дат;
FreeTDS по умолчанию использует us_english, но также может определить язык в freetds.conf		– MS SQL Server: SET LANGUAGE in MS SQL Server;
– Sybase ASE: Sybase ASE login options и SET LANGUAGE in Sybase ASE
character_setНетКлиентский набор символов, используемый для подключения, если нужно устанавливать его по какой-то причине;
Для протокола TDS версии выше 7.0, подключение использует UCS-2, данный параметр не требуется менять в большинстве случаев		Localization and TDS 7.0
tds_versionНетВерсия протокола TDS используемого для данного сервераChoosing a TDS protocol version;
History of TDS Versions
msg_handlerНетФункция обработчика сообщений TDS;
Допустимые значения:
– notice: сообщения от TDS будут переданы в сообщения PostgreSQL;
– blackhole (по умолчанию): сообщения TDS будут игнорироваться
fdw_startup_costНетСтоимость, которая используется при планировании запросов для представления накладных расходов на использование этого источника внешних данных
fdw_tuple_costНетСтоимость, которая используется при планировании запросов для представления накладных расходов на выборку строк с этого сервера

Foreign table​

Параметры внешней таблицы:

ПараметрОбязательныйОписание
queryДа, если не указан параметр table_nameСтрока запроса формирующая внешнюю таблицу; не может быть указан одновременно с параметром table_name
schema_nameНетИмя схемы содержащей внешнюю таблицу; имя схемы может быть также включено в параметр table_name
table_nameДа, если не указан параметр queryИмя внешней таблицы; не может быть указан одновременно с параметром query; алиас: table
match_column_namesНетЗадает логический признак, следует ли сопоставлять локальные столбцы с удаленными столбцами путем сравнения их имен (1) или использовать порядок их отображения в результирующем наборе (0)
use_remote_estimateНетЗадает логический признак, оценивать ли размер таблицы, выполняя какую-либо операцию на удаленном сервере (как определено row_estimate_method), или использовать локальную оценку, как определено local_tuple_estimate
local_tuple_estimateНетЛокально установленная оценка количества кортежей; используется, когда параметр use_remote_estimate выключен (0)
row_estimate_methodНетМожет принимать одно из следующих значений:
– execute (значение по умолчанию): выполнить запрос на удаленном сервере и получить актуальное количество строк в запросе;
– showplan_all: получить расчетное количество строк с использованием MS SQL Server's SET SHOWPLAN_ALL
column_nameНетДопустимый параметр столбцов в foreign table – имя столбца на удаленном сервере; если этот параметр не задан, предполагается, что удаленное имя столбца совпадает с локальным именем столбца; если для match_column_names для таблицы установлено значение 0, то имена столбцов не используются и этот параметр игнорируется

Пример:

Использование table_name:

CREATE FOREIGN TABLE mssql_table (
	id integer,
	data varchar)
	SERVER mssql_svr
	OPTIONS (table_name 'dbo.mytable', row_estimate_method 'showplan_all');

Использование schema_name и table_name:

CREATE FOREIGN TABLE mssql_table (
	id integer,
	data varchar)
	SERVER mssql_svr
	OPTIONS (schema_name 'dbo', table_name 'mytable', row_estimate_method 'showplan_all');

Использование query:

CREATE FOREIGN TABLE mssql_table (
	id integer,
	data varchar)
	SERVER mssql_svr
	OPTIONS (query 'SELECT * FROM dbo.mytable', row_estimate_method 'showplan_all');

Использование внешнего column_name:

CREATE FOREIGN TABLE mssql_table (
	id integer,
	col2 varchar OPTIONS (column_name 'data'))
	SERVER mssql_svr
	OPTIONS (schema_name 'dbo', table_name 'mytable', row_estimate_method 'showplan_all');

User mapping​

Параметры соответствия пользователей:

ПараметрОбязательныйОписание
usernameДаЛогин пользователя на внешнем сервере
passwordНетПароль пользователя на внешнем сервере; если не задан, то поиск пароля будет выполняться по параметрам сервера из foreign server в хранилище pg_auth_config

Пример:

CREATE USER MAPPING FOR postgres
	SERVER mssql_svr
	OPTIONS (username 'sa', password '');

Foreign schema​

ПараметрОбязательныйЗначение по умолчаниюОписание
import_defaultНетfalseЗадает включение значения DEFAULT для столбцов в определения внешних таблиц
import_not_nullНетtrueЗадает включение ограничения столбца NOT NULL в определения внешних таблиц

Пример:

IMPORT FOREIGN SCHEMA dbo
	EXCEPT (mssql_table)
	FROM SERVER mssql_svr
	INTO public
	OPTIONS (import_default 'true');

Explain​

EXPLAIN (VERBOSE) показывает запрос, сформированный в удаленной системе:

explain select * from tds."Inventory";

Пример ответа:

NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
                             QUERY PLAN
---------------------------------------------------------------------
 Foreign Scan on "Inventory"  (cost=200.00..500.04 rows=4 width=100)
(1 row)

Замечания​

  1. Возможна исключительная ситуация, когда из MS SQL Server при работе с данными в Unicode получены ошибки вида:

    NOTICE: DB-Library notice: Msg #: 4004, Msg state: 1, Msg: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier., Server: PILLIUM\SQLEXPRESS, Process: , Line: 1, Level: 16
    ERROR: DB-Library error: DB #: 4004, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: (null), Level: 16

    В этом случае необходимо вручную установить параметр tds_version в файле freetds.conf в значение 7.0 или выше.

  2. Несмотря на то, что новые версии протокола TDS используют USC-2 для взаимодействия с сервером, FreeTDS конвертирует UCS-2 в набор символов, выбранный при настройке.

    Для того чтобы установить необходимый набор символов, необходимо задать параметр client charset в файле freetds.conf.

  3. Возможно использование шифрованного соединения с базой данных MS SQL Server.

    Данная возможность предоставляется драйвером FreeTDS. Для настройки необходимо сконфигурировать файл freetds.conf.

  4. Некоторые типы данных, используемые в MS SQL Server, например DATETIMEOFFSET, не поддерживаются или не имеют аналогов в Pangolin.

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

Подсказка

Описание в документации:

Доработка​

В рамках доработки tds_fdw было реализовано использование защищенного хранилища паролей pg_auth_config.

Описание утилиты шифрования и хранения параметров подключения к базе данных (pg_auth_config) в документе «Функциональное администрирование», раздел «Засекречивание и хранение параметров подключения».

Параметры подключения к внешней базе данных берутся из pg_auth_config при условии, если они:

  • не указаны в pg_user_mappings;
  • присутствуют в pg_auth_config.

Установка​

Для работы расширения tds_fdw необходимо, чтобы в операционной системе был установлен драйвер freetds версии не ниже 1.1.20. Репозиторий EPEL в /etc/yum.repos.d/mirror.repo должен быть включен.

Установка драйвера freetds:

sudo yum install freetds

Вместе с установкой драйвера будут установлены зависимости:

  • freetds-libs;
  • unixODBC.

При наличии прав администратора СУБД включение модуля выполняется запросом:

CREATE EXTENSION tds_fdw SCHEMA ext;

Исключительная ситуация следующего вида при создании расширения в базе данных означает, что драйвер freetds был установлен некорректно или его версия ниже 1.1.20.

ERROR:  could not load library "/usr/pangolin-5.4.0/lib/tds_fdw.so": libsybdb.so.5: cannot open shared object file: No such file or directory

Настройка​

Добавьте параметры подключения к базе данных MSSQL при помощи утилиты pg_auth_config:

pg_auth_config add --host <ip_address_mssql> --port <mssql_port> --user <mssql_user> --database <mssql_database>

Пароль будет запрошен интерактивно.

Выдайте права на использование расширения пользователю:

GRANT USAGE ON FOREIGN DATA WRAPPER tds_fdw TO <user>;

Использование модуля​

  1. Создайте сторонний сервер MSSQL.

    CREATE SERVER mssql_svr
       FOREIGN DATA WRAPPER tds_fdw
       OPTIONS (servername '127.0.0.1', port '1433', database 'tds_fdw_test');

  2. Выдайте права на использование стороннего сервера пользователю.

    GRANT USAGE ON FOREIGN SERVER mssql_svr TO tdsuser;

  3. Добавьте параметры подключения к базе данных MsSQL в хранилище pg_auth_config.

    pg_auth_config add --host <IP-адрес> --port <Порт> --user mssql_user --database test_mssqldb

  4. Создайте соответствие пользователей в БД Pangolin и MSSQL.

    CREATE USER MAPPING FOR <postgres_user>
       SERVER mssql_svr
       OPTIONS (username 'mssql_user');

  5. Создайте стороннюю таблицу или выполнить импорт схемы.

    • сторонняя таблица:

      CREATE FOREIGN TABLE pgschema.mssql_table (
         id integer,
         data varchar)
         SERVER mssql_svr
         OPTIONS (schema_name 'dbo', table_name 'mytable', row_estimate_method 'showplan_all');

    • импорт схемы:

      IMPORT FOREIGN SCHEMA dbo
         FROM SERVER mssql_svr
         INTO pgschema
         OPTIONS (import_default 'true');

  6. Выполните транзакцию.

    SELECT * FROM pgschema.mssql_table;

Ссылки на документацию разработчика​

Дополнительно поставляемый модуль tds_fdw: https://access.crunchydata.com/documentation/tds_fdw/latest/.

Исходный код расширения tds_fdw: https://github.com/tds-fdw/tds_fdw.

Описание драйвера FreeTDS: https://www.freetds.org/index.html.