Сторонние таблицы

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

Обзор

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

  1. Используйте обертку сторонних данных (foreign data wrapper). Обертка сторонних данных подключается к внешним источникам и получает от них данные. Расширение contrib, включенное в установку ADP, содержит несколько оберток сторонних данных. За дополнительной информацией обратитесь к статье 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. Поскольку ADP включает расширение 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 чтобы сообщить о ней