J.2. mysql_fdw#

J.2. mysql_fdw
J.2. mysql_fdw
Назад НаверхПриложение J. Дополнительные внешние модули, поставляемые в виде отдельных пакетовНачало Далее

J.2. mysql_fdw

J.2.1. Обзор
J.2.2. Установка
J.2.3. Особенности
J.2.4. Поддерживаемые платформы
J.2.5. Предварительные условия
J.2.6. Использование
J.2.7. Функции
J.2.8. Сгенерированные столбцы
J.2.9. Примеры

J.2.1. Обзор

Версия: 2.9.3

Скачать с nexus-public

Это обертка для внешних данных (FDW) для подключения Tantor SE к MySQL.

J.2.2. Установка

Чтобы установить mysql_fdw, cкачайте установочный скрипт db_extension_installer.sh и сделайте его исполняемым: 

wget https://public.tantorlabs.ru/db_extension_installer.sh && \
chmod +x db_extension_installer.sh

Далее вы можете установить mysql_fdw:

  1. Локально из скачанного пакета.

  2. Автоматически из репозитория пакетов.

J.2.2.1. Локально из скачанного пакета

  1. Скачайте пакет mysql_fdw, подходящий для вашей операционной системы, с сайта nexus-public.

  2. Выполните установку mysql_fdw с помощью db_extension_installer.sh из скачанного пакета: 

    ./db_extension_installer.sh \
  --from-file=<path to mysql_fdw package>

J.2.2.2. Автоматически из репозитория пакетов

  1. Установите значение переменной окружения NEXUS_URL: 

    export NEXUS_URL="nexus-public.tantorlabs.ru"

  2. Выполните установку mysql_fdw с помощью db_extension_installer.sh со следующими параметрами: 

    ./db_extension_installer.sh \
  --database-type=tantor \
  --database-major-version=15 \
  --edition=se \
  --extension=mysql_fdw

J.2.3. Особенности

J.2.3.1. Общие функции и улучшения

Следующие улучшения добавлены в последнюю версию mysql_fdw:

J.2.3.1.1. Записываемый FDW

Предыдущая версия была только для чтения, последняя версия предоставляет возможность записи. Пользователь теперь может выполнять операторы вставки, обновления и удаления для внешних таблиц с использованием mysql_fdw. Она использует механизм приведения типов PG для обеспечения обратного приведения типов между типами данных MySQL и PG.

J.2.3.1.2. Пул соединений

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

J.2.3.1.3. Подготовленный оператор

(Рефакторинг для select запросов с использованием подготовленного выражения)

select запросы теперь используют подготовленные операторы вместо простого протокола запроса.

J.2.3.2. Выталкивание

J.2.3.2.1. Условие WHERE

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

J.2.3.2.2. Столбцы

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

J.2.3.2.3. JOIN

mysql_fdw теперь также поддерживает выполнение соединений на удалённом сервере. Соединения между двумя внешними таблицами с одного и того же удалённого сервера MySQL выполняются на удалённом сервере, вместо извлечения всех строк для обеих таблиц и выполнения соединения локально, что повышает производительность. В настоящее время, соединения, включающие только реляционные и арифметические операторы в условиях соединения, выполняются на удалённом сервере, чтобы избежать возможных ошибок соединения. Также поддерживаются только соединения INNER и LEFT/RIGHT OUTER, но не FULL OUTER, SEMI и ANTI соединения. Это функция, направленная на повышение производительности.

J.2.3.2.4. AGGREGATE

mysql_fdw теперь также поддерживает агрегацию с передачей на удаленный сервер MySQL. Передавайте агрегаты на удаленный сервер MySQL вместо того, чтобы извлекать все строки и агрегировать их локально. Это дает значительное повышение производительности в случаях, когда агрегаты могут быть переданы. В настоящее время передача ограничена агрегатными функциями min, max, sum, avg и count, чтобы избежать передачи функций, отсутствующих на сервере MySQL. Также фильтры и порядок агрегатов не передаются.

J.2.3.2.5. ORDER BY

mysql_fdw теперь также поддерживает передачу “order by”. Если возможно, передайте “order by” на удаленный сервер, чтобы получить отсортированный набор результатов непосредственно с удаленного сервера. Это может помочь нам в эффективном выполнении слияния соединений. Поведение NULL противоположно на сервере MySQL. Таким образом, чтобы получить эквивалентный результат, мы добавляем “expression IS NULL” в начало каждого из выражений “ORDER BY”.

J.2.3.2.6. LIMIT OFFSET

mysql_fdw теперь также поддерживает выполнение LIMIT и OFFSET на удаленном сервере. Это уменьшает сетевой трафик между локальным Tantor SE и удаленными серверами MySQL. Опции ALL/NULL не поддерживаются на сервере MySQL, и поэтому они не передаются. Также OFFSET без LIMIT не поддерживается на сервере MySQL, поэтому запросы с такой конструкцией не передаются.

J.2.4. Поддерживаемые платформы

mysql_fdw был разработан на Linux и должен работать на любой системе, которая в разумной степени соответствует стандарту POSIX.

Пожалуйста, обратитесь к mysql_fdw_документация.

J.2.5. Предварительные условия

Чтобы скомпилировать MySQL foreign data wrapper, необходима C-клиентская библиотека MySQL. Эту библиотеку можно скачать с официального веб-сайта MySQL.

J.2.6. Использование

J.2.6.1. Параметры CREATE SERVER

mysql_fdw принимает следующие параметры через команду CREATE SERVER:

  • host string, необязательный, по умолчанию 127.0.0.1

    Адрес или имя хоста сервера MySQL.

  • port integer, необязательный, по умолчанию 3306

    Номер порта сервера MySQL.

  • secure_auth boolean, необязательный, по умолчанию true

    Включить или отключить безопасную аутентификацию.

  • init_command string, необязательный, значение по умолчанию отсутствует

    SQL оператор для выполнения при подключении к серверу MySQL.

  • use_remote_estimate boolean, необязательный, по умолчанию false

    Управляет тем, будет ли mysql_fdw выполнять удаленные команды EXPLAIN для получения оценок стоимости.

  • reconnect boolean, необязательный, по умолчанию false

    Включить или отключить автоматическое переподключение к серверу MySQL, если обнаружено, что существующее соединение было потеряно.

  • sql_mode string, необязательный, по умолчанию ANSI_QUOTES

    Установить sql_mode MySQL для установленного соединения.

  • ssl_key string, необязательный, значение по умолчанию отсутствует

    Имя пути к файлу закрытого ключа клиента.

  • ssl_cert string, необязательный, значение по умолчанию отсутствует

    Имя пути файла сертификата открытого ключа клиента.

  • ssl_ca string, необязательный, значение по умолчанию отсутствует

    Имя пути файла сертификата Удостоверяющего центра (CA). Эта опция, если используется, должна указывать на тот же сертификат, который используется сервером.

  • ssl_capath string, необязательный, по умолчанию отсутствует

    Имя пути к каталогу, содержащему доверенные файлы сертификатов CA SSL.

  • ssl_cipher string, необязательный, значение по умолчанию отсутствует

    Список допустимых шифров для SSL-шифрования.

  • fetch_size integer, необязательный, по умолчанию 100

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

  • character_set string, необязательный, по умолчанию auto

    Набор символов для использования в соединении MySQL. По умолчанию auto, что означает автоматическое определение на основе настроек операционной системы. До введения опции character_set, набор символов устанавливался аналогично кодировке базы данных Tantor SE. Чтобы получить это старое поведение, установите character_set на специальное значение PGDatabaseEncoding.

  • mysql_default_file string, необязательный, по умолчанию отсутствует

    Установите путь к файлу по умолчанию MySQL, если детали подключения, такие как имя пользователя, пароль и т.д., необходимо взять из файла по умолчанию.

  • truncatable boolean, необязательный, по умолчанию true

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

J.2.6.2. Параметры CREATE USER MAPPING

mysql_fdw принимает следующие параметры через команду CREATE USER MAPPING:

  • username string, по умолчанию отсутствует

    Имя пользователя для использования при подключении к MySQL.

  • password string, по умолчанию отсутствует

    Пароль для аутентификации на сервере MySQL.

J.2.6.3. Параметры CREATE FOREIGN TABLE

mysql_fdw принимает следующие параметры на уровне таблицы через команду CREATE FOREIGN TABLE.

  • dbname string, обязательный

    Name of the MySQL базы данных для запроса. Это обязательный параметр.

  • table_name string, необязательный, имя по умолчанию внешней таблицы

    Название таблицы MySQL.

  • fetch_size integer, необязательный

    То же, что и параметр fetch_size для внешнего сервера.

  • max_blob_size integer, необязательный

    Максимальный размер блоба для чтения без усечения.

  • truncatable boolean, необязательный, по умолчанию true

    То же, что и параметр внешнего сервера.

J.2.6.4. Параметры IMPORT FOREIGN SCHEMA

mysql_fdw поддерживает IMPORT FOREIGN SCHEMA и принимает следующие пользовательские параметры:

  • import_default boolean, необязательный, по умолчанию false

    Эта опция управляет тем, включаются ли выражения по умолчанию для столбцов в определения внешних таблиц, импортируемых с внешнего сервера.

  • import_not_null boolean, необязательный, по умолчанию true

    Эта опция управляет тем, включаются ли ограничения столбца NOT NULL в определения внешних таблиц, импортированных с внешнего сервера.

  • import_enum_as_text boolean, необязательный, по умолчанию false

    Эта опция может быть использована для отображения типа MySQL ENUM в тип TEXT в определениях внешних таблиц, в противном случае выдать предупреждение о создании типа.

  • import_generated boolean, необязательный, по умолчанию true

    Эта опция управляет тем, включаются ли выражения СГЕНЕРИРОВАННЫХ столбцов в определения внешних таблиц, импортированных с внешнего сервера, или нет. ИМПОРТ полностью завершится неудачей, если импортированное сгенерированное выражение использует функцию или оператор, которые не существуют в Tantor SE.

J.2.6.5. Поддержка TRUNCATE

mysql_fdw реализует API обертки внешних данных TRUNCATE, доступное начиная с Tantor SE 14. MySQL предоставляет команду TRUNCATE, см. Оператор TRUNCATE TABLE.

Применяются следующие ограничения:

  • TRUNCATE ... CASCADE не поддерживается

  • TRUNCATE ... RESTART IDENTITY не поддерживается и игнорируется

  • TRUNCATE ... CONTINUE IDENTITY не поддерживается и игнорируется

  • Таблицы MySQL с внешними ключами не могут быть усечены

J.2.7. Функции

А также стандартные функции mysql_fdw_handler() и mysql_fdw_validator(), mysql_fdw предоставляет следующие утилиты, вызываемые пользователем:

  • mysql_fdw_version()

    Возвращает номер версии в виде целого числа.

  • mysql_fdw_display_pushdown_list()

    Отображает содержимое файла mysql_fdw_pushdown.config.

J.2.8. Сгенерированные столбцы

Обратите внимание, что хотя mysql_fdw будет вставлять или обновлять значение сгенерированной колонки в MySQL, ничто не мешает изменению значения в MySQL, и, следовательно, нет гарантии, что в последующих операциях SELECT колонка все еще будет содержать ожидаемое сгенерированное значение. Это ограничение также относится к postgres_fdw.

Для получения более подробной информации о сгенерированных столбцах смотрите:

J.2.9. Примеры

J.2.9.1. Установите расширение:

Один раз для базы данных вам нужно, как Tantor SE суперпользователь. 

    -- load extension first time after install
    CREATE EXTENSION mysql_fdw;

J.2.9.2. Создайте внешний сервер с соответствующей конфигурацией:

Один раз для внешнего источника данных вам нужно, как Tantor SE суперпользователь. 

    -- create server object
    CREATE SERVER mysql_server
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (host '127.0.0.1', port '3306');

J.2.9.3. Предоставьте права использования на внешнем сервере обычному пользователю в Tantor SE:

Один раз для обычного пользователя (не суперпользователя) в Tantor SE, как суперпользователь Tantor SE. Хорошей идеей является использование суперпользователя только там, где это действительно необходимо, поэтому давайте позволим обычному пользователю использовать внешний сервер (это не требуется для работы примера, но это рекомендация по безопасности). 

    GRANT USAGE ON FOREIGN SERVER mysql_server TO pguser;

Где pguser является примером пользователя для работы с внешним сервером (и внешними таблицами).

J.2.9.4. Отображение пользователей

Создайте соответствующее отображение пользователя: 

    -- create user mapping
    CREATE USER MAPPING FOR pguser
    SERVER mysql_server
    OPTIONS (username 'foo', password 'bar');

Где pguser является примером пользователя для работы с внешним сервером (и внешними таблицами).

J.2.9.5. Создать внешнюю таблицу

Все команды SQL CREATE FOREIGN TABLE могут быть выполнены обычным пользователем Tantor SE, если было предоставлено правильное GRANT USAGE ON FOREIGN SERVER. Нет необходимости в суперпользователе Tantor SE по соображениям безопасности, но также работает с суперпользователем Tantor SE.

Пожалуйста, укажите опцию table_name, если имя таблицы MySQL отличается от имени внешней таблицы. 

    -- create foreign table
    CREATE FOREIGN TABLE warehouse (
      warehouse_id int,
      warehouse_name text,
      warehouse_created timestamp
    )
    SERVER mysql_server
    OPTIONS (dbname 'db', table_name 'warehouse');

Некоторые другие операции с данными внешней таблицы 

-- insert new rows in table
INSERT INTO warehouse values (1, 'UPS', current_date);
INSERT INTO warehouse values (2, 'TV', current_date);
INSERT INTO warehouse values (3, 'Table', current_date);

-- select from table
SELECT * FROM warehouse ORDER BY 1;

warehouse_id | warehouse_name | warehouse_created
-------------+----------------+-------------------
           1 | UPS            | 10-JUL-20 00:00:00
           2 | TV             | 10-JUL-20 00:00:00
           3 | Table          | 10-JUL-20 00:00:00

-- delete row from table
DELETE FROM warehouse where warehouse_id = 3;

-- update a row of table
UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1;

-- explain a table with verbose option
EXPLAIN VERBOSE SELECT warehouse_id, warehouse_name FROM warehouse WHERE warehouse_name LIKE 'TV' limit 1;

                                   QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Limit  (cost=10.00..11.00 rows=1 width=36)
    Output: warehouse_id, warehouse_name
    ->  Foreign Scan on public.warehouse  (cost=10.00..1010.00 rows=1000 width=36)
        Output: warehouse_id, warehouse_name
        Local server startup cost: 10
        Remote query: SELECT `warehouse_id`, `warehouse_name` FROM `db`.`warehouse` WHERE ((`warehouse_name` LIKE BINARY 'TV'))

J.2.9.6. Импортировать базу данных MySQL как схему в Tantor SE:

    IMPORT FOREIGN SCHEMA someschema
    FROM SERVER mysql_server
    INTO public;
Назад Наверх Далее
J.1. ldap2pg Начало J.3. oracle_fdw