Лекция 14. Внешние источники данных

В этой главе:

Способы подключения к внешним источникам.
Расширение dblink.
Использование FDW.

Способы подключения к внешним источникам

repbase=# SELECT name, comment FROM pg_available_extensions where name ~ 'fdw' OR name = 'dblink';
         name | comment 
--------------+-----------------------------------------------------------------------------------
postgres_fdw  | foreign-data wrapper for remote PostgreSQL servers
tds_fdw       | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)
dblink        | connect to other PostgreSQL databases from within a database
file_fdw      | foreign-data wrapper for flat file access
oracle_fdw    | foreign data wrapper for Oracle access
(5 rows)

PostgreSQL позволяет подключаться к источникам данных, не принадлежащим текущей базе данных экземпляра.

С Pangolin поставляются расширения для доступа к внешним источниками данных:

– dblink - для подключения к другой БД или другому экземпляру PostgreSQL; – postgres_fdw - стандартный способ подключения к PostgreSQL с помощью внешних таблиц; – oracle_fdw - подключение к Oracle RDBMS с помощью внешних таблиц; – tds_fdw - то же, но для MS SQL Server или Sybase. – file_fdw - подключение к внешним текстовым файлам с представлением в виде внешних таблиц.

В PostgreSQL имеется несколько расширений, обеспечивающих обмен данными между таблицами баз данных экземпляра с внешними источниками данных. Под внешними источниками данных понимаются:

другие БД этого же экземпляра;
БД других экземпляров PostgreSQL;
БД, обслуживаемых СУБД других производителей;
нереляционные БД;
структурированныефайлы.

Из множества всех расширений для PostgreSQL с Pangolin поставляются расширения для подключения к основным промышленным СУБД, использующимися ныне: Oracle RDBMS и MS SQL Server. Кроме того, в Pangolin есть расширение для подключения к структурированным текстовым файлам. Основной способ доступа к внешним данным в PostgreSQL - это расширения, реализующие Foreign Data Wrappers - FDW. Все они строятся на стандартизованной основе и для доступа к внешним данным используют понятие внешней таблицы FOREIGN TABLE. https://www.postgresql.org/docs/15/ddl-foreign-data.html.

Другое расширение - dblink не использует эту инфраструктуру, но обладает широкими возможностями и также повсеместно используется для доступа к внешним PostgreSQL источникам данных и выполнения произвольных команд не удаленном сервере. https://www.postgresql.org/docs/15/dblink.html.

Расширение dblink

Подключение расширения dblink

externaldb=# \c localdb - - 5432
You are now connected to database "localdb" as user "postgres" via socket in "/tmp" at port "5432". 
localdb=# CREATE EXTENSION dblink ;
CREATE EXTENSION
localdb=# \dx+ dblink
                               Objects in extension "dblink"
                                     Object description 
-------------------------------------------------------------------------
foreign-data wrapper dblink_fdw
function dblink_build_sql_delete(text,int2vector,integer,text[]) 
function dblink(text,text,boolean)
...
type dblink_pkey_results
(43 rows)

Расширение dblink предоставляет множество функций для подключения и выполнения запросов и других действий в удаленной базе данных PostgreSQL.

Основные возможности: управление соединениями, выполнение запросов, управление курсорами, проверка статуса удаленного сервера, посылка асинхронных сообщений.

Подготовим инфраструктуру для дальнейшей демонстрации:

repbase=# \c - - - 5432
You are now connected to database "repbase" as user "postgres" via
socket in "/tmp" at port "5432".
repbase=# CREATE DATABASE localdb;
CREATE DATABASE
repbase=# \c - - - 6432
You are now connected to database "repbase" as user "postgres" via
socket in "/tmp" at port "6432".
repbase=# CREATE DATABASE externaldb;
CREATE DATABASE
repbase=# \c externaldb
You are now connected to database "externaldb" as user "postgres".
externaldb=# CREATE TABLE exttab(id serial PRIMARY KEY, msg text);
CREATE TABLE

Теперь на сервере, прослушивающем порт 5432 создана БД localdb, а на удаленном, прослушивающем порт 6432, создана БД externaldb. В ней же создана таблица exttab, к которой необходимо подключиться из сессии, подключенной к БД localdb. Показанные на слайде команды подключили расширение dblink к БД localdb и вывели функции, доступные из расширения (43 функции).

Соединение с удаленной БД

localdb=# SELECT dblink_connect('conn2extdb','port=6432 dbname=externaldb'); dblink_connect
----------------
 OK
(1 row)
localdb=# SELECT dblink_get_connections();
 dblink_get_connections
------------------------
 {conn2extdb}
(1 row)

Функция dblink_connect() открывает постоянное соединение с удаленным сервером.

Информацию об открытых соединениях возвращает функция dblink_get_connections().

Открытые соединения закрывает вызов функции dblink_close().

Многие функции расширения dblink самостоятельно открывают соединения, и после завершения своей работы - автоматически его закрывают. Но процедура открытия соединения требует запуска обслуживающего процесса на сервере, поэтому затратна. Во многих случаях достаточно один раз открыть соединение вызовом функции dblink_connect(), а после завершения обработки, закрыть соединение вызовом dblink_close(). В примере на слайде открыто соединение с именем conn2extdb. Вывод списка открытых соединений выполнен вызовом функции dblink_get_connections(). https://www.postgresql.org/docs/15/contrib-dblink-open.html.

https://www.postgresql.org/docs/15/contrib-dblink-close.html.

https://www.postgresql.org/docs/15/contrib-dblink-get-connections.html.

Запрос к удаленной БД

localdb=# SELECT * FROM dblink('conn2extdb','SELECT * FROM exttab') AS t(i integer, t text); i|t
---+---------------
 1 | Первая строка
 2 | Вторая
(2 rows)

Выполнить запрос к удаленной БД позволяет функция dblink().

Эта функция возвращает набор структурированных строк (SETOF record).

Для отображения набора строк в виде таблицы после ключевого слова AS были указаны, какие поля в имеются структурированной строке.

Функция dblink() одноименного расширения позволяет выполнить запрос, возвращающий данные. Все перегруженные варианты этой функции возвращают результат в виде набора строк:

localdb=# \df dblink
                              Список функций
 Схема  |  Имя   | Тип данных результата | Типы данных аргументов |  Тип
--------+--------+-----------------------+------------------------+-------
 public | dblink | SETOF record          | text                   | функ.
 public | dblink | SETOF record          | text, boolean          | функ.
 public | dblink | SETOF record          | text, text             | функ.
 public | dblink | SETOF record          | text, text, boolean    | функ.
(4 строки)

Для вывода результирующих строк в виде таблицы надо задать, какие поля имеются в структуре и каковы типы данных этих полей. Указание достигается добавлением конструкции AS t(i integer, t text). Она сообщает PostgreSQL, что в возвращаемом наборе строки имеют два поля: первое i - целочисленное, второе t - текстовое. Количество и тип полей должны соответствовать структуре удаленной таблицы. https://www.postgresql.org/docs/15/contrib-dblink-function.html.

https://www.postgresql.org/docs/15/rowtypes.html.

https://www.postgresql.org/docs/15/sql-createfunction.html.

Завершение соединения

localdb=# SELECT dblink_disconnect('conn2extdb'); dblink_disconnect
-------------------
 OK
(1 строка)
localdb=# SELECT dblink_get_connections();
 dblink_get_connections
------------------------
(1 строка)

После выполнения требуемой работы с удаленной БД необходимо завершить открытое соединение.
Закрыть соединение можно вызовом функции dblink_disconnect().

После выполнения запросов в удаленной БД не следует забывать закрывать удаленные сессии. Каждая открытая сессия порождает на стороне удаленного сервера обслуживающий процесс, который, даже простаивая, расходует системные ресурсы. Закрыть удаленную сессию dblink позволяет функция dblink_disconnect(). https://www.postgresql.org/docs/15/contrib-dblink-disconnect.html.

Если расширение dblink больше не требуется, его следует удалить:

localdb=# DROP EXTENSION dblink ;
DROP EXTENSION

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

Расширение postgres_fdw

localdb=# CREATE EXTENSION postgres_fdw ; CREATE EXTENSION

Данные, не находящиеся в текущей БД, делают доступными FDW (Foreign Data Wrappers), позволяя создавать внешние таблицы командой CREATE FOREIGN TABLE.

Расширение postgres_fdw позволяет создавать внешние таблицы, позволяющие подключиться к удаленным PostgreSQL серверам.

Для реализации удаленного доступа необходимо создать:

расширение postgres_fdw командой CREATE EXTENSION;
серверный объект командой CREATE SERVER;
отображение пользователей, используя CREATE USER MAPPING;
внешнюю таблицу командой CREATE FOREIGN TABLE или IMPORT FOREIGN SCHEMA.

Инфраструктура FDW стандартна и для использования различных способов доступа к внешним данным, основанным на FDW, приходится выполнять приблизительно одинаковые действия. Начать надо с установки соответствующего расширения. Далее необходимо создать серверный объект для представления внешних данных. После этого необходимо сопоставить роли в локальном экземпляре и удаленном сервере. После этого все будет готово для создания внешней таблицы. https://www.postgresql.org/docs/15/postgres-fdw.html.

Создание внешнего сервера

localdb=# CREATE SERVER srv6432 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', port '6432', dbname 'externaldb'); 
CREATE SERVER
localdb=# \x \des+ \x
List of foreign servers
-[ RECORD 1 ]--------+-----------------------------------------------------
Name                 | srv6432
Owner                | postgres
Foreign-data wrapper | postgres_fdw
Access privileges    | 
Type                 | 
Version              |
FDW options          | (host 'localhost', port '6432', dbname 'externaldb')
Description          |

Опции команды CREATE SERVER задают адрес хоста, номер порта TCP и имя БД для подключения.
Проверить созданные серверные объекты можно метакомандой psql \des.

Команда CREATE SERVER позволяет указать, к какому серверу и как подключаться посредством опций. В примере были использованы опции host для указания сетевого узла, на котором работает удаленный экземпляр PostgreSQL. Опция port указывает номер порта на удаленном сервере, а dbname - имя удаленной базы данных. Для проверки свойств серверных объектов используют метакоманду psql \des. В примере использована метакоманда \des+ с более подробным выводом. https://www.postgresql.org/docs/15/sql-createserver.html.

Карта сопоставления пользователей

localdb=# CREATE USER MAPPING FOR postgres SERVER srv6432 OPTIONS (user 'postgres', password 'postgres');
CREATE USER MAPPING
localdb=# \x \deu+ \x
List of user mappings
-[ RECORD 1 ]----------------------------------------- 
Server      | srv6432
User name   | postgres
FDW options | ("user" 'postgres', password 'postgres')

Карта соответствия локальных пользователей удаленным создается командой CREATE USER MAPPING.

Проверить карту отображений можно метакомандой \deu.

В примере создано отображение локально суперпользователя postgres на суперпользователя удаленного сервера postgres. Пароль задан здесь же. Задавать пароль в явном виде - плохая практика и существуют более безопасные способы для аутентификации. Здесь пароль для удаленного пользователя указан для простоты демонстрации. Проверить список отображений пользователей позволяет метакоманда psql \deu.

https://www.postgresql.org/docs/15/sql-createusermapping.html.

Внимание! Не путайте карту отображений пользователей FDW с файлом конфигурации pg_ident.conf, задающим карту отображений пользователей при внешней аутентификации.

Создание внешней таблицы

localdb=# IMPORT FOREIGN SCHEMA public LIMIT TO (exttab) FROM SERVER srv6432 INTO public; IMPORT FOREIGN SCHEMA
localdb=# \d exttab
                                   Foreign table "public.exttab"
Column  | Type    | Collation | Nullable | Default | FDW options
--------+---------+-----------+----------+---------+--------------------- 
id      | integer |           | not null |         | (column_name 'id') 
msg     | text    |           |          |         | (column_name 'msg')
Server: srv6432
FDW options: (schema_name 'public', table_name 'exttab')

Создать внешнюю таблицу можно командами CREATE FOREIGN TABLE или IMPORT FOREIGN SCHEMA, однако последней воспользоваться проще, так как она автоматически переносит структуру таблицы.

Команда создания внешней таблицы не сработает, если нет серверного объекта или карты отображений пользователей.

Запросы к внешней таблице

localdb=# SELECT * FROM exttab ; 
 id | msg
----+---------------
  1 | Первая строка
  2 | Вторая
(2 rows)
localdb=# UPDATE exttab SET msg = upper(msg); 
UPDATE 2
localdb=# INSERT INTO exttab VALUES (3,'ТРЕТЬЯ'); 
INSERT 0 1
localdb=# SELECT * FROM exttab ;
 id |      msg
----+---------------
  1 | ПЕРВАЯ СТРОКА
  2 | ВТОРАЯ
  3 | ТРЕТЬЯ
(3 rows)

Удобство инфраструктуры FDW в том, что она соответствует стандарту SQL и запросы к внешним таблицам можно выполнять так же, как и к обычным.

Запросы к внешним таблицам можно выполнять точно так же, как и к обычным таблицам базы данных. Однако, обертка может быть ограничена с помощью опций настройки. Так, например, можно включать и выключать импорт ограничений целостности. https://www.postgresql.org/docs/15/postgres-fdw.html.

Расширение file_fdw

Предназначено для доступа к текстовому файлу в файловой системе сервера с помощью внешней таблицы, доступной только для чтения.

Для выполнения запросов внешняя таблица удобнее, чем использование команды COPY.

Текстовый файл, в котором физически хранятся данные должен быть отформатирован в соответствии с любым поддерживаемым командой COPY FROM формате.

Обычное применение - загрузка данных из текстовых журналов отчета.

Загружать данные из структурированных текстовых данных можно, используя команду COPY FROM. Однако она способна загружать внешние данные в обычную таблицу базы данных, и лишь затем к этой таблице можно делать запросы. В случаях, когда было бы удобнее сразу использовать текстовые источники данных для запросов, можно использовать FDW, читающий текстовые структурированные файлы - расширение file_fdw. Пример часто решаемой задачи - загрузка журналов отчетов с фильтрацией. Расширение file_fdw поддерживает внешние таблицы для запросов только на чтение. https://www.postgresql.org/docs/15/file-fdw.html.

Итоги

Основной механизм для доступа к внешним данным - Foreign Data Wrappers (FDW).
FDW предполагают стандартизованный механизм обращения к различным источникам данных, позволяющий использовать внешние таблицы.
Для создания внешней таблицы необходимо создать серверный объект и (чаще всего) карту отображения пользователей.
Pangolin поставляется с FDW для подключения к PostgreSQL, Oracle RDBMS, MS SQL Server, а также к источникам данных в структурированных текстовых файлах.
Расширение dblink не следует стандарту FDW, но используется широко и позволяет выполнять на удаленном PostgreSQL сервере запросы и иные команды.

Способы подключения к внешним источникам​

Расширение dblink​

Подключение расширения dblink​

Соединение с удаленной БД​

Запрос к удаленной БД​

Завершение соединения​

Использование FDW​

Расширение postgres_fdw​

Создание внешнего сервера​

Карта сопоставления пользователей​

Создание внешней таблицы​

Запросы к внешней таблице​

Расширение file_fdw​

Итоги​