В SQL есть нюанс, который мучает многих дата-аналитиков: как сравнить таблицы между двумя базами данных? К сожалению, простого решения не существует:
- Вы можете создать SQL-скрипт, сравнивающий две таблицы, но он не универсален и должен переписываться для каждого нового случая.
- Сравнение таблиц и схем баз данных доступно в таких инструментах, как Azure Data Studio или SQL Server Data Tools. Однако они используются не повсеместно.
- И хотя существуют менее популярные инструменты, которые генерируют отчеты о разнице между таблицами, многие из них ограничены одним типом базы данных.
Задача может стать еще сложнее, если наборы данных поступают из разных SQL-движков и несвязанных систем, таких как Google Sheets и облачная CRM.
На помощь приходит зерокодинг. В этой статье мы разбираем универсальный подход, который позволяет сравнить две базы данных на предмет различий, независимо от их происхождения. Нам поможет n8n — инструмент автоматизации рабочего процесса с исходным кодом.
Вам больше не нужно будет создавать SQL-скрипты для каждого нового случая использования. Руководство позволит обойти ограничения, которые связаны с инструментами, привязанными к одному типу баз данных.
Подготовим тестовую среду
Создадим бесплатный сервер MySQL и импортируем базу данных примера. Если у вас уже есть база данных SQL, вы можете пропустить этот шаг и перейти непосредственно к первому сценарию: сравнению наборов данных SQL.
Шаг 1. Получите доступ к бесплатному серверу MySQL
Существует несколько бесплатных песочниц SQL, таких как DB fiddle или SQLZoo, которые предлагают среду для тестирования. Хотя они имеют простой в использовании браузерный пользовательский интерфейс, нам нужна платформа с возможностью удаленного подключения для импорта данных в n8n. Поэтому мы предлагаем создать бесплатный аккаунт на сайте db4free. Просто введите желаемое имя базы данных, пользователя, пароль и адрес электронной почты.
Db4free предлагает простую в настройке базу данных SQL с удаленным доступом
После регистрации вы получите электронное письмо со ссылкой для активации. Щелкните по ней, прежде чем пытаться подключиться к новой учетной записи. Теперь вы можете войти в систему через phpMyAdmin — удобный инструмент для управления удаленными базами данных SQL.
Шаг 2. Импортируйте готовую базу данных
Теперь, когда у вас есть бесплатная учетная запись с удаленным доступом к серверу MySQL, давайте заполним базу данных. Вы можете либо создать все таблицы с нуля, либо импортировать небольшой фрагмент существующих данных для тестирования.
Мы будем использовать базу данных model с сайта MySQL Tutorial. Скачайте базу данных classicmodels, распакуйте архив и откройте файл mysqlsampledatabase.sql
в текстовом редакторе:
Удалите оператор CREATE DATABASE и введите имя вашей базы данных в операторе USE
Поскольку служба db4free не позволяет создавать новые базы данных с произвольными именами, вам придется удалить оператор CREATE DATABASE и указать имя вашей базы данных в операторе USE. После внесения этих изменений сохраните файл mysqlsampledatabase.sql
.
Затем импортируйте эту базу данных в phpMyAdmin, выполнив следующие шаги:
- Щелкните по своей базе данных слева;
- Перейдите на вкладку Import;
- Выберите файл
mysqlsampledatabase.sql
; - Отключите опцию Enable foreign key checks («Включить проверку внешних ключей») — этот шаг очень важен для успешного импорта;
- Прокрутите страницу вниз и нажмите кнопку «Импорт».
После завершения импорта вы увидите новые таблицы под именем вашей базы данных.
phpMyAdmin поддерживает импорт из файлов нескольких форматов
Сценарий 1: Сравнение наборов данных SQL
В этом разделе мы расскажем вам, как сравнить две таблицы в SQL с помощью n8n. Вот базовая схема работы:
Простой сценарий n8n для сравнения двух наборов данных
Шаг 1. Добавьте новый узел MySQL
Сначала добавьте новый узел MySQL и укажите учетные данные для базы данных db4free:
Учетные данные MySQL для доступа к базе данных db4free
В этом примере мы сравниваем два немного отличающихся подмножества из одной таблицы, взятых из одной базы данных. В реальной жизни вам может понадобиться сравнить две версии одной и той же таблицы, хранящиеся в двух разных копиях базы данных. Или вы можете сравнить два подмножества данных из совершенно разных СУБД (в этом случае сначала необходимо переименовать переменные, чтобы они совпадали в обоих источниках). Узел Compare Datasets в n8n справится со всеми этими задачами.
Шаг 2. Загрузите данные из MySQL в n8n
Теперь импортируем некоторые данные. В этом руководстве мы смоделируем загрузку двух немного отличающихся сводных отчетов из MySQL.
Выберите операцию Execute SQL в первом узле MySQL и введите следующий запрос:
SELECT customerNumber, SUM(amount) as Total, COUNT(*) as ordercount, YEAR(paymentDate) as year
FROM payments
WHERE YEAR(paymentDate) = '2003' OR YEAR(paymentDate) = '2004'
GROUP BY customerNumber, year;
Этот SQL-запрос создает сводный отчет для таблицы платежей, рассчитывая общую сумму и количество транзакций для каждого клиента за два последовательных года. Вы можете изучить некоторые основные команды SQL на сайте MySQL Tutorial.
Шаг 3. Добавьте второй узел MySQL
Теперь нужно получить немного другой набор данных, добавив второй узел MySQL:
- Скопируйте первый узел MySQL и переименуйте его в «Заказы 2004 и 2005 годов»;
- Используйте те же настройки, что и в первом узле, но измените годы в запросе;
- Затем добавьте узел Set, чтобы переопределить количество платежей (ordercount), установив его на фиксированное значение 1. Таким образом мы немного изменим некоторые данные, поступающие из второго узла MySQL.
Переопределение переменной ordercount в узле Set
Шаг 4. Используйте узел Compare Datasets (Сравнение наборов данных)
Теперь можно сравнивать два разных набора данных. Добавьте новый узел Compare Datasets и настройте его, введя customerNumber и year в поля Input A и Input B.
Узел Compare Datasets классифицирует входящие данные на четыре различных выхода
Давайте подробнее рассмотрим правую часть скриншота. In A only Branch показывает только первый набор входных данных (например, записи за 2003 год). Аналогично, в ветке In B only Branch содержится только второй набор входных данных (например, записи за 2005 год):
B only Branch имеет записи, которые поступают только со второго входа
Результаты на других двух вкладках несколько интереснее.
Same Branch содержит элементы, которые присутствуют в обоих входах и являются абсолютно одинаковыми.
Примеры вывода данных из той же ветки
Different Branch отображает элементы с одинаковыми полями соответствия, но с различиями в других переменных.
Различные отделения предоставляют подробную информацию для сравнения
В этом выводе вы можете увидеть, какие поля использовались для сопоставления (ключи), какие из них одинаковые, а какие разные. Обратите внимание, что переменная Total осталась прежней (поскольку мы ее не меняли). Однако переменная ordercount указана как другая. Это ожидаемо, так как мы изменили ее ранее.
Выполнив описанные выше шаги, вы сможете успешно сравнить таблицы между двумя базами данных с помощью n8n. Эта функция позволяет выполнять подробный анализ и оценку данных в упрощенном режиме, экономя ваше время и обеспечивая точность процессов обработки данных.
Сценарий 2: синхронизация данных SQL и Pipedrive
Теперь, когда мы наглядно разобрали, как работает узел Compare Datasets, давайте рассмотрим более сложный сценарий. Он синхронизирует два отдельных источника путем добавления недостающих записей и обновления измененных значений для существующих записей.
Этот сценарий регулярно синхронизирует таблицы Pipedrive и MySQL
Шаг 1. Подготовьте учетные записи
В предыдущем разделе мы уже описали, как создать бесплатный сервер MySQL и подключиться к нему. Вы можете добавить новую таблицу persons с несколькими переменными (id, name, primary_email, phone и update_time).
Создайте новые учетные данные для Pipedrive. Необязательно использовать именно его. Рабочий процесс можно адаптировать для подключения к вашей CRM, если она поддерживает API.
Шаг 2. Загрузите данные из MySQL и Pipedrive в n8n
Когда у вас есть данные из обоих источников, соедините узлы Compare Datasets. Добавьте узел Set сразу после узла Pipedrive, определите несколько переменных (id, name, primary_email, phone, update_time) и активируйте переключатель Keep Only Set. Это гарантирует, что все лишние переменные будут отброшены перед сравнением наборов данных.
Шаг 3. Сравните данные SQL и Pipedrive
Выполните узел Compare Datasets и обратите внимание, что элементы находятся в четырех разных выходных данных, аналогично тому, что было в первом сценарии.
Чтобы добавить недостающие записи, подключите узлы MySQL и Pipedrive, как показано на скриншоте, и настройте их на добавление новых записей.
Обратите внимание, что элементы ветки In A only Branch содержат записи только из источника MySQL, а значит, узел Pipedrive должен быть подключен к этому выходу.
То же и с In B only Branch. Те элементы, которые находятся только в Pipedrive, должны быть добавлены в базу данных MySQL.
Сравните данные SQL и Pipedrive
Шаг 4. Обновите измененные данные
Записи из вывода Same Branch не требуют никаких дополнительных действий.
Однако записи Different Branch необходимо синхронизировать. Это достигается за три коротких шага:
- Добавьте узел IF и настройте новое булевое условие
{{ !!$json["different"]["name"] || !!$json["different"]["phone"] }}
. Это позволит нам отлавливать изменения только в переменных name и phone. - Далее добавим узел Date & Time и преобразуем переменную update_time из MySQL. Эта переменная, изначально имеющая символьный формат, должна быть преобразована для сравнения дат.
- В другом узле IF добавьте условие Date & Time. Введите даты из обоих источников и проверьте, какая из них более поздняя.
Узел IF сравнивает, произошло ли первое значение Date & Time (Value 1) позже, чем значение 2 (Value 2)
Наконец, подключите узлы MySQL и Pipedrive к соответствующим выходам и настройте их на обновление существующих записей. Устанавливать узлы перед Update Person и Update Contact необязательно. В них просто указываются короткие имена переменных для значений идентификатора записи, имени и телефона. Таким образом, если имя пользователя или номер телефона изменится в одной системе, обновления будут отражены во второй.
n8n поддерживает множество SQL-движков благодаря встроенным узлам для MySQL, PostgreSQL, MongoDB и MS SQL. Выберите наиболее подходящий для вашего проекта и начните синхронизировать данные с сотнями других инструментов с помощью узла Compare Datasets. Если у вас есть какие-то вопросы по работе с n8n, задайте их в тематическом чате Telegram, где собрались самые разные специалисты, использующие инструмент. Здесь вы найдете поддержку коллег и ответы на свои вопросы.