Привет, Хабр!

С ростом объёмов данных и увеличением популярности распределённых систем необходимость в анализе распределённых данных становится всё более актуальной. PostgreSQL, благодаря своему мощному функционалу и гибкости, предлагает инструмент, который значительно облегчает эту задачу — Foreign Data Wrappers (FDW).

FDW позволяет подключаться к удалённым базам данных, как если бы они были локальными таблицами. Это удобно для аналитики, интеграции данных из различных источников и упрощает работу с распределёнными системами. FDW поддерживает не только PostgreSQL, но и другие базы данных, такие как MySQL, SQLite и даже API.

Настройка FDW в PostgreSQL

Чтобы начать работать с FDW, первым делом нужно установить необходимое расширение. В нашем случае будем использовать postgres_fdw, которое позволяет подключаться к другим БД PostgreSQL.

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

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

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

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

Создаём сервер:

CREATE SERVER my_remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remote_host', dbname 'remote_db', port '5432');

Здесь my_remote_server — это имя сервера, а remote_host и remote_db — адрес и имя удалённой базы данных соответственно.

Теперь необходимо настроить аутентификацию пользователя, используя маппинг пользователя:

CREATE USER MAPPING FOR local_user
SERVER my_remote_server
OPTIONS (user 'remote_user', password 'remote_password');

Замените local_user, remote_user и remote_password на ваши значения.

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

Создаём внешнюю таблицу:

CREATE FOREIGN TABLE remote_table (
    id SERIAL PRIMARY KEY,
    name TEXT,
    value NUMERIC
)
SERVER my_remote_server
OPTIONS (table_name 'actual_table_name');

Теперь есть remote_table, которая ссылается на таблицу actual_table_name в удалённой базе данных.

Запросы к удалённым данным через FDW

Начнём с самого простого — выполнения запросов к удалённым данным.

Чтобы извлечь данные из удалённой таблицы, выполните:

SELECT * FROM remote_table;

Этот запрос вернёт все строки из удалённой таблицы.

Чтобы уменьшить количество возвращаемых данных и ускорить обработку, добавим фильтр:

SELECT * FROM remote_table WHERE value > 100;

Здесь мы получаем только те записи, где value больше 100.

FDW позволяет выполнять JOIN с локальными таблицами. Рассмотрим, как это сделать:

SELECT l.id, l.name, r.value
FROM local_table l
JOIN remote_table r ON l.id = r.id
WHERE r.value > 100;

Этот запрос объединяет данные из локальной таблицы local_table и удалённой remote_table, фильтруя результаты по значению value.

После выполнения запроса вы можете обрабатывать данные, как обычно, используя стандартные функции PostgreSQL. Например, чтобы вычислить среднее значение:

WITH filtered_data AS (
    SELECT name, value
    FROM remote_table
    WHERE value > 100
)
SELECT AVG(value) AS avg_value FROM filtered_data;

В этом примере сначала фильтруем данные, а затем вычисляем среднее значение.

Иногда нужно убедиться, что удалённые данные действительно существуют, прежде чем выполнять дальнейшие действия. Это можно сделать с помощью:

SELECT COUNT(*) FROM remote_table WHERE value IS NOT NULL;

FDW не ограничивается только PostgreSQL. Можно подключаться к другим базам данных, таким как MySQL или SQLite, используя соответствующие FDW-расширения. Например, для подключения к MySQL используйте mysql_fdw:

CREATE EXTENSION IF NOT EXISTS mysql_fdw;

CREATE SERVER my_mysql_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host 'mysql_host', port '3306');

Затем создаем маппинг пользователя и внешние таблицы так же, как мы сделали это для PostgreSQL.

Заключение

Foreign Data Wrappers позволяет интегрировать данные из различных источников, упрощая анализ и управление.

Как вы используете FDW в своих проектах? Поделитесь опытом в комментариях!

А изучить современные принципы и практики архитектуры, эффективное управление командой аналитиков, тенденции на международном рынке можно на онлайн-курсе «Системный аналитик. Team Lead».