Перемещение данных между SQL Server и MariaDB

Существует несколько способов перемещения данных между SQL Server и MariaDB. Здесь мы обсудим их и выделим некоторые особенности.

Перемещение структуры данных из SQL Server в MariaDB

Для копирования структур данных из SQL Server в MariaDB необходимо:

1. Сгенерировать CSV-файл из данных SQL Server.
2. Изменить синтаксис, чтобы он работал в MariaDB.
3. Запустить файл в MariaDB.

Переменные, влияющие на DDL-заявления

DDL-заявления зависят от некоторых системных переменных сервера.

sql_mode определяет поведение некоторых SQL-заявлений и выражений, включая строгость проверки ошибок и некоторые детали синтаксиса. Объекты, такие как хранимые процедуры, хранимые функции триггеры и представления, всегда выполняются с sql_mode, действующим во время их создания. sql_mode='MSSQL' может использоваться для того, чтобы MariaDB работала как можно ближе к SQL Server.

innodb_strict_mode включает так называемый строгий режим InnoDB. Обычно некоторые ошибки в параметрах CREATE TABLE игнорируются. Когда включен строгий режим InnoDB, создание таблиц InnoDB завершится ошибкой при определённых ошибках.

updatable_views_with_limit определяет, можно ли выполнять обновления представлений с помощью операторов UPDATE или DELETE с LIMITограничительным условием, если представление не содержит все столбцы первичного ключа или не все столбцы с ограничением NOT NULL из базовой таблицы.

Думпы и sys.sql_modules

SQL Server Management Studio позволяет создать рабочий SQL-скрипт для восстановления базы данных — то, что пользователи MariaDB называют «дампом». Несколько параметров позволяют уточнить сгенерированный синтаксис. Возможно, потребуется настроить некоторые из этих параметров, чтобы сделать вывод совместимым с MariaDB. Можно экспортировать схемы, данные или оба вместе. Можно создать один глобальный файл или один файл для каждого экспортированного объекта. Обычно более практично создавать один файл.

В качестве альтернативы, процедура sp_helptext() возвращает информацию о том, как восстановить определённый объект. Аналогичная информация также содержится в таблице sql_modules (definition столбец), в схеме sys. Однако такая информация не является готовым набором SQL-заявлений.

Однако помните, что MariaDB не поддерживает схемы. Схема в SQL Server примерно соответствует базе данных в MariaDB.

Для выполнения дампа можно передать файл в mariadb, командную оболочку MariaDB.

При условии, что файл дампа содержит синтаксис, допустимый в MariaDB, его можно выполнить следующим образом:

mariadb --show-warnings < dump.sql

--show-warnings сообщает MariaDB выводить любые предупреждения, сгенерированные утверждениями, содержащимися в дампе. Без этого параметра предупреждения не будут отображаться на экране. Предупреждения не останавливают выполнение дампа.

Ошибки будут отображаться на экране. Ошибки остановят выполнение дампа, если не указан параметр --force (или просто -f).

Другие mariadb параметры см. в Параметрах командной строки MariaDB.

Другой способ достижения той же цели — сначала запустить клиент mariadb в интерактивном режиме, а затем выполнить команду source. Например:

root@d5a54a082d1b:/# mariadb -uroot -psecret
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.4.7-MariaDB-1:10.4.7+maria~bionic mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> \W
Show warnings enabled.
MariaDB [(none)]> source dump.sql

В этом случае, чтобы показать предупреждения, мы использовали команду \W, где «w» заглавная. Чтобы скрыть предупреждения (что является стандартным поведением), можно использовать \w (строчная).

Другие mariadb команды см. в Командах MariaDB.

CSV-данные

Если структуры таблиц уже есть в MariaDB, нам нужно только импортировать данные в таблицы. Хотя это можно сделать, как описано выше, может быть более практичным экспортировать CSV-файлы из SQL Server и импортировать их в MariaDB.

SQL Server Management Studio и ряд других инструментов Microsoft позволяют экспортировать CSV-файлы.

MariaDB позволяет импортировать CSV-файлы с помощью инструкции LOAD DATA INFILE, которая по существу является аналогом BULK INSERT в MariaDB.

Возможно, нам нужно импортировать не все данные, а их отфильтрованную или преобразованную версию. В этом случае предпочтительнее использовать хранилище CONNECT для доступа к CSV-файлам и их запроса. Результаты запроса можно вставить в таблицу с помощью INSERT SELECT.

Перемещение данных из MariaDB в SQL Server

Существует несколько способов перемещения данных из MariaDB в SQL Server:

• Если таблицы вообще не существуют в SQL Server, сначала необходимо создать дамп. Думп может включать данные или нет.
• Если таблицы уже есть в SQL Server, вместо дампов можно использовать CSV-файлы для перемещения строк. CSV-файлы — наиболее компактный формат для перемещения данных между различными технологиями.
• Если таблицы уже есть в SQL Server, ещё один способ перемещения данных — вставка строк в таблицы CONNECT, которые «указывает» на удалённые таблицы SQL Server.

Использование дампа (структуры)

mariadb-dump можно использовать для создания дампов всех баз данных, определённой базы данных или набора таблиц. Можно даже сдумпировать набор строк, указав WHERE условие.

Указав параметр --no-data, можно сдумпировать структуру таблиц без данных.

--compatible=mssql выведет вывод, который должен быть пригоден для использования в SQL Server.

Использование дампа (данные)

mariadb-dump по умолчанию создаёт вывод с данными и структурой.

--no-create-info можно использовать для пропуска инструкций CREATE TABLE.

--compatible=mssql выведет вывод, который должен быть пригоден для использования в SQL Server.

--single-transaction следует указать для выбора исходных данных в рамках одной транзакции, чтобы гарантировать создание согласованного дампа.

--quick ускоряет процесс дампа при работе с большими таблицами.

Использование CSV-файла

CSV-файлы также можно использовать для экспорта данных в SQL Server. Существует несколько способов создания CSV-файлов из MariaDB:

• Инструкция SELECT INTO OUTFILE.
• Хранилище CONNECT с типом таблицы CSV.
• Хранилище CSV (обратите внимание, что оно не поддерживает NULL и индексы).

Использование таблиц CONNECT

Хранилище CONNECT позволяет получить доступ к внешним данным в различных форматах:

• Файлы данных (CSV, JSON, XML, HTML и др.).
• Удалённые базы данных, используя стандарты ODBC или JDBC, или нативный протокол MariaDB/MySQL.
• Некоторые специальные источники данных.

CONNECT упоминался ранее, поскольку он мог бы позволить чтение CSV-файла и его запрос в SQL, фильтрацию и преобразование данных, которые мы хотим переместить в обычные таблицы MariaDB.

Однако CONNECT также может получить доступ к удалённым таблицам SQL Server. Мы можем читать данные из него или даже записывать данные.

Для работы CONNECT с SQL Server необходимо выполнить следующие требования:

• Установить драйвер ODBC, загружаемый с веб-сайта Microsoft. Драйвер также доступен для Linux и MacOS.
• Установить unixODBC.
• Установить CONNECT (если он ещё не установлен).

Вот пример таблицы CONNECT , указывающей на таблицу SQL Server:

CREATE TABLE city (
    id INT PRIMARY KEY,
    city_name VARCHAR(100),
    province_id INT NOT NULL
)
    ENGINE=CONNECT,
    TABLE_TYPE=ODBC,
    TABNAME='city'
    CONNECTION='Driver=SQL Server Native Client 13.0;Server=sql-server-hostname;Database=world;UID=mariadb_connect;PWD=secret';

Ключевые моменты:

• ENGINE=CONNECT указывает MariaDB, что мы хотим создать таблицу CONNECT.
• TABLE_TYPE должен быть 'ODBC', чтобы CONNECT знал, какой тип источника данных использовать.
• CONNECTION — строка подключения, включая адрес сервера, имя пользователя и пароль.
• TABNAME указывает CONNECT имя удалённой таблицы. Локальное имя может быть другим.

CONNECT может запросить SQL Server для получения структуры удалённой таблицы. Это можно использовать для того, чтобы не указывать имена и типы столбцов:

CREATE TABLE city
    ENGINE=CONNECT,
    TABLE_TYPE=ODBC,
    TABNAME='city'
    CONNECTION='Driver=SQL Server Native Client 13.0;Server=sql-server-hostname;Database=world;UID=mariadb_connect;PWD=secret';

Однако мы можем предпочесть вручную указать типы, размеры и наборы символов MariaDB.

Связанный сервер

Вместо использования MariaDB CONNECT, можно использовать функциональность SQL Server Linked Server. Это позволит читать данные из удалённой базы данных MariaDB и копировать их в локальные таблицы SQL Server. Обратите внимание, что CONNECT обеспечивает больший контроль над сопоставлением типов и наборов символов.

Обратитесь к разделу «Связанные серверы» в документации Microsoft.