Практика
Не приходилось ли вам когда-либо, по самым разным причинам, думать о PostgreSQL? Свободно распространяемой, доступной, простой, гибкой, расширяемой, ненапыщенной, но гордой своими неоспоримыми преимуществами. Она совмещает простоту, предельную логичность пользования, и, в то же время, это действительно безгранично расширяемый инструмент. Она поддерживает многопоточность, позволяя проводить параллелные транзакции без Read lock’ов. Она имеет extensions для решения задач на любой цвет и вкус. Эх, жаль, жаль, она не сможет приготовить вам борщ.
Одно из самых популярных и мощных свойств PostgreSQL, о котором мы сегодня поговорим – это Foreign Data Wrapper’ы, то есть, программные обертки для всевозможных источников данных, позволяющие пользоваться ими изнутри этой СУБД.
Эта статья содержит быстрое введение и рассчитана на всех, хотя желательно, чтобы у вас был linux, и в общем не требует какой-либо подготовки.
или что это такое
Foreign Data Wrapper’ами называются расширения для PostgreSQL, которые позволяют осуществлять доступ к данным на удаленном сервере.
Характер и структура этих данных может быть самой разнообразной. На официальной вики постгреса кратко собраны ссылки на исходники и краткая информация о всех официально поддерживаемых foreign data wrapper’ах (см. “Суть вопроса”).
Ключевой момент заключается в возможности подключения к стороннему серверу. Идея проста. У вас сервер с postgreSQL, и сторонний сервер, на котором есть уже не так важно что. Вам нужна программная оболочка для доступа к этому “не так важно что”, которая на вход от вас получала бы только логин с паролем для подключения к серверу, а затем – пользовательские данные для доступа к источнику данных в нем (логин и пароль к базе, например). И для каждого этого “не так важно чего”, независимо от его природы, эта обертка должна быть встроена в postgres, и в использовании должна быть унифицирована с другими такими обертками.
Вот именно такими и являются foreign data wrapper’ы.
устанавливаем необходимое
Для начала вам понадобится сам postgres. В случае linux, вы получите основной функционал, введя
|
1
2
|
sudo apt-get update
sudo apt-get install postgresql-9.4 pgadmin3 postgresql-9.4-postgis-2.1
|
Более детальная информация о загрузках лежит на официальной вики. Ну, а в случае других операционных систем – никогда не поздно попробовать linux… Хотя, если серьезно, установка на Windows тривиальна и не отличается от установки обычного музыкального проигрывателя.
Теперь нам нужен более-менее интересный источник внешних данных. У меня на машине завалялась MySQL. В моем случае именно она послужит источником внешних данных. Однако возможными источниками данных могут быть и другие СУБД, в том числе нереляционные, и простые файлы, и еще много чего (см. “Суть вопроса”).
Мы создадим новую базу данных с одной таблицей, данными которых мы попробуем воспользоваться из postgres.
(Чтобы не было скучно, пускай это будет не затасканная всеми туториалами таблица users с полями контактных данных, а таблица, хранящая информацию о жертвах одного сумасшедшего маньяка, который хочет посеять хаос и анархию во всем мире, регулярно убивает невинных людей, а в свободное время сидит с ноутбуком и попивает кофе. До недавнего времени он вел свой учет жертв в MySQL, но сейчас он решил разработать более серьезный проект и ему нужно синхронизировать капельку его данных из mysql в postgres).
|
1
2
3
4
5
6
7
8
9
|
CREATE DATABASE cruel;
USE cruel;
CREATE TABLE victims (
victim_id SERIAL PRIMARY KEY,
first_name VARCHAR(30),
last_name VARCHAR(30),
birthdate DATE,
murdertime DATETIME
);
|
Также заполним эту таблицу какими-то самую капельку адекватными данными.
|
1
2
|
INSERT INTO victims VALUES (1, 'john', 'lennon', 1940-10-9, 1980-12-8-00-00-00);
INSERT INTO victims VALUES (2, 'john', 'kennedy', 1917-5-29, 1963-11-22-00-00-00);
|
Можем несколько раз убить Кенни из Саус Парка – для примера самое то.
|
1
2
3
|
INSERT INTO victims VALUES (3, 'Kenny', 'McCormick', 1987-01-01, 1997-08-13-00-00-00);
INSERT INTO victims VALUES (4, 'Kenny', 'McCormick', 1987-01-01, 1997-08-20-00-00-00);
INSERT INTO victims VALUES (5, 'Kenny', 'McCormick', 1987-01-01, 1997-08-20-00-00-00);
|
(Полный список смертей Кенни включать не будем, ограничимся тремя первыми, датой смерти будем считать даты выхода серий).
запускаем необходимое
Теперь обоснуемся в postgres. Нам нужна новая база данных, в которой мы созданим внешнюю таблицу, основываясь на данных из MySQL.
Запускаем postgreSQL от имени суперпользователя СУБД, именуемого “postgres”:
|
1
|
sudo -u postgres psql
|
Обращаясь к базе, кратко узнаем обстановку.
|
1
|
\list
|
-просмотр существующих баз данных
|
1
|
CREATE DATABASE pg_cruel;
|
-создаем новую базу данных
|
1
|
\connect pg_cruel
|
-выбираем базу для работы
|
1
|
\dt
|
-просмотр таблиц выбранной базы
Наконец, мы обладаем почти всеми необходимыми инструментами. Остался последний – собственно, сам foreign data wrapper.
Для нашей ситуации нам нужен foreign data wrapper (далее “обертка”) для данных из MySQL. Теоретически, обертку можно создать самостоятельнo, и postgreSQL предоставляет красивую и лаконичную документацию. Однако для СУБД всех сортов и мастей, и не только СУБД, но и, пожалуй, для всех вообразимых источников данных, можно подобрать себе готовый fdw на всякий вкус и цвет. Нам же для простого примера понадобится mysql_fdw. В случае другого источника данных, это была бы другая обертка.
Каждая обертка является расширением postgreSQL и имеет некоторую процедуру установки.
Скачиваем исходники архивом со страницы mysql_fdw на GitHub. Распаковываем, собираем, компилируем. Полная 4-шаговая инструкция по установке находится в файле README.md, ничего необычного или сложного, просто добавь два указанных пути к системной переменной $PATH, и запусти компиляцию.
У нас есть postgres на локальном сервере, есть расширение для обработки внешних данных, и есть MySQL. Тоже на локальном сервере, но каким бы ни был адрес, использование идентично.
Чтобы в нашей базе pg_cruel появились данные из cruel в MySQL, нужно выполнить 4 шага:
Создаем расширение
|
1
|
CREATE EXTENSION mysql_fdw;
|
Создаем внешний сервер для подключения, указав адрес и порт подключения, а так же, возможно, пароль и другие опции (здесь довольно широкий список)
|
1
2
3
|
CREATE SERVER f_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (hostaddr '127.0.0.1', port '3306');
|
Создаем пользовательский доступ к источнику данных. Здесь все зависит от конкретного FDW, для mysql_fdw требуется логин и пароль. Указываем также внешний сервер, для которого действительны пользовательские данные.
|
1
2
3
|
CREATE USER MAPPING FOR postgres
SERVER f_server
OPTIONS (user 'mysqluser', password 'mysqlpassword');
|
Создаем внешнюю таблицу, указав внешний сервер.
|
1
2
3
4
5
6
7
8
9
|
CREATE FOREIGN TABLE victims
(
victim_id SERIAL,
first_name VARCHAR(30),
last_name VARCHAR(30),
birthdate DATE,
murdertime TIME
)
SERVER f_server OPTIONS (table_name 'victims');
|
Теперь можно вволю насладиться данными из внешнего источника. Например, одно из самых главных применений – мы можем совершать выборку из смешанных данных: часть из них находятся на нашем сервере, часть – на удаленном.
|
1
|
\connect pg_cruel
|
Создадим пару обычных, локальных таблиц, связанных между собой и с внешней таблицей:
|
1
2
3
4
5
6
|
CREATE TABLE murders
(
murder_id SERIAL PRIMARY KEY,
victim_id BIGINT REFERENCES victims(victim_id),
way_of_death_id BIGINT REFERENCES ways_of_death(way_id)
);
|
|
1
2
3
4
5
|
CREATE TABLE ways_of_death
(
way_id SERIAL PRIMARY KEY,
value VARCHAR(140)
);
|
Начинка данными – по вкусу.
А теперь… Вуаля! Можем получить имена наших жертв в связи со способом их убийства!
|
1
2
3
4
|
SELECT victims.first_name, victims.last_name, way.value
FROM victims
LEFT JOIN murders ON (victims.victim_id=murders.victim_id)
LEFT JOIN ways_of_death AS way ON (murders.way_of_death_id=ways.way_id);
|
Комментарии
Оставить комментарий
MySql (Maria DB)
Термины: MySql (Maria DB)