Внешние таблицы

Евгения Кузина

Обзор

PostgreSQL частично реализует спецификацию SQL/MED. Это позволяет использовать SQL-запросы для доступа к данным, которые находятся за пределами кластера PostgreSQL. Эти данные называются внешними. Чтобы получить к ним доступ, выполните следующие действия:

  1. Используйте обёртку внешних данных (foreign data wrapper). Обёртка внешних данных подключается к внешним источникам и получает от них данные. Расширение contrib, включенное в установку ADPG, содержит несколько обёрток внешних данных. За дополнительной информацией обратитесь к статье Additional Supplied Modules. Также можно использовать сторонние обёртки или написать свои собственные, как рассказано в статье Writing a foreign data wrapper.

  2. Создайте внешний сервер (foreign server), который инкапсулирует информацию о соединении для соответствующей обёртки внешних данных. Используйте для этого команду CREATE SERVER:

    CREATE SERVER [ IF NOT EXISTS ] <имя_сервера> [ TYPE '<тип_сервера>' ] [ VERSION '<версия_сервера>' ]
FOREIGN DATA WRAPPER <имя_обёртки>
[ OPTIONS ( <опция 'значение' [, ... ]> ) ]
    Параметры команды CREATE SERVER

    IF NOT EXISTS

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

    имя_сервера

    Имя создаваемого внешнего сервера. Имя сервера должно быть уникальным в рамках базы данных

    тип_сервера

    Необязательный параметр, определяющий тип сервера, потенциально полезный для обёрток внешних данных

    версия_сервера

    Необязательный параметр, указывающий версию сервера, потенциально полезный для обёрток внешних данных

    имя_обёртки

    Имя обёртки внешних данных, которая управляет внешним сервером

    OPTIONS ( <опция 'значение' [, …​ ]> )

    Указывает параметры сервера. Эти параметры определяют детали подключения, но их имена и значения зависят от обёртки внешних данных

    Пользователь, создавший сервер, становится его владельцем. Чтобы создать сервер, пользователь должен иметь привилегию USAGE для указанной обёртки внешних данных.

  3. Для доступа к удаленным данным может потребоваться аутентификация во внешнем источнике данных. Создайте объект user mapping, использующийся для сопоставления пользователей. Он позволяет указать имя пользователя и пароль, которые можно использовать для аутентификации текущей роли PostgreSQL на стороннем сервере. Для этого выполните команду CREATE USER MAPPING со следующим синтаксисом:

    CREATE USER MAPPING [ IF NOT EXISTS ] FOR { <имя_пользователя> | USER | CURRENT_ROLE | CURRENT_USER | PUBLIC }
    SERVER <имя_сервера>
    [ OPTIONS ( <опция 'значение' [, ... ]> ) ]
    Параметры команды CREATE USER MAPPING

    IF NOT EXISTS

    Если указан этот параметр, PostgreSQL не выдаст ошибку, если объект user mapping для указанного пользователя для подключения к указанному серверу существует. Не гарантируется, что существующий объект соответствует тому, который мог бы быть создан

    имя_пользователя

    Указывает имя существующего пользователя, для сопоставления с пользователем на внешнем сервере. CURRENT_ROLE, CURRENT_USER и USER соответствуют имени текущего пользователя. Когда указано PUBLIC, создаётся так называемое общее сопоставление, которое будет использоваться при отсутствии сопоставления для конкретного пользователя

    имя_сервера

    Определяет имя существующего сервера, для которого создаётся объект user mapping

    OPTIONS ( <опция 'значение' [, …​ ]> )

    Указывает параметры объекта user mapping. Параметры обычно определяют фактическое имя пользователя и пароль на стороннем сервере. Допустимые имена и значения параметров зависят от обёртки внешних данных

    Владелец внешнего сервера может создавать объекты user mapping для этого сервера для любого пользователя. Пользователь также может создать user mapping для себя, если ему предоставлена привилегия USAGE на внешнем сервере.

  4. Создайте одну или несколько внешних таблиц (foreign tables), определяющих структуру внешних данных. Внешняя таблица не хранит данные на сервере PostgreSQL, но к ней можно обращаться с помощью запросов как к обычной таблице. PostgreSQL использует обёртку внешних данных для извлечения данных из стороннего источника и передачи данных в этот источник в случае команд обновления.

    Выполните команду CREATE FOREIGN TABLE, чтобы создать внешнюю таблицу:

    CREATE FOREIGN TABLE [ IF NOT EXISTS ] <имя_таблицы> ( [
  { <имя_столбца> <тип_данных> [ COLLATE <правило_сортировки> ] [ <ограничение_столбца> [ ... ] ] | <ограничение_таблицы> }
    [, ... ]
] )
[ INHERITS ( <таблица_родитель> [, ... ] ) ]
  SERVER <имя_сервера>
[ OPTIONS ( <опция 'значение' [, ... ]> ) ]

CREATE FOREIGN TABLE [ IF NOT EXISTS ] <table_name>
  PARTITION OF <главная_таблица> [ (
  { <имя_столбца> [ WITH OPTIONS ] [ <ограничение_столбца> [ ... ] ]
    | <ограничение_таблицы> }
    [, ... ]
) ]
{ FOR VALUES <границы_секции> | DEFAULT }
  SERVER <имя_сервера>
[ ( <опция 'значение' [, ... ]> ) ]
    Параметры команды CREATE FOREIGN TABLE

    IF NOT EXISTS

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

    имя_таблицы

    Имя таблицы, может включать имя схемы (<имя_схемы>.<имя_таблицы>)

    имя_столбца

    Имя столбца, который должен быть создан в новой таблице

    тип_данных

    Тип данных столбца, также может включать определение массива с этим типом. За дополнительными сведениями о типах данных обратитесь к статье Data types

    COLLATE <правило_сортировки>

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

    INHERITS ( <таблица_родитель> [, …​ ] )

    Указывает список таблиц, от которых новая таблица автоматически наследует все столбцы. Родительские таблицы могут быть обычными таблицами или внешними таблицами. За дополнительной информацией обратитесь к статье Наследование

    PARTITION OF <главная_таблица>

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

    ограничение_столбца

    Ограничение столбца, которое описано в разделе Использование ограничений

    ограничение_таблицы

    Ограничение таблицы, которое описано в разделе Использование ограничений

    имя_сервера

    Определяет имя существующего внешнего сервера, используемого для внешней таблицы

    OPTIONS ( <опция 'значение' [, …​ ]> )

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

    Кроме того, вы можете импортировать внешнюю схему целиком. Для этого выполните команду IMPORT FOREIGN SCHEMA. Она создает внешние таблицы, представляющие таблицы, существующие на внешнем сервере. PostgreSQL импортирует внешние таблицы с определениями столбцов и параметрами, которые соответствуют таблицам со стороннего сервера. Пользователь, выполнивший команду IMPORT FOREIGN SCHEMA, становится владельцем внешних таблиц. Команда имеет следующий синтаксис:

    IMPORT FOREIGN SCHEMA <внешняя_схема>
    [ { LIMIT TO | EXCEPT } ( <имя_таблицы> [, ...] ) ]
    FROM SERVER <имя_сервера>
    INTO <локальная_схема>
    [ OPTIONS ( <опция 'значение' [, ... ]> ) ]
    Параметры команды IMPORT FOREIGN SCHEMA

    внешняя_схема

    Указывает внешнюю схему для импорта. Что именно представляет собой внешняя схема, зависит от применяемой обёртки внешних данных

    LIMIT TO ( <имя_таблицы [, …​]> )

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

    EXCEPT ( <имя_таблицы [, …​]> )

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

    имя_сервера

    Определяет внешний сервер для импорта

    локальная_схема

    Указывает схему, в которой будут созданы импортированные таблицы

    OPTIONS ( <опция 'значение' [, …​ ]> )

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

    Список импортируемых таблиц может быть ограничен определенным подмножеством, либо отдельные таблицы могут быть исключены из импорта. Локальная схема уже должна существовать на момент выполнения команды.

    Чтобы выполнить IMPORT FOREIGN SCHEMA, пользователь должен иметь привилегию USAGE для внешнего сервера и привилегию CREATE для целевой локальной схемы.

Чтобы отобразить внешние таблицы, существующие в текущей базы данных, используйте следующий запрос:

SELECT * FROM information_schema.foreign_tables;

Примеры

Расширение contrib содержит следующие обёртки внешних данных:

  • postgres_fdw — используется для доступа к данным, хранящимся на сторонних серверах PostgreSQL. Модуль описан в статье postgres_fdw.

  • file_fdw — используется для доступа к файлам данных в файловой системе сервера или для выполнения программ на сервере и чтения их вывода. Модуль описан в статье file_fdw.

Примеры использования этих обёрток приведены ниже.

Доступ к данным, хранящимся на сторонних серверах PostgreSQL

Предположим, что внешний сервер PostgreSQL содержит таблицы books и authors в базе данных books_store.

Таблица authors создана следующим образом:

CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Чтобы получить доступ к данным с этого сервера, выполните следующие действия:

  1. Используйте существующую роль на удаленном сервере или создайте нового пользователя и предоставьте ему соответствующие права. Например:

    CREATE ROLE user1 WITH PASSWORD 'password' LOGIN;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO user1;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO user1;

  2. Поскольку ADPG включает расширение contrib, нужно просто выполнить команду CREATE EXTENSION на локальном сервере, чтобы использовать обёртку postgres_fdw:

    CREATE EXTENSION postgres_fdw;

  3. Создайте внешний сервер в своей локальной базе данных. Следующий код создает сервер, который подключается к базе данных books_store, расположенной на хосте 10.92.6.225:

    CREATE SERVER server1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '10.92.6.225', port '5432', dbname 'books_store');

    Если удаленный сервер использует стандартный порт, параметр port можно опустить.

  4. Создайте объект user mapping. Передайте учетные данные пользователя, которого вы создали на удаленном сервере, в качестве параметров. Следующий код создает user mapping для пользователя postgres c локального сервера:

    CREATE USER MAPPING FOR postgres SERVER server1 OPTIONS (user 'user1', password 'password');

  5. Создайте внешнюю таблицу на локальном сервере:

    CREATE FOREIGN TABLE authors (
    id int,
    name VARCHAR(100) NOT NULL
)
SERVER server1;

    Проверьте результат:

    SELECT * FROM authors;

    Вывод команды:

     id |        name
----+---------------------
  1 | Virginia Woolf
  2 | Harper Lee
  3 | F. Scott Fitzgerald
  4 | J.R.R. Tolkien
  5 | George Orwell

    Также можно импортировать схему целиком:

    CREATE SCHEMA schema1;
IMPORT FOREIGN SCHEMA public from SERVER server1 into schema1;

    Отобразите существующие внешние таблицы, чтобы проверить результат:

    SELECT * FROM information_schema.foreign_tables;

    Результат:

     foreign_table_catalog | foreign_table_schema | foreign_table_name | foreign_server_catalog | foreign_server_name
-----------------------+----------------------+--------------------+------------------------+---------------------
 postgres              | public               | authors            | postgres               | server1
 postgres              | schema1              | authors            | postgres               | server1
 postgres              | schema1              | books              | postgres               | server1

Таблицы books и authors были импортированы в схему shema1.

Доступ к файлам в файловой системе сервера

Предположим, у нас есть следующий файл books.csv:

1,Mrs. Dalloway,1,1925,novel
2,To the Lighthouse,1,1927,novel
3,To Kill a Mockingbird,2,1960,novel
4,The Lord of the Rings,4,1955,fantasy
5,1984,5,1949,sci-fi

Нам нужно использовать SQL для доступа к данным из файла. Для решения этой задачи выполните следующие шаги:

  1. Выполните команду CREATE EXTENSION, чтобы использовать обёртку внешних данных file_fdw:

    CREATE EXTENSION file_fdw;

  2. Создайте внешний сервер:

    CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;

  3. Создайте внешнюю таблицу таким образом, чтобы её структура совпадала со структурой файла. Передайте путь к файлу и формат в выражении OPTIONS:

    CREATE FOREIGN TABLE foreign_books (
    id INT,
    title VARCHAR(255),
    author_id INT,
    public_year INT,
    genre VARCHAR(50)
) SERVER file_server
OPTIONS ( filename '/var/lib/pgsql/books.csv', format 'csv' );

Теперь вы можете использовать команды SQL для доступа к данным, например:

SELECT * FROM foreign_books;

Результат:

 id |         title         | author_id | public_year |  genre
----+-----------------------+-----------+-------------+---------
  1 | Mrs. Dalloway         |         1 |        1925 | novel
  2 | To the Lighthouse     |         1 |        1927 | novel
  3 | To Kill a Mockingbird |         2 |        1960 | novel
  4 | The Lord of the Rings |         4 |        1955 | fantasy
  5 | 1984                  |         5 |        1949 | sci-fi
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней