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.

Параметры#

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

SET tds_fdw.show_finished_memory_stats=1;

Foreign server#

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

Foreign table#

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

Пример:

Использование 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#

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

Пример:

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

Foreign schema#

Пример:

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.