Перед нами стоит задача интегрирования нескольких баз данных, различных видов (Oracle, SQL Server) и имеющих различную структуру.
Ранее на форуме я задавал вопрос о том, имеются ли стандартные способы решения задачи такого рода, на что не получил достаточного количества ответов и отзывов. Один из ответивших сказал, что лучше реализовывать такой механизм вручную.
Я опишу упрощенный теоретический кейс (чтобы не вдаваться в излишние детали конкретно нашей ситуации) и изложу механизм интеграции, который у меня возник в голове.
Преположим, что у нас есть 2 базы данных (Oracle, SQL Server 2000), в каждой из которых имеется таблица клиентов. Это разные таблицы, имеющие разную структуру данных, и сами данные (клиенты) в этих таблицах разные. Для наглядности можем считать, что в Oracle содержатся клиенты-юрлица (ClientsCompanies), а в SQL Server 2000 — клиенты-физлица (ClientsPeople).
Перед нами стоит задача разработка еще одной базы данных (на этот раз SQL Server 2005), в которой была бы таблица AllClients, в которой содержались бы данные из двух перечисленных выше таблиц.
Задача состоит в том, чтобы в таблице AllClients всегда находились актуальные данные из двух других таблиц — то есть данные должны быть синхронизированы в режиме real-time (или с совсем небольшой задержкой).
Вот такая задача. Как ее реализовать?
Я приведу решение, к которому я пришел — но т. к. я не специализируюсь на БД и на SQL, мое решение может оказаться не самым лучшим.
1) В БД SQL Server 2005 (главной БД, в которой таблица AllClients) создаются таблицы ClientsCompanies и ClientsPeople, имеющие в точности такие же структуры, как и соответствующие таблицы в двух исходных БД.
2) С помощью некоторого механизма (простого копирования, репликации и др.) данные из исходных таблиц переносятся в таблицы в SQL Server 2005.
3) В SQL Server 2005 я создаю дополнительную таблицу соответствий идентификаторов, имеющую следующую структуру.
а) SourceTable = "ClientsCompanies" | "ClientsPeople".
б) ExternalClientId — идентификатор клиента в таблице внешней БД (т. е. идентификатор клиента в таблице ClientsCompanies или ClientsPeople).
в) ClientId — идентификатор клиента в таблице "AllClients".
4) Делаю View, которое возвращает клиентов из таблиц ClientsCompanies и ClientsPeople, а идентификаторы берет из таблицы соответствий.
То есть в реальности таблицы AllClients не будет — будет виртуальная таблица / представление, которое будет содержать клиентов из двух внешних таблиц, при этом клиенты в таблице AllClients будут иметь уникальные идентификаторы.
5) При добавлении или удалении клиентов из таблиц ClientsCompanies и ClientsPeople обновляем таблицу соответствий идентификаторов.
Вот это хитрая часть — как раз хотел спросить, как лучше реализовать такое авообновление таблицы соответствий идентификаторов — напишу вопрос в виде отдельной темы.
Хмм... Понятно ли то, что я написал? Мне кажется, что не очень — хотя старался написать понятно )
В любом случае — кто может какие дать советы по решению данной задачи? Спасибо!
Re: Хитрая задачка: интеграция таблиц клиентов из разных БД
Здравствуйте, michag, Вы писали:
M>Перед нами стоит задача интегрирования нескольких баз данных, различных видов (Oracle, SQL Server) и имеющих различную структуру.
Ответов нет... Неужели никто не решал подобную задачу? Мне кажется, что интеграция разных систем и баз данных — одна из самых типичных и распространенных задач. Неужели никто не занимался этим? Чем же тогда занимаются системные интеграторы, зарабатывающие миллионы долларов?
Re[2]: Хитрая задачка: интеграция таблиц клиентов из разных
Здравствуйте, michag, Вы писали:
M>Здравствуйте, michag, Вы писали:
M>>Перед нами стоит задача интегрирования нескольких баз данных, различных видов (Oracle, SQL Server) и имеющих различную структуру.
M>Ответов нет... Неужели никто не решал подобную задачу? Мне кажется, что интеграция разных систем и баз данных — одна из самых типичных и распространенных задач. Неужели никто не занимался этим? Чем же тогда занимаются системные интеграторы, зарабатывающие миллионы долларов?
раз все молчат...
Не вижу смысла в "виртуальной" таблице. Написать на DTS (в mssql05 он Integration Service) простенький перекидыватель данных из двух этих баз в реальную таблицу клиентов, дело 15 минут (или 1 дня если ниразу с DTS не сталкивался). Чтоб не ломать голову, лучше, конечно, две таблицы -- переходную и рабочую. В переходную сначала сливаются все записи по клиентам из обоих баз, а потом, запросом синхронизируются рабочая и основная таблицы. Запуск перекидывателя можно в Job'ы добавить, а можно и в триггере прописать.
Я подобное решение видел, но там синхронизация два-три раза в день требовалась... а тебе как часто?
Наука изощряет ум; ученье вострит память.
(c) Козьма Прутков
Re: Хитрая задачка: интеграция таблиц клиентов из разных БД
Здравствуйте, michag, Вы писали:
M>Перед нами стоит задача интегрирования нескольких баз данных, различных видов (Oracle, SQL Server) и имеющих различную структуру.
А нельзя сделать ф-цию на .NET ? которая будет конентится и возвращать некий роусет ?
Re: Хитрая задачка: интеграция таблиц клиентов из разных БД
michag пишет:
> Задача состоит в том, чтобы в таблице AllClients всегда находились > актуальные данные из двух других таблиц — то есть данные должны быть > синхронизированы в режиме real-time (или с совсем небольшой задержкой).
В real-time почти ни одна СУБД не работает.
Так что вам не это надо, а в on-line.
Но подозреваю и это вам излишне.
> > Вот такая *задача*. Как ее реализовать?
Если on-line — только распределёнными транзакциями.
Удолбаетесь реализовывать.
> *2)* С помощью некоторого механизма (простого копирования, репликации и > др.) данные из исходных таблиц переносятся в таблицы в SQL Server 2005.
Репликация вам не даст ваш "Real-time". Репликации всегда
(ну или скажем — как правило) асинхронные. Это значит, изменение
данны в источнике и приёмнике разнесены во времени. Гарантируется
только, что приёмник будет в одном из последовательных согласованных
состояний, в которых находился какое-то время назад источник.
> Хмм... Понятно ли то, что я написал? Мне кажется, что не очень — хотя > старался написать понятно ) > > В любом случае — кто может какие дать советы по решению данной задачи?
Понятно. Вы не знаете, чего хотите, и изобретаете велосипед.
Деревянный.
Posted via RSDN NNTP Server 2.1 beta
Re[2]: Хитрая задачка: интеграция таблиц клиентов из разных
michag пишет:
> Ответов нет... Неужели никто не решал подобную задачу? Мне кажется, что > интеграция разных систем и баз данных — одна из самых типичных и > распространенных задач.
Это — одна из сложнейших задач в БД, на самом деле.
Неужели никто не занимался этим? Чем же тогда > занимаются системные интеграторы, зарабатывающие миллионы долларов?
Вот этим и занимаются .... думаете им зря платят, что ли ?
Posted via RSDN NNTP Server 2.1 beta
Re[2]: Хитрая задачка: интеграция таблиц клиентов из разных
andy.wu пишет:
> M>Перед нами стоит задача *интегрирования нескольких баз данных*, > различных видов (Oracle, SQL Server) и имеющих различную структуру. > > А нельзя сделать ф-цию на .NET ? которая будет конентится и возвращать > некий роусет ?
У него вроде как гетерогенная сеть из разных СУБД, или что?
Posted via RSDN NNTP Server 2.1 beta
Re[2]: Хитрая задачка: интеграция таблиц клиентов из разных
Здравствуйте, MasterZiv, Вы писали:
MZ>В real-time почти ни одна СУБД не работает. MZ>Так что вам не это надо, а в on-line. MZ>Но подозреваю и это вам излишне.
Да, действительно, real-time синхронизация данных между БД — это я немного преувеличил. Если будет задержка несколько минут — это не так страшно.
>> >> Вот такая *задача*. Как ее реализовать?
MZ>Если on-line — только распределёнными транзакциями. MZ>Удолбаетесь реализовывать.
А что значит "распределенные транзакции"? Это на языке SQL пишется?
>> *2)* С помощью некоторого механизма (простого копирования, репликации и >> др.) данные из исходных таблиц переносятся в таблицы в SQL Server 2005.
MZ>Понятно. Вы не знаете, чего хотите, и изобретаете велосипед. MZ>Деревянный. MZ>
Вот именно поэтому я и решил поинтересовать на форуме, как другие решали подобные задачи. Делать собственный, деревянный велосипед в данной ситуации мне совершенно не хочется — поэтому и спрашиваю, нет ли стандартных велосипедов для ситуаций подобного рода? Или хотя бы ясных и четких инструкций, как самому смастерить хороший велосипед? Где можно об этом почитать? Я что-то ничего не нашел на эту тему.
Re[3]: Хитрая задачка: интеграция таблиц клиентов из разных
michag пишет:
> Да, действительно, real-time синхронизация данных между БД — это я > немного преувеличил. Если будет задержка несколько минут — это не так > страшно.
Ну тогда вам просто репликация подходит. Но учтите,
что рассинхронизацию вам контролировать никак не удастся
(типа " чтобы была не менее Х минут")
> А что значит "распределенные транзакции"? Это на языке SQL пишется?
Почитайте в какой-нибудь *педии.
> Вот именно поэтому я и решил поинтересовать на форуме, как другие решали > подобные задачи. Делать собственный, деревянный велосипед в данной > ситуации мне совершенно не хочется — поэтому и спрашиваю, нет ли > стандартных велосипедов для ситуаций подобного рода?
Есть. Очень дорогостоящие трансокеанские лайнеры.
Или хотя бы ясных и > четких инструкций, как самому смастерить хороший велосипед? Где можно об > этом почитать? Я что-то ничего не нашел на эту тему.
Не знаю.
Posted via RSDN NNTP Server 2.1 beta
Re[3]: Хитрая задачка: интеграция таблиц клиентов из разных
Здравствуйте, ZAMUNDA, Вы писали:
ZAM>Не вижу смысла в "виртуальной" таблице. Написать на DTS (в mssql05 он Integration Service) простенький перекидыватель данных из двух этих баз в реальную таблицу клиентов, дело 15 минут (или 1 дня если ниразу с DTS не сталкивался). Чтоб не ломать голову, лучше, конечно, две таблицы -- переходную и рабочую. В переходную сначала сливаются все записи по клиентам из обоих баз, а потом, запросом синхронизируются рабочая и основная таблицы. Запуск перекидывателя можно в Job'ы добавить, а можно и в триггере прописать.
А что, если данные в исходной таблице изменились? Например, в таблице 1 000 000 строк, и изменилась только одна строка в исходной таблице. Как обновить конечную таблицу? Удалить в ней все строки, а затем заново залить 1 000 000 срок? Это явно неэффективно. А view всегда будет возвращать актуальные данные — в этом я вижу достоинство подхода с виртуальной таблицей.
Re[4]: Хитрая задачка: интеграция таблиц клиентов из разных
Здравствуйте, michag, Вы писали:
M>А что, если данные в исходной таблице изменились? Например, в таблице 1 000 000 строк, и изменилась только одна строка в исходной таблице. Как обновить конечную таблицу? Удалить в ней все строки, а затем заново залить 1 000 000 срок? Это явно неэффективно. А view всегда будет возвращать актуальные данные — в этом я вижу достоинство подхода с виртуальной таблицей.
Для этого существует тип данных rowversion.
Re[3]: Хитрая задачка: интеграция таблиц клиентов из разных
Здравствуйте, ZAMUNDA, Вы писали:
ZAM>Здравствуйте, michag, Вы писали:
M>>Здравствуйте, michag, Вы писали:
M>>>Перед нами стоит задача интегрирования нескольких баз данных, различных видов (Oracle, SQL Server) и имеющих различную структуру.
M>>Ответов нет... Неужели никто не решал подобную задачу? Мне кажется, что интеграция разных систем и баз данных — одна из самых типичных и распространенных задач. Неужели никто не занимался этим? Чем же тогда занимаются системные интеграторы, зарабатывающие миллионы долларов? ZAM>раз все молчат... ZAM>Не вижу смысла в "виртуальной" таблице. Написать на DTS (в mssql05 он Integration Service) простенький перекидыватель данных из двух этих баз в реальную таблицу клиентов, дело 15 минут (или 1 дня если ниразу с DTS не сталкивался).
Немного усложню свой пример (сделаю его более приближенным к нашей реальной ситуации). В Oracle содержатся не только клиенты, но и сделки, заключенные с этими клиентами.
Когда сделки будут копироваться в БД назначения, в таблице сделок будет создано поле ClientId, содержащее идентификатор клиента — при этом это будет идентификатор из таблицы AllClients, которая была создана в БД назначения.
Далее. Пусть нам нужно скопировать из Oracle некоторую сделки. В Oracle в строке сделки стоит свой идентификатор клиента, а не ClientId. Поэтому требуется еще установить соответствие между идентификаторами клиентов в Oracle и БД назначения (SQL Server), для чего требуется создать таблицу соответствия идентификаторов. И дальше — возникают вопросы, которые я задавал в http://www.rsdn.ru/forum/message/3163605.1.aspx
Здравствуйте, michag, Вы писали:
M>Далее. Пусть нам нужно скопировать из Oracle некоторую сделки. В Oracle в строке сделки стоит свой идентификатор клиента, а не ClientId. Поэтому требуется еще установить соответствие между идентификаторами клиентов в Oracle и БД назначения (SQL Server), для чего требуется создать таблицу соответствия идентификаторов. И дальше — возникают вопросы, которые я задавал в http://www.rsdn.ru/forum/message/3163605.1.aspx
Здравствуйте, _d_m_, Вы писали:
___>Здравствуйте, michag, Вы писали:
M>>Далее. Пусть нам нужно скопировать из Oracle некоторую сделки. В Oracle в строке сделки стоит свой идентификатор клиента, а не ClientId. Поэтому требуется еще установить соответствие между идентификаторами клиентов в Oracle и БД назначения (SQL Server), для чего требуется создать таблицу соответствия идентификаторов. И дальше — возникают вопросы, которые я задавал в http://www.rsdn.ru/forum/message/3163605.1.aspx
. В общем, мне кажется, что без таблицы соответствия идентификаторов тут не обойтись.
___>Можно и нужно обойтись.
M>>Или же здесь можно применить какой-то другой, более умный подход?
___>В качестве идентификаторов используй GUID-ы.
Не понял, а как это поможет?
Ситуация такая: таблицы в Oracle править точно никак нельзя, но и таблицы в SQL Server тоже очень нежелательно было бы менять — т. е. у нас исходная и конечная базы данных имеют заданную структуру, менять которую нельзя. Можно только добавлять новые вспомогательные таблицы, но не более того.
И куда мне вставлять эти GUID'ы? Расскажите на моем конкретном примере с клиентами.
Re[6]: Хитрая задачка: интеграция таблиц клиентов из разных
Здравствуйте, michag, Вы писали:
M>Ситуация такая: таблицы в Oracle править точно никак нельзя, но и таблицы в SQL Server тоже очень нежелательно было бы менять — т. е. у нас исходная и конечная базы данных имеют заданную структуру, менять которую нельзя. Можно только добавлять новые вспомогательные таблицы, но не более того.
M>И куда мне вставлять эти GUID'ы? Расскажите на моем конкретном примере с клиентами.
Если менять нельзя, то никуда.
Невозможно сделать репликацию изменений, если реплицируемые таблицы менять нельзя. Ну или в крайнем случае триггеры надо навесить на таблицы. Иначе никак.
Re[7]: Хитрая задачка: интеграция таблиц клиентов из разных
Здравствуйте, _d_m_, Вы писали:
M>>И куда мне вставлять эти GUID'ы? Расскажите на моем конкретном примере с клиентами.
___>Если менять нельзя, то никуда. ___>Невозможно сделать репликацию изменений, если реплицируемые таблицы менять нельзя. Ну или в крайнем случае триггеры надо навесить на таблицы. Иначе никак.
Триггеры добавить можно. Я имел в виду, что нельзя менять структуру таблиц.
Хорошо, а если представить, что менять структуру таблиц, добавляя туда новые столбцы, можно — как тогда GUID'ы могут помочь?
Re: Хитрая задачка: интеграция таблиц клиентов из разных БД
Здравствуйте, michag, Вы писали:
M>В любом случае — кто может какие дать советы по решению данной задачи? Спасибо!
Я бы для начала потренировался примерно так:
1. Придумал бы "общую" структуру для интегрированной таблицы; вместе с полем-признаком "происхождение данных".
2. Прицепил бы оба сервера как Linked Servers к главному серверу
3. Сделал бы view в главном сервере, который бы возвращал бы union данных из обеих исходных таблиц
4. Посмотрел бы на шустродействие решения
5. Если устраивает, попробовал бы сделать триггеры instead of update на это view.
В триггере проверял бы признак происхождения данных, и в зависимости от него выполнял бы update соответствующей исходной таблицы.
6. Реализовал бы полный комплект триггеров и наслаждался результатом.
... << RSDN@Home 1.2.0 alpha rev. 677>>
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Re[8]: Хитрая задачка: интеграция таблиц клиентов из разных
Здравствуйте, michag, Вы писали:
M>Здравствуйте, _d_m_, Вы писали:
M>>>И куда мне вставлять эти GUID'ы? Расскажите на моем конкретном примере с клиентами.
___>>Если менять нельзя, то никуда. ___>>Невозможно сделать репликацию изменений, если реплицируемые таблицы менять нельзя. Ну или в крайнем случае триггеры надо навесить на таблицы. Иначе никак.
M>Триггеры добавить можно. Я имел в виду, что нельзя менять структуру таблиц.
M>Хорошо, а если представить, что менять структуру таблиц, добавляя туда новые столбцы, можно — как тогда GUID'ы могут помочь?
Элементарно. Добавляешь в свои таблицы столбец типа uniqueidentifier, default = newid()
Все. Идентификатор уникальный глобально — не надо никакой таблицы соответствий. Реплицируй себе наздоровье.
Но для полноценной репликации изменения надо еще добавить столбец типа rowversion
Re[9]: Хитрая задачка: интеграция таблиц клиентов из разных
Здравствуйте, _d_m_, Вы писали:
M>>Хорошо, а если представить, что менять структуру таблиц, добавляя туда новые столбцы, можно — как тогда GUID'ы могут помочь?
___>Элементарно. Добавляешь в свои таблицы столбец типа uniqueidentifier, default = newid() ___>Все. Идентификатор уникальный глобально — не надо никакой таблицы соответствий. Реплицируй себе наздоровье.
___>Но для полноценной репликации изменения надо еще добавить столбец типа rowversion
Блин, то ли я туплю, то ли неясно излагаю свои мысли, в результате чего между нами возникает неполное понимание.
Нам нужно не только таблицу клиентов импортировать, но и связанные с клиентами данные — например, Orders (заказы, сделанные клиентами).
Представим себе: мы проимпортировал таблицу клиентов, из двух источников. В единой таблице в БД назначения у нас есть поле ClientGuid (как вы говорите).
Затем, нам нужно импортировать заказы. Вот берем первую запись из таблицы OrdersFromOracle — там стоит ClientId = 13245 (это идентификатор клиента в одной из БД-источников, но в БД назначения у нас этого идентификатора нет). А нам в БД назначения нужно, чтобы Orders ссылались на Clients.ClientGuid. То есть нам нужно из ClientId получить ClientGuid. И сделать это никак, кроме как через таблицу, хранящую соответствие между этими идентификаторами, по-моему, нельзя.
Re[9]: Хитрая задачка: интеграция таблиц клиентов из разных
Здравствуйте, _d_m_, Вы писали:
M>>Хорошо, а если представить, что менять структуру таблиц, добавляя туда новые столбцы, можно — как тогда GUID'ы могут помочь?
___>Элементарно. Добавляешь в свои таблицы столбец типа uniqueidentifier, default = newid() ___>Все. Идентификатор уникальный глобально — не надо никакой таблицы соответствий. Реплицируй себе наздоровье.
___>Но для полноценной репликации изменения надо еще добавить столбец типа rowversion
Блин, то ли я туплю, то ли неясно излагаю свои мысли, в результате чего между нами возникает неполное понимание.
Нам нужно не только таблицу клиентов импортировать, но и связанные с клиентами данные — например, Orders (заказы, сделанные клиентами).
Представим себе: мы проимпортировал таблицу клиентов, из двух источников. В единой таблице в БД назначения у нас есть поле ClientGuid (как вы говорите).
Затем, нам нужно импортировать заказы. Вот берем первую запись из таблицы OrdersFromOracle — там стоит ClientId = 13245 (это идентификатор клиента в одной из БД-источников, но в БД назначения у нас этого идентификатора нет). А нам в БД назначения нужно, чтобы Orders ссылались на Clients.ClientGuid. То есть нам нужно из ClientId получить ClientGuid. И сделать это никак, кроме как через таблицу, хранящую соответствие между этими идентификаторами, по-моему, нельзя.
Мне кажется, что интеграция разных систем и баз данных — одна из самых типичных и распространенных задач. Неужели никто не занимался этим? Чем же тогда занимаются системные интеграторы, зарабатывающие миллионы долларов? 
