Копирование данных в DWH (Oracle)

← →
ANB (2009-12-23 09:27) [0]

Стоит следущая задача (не передо мной, но нас коснется) :
Имеется таблица (проводки) в которой чуток больше миллиарда записей.
Нужно :
1) Скопировать данные в хранилище данных
2) Ежедневно поддерживать хранилище в актуальном состоянии
3) Не притормозить ОЛТП систему
Дельта :
в обычные дни в таблицу добавляется несколько сотен тысяч записей. В особые дни - несколько миллионов, в конце месяца - порядка 30-40 миллионов.

Вопрос : у кого нибудь есть опыт, как реализовать данную задачу ?
Своих идей хватает, но т.к. опыта работы с DWH нету - все варианты стремно применять из-за условия 3).

← →
Sergey13 © (2009-12-23 09:46) [1]

Примерно на таких же условиях у нас каждую ночь данные джобами переносятся из биллинга в ОЛАП-кубы. Правда на МССКЛ, но сути, думаю, это не меняет.
Главное, ИМХО, не затягивать с началом копирования - как только ОЛТП активность кончилась, так и начинай.

← →
ANB (2009-12-23 09:48) [2]

> Sergey13 © (23.12.09 09:46) [1]

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

← →
Sergey13 © (2009-12-23 09:51) [3]

> [2] ANB (23.12.09 09:48)

Отдельное поле с датой последней модификации записи не подходит?

← →
ANB (2009-12-23 10:08) [4]

> Отдельное поле с датой последней модификации записи не подходит?

Да вот думаем, но его заполнять неоткуда (все места в коде не поправишь) - это придется триггер вешать.
Сидим чешем репу - замедлит или нет потом массовые процедуры.

← →
Sergey13 © (2009-12-23 10:16) [5]

> [4] ANB (23.12.09 10:08)
> Сидим чешем репу - замедлит или нет потом массовые процедуры.

А с чего бы ему замедлять? Он же никуда не полезет - просто возьмет текущую системную дату. И уникальность (как источник тормозов) не надо поддерживать.

← →
ANB (2009-12-23 11:12) [6]

> Sergey13 © (23.12.09 10:16) [5]

Идея катит. На удаление повесим триггер - пусть складывает логи, это редко бывает и плевать на скорость.
Остается вопрос с местом решить.

← →
Sergey13 © (2009-12-23 11:16) [7]

> [6] ANB (23.12.09 11:12)
> На удаление повесим триггер - пусть складывает логи

А у вас физически проводка удаляется? Вроде как неправильно это.

← →
ANB (2009-12-23 11:26) [8]

> А у вас физически проводка удаляется? Вроде как неправильно
> это.

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

← →
sniknik © (2009-12-23 12:26) [9]

> Отдельное поле с датой последней модификации записи не подходит?
> Да вот думаем, но его заполнять неоткуда (все места в коде не поправишь) - это придется триггер вешать.
timestamp ?

← →
sniknik © (2009-12-23 12:27) [10]

блин, не обратил внимания на оракл, сбило
> Правда на МССКЛ,

← →
ANB (2009-12-23 12:33) [11]

> timestamp ?

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

← →
Sergey13 © (2009-12-23 12:52) [12]

> [11] ANB (23.12.09 12:33)

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

← →
sniknik © (2009-12-23 13:01) [13]

> Только заполнять при апдейте придется триггером.
я вообще то предложил его как раз потому, что заполнять не надо... в mssql. уникальное возрастающее значение после любого редактирования записи, можно использовать в общем, и без тригера. т.к. было -
> Да вот думаем, но его заполнять неоткуда (все места в коде не поправишь) - это придется триггер вешать.

но в общем то ладно, все одно не пойдет, из-за платформы.

← →
Игорь Шевченко © (2009-12-23 14:48) [14]

materialized views ? оно само умеет "актуальность" поддерживать, AFAIK

← →
ANB (2009-12-23 15:27) [15]

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

30 миллионов записей джойнить нестед лупсом стремно потом.

> materialized views ? оно само умеет "актуальность" поддерживать,
> AFAIK

Пробовали. ОЛТП система легла. Ведение снапшот логов затормозило массовые процедуры раза в 3.

Тут с тамйштампом проблемка рисуется.

Имеем :
1) последний раз выгружали 00:00:00.0000, сейчас 01:00:00.0000.
В 00:59:59.9999 легла инсертом запись. Таймштамп триггером выставился, есно 00:59:59.9999. Коммита не было. Стартует джоб, выгребает все записи
с 00:00:00.0001 по 01:00:00.0000. Запись 00:59:59.9999 он не видит (не закоммичена).
2) сейчас 02:00:00.0000. Стартует джоб. Запись 00:59:59.9999 закоммитилась в 01:00:01.0000. Но в новой итерации джоб опять ее не видит, т.к. джоб смотрит с 01:00:00.0001 по 02:00:00.0000.
Проводка потерялась.

Чего тут можно придумать ?

← →
Sergey13 © (2009-12-23 15:33) [16]

> [15] ANB (23.12.09 15:27)
> 30 миллионов записей джойнить нестед лупсом стремно потом.

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

> Чего тут можно придумать ?

Еще одно поле-признак архивирована/нет. Может тогда и дату время не надо писать.

← →
Игорь Шевченко © (2009-12-23 15:58) [17]

> Пробовали. ОЛТП система легла. Ведение снапшот логов затормозило
> массовые процедуры раза в 3.

Что вы пробовали ?

Это читал наизусть ?
http://download.oracle.com/docs/cd/B14117_01/server.101/b10736/basicmv.htm#i1007299
http://download.oracle.com/docs/cd/B14117_01/server.101/b10736/advmv.htm#i1007694

← →
ANB (2009-12-23 16:11) [18]

> Еще одно поле-признак архивирована/нет. Может тогда и дату
> время не надо писать.

RBS не хватит столько проапдейтить.

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

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

> Это читал наизусть ?
> http://download.oracle.com/docs/cd/B14117_01/server.101/b10736/basicmv.
> htm#i1007299
> http://download.oracle.com/docs/cd/B14117_01/server.101/b10736/advmv.
> htm#i1007694

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

← →
Игорь Шевченко © (2009-12-23 16:35) [19]

ANB (23.12.09 16:11) [18]

Видишь ли, проблема перекачки данных из OLTP в DWH появилась не у тебя первого. Из большого OLTP в большой DWH тоже. Потому я склонен все-таки доверять Oracle, который на этих процессах не одну собаку съел.
У меня сейчас под рукой нет ни одной книги по DWH в Oracle, я б процитировал насчет materialized views.

Martin Rennhackkamp в журнале DBMS много писал как про организацию DWH, так и про его загрузку, причем было это лет 8 назад. Я к чему - я к тому, что с тех пор многое из описанного взято на вооружение производителями СУБД :)

← →
Sergey13 © (2009-12-23 16:40) [20]

> [18] ANB (23.12.09 16:11)
> RBS не хватит столько проапдейтить.

Никто и не заставляет всю кучу сразу тянуть. Тяни пачками.

← →
ANB (2009-12-23 17:23) [21]

> У меня сейчас под рукой нет ни одной книги по DWH в Oracle,
> я б процитировал насчет materialized views.

Игорь, материализованные представления мы первым делом опробовали. Может мы их неправильно готовили, конечно.
Делали :
1) Прицепили на одну таблицу снап шот лог (всего их надо несколько десятков реплицировать)
2) Создали мат.представление с фаст обновлением раз в сутки
Запустили массовую процедуру. Причем обновление в этот момент не работало. Массовая процедура отработала раза в 3 медленнее. Да и вся система стала притормаживать. Снесли логи - система задышала.

← →
ANB (2009-12-23 17:25) [22]

> Никто и не заставляет всю кучу сразу тянуть. Тяни пачками.

апдейт 30 миллионов записей что пачками что разом - не быстро это. Плюс тянульщиков уже минимум 2 образовалось.

← →
zorik © (2009-12-23 17:57) [23]

Можно пофлудить? Просто интересна область применения такого огромного количества данных? Сори за офтоп

← →
ANB (2009-12-23 18:10) [24]

> Можно пофлудить? Просто интересна область применения такого
> огромного количества данных? Сори за офтоп

Маленький скромный банк.

← →
Кщд (2009-12-24 05:47) [25]

чем плохи Oracle Streams, BPEL?

← →
Sergey13 © (2009-12-24 08:54) [26]

> [22] ANB (23.12.09 17:25)
> апдейт 30 миллионов записей что пачками что разом - не быстро это.

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

← →
Кщд (2009-12-24 09:24) [27]

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

зачем ориентироваться на timestamp, который к тому же надо будет дважды в год анализировать на таймзону, когда в Oracle есть SCN?
если банк, то база точно в архивлогах, поэтому стандартный механизм репликации не создаст существенной доп. нагрузки наOLTP-сервер.

← →
ANB (2009-12-24 09:25) [28]

> чем плохи Oracle Streams, BPEL?

А чем хороши ? У них другой принцип, по сравнению с мат.вьюхами ?
У меня почему то сложилось стойкое предубеждение к новомодным фичам оракла.
Испытывались :
1. Репликация односторонняя
2. Адвансед репликация (двухсторонняя)
3. Очереди
От всех этих фичей осталось самое хреновое впечатление. Тормозят безбожно, много ограничений, писать под них много и неудобно.
И при любом раскладе требуют толстого и надежного канала связи.

> Ну во первых не так уж и много.

Для инсерта - да. Для апдейта - достаточно много.

← →
Кщд (2009-12-24 09:52) [29]

ANB (24.12.09 09:25) [28]
Streams - фича довольно-таки "старая"
в 10-ке Ваши пп.1-3 отлично работают
"тормозов" не замечено
удобство написания: три основных пакета с основными интерфейсами - capture, propagation, apply - ничего сложного
канал связи - действительно, желателен стабильный, но если связь прервется - ничего страшного, т.к. при возобновлении соединения данные польются ровно с того места(SCN), на котором связь пропала

> [28] ANB (24.12.09 09:25)
> Для инсерта - да. Для апдейта - достаточно много.

Помнится в институте слышал - критерием истины является практика. Наколбасить 30 лимонов записей да проапдейтить.

Кстати можно и без апдейта и без всяких доп полей.
В отдельной таблице хранятся просто ид-шники измененных/новых и НЕзаархивированных записей. При перетаскивании/архивации из нее соответственно удалять.

← →
ANB (2009-12-24 10:52) [32]

> В отдельной таблице хранятся просто ид-шники измененных/новых
> и НЕзаархивированных записей. При перетаскивании/архивации
> из нее соответственно удалять.

Примерно так и работает фаст мат.вьюха на снапшот логах. Тормоза начинаются.

> "тормозов" не замечено

На каких обьемах ?

← →
ANB (2009-12-24 10:54) [33]

> Наколбасить 30 лимонов записей да проапдейтить.

Колбасил - апдейт висит долго.

> [32] ANB (24.12.09 10:52)
> Тормоза начинаются.

На чем тормоза? На перекачке? Так они ИМХО по любому будут на таких объемах. Потому и надо их на ночь переносить - пусть подтормаживает. Лишь бы за ночь отрабатывало.

← →
ANB (2009-12-24 12:02) [35]

> Потому и надо их на ночь переносить - пусть подтормаживает.
> Лишь бы за ночь отрабатывало.

Нужно каждый час запускать.
На перекачке тормозов не будет - минут 5-10. Висеть будет апдейт. Более того - он будет тормозить ОЛТП базу.

Кстати, покопал стрим - вроде ничего по идее. Архивные логи пишуться по любому.
У нас уже отрабатывается такой вариант. Только места под него пока нет - придется еще один дисковый массив покупать, а он дорогой зараза.
Сейчас архив логи раз в сутки у нас чистяться.

> [35] ANB (24.12.09 12:02)
> Висеть будет апдейт.

Можно же без апдейта. На отдельную таблицу ссылок не будет - удалить должно быстро. Да и будет она при таком подходе небольшая.

← →
Кщд (2009-12-24 13:41) [37]

> ANB (24.12.09 10:52) [32]
> > "тормозов" не замечено
> На каких обьемах ?

на сравнимых, т.е. порядка 10^6 записей в день
впрочем, объемы не важны, т.к. репликация идет по мере поступления, а 10-20 записей в секунду - не тот объем, который повесит Oracle)

← →
ANB (2009-12-24 14:28) [38]

> Можно же без апдейта. На отдельную таблицу ссылок не будет
> - удалить должно быстро.

Таблицу можно вообще транкейтить.
С обработкой отдельной таблицы проблем особых нет, но :
1) Будет момент, когда в ней будет 30 миллионов записей (джойн с основной таблицей повиснет)
2) Можно не джойнить, а сразу складывать все поля, но ведение лога сильно затормозит массовые процессы.
Короче, будем смотреть в сторону стрима, если не придумаем ничего толкового с таймштампом.
Но, милин, начальство нас убъет :)
Там не один лям баксов надо будет вложить.

← →
ANB (2009-12-24 14:30) [39]

> не тот объем, который повесит Oracle)

По стриму, как я понял, самое полезное, что ОЛТП база вообще не затрагивается. Все на архив.логах сделано. А тормоза DWH нас волнуют слабо. Не наша проблема.

← →
Кщд (2009-12-25 07:46) [40]

>ANB (24.12.09 14:30) [39]
>По стриму, как я понял, самое полезное, что ОЛТП база вообще не >затрагивается.
именно так, а процесс, который шерстит архивлоги со скоростью 10-20 строк в секунду сколько-нибудь существенного вклада в утилизацию CPU и I/O не вносит

Копирование данных в DWH (Oracle) Найти похожие ветки