Выгрузка данных напрямую через SQL

Система настройки и формирования файлов с SQL запросами, для обновления данных в удаленной системе.

Основная идея в подготовке, на стороне учетной системы, готовых запросов SQL для обновления данных во внешней системе системе (интернет-магазине).

Процесс выгрузки данных

Основная идея в подготовке, на стороне учетной системы, готовых запросов SQL для обновления данных во внешней системе системе (интернет-магазине).

1. По настроенному шаблону структуры данных внешней системы и настроенным источникам данных формируются файлы с SQL запросами и каталог дополнительных файлов (картинки и пр.);
2. В процессе формирования файлов с запросами регистрируются коды данных внешних таблиц и связываются с локальными данными, для последующей выгрузки обновлений;
3. Архив с подготовленными файлами (текстовые файлы с SQL запросами и дополнительные файлы) копируется на сервер внешней системы через FTP;
4. Запускается процесс (Bash, Python, PHP Cli и т.п.) обработки выгруженных файлов на внешнем сервере по SSH;
5. В случае успешной отправки файлов во внешнюю систему, в локальной системе удаляются подготовленные файлы.

Для чего нужен промежуточный этап записи запросов в файлы, почему бы сразу не отправлять запросы SQL серверу внешней системы?

Данный метод используется в первую очередь, для выгрузки данных в интернет-магазины, которые как правило не работают в одной сети с учетной системой, а расположены на удаленном сервере, где закрыт доступ к SQL серверу, кроме локальных запросов из соображений безопасности. Но возможность выгрузить файлы по FTP и затем выполнить через консоль SSH команду для выполнения запросов из выгруженных файлов есть всегда.

Структура запросов

В большинстве случаев запросы заполняют сразу рабочие таблицы внешней системы.

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

• Отказаться от обновления части, либо всех данных, в зависимости от прописанных в запросе условий;
• Добавить в запрос данные из базы внешней системы, для фильтров, либо как вспомогательные для обновления, создания данных.

Для этих целей структура выгруженных SQL запросов делится на части:

1. Перед запросами создания обновления данных. Выполняется глобальная очистка данных, создаются временные таблицы и тп.;
2. Процесс создания или обновления данных. Заполняют рабочие таблицы, либо предварительно созданные временные;
3. После создания данных.  Заполнение рабочих таблиц из временной, обработка данных (например рассчитать количество записей в группах) другие операции с базой данных.

Преимущества

Преимущества метода в сравнении с выгрузкой через XML файлы и API службы.

Скорость выгрузки

Максимально быстрый вариант, быстрее ничего не придумать. Достигается благодаря ряду причин:

1. Для формирования запросов достаточно выбора данных в виде простых таблиц, и относительно простыми и быстрыми запросами. В отличии от формирования XML файлов, либо объектов для API, где данные представляют собой дерево, иногда довольно сложное и требующее вложенных запросов;
2. Меньше избыточных данных, как теги в XML файлах, иногда довольно длинные, по требованию внешней системы;
3. Нет промежуточных операций преобразования данных, внешняя система получает готовые SQL запросы. При выгрузке через XML файлы, сначала формируется файл, затем файл читается и в итоге приходим к тем же запросам;
4. Возможность настройки наиболее подходящего модуля выбора для каждого вида данных, что позволяет для универсальности разработать несколько отдельных, более простых и быстрых вариантов выбора данных, вместо одного тяжелого, но учитывающего всею нюансы выбора данных;
5. Табличная структура данных на входе и выходе позволяет записывать данные в файл с запросами SQL для каждой строки. В оперативной памяти не формируются временные объекты данных, для последующей записи. Это быстрее и экономит ресурсы.

Как сделать выгрузку максимально тяжелой и медленной?

Взять за тело выгрузки XML файл с не очень удобной структурой и названиями тегов в пол строки, что бы дольше формировать и  читать файл, и было больше избыточных данных.

Сделать формирование файла на стороне 1С не прямой записью в файл, по мере формирования, а через объекты в памяти, что бы загрузить систему по максиму, и что бы падала при выгрузке больших объемов.

Затем на стороне сайта, сделать чтение файла так, что каждая строка не будет писать данные в базу сразу, а вызывать на каждой строке тяжелые встроенные функции записи данных.

Сделать более медленно нельзя даже намеренно. Примерно так это и реализовано в одной очень раскрученной CMS.

Универсальность

На стороне внешней системы не требуется никаких доработок ни в коде, ни в базе данных, так как система получает готовые запросы для создания и обновления данных.

Ограничения

Основные ограничения выгрузки данных в базу внешней системы через файлы с запросами SQL.

Несовместимая структура базы данных

Не любая структура базы данных внешней системы пригодна для данного способа выгрузки.

Основное ограничение! Во внешней системе (CMS, интернет-магазине) есть общие таблицы, в которых хранится несколько видов данных, такие системы не подходят!

Например в одной таблице хранятся названия товаров, названия групп и названия других материалов сайта.  Такой вариант не подходит для использования выгрузки данным методом!

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

Совместное редактирование

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

Например, товары нельзя одновременно и выгружать из учетной системы и создавать в интернет магазине.

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

Например, выгружаем товары в интернет-магазин.

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

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

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

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

Идентификаторы необходимы для связи внешних и локальных данных, используются при повторной выгрузке и обновлении данных во внешней системе.

Проблему возможно решить, если создать во внешней системе отдельные таблицы, где хранить связь данных в двух системах, но это потребует доработки внешней системы, добавить таблицы. Так же усложнит запросы выгрузки данных. В готовых решениях данный подход, не используется, но при необходимости возможно реализовать.

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

Нет контроля выполнения запросов

После выгрузки файлов с запросами на сервер удаленной системы и запуска скрипта распаковки и выполнения запросов, процесс не контролируется. Если на стадии выполнения запросов есть ошибки, в структуре SQL запросов, этого не увидеть.

Данное ограничение не критично:

1. Если была изменена или обновлена структура базы данных удаленной системы (интернет-магазина);
2. На стадии разработки шаблона выгрузки данных для новой внешней системы.

В таких случаях для просмотра ошибок требуется выполнять запуск распаковки и выполнения запросов вручную из консоли, либо выполнять запросы через администратор базы данных внешней системы (PHPMyAdmin и т.п.) что бы отследить ошибки при выполнении сформированных SQL запросов.

Нет контроля данных

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

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

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

Например выгрузка в маркетплейс, где данные выгружаются из различных систем (учетные системы клиентов маркетплейса), которые он не может контролировать.

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