Русский
Русский
English
Статистика
Реклама

Разработчик

Перевод Проект, который сжег меня до тла

14.04.2021 02:22:44 | Автор: admin

Сейчас 3 часа ночи 10 августа 2013 года. Бесконечный приступ паники не дает мне сомкнуть глаз. Как сумасшедший, я раз за разом прокручиваю только что написанный код в голове. Внезапно я осознаю, что в архитектуре приложения есть серьезная проблема. Я вскрикиваю и скатываюсь с кровати, спотыкаясь о свою девушку, я бегу как сумасшедший к своему компьютеру, чтобы все исправить.

Я знаю, что этот день будет прекрасной солнечной субботой. Я снова буду работать 12 часов подряд. А стресс и горящий дедлайн снова не дадут мне уснуть. Как я докатился до всего этого?

Пролог

Чтобы понять, что со мной происходит в данный момент, надо немного отмотать время.

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

Менеджер вызывает меня в комнату и объясняет, что мое первое задание это крупный проект. Да, я работаю в небольшом агентстве, но клиент очень важный. Объем проекта? Сумасшедший. Сроки? Еще безумнее. Хуже всего, что мне придется работать над этим самостоятельно. Я еще не понимаю почему, но осознаю, что мне это не понравится.

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

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

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

Запомни, ты не рок-звезда

Прежде чем я продолжу рассказ, давайте обсудим эту фразу. Стресс и выгорание для разработчиков уже привычные диагнозы. Обусловлено это целой кучей причин. Например, тем, что в IT-сфере появилась культура рок-звезд. Это идеологизация программиста, который все делает сам. Но для этого надо быть ниндзя, гуру, джедаем или еще каким-нибудь дерьмом вроде этого.

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

Проблема в том, что вы не дотягиваете до рок-звезд...

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

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

Лучший способ избежать выгорания не попадать в ситуацию выгорания

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

Если сможете остановиться, вы уже победили. Вы сможете действовать. А самое эффективное действие это достижение полной прозрачности в отношениях. Вас ожидает трудная, жестокая дискуссия в лоб со всеми действующими лицами рабочих процессов. Да, что уж там! Вам придется вступить в открытый конфликт, которым будет трудно управлять. Но именно признание того, что проблема существует, лучший способ найти ее решение.

Скажите "НЕТ!" прямо сейчас. Вы не рок-звезда, и не должны всех спасать. Вам нужно взять под контроль эту критическую ситуацию, прежде чем она достигнет точки невозврата.

Я могу заверить вас, что, просто помня об этом первом совете, вы избежите 80% стрессовых ситуаций на своей работе. Избежать ситуации это, безусловно, самый эффективный способ справиться с ней. Но что происходит, если избежать не получилось?

Добро пожаловать в ад

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

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

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

Я не мог остановиться, несмотря на то, сколько людей говорили мне это.

Через месяц-другой в таком состоянии я должен был найти решение, чтобы двигаться быстрее. Тогда я начал писать код небрежно. Я думал, что это спасет меня. Но за все надо было платить. Моя цена просыпаться в 3 часа ночи, чтобы потом работать 12 часов подряд и париться по поводу архитектуры.

Тот день стал для меня официальным днем, когда я встал на путь выгорания.

Ты не машина

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

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

1. Чем больше времени вы тратите на проект, тем менее вы продуктивны

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

Чем больше ты работаешь, тем больше устаешь, чем больше устаешь, тем хуже работаешь, чем хуже работаешь, тем больше стрессуешь, чем больше стрессуешь, тем хуже работаешь.

Если вы сможете избежать этого порочного круга, вы избежите выгорания. Что заставит вас сойти с ума и погрузиться на дно, так это адский ритм работы. Рабочий ритм, созданный для машины. Вы человеческое существо, и вы не можете функционировать с таким ритмом.

2. Коммуникация и прозрачность являются ключевыми факторами

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

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

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

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

3. Спокойствие и дисциплина это не вариант

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

Нет, он обязательно облажается. И вы вместе с ним.

Для разработчиков это одно и то же. Именно в моменты сильного напряжения надо сохранять хладнокровие и дисциплину, чтобы процессы были выполнены качествено. У тех, кто способен на это, огромное преимущество.

Я буду честен, это может не сработать. Тогда придется взглянуть на вещи по-другому. Потребуется принять важное решение. А именно, понять, что этот проект не так уж и важен.

Не так уж и важно

К концу августа 2013 года мне это удалось. Бета-версия продукта, полная костылей, была пригодна для использования. Пожертвовав своим психическим и физическим здоровьем, я уложился в сроки, на которые никогда не должен был соглашаться. И ясно, что моя жизнь подходила к концу.

Пора было идти к клиенту.

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

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

Но никакой эйфории. Мне уже все равно, я просто хочу покончить с этим. Хочу, чтобы клиент что-то сказал, что заставит меня отреагировать, несмотря на мое состояние зомби.

Это отличная работа, теперь вопрос в том, будет ли она использоваться в нашей новой стратегии

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

Что бы вы ни кодили, это не так уж важно

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

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

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

У вас есть выбор

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

Вы склонны забывать об этом, но вы король на рынке незнакомцев.

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

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

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

Ситуация, в которой вы оказались, это ваша вина.

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

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

Никогда так эмоционально не увлекайтесь работой.

Эпилог

Мне потребовался почти год, чтобы выбраться из этой ямы. Оно того не стоило. Всего этого можно было избежать. Я верю в вас и ценю вас. Попробуйте сделать то же самое.

Оригинал текста отсюда, картинки из фильмов "Адреналин" и "Адреналин 2"

Подробнее..

Когда у вас сберовские масштабы. Использование Ab Initio при работе с Hive и GreenPlum

07.07.2020 18:11:11 | Автор: admin
Некоторое время назад перед нами встал вопрос выбора ETL-средства для работы с BigData. Ранее использовавшееся решение Informatica BDM не устраивало нас из-за ограниченной функциональности. Её использование свелось к фреймворку по запуску команд spark-submit. На рынке имелось не так много аналогов, в принципе способных работать с тем объёмом данных, с которым мы имеем дело каждый день. В итоге мы выбрали Ab Initio. В ходе пилотных демонстраций продукт показал очень высокую скорость обработки данных. Информации об Ab Initio на русском языке почти нет, поэтому мы решили рассказать о своём опыте на Хабре.

Ab Initio обладает множеством классических и необычных трансформаций, код которых может быть расширен с помощью собственного языка PDL. Для мелкого бизнеса такой мощный инструмент, вероятно, будет избыточным, и большинство его возможностей могут оказаться дорогими и невостребованными. Но если ваши масштабы приближаются к сберовским, то вам Ab Initio может быть интересен.

Он помогает бизнесу глобально копить знания и развивать экосистему, а разработчику прокачивать свои навыки в ETL, подтягивать знания в shell, предоставляет возможность освоения языка PDL, даёт визуальную картину процессов загрузки, упрощает разработку благодаря обилию функциональных компонентов.

В посте я расскажу о возможностях Ab Initio и приведу сравнительные характеристики по его работе с Hive и GreenPlum.

  • Описание фреймворка MDW и работ по его донастройке под GreenPlum
  • Сравнительные характеристики производительности Ab Initio по работе с Hive и GreenPlum
  • Работа Ab Initio с GreenPlum в режиме Near Real Time


Функционал этого продукта очень широк и требует немало времени на своё изучение. Однако, при должных навыках работы и правильных настройках производительности результаты обработки данных получаются весьма впечатляющие. Использование Ab Initio для разработчика может дать ему интересный опыт. Это новый взгляд на ETL-разработку, гибрид между визуальной средой и разработкой загрузок на скрипто-подобном языке.
Бизнес развивает свои экосистемы и этот инструмент оказывается ему как никогда кстати. С помощью Ab Initio можно копить знания о текущем бизнесе и использовать эти знания для расширения старых и открытия новых бизнесов. Альтернативами Ab Initio можно назвать из визуальных сред разработки Informatica BDM и из невизуальных сред Apache Spark.

Описание Ab Initio


Ab Initio, как и другие ETL-средства, представляет собой набор продуктов.

Ab Initio GDE (Graphical Development Environment) это среда для разработчика, в которой он настраивает трансформации данных и соединяет их потоками данных в виде стрелочек. При этом такой набор трансформаций называется графом:

Входные и выходные соединения функциональных компонентов являются портами и содержат поля, вычисленные внутри преобразований. Несколько графов, соединённых потоками в виде стрелочек в порядке их выполнения называются планом.
Имеется несколько сотен функциональных компонентов, что очень много. Многие из них узкоспециализированные. Возможности классических трансформаций в Ab Initio шире, чем в других ETL-средствах. Например, Join имеет несколько выходов. Помимо результата соединения датасетов можно получить на выходе записи входных датасетов, по ключам которых не удалось соединиться. Также можно получить rejects, errors и лог работы трансформации, который можно в этом же графе прочитать как текстовый файл и обработать другими трансформациями:

Или, например, можно материализовать приёмник данных в виде таблицы и в этом же графе считать из него данные.
Есть оригинальные трансформации. Например, трансформация Scan имеет функционал, как у аналитических функций. Есть трансформации с говорящими названиями: Create Data, Read Excel, Normalize, Sort within Groups, Run Program, Run SQL, Join with DB и др. Графы могут использовать параметры времени выполнения, в том числе возможна передача параметров из операционной системы или в операционную систему. Файлы с готовым набором передаваемых графу параметров называются parameter sets (psets).
Как и полагается, Ab Initio GDE имеет свой репозиторий, именуемый EME (Enterprise Meta Environment). Разработчики имеют возможность работать с локальными версиями кода и делать check in своих разработок в центральный репозиторий.
Имеется возможность во время выполнения или после выполнения графа кликнуть по любому соединяющему трансформации потоку и посмотреть на данные, прошедшие между этими трансформациями:

Также есть возможность кликнуть по любому потоку и посмотреть tracking details в сколько параллелей работала трансформация, сколько строк и байт в какой из параллелей загрузилось:

Есть возможность разбить выполнение графа на фазы и пометить, что одни трансформации нужно выполнять первым делом (в нулевой фазе), следующие в первой фазе, следующие во второй фазе и т.д.
У каждой трансформации можно выбрать так называемый layout (где она будет выполняться): без параллелей или в параллельных потоках, число которых можно задать. При этом временные файлы, которые создаёт Ab Initio при работе трансформаций, можно размещать как в файловой системе сервера, так и в HDFS.
В каждой трансформации на базе шаблона по умолчанию можно создать свой скрипт на языке PDL, который немного напоминает shell.
С помощью языка PDL вы можете расширять функционал трансформаций и, в частности, вы можете динамически (во время выполнения) генерировать произвольные фрагменты кода в зависимости от параметров времени выполнения.
Также в Ab Initio хорошо развита интеграция с ОС через shell. Конкретно в Сбербанке используется linux ksh. Можно обмениваться с shell переменными и использовать их в качестве параметров графов. Можно из shell вызывать выполнение графов Ab Initio и администрировать Ab Initio.
Помимо Ab Initio GDE в поставку входит много других продуктов. Есть своя Co>Operation System с претензией называться операционной системой. Есть Control>Center, в котором можно ставить на расписание и мониторить потоки загрузки. Есть продукты для осуществления разработки на более примитивном уровне, чем позволяет Ab Initio GDE.

Описание фреймворка MDW и работ по его донастройке под GreenPlum


Вместе со своими продуктами вендор поставляет продукт MDW (Metadata Driven Warehouse), который представляет собой конфигуратор графов, предназначенный для помощи в типичных задачах по наполнению хранилищ данных или data vaults.
Он содержит пользовательские (специфичные для проекта) парсеры метаданных и готовые генераторы кода из коробки.

На входе MDW получает модель данных, конфигурационный файл по настройке соединения с базой данных (Oracle, Teradata или Hive) и некоторые другие настройки. Специфическая для проекта часть, например, разворачивает модель в базе данных. Коробочная часть продукта генерирует графы и настроечные файлы к ним по загрузке данных в таблицы модели. При этом создаются графы (и psets) для нескольких режимов инициализирующей и инкрементальной работы по обновлению сущностей.
В случаях Hive и RDBMS генерируются различающиеся графы по инициализирующему и инкрементальному обновлению данных.
В случае Hive поступившие данные дельты соединяется посредством Ab Initio Join с данными, которые были в таблице до обновления. Загрузчики данных в MDW (как в Hive, так и в RDBMS) не только вставляют новые данные из дельты, но и закрывают периоды актуальности данных, по первичным ключам которых поступила дельта. Кроме того, приходится переписать заново неизменившуюся часть данных. Но так приходится делать, поскольку в Hive нет операций delete или update.

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

Поступившая дельта загружается в промежуточную таблицу в базу данных. После этого происходит соединение дельты с данными, которые были в таблице до обновления. И делается это силами SQL посредством сгенерированного SQL-запроса. Далее с помощью SQL-команд delete+insert в целевую таблицу происходит вставка новых данных из дельты и закрытие периодов актуальности данных, по первичным ключам которых поступила дельта. Неизменившиеся данные переписывать нет нужды.
Таким образом, мы пришли к выводу, что в случае Hive MDW должен пойти на переписывание всей таблицы, потому что Hive не имеет функции обновления. И ничего лучше полного переписывания данных при обновлении не придумано. В случае же RDBMS, наоборот, создатели продукта сочли нужным доверить соединение и обновление таблиц использованию SQL.
Для проекта в Сбербанке мы создали новую многократно используемую реализацию загрузчика базы данных для GreenPlum. Сделано это было на основе версии, которую MDW генерирует для Teradata. Именно Teradata, а не Oracle подошла для этого лучше и ближе всего, т.к. тоже является MPP-системой. Способы работы, а также синтаксис Teradata и GreenPlum оказались близки.
Примеры критичных для MDW различий между разными RDBMS таковы. В GreenPlum в отличии от Teradata при создании таблиц нужно писать клаузу
distributed by

В Teradata пишут
delete <table> all

, а в GreеnPlum пишут
delete from <table>

В Oracle в целях оптимизации пишут
delete from t where rowid in (<соединение t с дельтой>)

, а в Teradata и GreenPlum пишут
delete from t where exists (select * from delta where delta.pk=t.pk)

Ещё отметим, что для работы Ab Initio с GreenPlum потребовалось установить клиент GreenPlum на все ноды кластера Ab Initio. Это потому, что мы подключились к GreenPlum одновременно со всех узлов нашего кластера. А для того, чтобы чтение из GreenPlum было параллельным и каждый параллельный поток Ab Initio читал свою порцию данных из GreenPlum, пришлось в секцию where SQL-запросов поместить понимаемую Ab Initio конструкцию
where ABLOCAL()

и определить значение этой конструкции, указав читающей из БД трансформации параметр
ablocal_expr=string_concat("mod(t.", string_filter_out("{$TABLE_KEY}","{}"), ",", (decimal(3))(number_of_partitions()),")=", (decimal(3))(this_partition()))

, которая компилируется в что-то типа
mod(sk,10)=3

, т.е. приходится подсказывать GreenPlum явный фильтр для каждой партиции. Для других баз данных (Teradata, Oracle) Ab Initio может выполнить это распараллеливание автоматически.

Сравнительные характеристики производительности Ab Initio по работе с Hive и GreenPlum


В Сбербанке был проведён эксперимент по сравнению производительности сгенерированных MDW графов применительно к Hive и применительно к GreenPlum. В рамках эксперимента в случае Hive имелось 5 нод на том же кластере, что и Ab Initio, а в случае GreenPlum имелось 4 ноды на отдельном кластере. Т.е. Hive имел некоторое преимущество над GreenPlum по железу.
Было рассмотрено две пары графов, выполняющих одну и ту же задачу обновления данных в Hive и в GreenPlum. При этом запускали графы, сгенерированные конфигуратором MDW:

  • инициализирующая загрузка + инкрементальная загрузка случайно сгенерированных данных в таблицу Hive
  • инициализирующая загрузка + инкрементальная загрузка случайно сгенерированных данных в такую же таблицу GreenPlum

В обоих случаях (Hive и GreenPlum) запускали загрузки в 10 параллельных потоков на одном и том же кластере Ab Initio. Промежуточные данные для расчётов Ab Initio сохранял в HDFS (в терминах Ab Initio был использован MFS layout using HDFS). Одна строка случайно сгенерированных данных занимала в обоих случаях по 200 байт.
Результат получился такой:

Hive:
Инициализирующая загрузка в Hive
Вставлено строк 6 000 000 60 000 000 600 000 000
Продолжительность инициализирующей
загрузки в секундах
41 203 1 601
Инкрементальная загрузка в Hive
Количество строк, имевшихся в
целевой таблице на начало эксперимента
6 000 000 60 000 000 600 000 000
Количество строк дельты, применённых к
целевой таблице в ходе эксперимента
6 000 000 6 000 000 6 000 000
Продолжительность инкрементальной
загрузки в секундах
88 299 2 541

GreenPlum:
Инициализирующая загрузка в GreenPlum
Вставлено строк 6 000 000 60 000 000 600 000 000
Продолжительность инициализирующей
загрузки в секундах
72 360 3 631
Инкрементальная загрузка в GreenPlum
Количество строк, имевшихся в
целевой таблице на начало эксперимента
6 000 000 60 000 000 600 000 000
Количество строк дельты, применённых к
целевой таблице в ходе эксперимента
6 000 000 6 000 000 6 000 000
Продолжительность инкрементальной
загрузки в секундах
159 199 321

Видим, что скорость инициализирующей загрузки как в Hive, так и в GreenPlum линейно зависит от объёма данных и по причинам лучшего железа она несколько быстрее для Hive, чем для GreenPlum.
Инкрементальная загрузка в Hive также линейно зависит от объёма имеющихся в целевой таблице ранее загруженных данных и проходит достаточно медленно с ростом объёма. Вызвано это необходимостью перезаписывать целевую таблицу полностью. Это означает, что применение маленьких изменений к огромным таблицам не очень хороший вариант использования для Hive.
Инкрементальная же загрузка в GreenPlum слабо зависит от объёма имеющихся в целевой таблице ранее загруженных данных и проходит достаточно быстро. Получилось это благодаря SQL Joins и архитектуре GreenPlum, допускающей операцию delete.

Итак, GreenPlum вливает дельту методом delete+insert, а в Hive нету операций delete либо update, поэтому весь массив данных при инкрементальном обновлении были вынуждены переписывать целиком. Наиболее показательно сравнение выделенных жирным ячеек, так как оно соответствует наиболее частому варианту эксплуатации ресурсоёмких загрузок. Видим, что GreenPlum выиграл у Hive в этом тесте в 8 раз.

Работа Ab Initio с GreenPlum в режиме Near Real Time


В этом эксперименте проверим возможность Ab Initio производить обновление таблицы GreenPlum случайно формируемыми порциями данных в режиме, близком к реальному времени. Рассмотрим таблицу GreenPlum dev42_1_db_usl.TESTING_SUBJ_org_finval, с которой будет вестись работа.
Будем использовать три графа Ab Initio по работе с ней:

1) Граф Create_test_data.mp создаёт в 10 параллельных потоков файлы с данными в HDFS на 6 000 000 строк. Данные случайные, структура их организована для вставки в нашу таблицу



2) Граф mdw_load.day_one.current.dev42_1_db_usl_testing_subj_org_finval.pset сгенерированный MDW граф по инициализирующей вставке данных в нашу таблицу в 10 параллельных потоков (используются тестовые данные, сгенерированные графом (1))


3) Граф mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset сгенерированный MDW граф по инкрементальному обновлению нашей таблицы в 10 параллельных потоков с использованием порции свежих поступивших данных (дельты), сгенерированных графом (1)


Выполним нижеприведённый сценарий в режиме NRT:

  • сгенерировать 6 000 000 тестовых строк
  • произвести инициализирующую загрузку вставить 6 000 000 тестовых строк в пустую таблицу
  • повторить 5 раз инкрементальную загрузку
    • сгенерировать 6 000 000 тестовых строк
    • произвести инкрементальную вставку 6 000 000 тестовых строк в таблицу (при этом старым данным проставляется время истечения актуальности valid_to_ts и вставляются более свежие данные с тем же первичным ключом)

Такой сценарий эмулирует режим реальной работы некой бизнес-системы в режиме реального времени появляется достаточно объёмная порция новых данных и тут же вливается в GreenPlum.

Теперь посмотрим лог работы сценария:
Start Create_test_data.input.pset at 2020-06-04 11:49:11
Finish Create_test_data.input.pset at 2020-06-04 11:49:37
Start mdw_load.day_one.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 11:49:37
Finish mdw_load.day_one.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 11:50:42
Start Create_test_data.input.pset at 2020-06-04 11:50:42
Finish Create_test_data.input.pset at 2020-06-04 11:51:06
Start mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 11:51:06
Finish mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 11:53:41
Start Create_test_data.input.pset at 2020-06-04 11:53:41
Finish Create_test_data.input.pset at 2020-06-04 11:54:04
Start mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 11:54:04
Finish mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 11:56:51
Start Create_test_data.input.pset at 2020-06-04 11:56:51
Finish Create_test_data.input.pset at 2020-06-04 11:57:14
Start mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 11:57:14
Finish mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 11:59:55
Start Create_test_data.input.pset at 2020-06-04 11:59:55
Finish Create_test_data.input.pset at 2020-06-04 12:00:23
Start mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 12:00:23
Finish mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 12:03:23
Start Create_test_data.input.pset at 2020-06-04 12:03:23
Finish Create_test_data.input.pset at 2020-06-04 12:03:49
Start mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 12:03:49
Finish mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 12:06:46


Получается такая картина:
Graph Start time Finish time Length
Create_test_data.input.pset 04.06.2020 11:49:11 04.06.2020 11:49:37 00:00:26
mdw_load.day_one.current.
dev42_1_db_usl_testing_subj_org_finval.pset
04.06.2020 11:49:37 04.06.2020 11:50:42 00:01:05
Create_test_data.input.pset 04.06.2020 11:50:42 04.06.2020 11:51:06 00:00:24
mdw_load.regular.current.
dev42_1_db_usl_testing_subj_org_finval.pset
04.06.2020 11:51:06 04.06.2020 11:53:41 00:02:35
Create_test_data.input.pset 04.06.2020 11:53:41 04.06.2020 11:54:04 00:00:23
mdw_load.regular.current.
dev42_1_db_usl_testing_subj_org_finval.pset
04.06.2020 11:54:04 04.06.2020 11:56:51 00:02:47
Create_test_data.input.pset 04.06.2020 11:56:51 04.06.2020 11:57:14 00:00:23
mdw_load.regular.current.
dev42_1_db_usl_testing_subj_org_finval.pset
04.06.2020 11:57:14 04.06.2020 11:59:55 00:02:41
Create_test_data.input.pset 04.06.2020 11:59:55 04.06.2020 12:00:23 00:00:28
mdw_load.regular.current.
dev42_1_db_usl_testing_subj_org_finval.pset
04.06.2020 12:00:23 04.06.2020 12:03:23 00:03:00
Create_test_data.input.pset 04.06.2020 12:03:23 04.06.2020 12:03:49 00:00:26
mdw_load.regular.current.
dev42_1_db_usl_testing_subj_org_finval.pset
04.06.2020 12:03:49 04.06.2020 12:06:46 00:02:57

Видим, что 6 000 000 строк инкремента обрабатываются за 3 минуты, что достаточно быстро.
Данные в целевой таблице получились распределёнными следующим образом:
select valid_from_ts, valid_to_ts, count(1), min(sk), max(sk) from dev42_1_db_usl.TESTING_SUBJ_org_finval group by valid_from_ts, valid_to_ts order by 1,2;


Можно разглядеть соответствие вставленных данных моментам запуска графов.
Значит можно запускать в Ab Initio инкрементальную загрузку данных в GreenPlum с очень высокой частотой и наблюдать высокую скорость вставки этих данных в GreenPlum. Конечно, раз в секунду запускаться не получится, так как Ab Initio, как и любое ETL-средство, при запуске требует времени на раскачку.

Заключение


Сейчас Ab Initio используется в Сбербанке для построения Единого семантического слоя данных (ЕСС). Этот проект подразумевает построение единой версии состояния различных банковских бизнес-сущностей. Информация приходит из различных источников, реплики которых готовятся на Hadoop. Исходя из потребностей бизнеса, готовится модель данных и описываются трансформации данных. Ab Initio загружает информацию в ЕСС и загруженные данные не только представляют интерес для бизнеса сами по себе, но и служат источником для построения витрин данных. При этом функционал продукта позволяет использовать в качестве приёмника различные системы (Hive, Greenplum, Teradata, Oracle), что даёт возможность без особых усилий подготавливать данные для бизнеса в различных требуемых ему форматах.

Возможности Ab Initio широки, например, прилагающийся фреймворк MDW даёт возможность строить техническую и бизнес-историчность данных из коробки. Для разработчиков Ab Initio даёт возможность не изобретать велосипед, а пользоваться множеством имеющихся функциональных компонентов, по сути являющихся библиотеками, нужными при работе с данными.

Автор эксперт профессионального сообщества Сбербанка SberProfi DWH/BigData. Профессиональное сообщество SberProfi DWH/BigData отвечает за развитие компетенций в таких направлениях, как экосистема Hadoop, Teradata, Oracle DB, GreenPlum, а также BI инструментах Qlik, SAP BO, Tableau и др.
Подробнее..

10 приёмов работы с Oracle

01.10.2020 10:13:11 | Автор: admin
В Сбере есть несколько практик Oracle, которые могут оказаться вам полезны. Думаю, часть вам знакома, но мы используем для загрузки не только ETL-средства, но и хранимые процедуры Oracle. На Oracle PL/SQL реализованы наиболее сложные алгоритмы загрузки данных в хранилища, где требуется прочувствовать каждый байт.

  • Автоматическое журналирование компиляций
  • Как быть, если хочется сделать вьюшку с параметрами
  • Использование динамической статистики в запросах
  • Как сохранить план запроса при вставке данных через database link
  • Запуск процедур в параллельных сессиях
  • Протягивание остатков
  • Объединение нескольких историй в одну
  • Нормалайзер
  • Визуализация в формате SVG
  • Приложение поиска по метаданным Oracle


Автоматическое журналирование компиляций


На некоторых базах данных Oracle в Сбере стоит триггер на компиляцию, который запоминает, кто, когда и что менял в коде серверных объектов. Тем самым из таблицы журнала компиляций можно установить автора изменений. Также автоматически реализуется система контроля версий. Во всяком случае, если программист забыл сдать изменения в Git, то этот механизм его подстрахует. Опишем пример реализации такой системы автоматического журналирования компиляций. Один из упрощённых вариантов триггера на компиляцию, пишущего в журнал в виде таблицы ddl_changes_log, выглядит так:

create table DDL_CHANGES_LOG(  id               INTEGER,  change_date      DATE,  sid              VARCHAR2(100),  schemaname       VARCHAR2(30),  machine          VARCHAR2(100),  program          VARCHAR2(100),  osuser           VARCHAR2(100),  obj_owner        VARCHAR2(30),  obj_type         VARCHAR2(30),  obj_name         VARCHAR2(30),  previous_version CLOB,  changes_script   CLOB);create or replace trigger trig_audit_ddl_trg  before ddl on databasedeclare  v_sysdate              date;  v_valid                number;  v_previous_obj_owner   varchar2(30) := '';  v_previous_obj_type    varchar2(30) := '';  v_previous_obj_name    varchar2(30) := '';  v_previous_change_date date;  v_lob_loc_old          clob := '';  v_lob_loc_new          clob := '';  v_n                    number;  v_sql_text             ora_name_list_t;  v_sid                  varchar2(100) := '';  v_schemaname           varchar2(30) := '';  v_machine              varchar2(100) := '';  v_program              varchar2(100) := '';  v_osuser               varchar2(100) := '';begin  v_sysdate := sysdate;  -- find whether compiled object already presents and is valid  select count(*)    into v_valid    from sys.dba_objects   where owner = ora_dict_obj_owner     and object_type = ora_dict_obj_type     and object_name = ora_dict_obj_name     and status = 'VALID'     and owner not in ('SYS', 'SPOT', 'WMSYS', 'XDB', 'SYSTEM')     and object_type in ('TRIGGER', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'VIEW');  -- find information about previous compiled object  select max(obj_owner) keep(dense_rank last order by id),         max(obj_type) keep(dense_rank last order by id),         max(obj_name) keep(dense_rank last order by id),         max(change_date) keep(dense_rank last order by id)    into v_previous_obj_owner, v_previous_obj_type, v_previous_obj_name, v_previous_change_date    from ddl_changes_log;  -- if compile valid object or compile invalid package body broken by previous compilation of package then log it  if (v_valid = 1 or v_previous_obj_owner = ora_dict_obj_owner and     (v_previous_obj_type = 'PACKAGE' and ora_dict_obj_type = 'PACKAGE BODY' or     v_previous_obj_type = 'PACKAGE BODY' and ora_dict_obj_type = 'PACKAGE') and     v_previous_obj_name = ora_dict_obj_name and     v_sysdate - v_previous_change_date <= 1 / 24 / 60 / 2) and     ora_sysevent in ('CREATE', 'ALTER') then    -- store previous version of object (before compilation) from dba_source or dba_views in v_lob_loc_old    if ora_dict_obj_type <> 'VIEW' then      for z in (select substr(text, 1, length(text) - 1) || chr(13) || chr(10) as text                  from sys.dba_source                 where owner = ora_dict_obj_owner                   and type = ora_dict_obj_type                   and name = ora_dict_obj_name                 order by line) loop        v_lob_loc_old := v_lob_loc_old || z.text;      end loop;    else      select sys.dbms_metadata_util.long2clob(v.textlength, 'SYS.VIEW$', 'TEXT', v.rowid) into v_lob_loc_old        from sys."_CURRENT_EDITION_OBJ" o, sys.view$ v, sys.user$ u       where o.obj# = v.obj#         and o.owner# = u.user#         and u.name = ora_dict_obj_owner         and o.name = ora_dict_obj_name;    end if;    -- store new version of object (after compilation) from v_sql_text in v_lob_loc_new    v_n := ora_sql_txt(v_sql_text);    for i in 1 .. v_n loop      v_lob_loc_new := v_lob_loc_new || replace(v_sql_text(i), chr(10), chr(13) || chr(10));    end loop;    -- find information about session that changed this object    select max(to_char(sid)), max(schemaname), max(machine), max(program), max(osuser)      into v_sid, v_schemaname, v_machine, v_program, v_osuser      from v$session     where audsid = userenv('sessionid');    -- store changes in ddl_changes_log    insert into ddl_changes_log      (id, change_date, sid, schemaname, machine, program, osuser,       obj_owner, obj_type, obj_name, previous_version, changes_script)    values      (seq_ddl_changes_log.nextval, v_sysdate, v_sid, v_schemaname, v_machine, v_program, v_osuser,       ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name, v_lob_loc_old, v_lob_loc_new);  end if;exception  when others then    null;end;

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

Как быть, если хочется сделать вьюшку с параметрами


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

create table DIVISION_SALES(  division_id INTEGER,  dt          DATE,  sales_amt   NUMBER);

Такой запрос сравнивает продажи по подразделениям за два дня. В данном случае, 30.04.2020 и 11.09.2020.

select t1.division_id,       t1.dt          dt1,       t2.dt          dt2,       t1.sales_amt   sales_amt1,       t2.sales_amt   sales_amt2  from (select dt, division_id, sales_amt          from division_sales         where dt = to_date('30.04.2020', 'dd.mm.yyyy')) t1,       (select dt, division_id, sales_amt          from division_sales         where dt = to_date('11.09.2020', 'dd.mm.yyyy')) t2 where t1.division_id = t2.division_id;

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

create or replace view vw_division_sales_report(in_dt1 date, in_dt2 date) asselect t1.division_id,       t1.dt          dt1,       t2.dt          dt2,       t1.sales_amt   sales_amt1,       t2.sales_amt   sales_amt2  from (select dt, division_id, sales_amt          from division_sales         where dt = in_dt1) t1,       (select dt, division_id, sales_amt          from division_sales         where dt = in_dt2) t2 where t1.division_id = t2.division_id;

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

create type t_division_sales_report as object(  division_id INTEGER,  dt1         DATE,  dt2         DATE,  sales_amt1  NUMBER,  sales_amt2  NUMBER);

И создадим тип под таблицу из таких строк.

create type t_division_sales_report_table as table of t_division_sales_report;

Вместо вьюшки напишем pipelined функцию с входными параметрами-датами.

create or replace function func_division_sales(in_dt1 date, in_dt2 date)  return t_division_sales_report_table  pipelined asbegin  for z in (select t1.division_id,                   t1.dt          dt1,                   t2.dt          dt2,                   t1.sales_amt   sales_amt1,                   t2.sales_amt   sales_amt2              from (select dt, division_id, sales_amt                      from division_sales                     where dt = in_dt1) t1,                   (select dt, division_id, sales_amt                      from division_sales                     where dt = in_dt2) t2             where t1.division_id = t2.division_id) loop    pipe row(t_division_sales_report(z.division_id,                                     z.dt1,                                     z.dt2,                                     z.sales_amt1,                                     z.sales_amt2));  end loop;end;

Обращаться к ней можно так:

select *  from table(func_division_sales(to_date('30.04.2020', 'dd.mm.yyyy'),                                 to_date('11.09.2020', 'dd.mm.yyyy')));

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

create or replace view complex_view as select field1, ...   from (select field1, ...           from (select field1, ... from deep_table), table1          where ...),        table2  where ...;

И запрос из вьюшки с фиксированным значением field1 может иметь плохой план выполнения.

select field1, ... from complex_view where field1 = 'myvalue';

Т.е. вместо того, чтобы сначала отфильтровать deep_table по условию field1 = 'myvalue', запрос может сначала соединить все таблицы, обработав излишне большой объём данных, а потом уже фильтровать результат по условию field1 = 'myvalue'. Такой сложности можно избежать, если сделать вместо вьюшки pipelined функцию с параметром, значение которого присваивается полю field1.

Использование динамической статистики в запросах


Бывает, что один и тот же запрос в БД Oracle обрабатывает всякий раз различный объём данных в использующихся в нём таблицах и подзапросах. Как заставить оптимизатор всякий раз понимать, какой из способов соединения таблиц на этот раз лучше и какие индексы использовать? Рассмотрим, например, запрос, который соединяет порцию изменившихся с последней загрузки остатков по счетам со справочником счетов. Порция изменившихся остатков по счетам сильно меняется от загрузки к загрузке, составляя то сотни строк, то миллионы строк. В зависимости от размера этой порции требуется соединять изменившиеся остатки со счетами то способом /*+ use_nl*/, то способом /*+ use_hash*/. Всякий раз повторно собирать статистику неудобно, особенно, если от загрузки к загрузке изменяется количество строк не в соединяемой таблице, а в соединяемом подзапросе. На помощь тут может прийти хинт /*+ dynamic_sampling()*/. Покажем, как он влияет, на примере запроса. Пусть таблица change_balances содержит изменения остатков, а accounts справочник счетов. Соединяем эти таблицы по полям account_id, имеющимся в каждой из таблиц. В начале эксперимента запишем в эти таблицы побольше строк и не будем менять их содержимое.
Сначала возьмём 10% изменений остатков в таблице change_balances и посмотрим, какой план будет с использованием dynamic_sampling:

SQL> EXPLAIN PLAN  2   SET statement_id = 'test1'  3   INTO plan_table  4  FOR  with c as  5   (select /*+ dynamic_sampling(change_balances 2)*/  6     account_id, balance_amount  7      from change_balances  8     where mod(account_id, 10) = 0)  9  select a.account_id, a.account_number, c.balance_amount 10    from c, accounts a 11   where c.account_id = a.account_id;Explained.SQL>SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY);Plan hash value: 874320301----------------------------------------------------------------------------------------------| Id  | Operation          | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |                 |  9951K|   493M|       |   140K  (1)| 00:28:10 ||*  1 |  HASH JOIN         |                 |  9951K|   493M|  3240K|   140K  (1)| 00:28:10 ||*  2 |   TABLE ACCESS FULL| CHANGE_BALANCES |   100K|  2057K|       |  7172   (1)| 00:01:27 ||   3 |   TABLE ACCESS FULL| ACCOUNTS        |    10M|   295M|       |   113K  (1)| 00:22:37 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("ACCOUNT_ID"="A"."ACCOUNT_ID")   2 - filter(MOD("ACCOUNT_ID",10)=0)Note-----   - dynamic sampling used for this statement (level=2)20 rows selected.

Итак, видим, что предлагается пройти таблицы change_balances и accounts с помощью full scan и соединить их посредством hash join.
Теперь резко уменьшим выборку из change_balances. Возьмём 0.1% изменений остатков и посмотрим, какой план будет с использованием dynamic_sampling:

SQL> EXPLAIN PLAN  2   SET statement_id = 'test2'  3   INTO plan_table  4  FOR  with c as  5   (select /*+ dynamic_sampling(change_balances 2)*/  6     account_id, balance_amount  7      from change_balances  8     where mod(account_id, 1000) = 0)  9  select a.account_id, a.account_number, c.balance_amount 10    from c, accounts a 11   where c.account_id = a.account_id;Explained.SQL>SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY);Plan hash value: 2360715730-------------------------------------------------------------------------------------------------------| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |                        | 73714 |  3743K| 16452   (1)| 00:03:18 ||   1 |  NESTED LOOPS                |                        |       |       |            |          ||   2 |   NESTED LOOPS               |                        | 73714 |  3743K| 16452   (1)| 00:03:18 ||*  3 |    TABLE ACCESS FULL         | CHANGE_BALANCES        |   743 | 15603 |  7172   (1)| 00:01:27 ||*  4 |    INDEX RANGE SCAN          | IX_ACCOUNTS_ACCOUNT_ID |   104 |       |     2   (0)| 00:00:01 ||   5 |   TABLE ACCESS BY INDEX ROWID| ACCOUNTS               |    99 |  3069 |   106   (0)| 00:00:02 |-------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - filter(MOD("ACCOUNT_ID",1000)=0)   4 - access("ACCOUNT_ID"="A"."ACCOUNT_ID")Note-----   - dynamic sampling used for this statement (level=2)22 rows selected.

На этот раз к таблице change_balances таблица accounts присоединяется посредством nested loops и используется индекс для чтения строк из accounts.
Если же хинт dynamic_sampling убрать, то во втором случае план останется такой же, как в первом случае, и это не оптимально.
Подробности о хинте dynamic_sampling и возможных значениях его числового аргумента можно найти в документации.

Как сохранить план запроса при вставке данных через database link


Решаем такую задачу. На сервере-источнике данных имеются таблицы, которые нужно соединить и загрузить в хранилище данных. Допустим, на сервере-источнике написана вьюшка, которая содержит в себе всю нужную ETL-логику преобразований. Вьюшка написана оптимально, в ней указаны хинты оптимизатору, подсказывающие, как соединять таблицы и какие индексы использовать. На стороне сервера хранилища данных нужно сделать несложную вещь вставить данные из вьюшки в целевую таблицу. И тут могут возникнуть сложности. Если вставку в целевую таблицу осуществить командой вида

insert into dwh_table  (field1, field2)  select field1, field2 from vw_for_dwh_table@xe_link;

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

SQL> EXPLAIN PLAN  2   SET statement_id = 'test'  3   INTO plan_table  4  FOR  insert into dwh_table  5    (field1, field2)  6    select field1, field2 from vw_for_dwh_table@xe_link;Explained.SQL>SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY);Plan hash value: 1788691278-------------------------------------------------------------------------------------------------------------| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|-------------------------------------------------------------------------------------------------------------|   0 | INSERT STATEMENT         |                  |     1 |  2015 |     2   (0)| 00:00:01 |        |      ||   1 |  LOAD TABLE CONVENTIONAL | DWH_TABLE        |       |       |            |          |        |      ||   2 |   REMOTE                 | VW_FOR_DWH_TABLE |     1 |  2015 |     2   (0)| 00:00:01 | XE_LI~ | R->S |-------------------------------------------------------------------------------------------------------------Remote SQL Information (identified by operation id):----------------------------------------------------   2 - SELECT /*+ OPAQUE_TRANSFORM */ "FIELD1","FIELD2" FROM "VW_FOR_DWH_TABLE" "VW_FOR_DWH_TABLE"       (accessing 'XE_LINK' )16 rows selected.

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

declare  cursor cr is    select field1, field2 from vw_for_dwh_table@xe_link;  cr_row cr%rowtype;begin  open cr;  loop    fetch cr      into cr_row;    insert into dwh_table      (field1, field2)    values      (cr_row.field1, cr_row.field2);    exit when cr%notfound;  end loop;  close cr;end;

Запрос из курсора

select field1, field2 from vw_for_dwh_table@xe_link;

в отличие от вставки

insert into dwh_table  (field1, field2)  select field1, field2 from vw_for_dwh_table@xe_link;

сохранит план запроса, заложенный во вьюшку на сервере-источнике.

Запуск процедур в параллельных сессиях


Часто стоит задача запустить из некоторой родительской процедуры несколько параллельных расчётов и, дождавшись завершения каждого из них, продолжить выполнение родительской процедуры. Это может быть полезно при параллельных вычислениях, если ресурсы сервера позволяют это. Есть много способов сделать это.
Опишем очень простой вариант реализации такого механизма. Параллельные процедуры будем выполнять в параллельных одноразовых джобах, родительская же процедура в цикле будет ожидать завершения всех этих джобов.
Создадим таблицы с метаданными для этого механизма. Для начала сделаем таблицу с группами параллельно запускаемых процедур:

create table PARALLEL_PROC_GROUP_LIST(  group_id   INTEGER,  group_name VARCHAR2(4000));comment on column PARALLEL_PROC_GROUP_LIST.group_id  is 'Номер группы параллельно запускаемых процедур';comment on column PARALLEL_PROC_GROUP_LIST.group_name  is 'Название группы параллельно запускаемых процедур';

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

create table PARALLEL_PROC_LIST(  group_id    INTEGER,  proc_script VARCHAR2(4000),  is_active   CHAR(1) default 'Y');comment on column PARALLEL_PROC_LIST.group_id  is 'Номер группы параллельно запускаемых процедур';comment on column PARALLEL_PROC_LIST.proc_script  is 'Pl/sql блок с кодом процедуры';comment on column PARALLEL_PROC_LIST.is_active  is 'Y - active, N - inactive. С помощью этого поля можно временно отключать процедуру из группы';

И сделаем таблицу журнала, где будем собирать лог того, какая процедура когда в каком джобе запускалась:

create table PARALLEL_PROC_LOG(  run_id      INTEGER,  group_id    INTEGER,  proc_script VARCHAR2(4000),  job_id      INTEGER,  start_time  DATE,  end_time    DATE);comment on column PARALLEL_PROC_LOG.run_id  is 'Номер запуска процедуры run_in_parallel';comment on column PARALLEL_PROC_LOG.group_id  is 'Номер группы параллельно запускаемых процедур';comment on column PARALLEL_PROC_LOG.proc_script  is 'Pl/sql блок с кодом процедуры';comment on column PARALLEL_PROC_LOG.job_id  is 'Job_id джоба, в котором была запущена эта процедура';comment on column PARALLEL_PROC_LOG.start_time  is 'Время начала работы';comment on column PARALLEL_PROC_LOG.end_time  is 'Время окончания работы';create sequence Seq_Parallel_Proc_Log;

Теперь приведём код процедуры по запуску параллельных потоков:

create or replace procedure run_in_parallel(in_group_id integer) as  -- Процедура по параллельному запуску процедур из таблицы parallel_proc_list.  -- Параметр - номер группы из parallel_proc_list  v_run_id             integer;  v_job_id             integer;  v_job_id_list        varchar2(32767);  v_job_id_list_ext    varchar2(32767);  v_running_jobs_count integer;begin  select seq_parallel_proc_log.nextval into v_run_id from dual;  -- submit jobs with the same parallel_proc_list.in_group_id  -- store seperated with ',' JOB_IDs in v_job_id_list  v_job_id_list     := null;  v_job_id_list_ext := null;  for z in (select pt.proc_script              from parallel_proc_list pt             where pt.group_id = in_group_id               and pt.is_active = 'Y') loop    dbms_job.submit(v_job_id, z.proc_script);    insert into parallel_proc_log      (run_id, group_id, proc_script, job_id, start_time, end_time)    values      (v_run_id, in_group_id, z.proc_script, v_job_id, sysdate, null);    v_job_id_list     := v_job_id_list || ',' || to_char(v_job_id);    v_job_id_list_ext := v_job_id_list_ext || ' union all select ' ||                         to_char(v_job_id) || ' job_id from dual';  end loop;  commit;  v_job_id_list     := substr(v_job_id_list, 2);  v_job_id_list_ext := substr(v_job_id_list_ext, 12);  -- loop while not all jobs finished  loop    -- set parallel_proc_log.end_time for finished jobs    execute immediate 'update parallel_proc_log set end_time = sysdate where job_id in (' ||                      v_job_id_list_ext ||                      ' minus select job from user_jobs where job in (' ||                      v_job_id_list ||                      ') minus select job_id from parallel_proc_log where job_id in (' ||                      v_job_id_list || ') and end_time is not null)';    commit;    -- check whether all jobs finished    execute immediate 'select count(1) from user_jobs where job in (' ||                      v_job_id_list || ')'      into v_running_jobs_count;    -- if all jobs finished then exit    exit when v_running_jobs_count = 0;    -- sleep a little    sys.dbms_lock.sleep(0.1);  end loop;end;

Проверим, как работает процедура run_in_parallel. Создадим тестовую процедуру, которую будем вызывать в параллельных сессиях.

create or replace procedure sleep(in_seconds integer) asbegin  sys.Dbms_Lock.Sleep(in_seconds);end;

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

insert into PARALLEL_PROC_GROUP_LIST(group_id, group_name) values(1, 'Тестовая группа');insert into PARALLEL_PROC_LIST(group_id, proc_script, is_active) values(1, 'begin sleep(5); end;', 'Y');insert into PARALLEL_PROC_LIST(group_id, proc_script, is_active) values(1, 'begin sleep(10); end;', 'Y');

Запустим группу параллельных процедур.

begin  run_in_parallel(1);end;

По завершении посмотрим лог.

select * from PARALLEL_PROC_LOG;

RUN_ID GROUP_ID PROC_SCRIPT JOB_ID START_TIME END_TIME
1 1 begin sleep(5); end; 1 11.09.2020 15:00:51 11.09.2020 15:00:56
1 1 begin sleep(10); end; 2 11.09.2020 15:00:51 11.09.2020 15:01:01

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

Протягивание остатков


Опишем вариант решения достаточно типовой банковской задачи по протягиванию остатков. Допустим, имеется таблица фактов изменения остатков по счетам. Требуется на каждый день календаря указать актуальный остаток по счёту (последний за день). Такая информация часто бывает нужна в хранилищах данных. Если в какой-то день не было движений по счёту, то нужно повторить последний известный остаток. Если объёмы данных и вычислительные мощности сервера позволяют, то можно решить такую задачу с помощью SQL-запроса, даже не прибегая к PL/SQL. Поможет нам в этом функция last_value(* ignore nulls) over(partition by * order by *), которая протянет последний известный остаток на последующие даты, в которых не было изменений.
Создадим таблицу и заполним её тестовыми данными.

create table ACCOUNT_BALANCE(  dt           DATE,  account_id   INTEGER,  balance_amt  NUMBER,  turnover_amt NUMBER);comment on column ACCOUNT_BALANCE.dt  is 'Дата и время остатка по счёту';comment on column ACCOUNT_BALANCE.account_id  is 'Номер счёта';comment on column ACCOUNT_BALANCE.balance_amt  is 'Остаток по счёту';comment on column ACCOUNT_BALANCE.turnover_amt  is 'Оборот по счёту';insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('01.01.2020 00:00:00','dd.mm.yyyy hh24.mi.ss'), 1, 23, 23);insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('05.01.2020 01:00:00','dd.mm.yyyy hh24.mi.ss'), 1, 45, 22);insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('05.01.2020 20:00:00','dd.mm.yyyy hh24.mi.ss'), 1, 44, -1);insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('05.01.2020 00:00:00','dd.mm.yyyy hh24.mi.ss'), 2, 67, 67);insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('05.01.2020 20:00:00','dd.mm.yyyy hh24.mi.ss'), 2, 77, 10);insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('07.01.2020 00:00:00','dd.mm.yyyy hh24.mi.ss'), 2, 72, -5);

Нижеприведённый запрос решает нашу задачу. Подзапрос cld содержит календарь дат, в подзапросе ab группируем остатки за каждый день, в подзапросе a запоминаем перечень всех счетов и дату начала истории по каждому счёту, в подзапросе pre для каждого счёта составляем календарь дней с начала его истории. Финальный запрос присоединяет к календарю дней активности каждого счёта последние остатки на каждый день и протягивает их на дни, в которых не было изменений.

with cld as (select /*+ materialize*/   to_date('01.01.2020', 'dd.mm.yyyy') + level - 1 dt    from dual  connect by level <= 10),ab as (select trunc(dt) dt,         account_id,         max(balance_amt) keep(dense_rank last order by dt) balance_amt,         sum(turnover_amt) turnover_amt    from account_balance   group by trunc(dt), account_id),a as (select min(dt) min_dt, account_id from ab group by account_id),pre as (select cld.dt, a.account_id from cld left join a on cld.dt >= a.min_dt)select pre.dt,       pre.account_id,       last_value(ab.balance_amt ignore nulls) over(partition by pre.account_id order by pre.dt) balance_amt,       nvl(ab.turnover_amt, 0) turnover_amt  from pre  left join ab    on pre.dt = ab.dt   and pre.account_id = ab.account_id order by 2, 1;

Результат запроса соответствует ожиданиям.
DT ACCOUNT_ID BALANCE_AMT TURNOVER_AMT
01.01.2020 1 23 23
02.01.2020 1 23 0
03.01.2020 1 23 0
04.01.2020 1 23 0
05.01.2020 1 44 21
06.01.2020 1 44 0
07.01.2020 1 44 0
08.01.2020 1 44 0
09.01.2020 1 44 0
10.01.2020 1 44 0
05.01.2020 2 77 77
06.01.2020 2 77 0
07.01.2020 2 72 -5
08.01.2020 2 72 0
09.01.2020 2 72 0
10.01.2020 2 72 0

Объединение нескольких историй в одну


При загрузке данных в хранилища часто решается задача, когда нужно выстроить единую историю по сущности, имея отдельные истории атрибутов этой сущности, пришедшие из различных источников. Допустим, имеется некоторая сущность с первичным ключом primary_key_id, о которой известна история (start_dt end_dt) трёх её различных атрибутов, расположенная в трёх различных таблицах.

create table HIST1(  primary_key_id INTEGER,  start_dt       DATE,  attribute1     NUMBER);insert into HIST1(primary_key_id, start_dt, attribute1) values(1, to_date('2014-01-01','yyyy-mm-dd'), 7);insert into HIST1(primary_key_id, start_dt, attribute1) values(1, to_date('2015-01-01','yyyy-mm-dd'), 8);insert into HIST1(primary_key_id, start_dt, attribute1) values(1, to_date('2016-01-01','yyyy-mm-dd'), 9);insert into HIST1(primary_key_id, start_dt, attribute1) values(2, to_date('2014-01-01','yyyy-mm-dd'), 17);insert into HIST1(primary_key_id, start_dt, attribute1) values(2, to_date('2015-01-01','yyyy-mm-dd'), 18);insert into HIST1(primary_key_id, start_dt, attribute1) values(2, to_date('2016-01-01','yyyy-mm-dd'), 19);create table HIST2(  primary_key_id INTEGER,  start_dt       DATE,  attribute2     NUMBER); insert into HIST2(primary_key_id, start_dt, attribute2) values(1, to_date('2015-01-01','yyyy-mm-dd'), 4);insert into HIST2(primary_key_id, start_dt, attribute2) values(1, to_date('2016-01-01','yyyy-mm-dd'), 5);insert into HIST2(primary_key_id, start_dt, attribute2) values(1, to_date('2017-01-01','yyyy-mm-dd'), 6);insert into HIST2(primary_key_id, start_dt, attribute2) values(2, to_date('2015-01-01','yyyy-mm-dd'), 14);insert into HIST2(primary_key_id, start_dt, attribute2) values(2, to_date('2016-01-01','yyyy-mm-dd'), 15);insert into HIST2(primary_key_id, start_dt, attribute2) values(2, to_date('2017-01-01','yyyy-mm-dd'), 16);create table HIST3(  primary_key_id INTEGER,  start_dt       DATE,  attribute3     NUMBER); insert into HIST3(primary_key_id, start_dt, attribute3) values(1, to_date('2016-01-01','yyyy-mm-dd'), 10);insert into HIST3(primary_key_id, start_dt, attribute3) values(1, to_date('2017-01-01','yyyy-mm-dd'), 20);insert into HIST3(primary_key_id, start_dt, attribute3) values(1, to_date('2018-01-01','yyyy-mm-dd'), 30);insert into HIST3(primary_key_id, start_dt, attribute3) values(2, to_date('2016-01-01','yyyy-mm-dd'), 110);insert into HIST3(primary_key_id, start_dt, attribute3) values(2, to_date('2017-01-01','yyyy-mm-dd'), 120);insert into HIST3(primary_key_id, start_dt, attribute3) values(2, to_date('2018-01-01','yyyy-mm-dd'), 130);

Целью является загрузка единой истории изменения трёх атрибутов в одну таблицу.
Ниже приведён запрос, решающий такую задачу. В нём сначала формируется диагональная таблица q1 с данными из разных источников по разным атрибутам (отсутствующие в источнике атрибуты заполняются null-ами). Затем с помощью функции last_value(* ignore nulls) диагональная таблица схлопывается в единую историю, а последние известные значения атрибутов протягиваются вперёд на те даты, в которые изменений по ним не было:

select primary_key_id,       start_dt,       nvl(lead(start_dt - 1)           over(partition by primary_key_id order by start_dt),           to_date('9999-12-31', 'yyyy-mm-dd')) as end_dt,       last_value(attribute1 ignore nulls) over(partition by primary_key_id order by start_dt) as attribute1,       last_value(attribute2 ignore nulls) over(partition by primary_key_id order by start_dt) as attribute2,       last_value(attribute3 ignore nulls) over(partition by primary_key_id order by start_dt) as attribute3  from (select primary_key_id,               start_dt,               max(attribute1) as attribute1,               max(attribute2) as attribute2,               max(attribute3) as attribute3          from (select primary_key_id,                       start_dt,                       attribute1,                       cast(null as number) attribute2,                       cast(null as number) attribute3                  from hist1                union all                select primary_key_id,                       start_dt,                       cast(null as number) attribute1,                       attribute2,                       cast(null as number) attribute3                  from hist2                union all                select primary_key_id,                       start_dt,                       cast(null as number) attribute1,                       cast(null as number) attribute2,                       attribute3                  from hist3) q1         group by primary_key_id, start_dt) q2 order by primary_key_id, start_dt;

Результат получается такой:
PRIMARY_KEY_ID START_DT END_DT ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3
1 01.01.2014 31.12.2014 7 NULL NULL
1 01.01.2015 31.12.2015 8 4 NULL
1 01.01.2016 31.12.2016 9 5 10
1 01.01.2017 31.12.2017 9 6 20
1 01.01.2018 31.12.9999 9 6 30
2 01.01.2014 31.12.2014 17 NULL NULL
2 01.01.2015 31.12.2015 18 14 NULL
2 01.01.2016 31.12.2016 19 15 110
2 01.01.2017 31.12.2017 19 16 120
2 01.01.2018 31.12.9999 19 16 130

Нормалайзер


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

create table DENORMALIZED_TABLE(  id  INTEGER,  val VARCHAR2(4000));insert into DENORMALIZED_TABLE(id, val) values(1, 'aaa,cccc,bb');insert into DENORMALIZED_TABLE(id, val) values(2, 'ddd');insert into DENORMALIZED_TABLE(id, val) values(3, 'fffff,e');

Такой запрос нормализует данные, расклеив соединённые запятой поля в виде нескольких строк:

select id, regexp_substr(val, '[^,]+', 1, column_value) val, column_value  from denormalized_table,       table(cast(multiset                  (select level                     from dual                   connect by regexp_instr(val, '[^,]+', 1, level) > 0) as                  sys.odcinumberlist)) order by id, column_value;

Результат получается такой:
ID VAL COLUMN_VALUE
1 aaa 1
1 cccc 2
1 bb 3
2 ddd 1
3 fffff 1
3 e 2

Визуализация в формате SVG


Часто возникает желание как-то визуализировать числовые показатели, хранящиеся в базе данных. Например, построить графики, гистограммы, диаграммы. В этом могут помочь специализированные средства, например, Oracle BI. Но лицензии на эти средства могут стоить денег, а настройка их может занять больше времени, чем написание на коленке SQL-запроса к Oracle, который выдаст готовую картинку. Продемонстрируем на примере, как с помощью запроса быстро нарисовать такую картинку в формате SVG.
Предположим, у нас есть таблица с данными

create table graph_data(dt date, val number, radius number);insert into graph_data(dt, val, radius) values (to_date('01.01.2020','dd.mm.yyyy'), 12, 3);insert into graph_data(dt, val, radius) values (to_date('02.01.2020','dd.mm.yyyy'), 15, 4);insert into graph_data(dt, val, radius) values (to_date('05.01.2020','dd.mm.yyyy'), 17, 5);insert into graph_data(dt, val, radius) values (to_date('06.01.2020','dd.mm.yyyy'), 13, 6);insert into graph_data(dt, val, radius) values (to_date('08.01.2020','dd.mm.yyyy'),  3, 7);insert into graph_data(dt, val, radius) values (to_date('10.01.2020','dd.mm.yyyy'), 20, 8);insert into graph_data(dt, val, radius) values (to_date('11.01.2020','dd.mm.yyyy'), 18, 9);

dt это дата актуальности,
val это числовой показатель, динамику которого по времени мы визуализируем,
radius это ещё один числовой показатель, который будем рисовать в виде кружка с таким радиусом.
Скажем пару слов о формате SVG. Это формат векторной графики, который можно смотреть в современных браузерах и конвертировать в другие графические форматы. В нём, среди прочего, можно рисовать линии, кружки и писать текст:

<line x1="94" x2="94" y1="15" y2="675" style="stroke:rgb(150,255,255); stroke-width:1px"/><circle cx="30" cy="279" r="3" style="fill:rgb(255,0,0)"/><text x="7" y="688" font-size="10" fill="rgb(0,150,255)">2020-01-01</text>

Ниже SQL-запрос к Oracle, который строит график из данных в этой таблице. Здесь подзапрос const содержит различные константные настройки размеры картинки, количество меток на осях графика, цвета линий и кружочков, размеры шрифта и т.д. В подзапросе gd1 мы приводим данные из таблицы graph_data к координатам x и y на рисунке. Подзапрос gd2 запоминает предыдущие по времени точки, из которых нужно вести линии к новым точкам. Блок header это заголовок картинки с белым фоном. Блок vertical lines рисует вертикальные линии. Блок dates under vertical lines подписывает даты на оси x. Блок horizontal lines рисует горизонтальные линии. Блок values near horizontal lines подписывает значения на оси y. Блок circles рисует кружочки указанного в таблице graph_data радиуса. Блок graph data строит из линий график динамики показателя val из таблицы graph_data. Блок footer добавляет замыкающий тэг.

with const as (select 700 viewbox_width,         700 viewbox_height,         30 left_margin,         30 right_margin,         15 top_margin,         25 bottom_margin,         max(dt) - min(dt) + 1 num_vertical_lines,         11 num_horizontal_lines,         'rgb(150,255,255)' stroke_vertical_lines,         '1px' stroke_width_vertical_lines,         10 font_size_dates,         'rgb(0,150,255)' fill_dates,         23 x_dates_pad,         13 y_dates_pad,         'rgb(150,255,255)' stroke_horizontal_lines,         '1px' stroke_width_horizontal_lines,         10 font_size_values,         'rgb(0,150,255)' fill_values,         4 x_values_pad,         2 y_values_pad,         'rgb(255,0,0)' fill_circles,         'rgb(51,102,0)' stroke_graph,         '1px' stroke_width_graph,         min(dt) min_dt,         max(dt) max_dt,         max(val) max_val    from graph_data),gd1 as (select graph_data.dt,         const.left_margin +         (const.viewbox_width - const.left_margin - const.right_margin) *         (graph_data.dt - const.min_dt) / (const.max_dt - const.min_dt) x,         const.viewbox_height - const.bottom_margin -         (const.viewbox_height - const.top_margin - const.bottom_margin) *         graph_data.val / const.max_val y,         graph_data.radius    from graph_data, const),gd2 as (select dt,         round(nvl(lag(x) over(order by dt), x)) prev_x,         round(x) x,         round(nvl(lag(y) over(order by dt), y)) prev_y,         round(y) y,         radius    from gd1)/* header */select '<?xml version="1.0" encoding="UTF-8" standalone="no"?>' txt  from dualunion allselect '<svg version="1.1" width="' || viewbox_width || '" height="' ||       viewbox_height || '" viewBox="0 0 ' || viewbox_width || ' ' ||       viewbox_height ||       '" style="background:yellow" baseProfile="full" xmlns="http://personeltest.ru/away/www.w3.org/2000/svg" xmlns:xlink="http://personeltest.ru/away/www.w3.org/1999/xlink" xmlns:ev="http://personeltest.ru/away/www.w3.org/2001/xml-events">'  from constunion allselect '<title>Test graph</title>'  from dualunion allselect '<desc>Test graph</desc>'  from dualunion allselect '<rect width="' || viewbox_width || '" height="' || viewbox_height ||       '" style="fill:white" />'  from constunion all/* vertical lines */select '<line x1="' ||       to_char(round(left_margin +                     (viewbox_width - left_margin - right_margin) *                     (level - 1) / (num_vertical_lines - 1))) || '" x2="' ||       to_char(round(left_margin +                     (viewbox_width - left_margin - right_margin) *                     (level - 1) / (num_vertical_lines - 1))) || '" y1="' ||       to_char(round(top_margin)) || '" y2="' ||       to_char(round(viewbox_height - bottom_margin)) || '" style="stroke:' ||       const.stroke_vertical_lines || '; stroke-width:' ||       const.stroke_width_vertical_lines || '"/>'  from constconnect by level <= num_vertical_linesunion all/* dates under vertical lines */select '<text x="' ||       to_char(round(left_margin +                     (viewbox_width - left_margin - right_margin) *                     (level - 1) / (num_vertical_lines - 1) - x_dates_pad)) ||       '" y="' ||       to_char(round(viewbox_height - bottom_margin + y_dates_pad)) ||       '" font-size="' || font_size_dates || '" fill="' || fill_dates || '">' ||       to_char(min_dt + level - 1, 'yyyy-mm-dd') || '</text>'  from constconnect by level <= num_vertical_linesunion all/* horizontal lines */select '<line x1="' || to_char(round(left_margin)) || '" x2="' ||       to_char(round(viewbox_width - right_margin)) || '" y1="' ||       to_char(round(top_margin +                     (viewbox_height - top_margin - bottom_margin) *                     (level - 1) / (num_horizontal_lines - 1))) || '" y2="' ||       to_char(round(top_margin +                     (viewbox_height - top_margin - bottom_margin) *                     (level - 1) / (num_horizontal_lines - 1))) ||       '" style="stroke:' || const.stroke_horizontal_lines ||       '; stroke-width:' || const.stroke_width_horizontal_lines || '"/>'  from constconnect by level <= num_horizontal_linesunion all/* values near horizontal lines */select '<text text-anchor="end" x="' ||       to_char(round(left_margin - x_values_pad)) || '" y="' ||       to_char(round(viewbox_height - bottom_margin -                     (viewbox_height - top_margin - bottom_margin) *                     (level - 1) / (num_horizontal_lines - 1) +                     y_values_pad)) || '" font-size="' || font_size_values ||       '" fill="' || fill_values || '">' ||       to_char(round(max_val / (num_horizontal_lines - 1) * (level - 1), 2)) ||       '</text>'  from constconnect by level <= num_horizontal_linesunion all/* circles */select '<circle cx="' || to_char(gd2.x) || '" cy="' || to_char(gd2.y) ||       '" r="' || gd2.radius || '" style="fill:' || const.fill_circles ||       '"/>'  from gd2, constunion all/* graph data */select '<line x1="' || to_char(gd2.prev_x) || '" x2="' || to_char(gd2.x) ||       '" y1="' || to_char(gd2.prev_y) || '" y2="' || to_char(gd2.y) ||       '" style="stroke:' || const.stroke_graph || '; stroke-width:' ||       const.stroke_width_graph || '"/>'  from gd2, constunion all/* footer */select '</svg>' from dual;

Результат запроса можно сохранить в файл с расширением *.svg и посмотреть в браузере. При желании можно с помощью какой-либо из утилит конвертировать его в другие графические форматы, размещать на веб-страницах своего приложения и т.д.
Получилась такая картинка:



Приложение поиска по метаданным Oracle


Представьте себе, что стоит задача найти что-либо в исходном коде на Oracle, поискав информацию сразу на нескольких серверах. Речь идёт о поиске по объектам словаря данных Oracle. Рабочим местом для поиска является веб-интерфейс, куда пользователь-программист вводит искомую строку и выбирает галочками, на каких серверах Oracle осуществить этот поиск.
Веб-поисковик умеет искать строку по серверным объектам Oracle одновременно в нескольких различных базах данных банка. Например, можно поискать:
  • Где в коде Oracle зашита константа 61209, представляющая собой номер счёта второго порядка?
  • Где в коде и на каких серверах используется таблица accounts (в т.ч. через database link)?
  • С какого сервера из какой хранимой процедуры или триггера приходит сгенерированная программистом ошибка, например, ORA-20001 Курс валюты не найден?
  • Прописан ли планируемый к удалению индекс IX_CLIENTID где-либо явным образом в хинтах оптимизатора в SQL-запросах?
  • Используются ли где-либо (в т.ч. через database link) планируемые к удалению таблица, поле, процедура, функция и т.д.?
  • Где в коде явно зашит чей-то е-мэйл или номер телефона? Такие вещи лучше выносить из серверных объектов в настроечные таблицы.
  • Где в коде на серверах используется зависящий от версии Oracle функционал? Например, функция wm_concat выдаёт различный тип данных на выходе в зависимости от версии Oracle. Это может быть критично и требует внимания при миграции на более новую версию.
  • Где в коде используется какой-либо редкий приём, на который программисту хочется посмотреть, как на образец? Например, поискать в коде Oracle примеры использования функций sys_connect_by_path, regexp_instr или хинта push_subq.

По результатам поиска пользователю выдаётся информация, на каком сервере в коде каких функций, процедур, пакетов, триггеров, вьюшек и т.п. найдены требуемые результаты.
Опишем, как реализован такой поисковик.
Клиентская часть не сложная. Веб-интерфейс получает введённую пользователем поисковую строку, список серверов для поиска и логин пользователя. Веб-страница передаёт их в хранимую процедуру Oracle на сервере-обработчике. История обращений к поисковику, т.е. кто какой запрос выполнял, на всякий случай журналируется.
Получив поисковый запрос, серверная часть на поисковом сервере Oracle запускает в параллельных джобах несколько процедур, которые по database links на выбранных серверах Oracle сканируют следующие представления словаря данных в поисках искомой строки: dba_col_comments, dba_jobs, dba_mviews, dba_objects, dba_scheduler_jobs, dba_source, dba_tab_cols, dba_tab_comments, dba_views. Каждая из процедур, если что-то обнаружила, записывает найденное в таблицу результатов поиска (с соответствующим ID поискового запроса). Когда все поисковые процедуры завершили работу, клиентская часть выдаёт пользователю всё, что записалось в таблицу результатов поиска с соответствующим ID поискового запроса.
Но это ещё не всё. Помимо поиска по словарю данных Oracle в описанный механизм прикрутили ещё и поиск по репозиторию Informatica PowerCenter. Informatica PowerCenter является популярным ETL-средством, использующимся в Сбербанке при загрузке различной информации в хранилища данных. Informatica PowerCenter имеет открытую хорошо задокументированную структуру репозитория. По этому репозиторию есть возможность искать информацию так же, как и по словарю данных Oracle. Какие таблицы и поля используются в коде загрузок, разработанном на Informatica PowerCenter? Что можно найти в трансформациях портов и явных SQL-запросах? Вся эта информация имеется в структурах репозитория и может быть найдена. Для знатоков PowerCenter напишу, что наш поисковик сканирует следующие места репозитория в поисках маппингов, сессий или воркфловов, содержащих в себе где-то искомую строку: sql override, mapplet attributes, ports, source definitions in mappings, source definitions, target definitions in mappings, target_definitions, mappings, mapplets, workflows, worklets, sessions, commands, expression ports, session instances, source definition fields, target definition fields, email tasks.

Автор: Михаил Гричик, эксперт профессионального сообщества Сбербанка SberProfi DWH/BigData.

Профессиональное сообщество SberProfi DWH/BigData отвечает за развитие компетенций в таких направлениях, как экосистема Hadoop, Teradata, Oracle DB, GreenPlum, а также BI инструментах Qlik, SAP BO, Tableau и др.
Подробнее..

Как мы, сотрудники Сбера, считаем и инвестируем свои деньги

19.10.2020 10:13:52 | Автор: admin


Нужно ли покупать автомобиль за 750 тысяч рублей при том, что вы ездите 18 раз в месяц или дешевле пользоваться такси? Если вы работаете на заднем сидении или слушаете музыку как это меняет оценку? Как правильнее покупать квартиру в какой момент оптимально заканчивать копить на депозите и делать первый взнос по ипотеке? Или даже тривиальный вопрос: выгоднее положить деньги на депозит под 6% с ежемесячной капитализацией или под 6,2% с ежегодной капитализацией? Большинство людей даже не пытается производить такие подсчёты и даже не хотят собирать детальную информацию о своих деньгах. Вместо подсчётов подключают чувства и эмоции. Либо делают какую-то узкую оценку, например, детально подсчитывают годовую стоимость владения автомобилем, в то время как все эти расходы могут составлять лишь 5% от общих трат (а траты на другие стороны жизни при этом не подсчитывают). Мозг человека подвержен когнитивным искажениям. Например, сложно бросить, несмотря на неокупаемость, дело, в которое вложены масса времени и денег. Люди обычно излишне оптимистичны и недооценивают риски, а также легко внушаемы и могут купить дорогую безделушку или вложиться в финансовую пирамиду.
Понятное дело, в случае банка эмоциональная оценка не работает. Поэтому я хочу сначала рассказать о том, как оценивает деньги обычное физлицо (я, в том числе), и как это делает банк. Ниже будет немного финансового ликбеза и много про аналитику данных в Сбербанке для всего банка в целом.
Полученные выводы приведены только в качестве примера и не могут расцениваться как рекомендации для частных инвесторов, поскольку не учитывают множества факторов, оставшихся за рамками данной статьи.
Например, любое событие типа черный лебедь в макроэкономике, в корпоративном управлении любой из компаний и пр., может привести к кардинальным изменениям.

Предположим, что вы уже расплатились с ипотекой и у вас есть накопления. Эта статья может быть вам полезна, если вам:

  • не безразлично, сколько у вас накопилось имущества и как вести его учёт
  • интересно, как заставить своё имущество приносить вам дополнительный доход
  • хочется понять, какой же из способов вложения денег лучший: недвижимость, депозиты или акции
  • любопытно, что посоветует по этому вопросу анализ данных Сбербанка

Часто люди принимают финансовые решения, не располагая полной информацией о динамике своих собственных доходов и расходов, не имея оценки стоимости собственного имущества, не учитывая в расчётах инфляцию и т.п.
Иногда люди допускают ошибки, например, берут кредит, думая, что смогут его выплачивать, а затем не справляются. При этом ответ на вопрос, осилит ли человек обслуживание кредита, зачастую известен заранее. Нужно просто знать, сколько зарабатываешь, сколько тратишь, какова динамика изменений этих показателей.
Или, например, человек получает какую-то зарплату на работе, её периодически повышают, преподнося, как оценку заслуг. А в действительности по сравнению с инфляцией заработок этого человека может падать, и он может этого не осознавать, если не ведёт учёт доходов.
Некоторые люди не могут оценить, какой выбор выгоднее в сложившейся у них ситуации: снимать квартиру или взять ипотеку под такую-то ставку.
И вместо того, чтобы произвести подсчёт, каковы будут расходы в том и ином случае, как-то монетизировав в расчётах нефинансовые показатели (выгоду от московской прописки оцениваю в M рублей в месяц, удобство от проживания в снимаемой возле работы квартире оцениваю в N рублей в месяц), люди бегут в интернет дискутировать с собеседниками, у которых может быть иная финансовая ситуация и другие приоритеты в оценке нефинансовых показателей.
Я за ответственное финансовое планирование. Прежде всего, предлагается осуществлять сбор следующих данных о собственном финансовом положении:

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

Учёт и оценка всего имеющегося в наличии имущества


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

Оцените, что у вас есть:

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

Среди имущества можно отметить ликвидную долю, которую возможно достаточно быстро отозвать и конвертировать в другие формы. Например, долю в квартире, которой владеешь совместно с проживающими в ней родственниками, можно отнести к неликвидному имуществу. Долгосрочные вложения во вклады или акции, которые нельзя отозвать без потерь, также можно считать неликвидными. В свою очередь, недвижимость, которой владеешь, но не проживаешь, транспортные средства, краткосрочные и отзываемые вклады можно отнести к ликвидному имуществу. Например, если понадобятся деньги на срочное лечение, то польза от каких-то инструментов примерно нулевая, поэтому ликвидная доля более ценна.
Далее, среди имущества можно выделить неприбыльное и прибыльное. Например, недвижимость, которая не сдаётся в аренду, а также транспортные средства можно расценить как неприбыльные. А сдаваемые в аренду объекты недвижимости, вложенные под ставку выше инфляции депозиты и акции прибыльное имущество.
Получится, например, такая картинка (данные сгенерированы случайным образом):



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

Учёт доходов, расходов и динамики накопления имущества


Предлагается регулярно записывать в электронной форме свои доходы и расходы. В эпоху интернет-банков это не требует особых усилий. При этом доходы и расходы можно делить по категориям. Далее, агрегируя их по годам, можно делать выводы об их динамике. Важно учитывать инфляцию, чтобы иметь представление о том, как выглядят суммы за прошлые годы в современных ценах. У каждого своя потребительская корзина. Бензин и еда дорожают с разной скоростью. Но посчитать свою личную инфляцию достаточно сложно. Поэтому, с некоторой погрешностью, можно пользоваться данными об официальном уровне инфляции.
Данные по ежемесячной инфляции имеются во многих открытых источниках, в том числе загружены и в озеро данных Сбербанка.
Пример визуализации динамики доходов-расходов (данные сгенерированы случайным образом, динамика инфляции настоящая):



Имея такую полную картину, можно делать выводы о своём реальном росте/снижении доходов и реальном росте/снижении накоплений, анализировать динамику расходов по категориям и принимать обоснованные финансовые решения.
Какой способ вложения свободных денежных средств обыгрывает инфляцию и приносит наибольший пассивный доход?
В озере данных Сбербанка есть ценные данные на эту тему:

  • динамика стоимости квадратного метра в Москве
  • база предложений по продаже и аренде недвижимости в Москве и ближнем Подмосковье
  • динамика средней годовой процентной ставки по депозитам
  • динамика уровня рублёвой инфляции
  • динамика индекса Мосбиржи полной доходности брутто (MCFTR)
  • котировки акций Мосбиржи и данные о выплаченных дивидендах

Эти данные позволят нам сравнить доходность и риски от вложений в сдаваемую в аренду недвижимость, в банковские депозиты и в рынок акций. При этом не забудем учесть инфляцию.
Сразу скажу, что в этом посте мы занимаемся исключительно анализом данных и не прибегаем к использованию каких-либо экономических теорий. Просто посмотрим, что говорят наши данные какой способ сохранить и преумножить сбережения в России за последние годы дал наилучший результат.
Кратко расскажем, о том, как собираются и анализируются данные, использующиеся в этой статье, и прочие данные в Сбербанке. Имеется слой реплик источников, которые хранятся в формате parquet на hadoop. Используются как внутренние источники (различные АС банка), так и внешние. Реплики источников собираются разными способами. Есть продукт stork, в основе которого лежит spark, набирает обороты и второй продукт Ab Initio AIR. Реплики источников загружаются на различные кластеры hadoop под управлением Cloudera, в том числе могут быть прилинкованы с одного кластера на другой. Кластеры разделены преимущественно по бизнес-блокам, имеются также и кластеры Лаборатории данных. На базе реплик источников строятся различные витрины данных, доступные бизнес-пользователям и data scientist-ам. Для написания этой статьи были использованы различные приложения spark, запросы к hive, приложения по анализу данных и визуализации результатов в формате графики SVG.

Исторический анализ рынка недвижимости


Анализ показывает, что недвижимость в долгосрочной перспективе растёт пропорционально инфляции, т.е. в реальных ценах не растёт и не снижается. Приведём графики динамики цен на жилую недвижимость в Москве, демонстрирующий имеющиеся исходные данные.

График цен в рублях без учёта инфляции:



График цен в рублях с учётом инфляции (в современных ценах):



Видим, что исторически цена колебалась около 200 000 руб./кв.м. в современных ценах и изменчивость была достаточно низкая.

Сколько же процентов годовых сверх инфляции приносят инвестиции в жилую недвижимость? Как доходность зависит от количества комнат в квартире? Проанализируем имеющуюся у Сбербанка базу объявлений о продаже и аренде квартир в Москве и ближнем Подмосковье.
В нашей базе нашлось достаточно много многоквартирных домов, в которых имеются одновременно объявления о продаже квартир и объявления об аренде квартир, причём количество комнат в продаваемых и сдаваемых в аренду квартирах одинаково. Мы сопоставили такие случаи, сгруппировав по домам и количеству комнат в квартире. Если предложений в такой группе имелось несколько, вычисляли среднюю цену. Если площадь продаваемой и сдаваемой в аренду квартиры отличалась, то пропорционально изменяли цену предложения, чтобы площади сравниваемых квартир соответствовали. В результате, предложения нанесли на график. Каждый кружок это фактически квартира, которую одновременно предлагают приобрести и снять. На горизонтальной оси видим стоимость приобретения квартиры, а на вертикальной оси стоимость снятия этой же квартиры. Количество комнат в квартире понятно из цвета кружка, а чем больше площадь квартиры, тем больше радиус кружка. С учётом сверхдорогих предложений график получился такой:



Если убрать дорогие предложения, то можно детальнее разглядеть цены в эконом-сегменте:



Корреляционный анализ показывает, что зависимость между стоимостью аренды квартиры и стоимостью её покупки близка к линейной.
Получилось такое соотношение между стоимостью годовой аренды квартиры и стоимостью приобретения квартиры (не забудем, что годовая стоимость это 12 месячных):
Количество комнат: Отношение стоимости годовой аренды квартиры к стоимости приобретения квартиры:
1-комнатные 5,11%
2-комнатные 4,80%
3-комнатные 4,94%
Всего 4,93%

Получили среднюю оценку в 4,93% годовых доходности от сдачи квартиры в аренду сверх инфляции. Также интересен момент, что дешёвые 1-комнатные квартиры сдавать в аренду немного выгоднее. Мы сравнивали цену предложения, которая в обоих случаях (аренды и покупки) немного завышена, поэтому корректировка не требуется. Однако требуются другие корректировки: сдаваемые в аренду квартиры нужно иногда хотя бы косметически ремонтировать, некоторое время занимает поиск арендатора и квартиры пустуют, иногда в цену аренды не заложены коммунальные платежи частично или полностью, также имеет место крайне незначительное обесценивание квартир с годами.
С учётом корректировок, от сдачи жилой недвижимости в аренду можно иметь доход до 4,5% годовых (сверх того, что сама недвижимость не обесценивается). Если такая доходность впечатляет, у Сбербанка есть множество предложений на ДомКлик.

Исторический анализ ставок по депозитам


Рублёвые депозиты в России в последние несколько лет в основном обыгрывают инфляцию. Но не на 4,5%, как сдаваемая недвижимость, а, в среднем, на 2%.
На графике ниже видим динамику сравнения ставок по депозитам и уровня инфляции.



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

  • Можно фиксировать ставку по пополняемым вкладам в благоприятное время на несколько месяцев вперёд
  • Ежемесячная капитализация, свойственная многим учтённым в этих усреднённых данных вкладам, за счёт сложных процентов добавляет прибыли
  • Выше были учтены ставки по топ-10 банкам по информации от Банка России, вне топ-10 можно найти ставки несколько выше

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

Исторический анализ рынка акций


Теперь посмотрим на более разнообразный и рискованный рынок российских акций. Доходность от вложения в акции не фиксирована и может сильно меняться. Однако, если диверсифицировать активы и заниматься инвестированием длительный период, то можно проследить среднюю годовую процентную ставку, характеризующую успешность инвестирования в портфель акций.
Для далёких от темы читателей скажу пару слов об индексах акций. В России есть индекс Мосбиржи, который показывает, динамику рублевой стоимости портфеля, состоящего из 50 крупнейших российских акций. Состав индекса и доля акций каждой компании зависит от объема торговых операций, объема бизнеса, количества находящихся в обращении акций. График ниже показывает, как рос индекс Мосбиржи (т.е. такой усреднённый портфель) в последние годы.



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

Поэтому нам будет интереснее индекс Мосбиржи полной доходности брутто (MCFTR), который учитывает полученные дивиденды и списанный с этих дивидендов налог. Покажем на графике ниже, как менялся этот индекс в последние годы. Кроме того, учтём инфляцию и посмотрим, как рос этот индекс в современных ценах:



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

Посмотрим, какой же был коэффициент роста индекса MCFTR за последние 1,2,3,,11 лет. Т.е. какова же была бы наша доходность, если бы мы купили акции в пропорциях этого индекса и регулярно реинвестировали бы полученные дивиденды в те же самые акции:
Лет Начало Конец MCFTR
нач. с
учётом
инфл.
MCFTR
кон. с
учётом
инфл.
Коэфф.
роста
Годовой
коэфф.
роста
1 30.07.2019 30.07.2020 4697,47 5095,54 1,084741 1,084741
2 30.07.2018 30.07.2020 3835,52 5095,54 1,328513 1,152612
3 30.07.2017 30.07.2020 3113,38 5095,54 1,636659 1,178472
4 30.07.2016 30.07.2020 3115,30 5095,54 1,635650 1,130896
5 30.07.2015 30.07.2020 2682,35 5095,54 1,899655 1,136933
6 30.07.2014 30.07.2020 2488,07 5095,54 2,047989 1,126907
7 30.07.2013 30.07.2020 2497,47 5095,54 2,040281 1,107239
8 30.07.2012 30.07.2020 2634,99 5095,54 1,933799 1,085929
9 30.07.2011 30.07.2020 3245,76 5095,54 1,569907 1,051390
10 30.07.2010 30.07.2020 2847,81 5095,54 1,789284 1,059907
11 30.07.2009 30.07.2020 2223,17 5095,54 2,292015 1,078318

Видим, что, вложившись любое количество лет назад, мы получили бы победу над инфляцией в 5-18% ежегодно в зависимости от удачности точки входа.

Составим еще одну табличку не прибыльность за каждые последние N лет, а прибыльность за каждый из последних N одногодовых периодов:
Год Начало Конец MCFTR
нач. с
учётом
инфл.
MCFTR
кон. с
учётом
инфл.
Годовой
коэфф.
роста
1 30.07.2019 30.07.2020 4697,47 5095,54 1,084741
2 30.07.2018 30.07.2019 3835,52 4697,47 1,224728
3 30.07.2017 30.07.2018 3113,38 3835,52 1,231947
4 30.07.2016 30.07.2017 3115,30 3113,38 0,999384
5 30.07.2015 30.07.2016 2682,35 3115,30 1,161407
6 30.07.2014 30.07.2015 2488,07 2682,35 1,078085
7 30.07.2013 30.07.2014 2497,47 2488,07 0,996236
8 30.07.2012 30.07.2013 2634,99 2497,47 0,947810
9 30.07.2011 30.07.2012 3245,76 2634,99 0,811825
10 30.07.2010 30.07.2011 2847,81 3245,76 1,139739
11 30.07.2009 30.07.2010 2223,17 2847,81 1,280968

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

Теперь, для лучшего понимания, давайте абстрагируемся от этого индекса и посмотрим на примере конкретной акции, какой бы получился результат, если вложиться в эту акцию 15 лет назад, повторно вкладывать дивиденды и платить налоги. Результат посмотрим с учётом инфляции, т.е. в современных ценах. Ниже показан пример обыкновенной акции Сбербанка. Зелёный график показывает динамику стоимости портфеля, изначально состоявшего из одной акции Сбербанка в современных ценах с учётом реинвестиции дивидендов. За 15 лет инфляция обесценила рубли в 3.014135 раз. Акция Сбербанка за эти годы подорожала с 21.861 руб. до 218.15 руб., т.е. цена выросла в 9.978958 раз без учёта инфляции. За эти годы владельцу одной акции было выплачено в разное время дивидендов за вычетом налогов в сумме 40.811613 руб. Суммы выплаченных дивидендов показаны на графике красными вертикальными палочками и не относятся к самому графику, в котором дивиденды и их реинвестиция также учтены. Если всякий раз на эти дивиденды вновь покупались акции Сбербанка, то в конце периода акционер уже владел не одной, а 1.309361 акциями. С учётом реинвестиции дивидендов и инфляции исходный портфель подорожал в 4.334927 раз за 15 лет, т.е. дорожал в 1.102721 раз ежегодно. Итого, обыкновенная акция Сбербанка приносила владельцу в среднем 10,27% годовых сверх инфляции каждый из 15 последних лет:



В качестве ещё одного примера приведём аналогичную картинку с динамикой по привилегированной акции Сбербанка. Привилегированная акция Сбербанка приносила владельцу в среднем ещё больше, 13,59% годовых сверх инфляции каждый из 15 последних лет:



Эти результаты на практике будут чуть ниже, потому что при покупке акций нужно платить небольшую брокерскую комиссию. В то же время результат может быть ещё улучшен, если использовать Индивидуальный Инвестиционный Счёт, позволяющий получить от государства налоговый вычет в некотором ограниченном объёме. Если вы не слышали о таком, предлагается поискать по аббревиатуре ИИС. Не забудем также упомянуть, что ИИС можно открыть в Сбербанке.

Итак, мы предварительно получили, что инвестировать в акции исторически выгоднее, чем в недвижимость и депозиты. Для развлечения приведём полученный в результате анализа данных хит-парад из 20 наилучших акций, которые торгуются на рынке более 10 лет. В последнем столбце видим, во сколько раз в среднем каждый год рос портфель из акций с учётом инфляции и реинвестиции дивидендов. Видим, что многие акции обыгрывали инфляцию более, чем на 10%:
Акция Начало Конец Коэфф. инфляции Нач. цена Кон. цена Рост
числа
акций
за счёт
реинве-
стиции
диви-
дендов,
раз
Итоговый
средне-
годовой
рост, раз
Лензолото 30.07.2010 30.07.2020 1,872601 1267,02 17290 2,307198 1,326066
НКНХ ап 30.07.2010 30.07.2020 1,872601 5,99 79,18 2,319298 1,322544
МГТС-4ап 30.07.2010 30.07.2020 1,872601 339,99 1980 3,188323 1,257858
Татнфт 3ап 30.07.2010 30.07.2020 1,872601 72,77 538,8 2,037894 1,232030
МГТС-5ао 30.07.2010 30.07.2020 1,872601 380,7 2275 2,487047 1,230166
Акрон 30.07.2010 30.07.2020 1,872601 809,88 5800 2,015074 1,226550
Лензол. ап 30.07.2010 30.07.2020 1,872601 845 5260 2,214068 1,220921
НКНХ ао 30.07.2010 30.07.2020 1,872601 14,117 92,45 1,896548 1,208282
Ленэнерг-п 30.07.2010 30.07.2020 1,872601 25,253 149,5 1,904568 1,196652
ГМКНорНик 30.07.2010 30.07.2020 1,872601 4970 19620 2,134809 1,162320
Сургнфгз-п 30.07.2010 30.07.2020 1,872601 13,799 37,49 2,480427 1,136619
ИРКУТ-3 30.07.2010 30.07.2020 1,872601 8,127 35,08 1,543182 1,135299
Татнфт 3ао 30.07.2010 30.07.2020 1,872601 146,94 558,4 1,612350 1,125854
Новатэк ао 30.07.2010 30.07.2020 1,872601 218,5 1080,8 1,195976 1,121908
СевСт-ао 30.07.2010 30.07.2020 1,872601 358 908,4 2,163834 1,113569
Красэсб ао 30.07.2010 30.07.2020 1,872601 3,25 7,07 2,255269 1,101105
ЧТПЗ ао 30.07.2010 30.07.2020 1,872601 55,7 209,5 1,304175 1,101088
Сбербанк-п 30.07.2010 30.07.2020 1,872601 56,85 203,33 1,368277 1,100829
ПИК ао 30.07.2010 30.07.2020 1,872601 108,26 489,5 1,079537 1,100545
ЛУКОЙЛ 30.07.2010 30.07.2020 1,872601 1720 5115 1,639864 1,100444

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

Задача. Найти акцию, стабильно дающую доход выше недвижимости (среднегодовой коэффициент роста 1.045 свыше инфляции) максимальное число раз по каждому из последних 10 одногодовых периодов, когда акция торговалась.
В этой и следующих задачах имеется в виду вышеописанная модель с реинвестицией дивидендов и учётом инфляции.
Вот победители в этой номинации согласно нашему анализу данных. Акции в верхней части таблицы из года в год стабильно показывают высокую доходность без провалов. Здесь Год 1 это 30.07.2019-30.07.2020, Год 2 это 30.07.2018-30.07.2019 и т.д.:
Акция Число
побед
над
недви-
жимо-
стью
за
после-
дние
10 лет
Год 1 Год 2 Год 3 Год 4 Год 5 Год 6 Год 7 Год 8 Год 9 Год 10
Татнфт 3ап 8 0,8573 1,4934 1,9461 1,6092 1,0470 1,1035 1,2909 1,0705 1,0039 1,2540
МГТС-4ап 8 1,1020 1,0608 1,8637 1,5106 1,7244 0,9339 1,1632 0,9216 1,0655 1,6380
ЧТПЗ ао 7 1,5532 1,2003 1,2495 1,5011 1,5453 1,2926 0,9477 0,9399 0,3081 1,3666
СевСт-ао 7 0,9532 1,1056 1,3463 1,1089 1,1955 2,0003 1,2501 0,6734 0,6637 1,3948
НКНХ ао 7 1,3285 1,5916 1,0821 0,8403 1,7407 1,3632 0,8729 0,8678 1,0716 1,7910
МГТС-5ао 7 1,1969 1,0688 1,8572 1,3789 2,0274 0,8394 1,1685 0,8364 1,0073 1,4460
Газпрнефть 7 0,8119 1,3200 1,6868 1,2051 1,1751 0,9197 1,1126 0,7484 1,1131 1,0641
Татнфт 3ао 7 0,7933 1,0807 1,9714 1,2109 1,0728 1,1725 1,0192 0,9815 1,0783 1,1785
Ленэнерг-п 7 1,3941 1,1865 1,7697 2,4403 2,2441 0,6250 1,2045 0,7784 0,4562 1,4051
НКНХ ап 7 1,3057 2,4022 1,2896 0,8209 1,2356 1,6278 0,7508 0,8449 1,5820 2,4428
Сургнфгз-п 7 1,1897 1,0456 1,2413 0,8395 0,9643 1,4957 1,2140 1,1280 1,4013 1,0031

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

Теперь сформулируем и решим такую задачу на анализ данных. Целесообразно ли немножко спекулировать, всякий раз покупая акции за M дней до даты выплаты дивидендов и продавая акции через N дней после даты выплаты дивидендов? Лучше ли собирать урожай с дивидендов и выходить из акции, чем сидеть в акции круглый год? Допустим, что нет потерь на комиссии от такого входа-выхода. И анализ данных поможет нам найти границы коридора M и N, который был исторически наиболее удачен в деле сбора урожая дивидендов вместо долгого владения акциями.

Приведём анекдот 2008 года.
Джон Смит, выпрыгнувший из окна 75-го этажа на Уолл Стрит, после удара о землю подпрыгнул на 10 метров, чем немного отыграл свое утреннее падение.

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

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

Наша модель просчитала все варианты ширины окрестности вокруг дат выплаты дивидендов за всю историю. Были приняты следующие ограничения: M<=30, N>=20. Дело в том, что далеко не всегда ранее, чем за 30 дней до выплаты дивидендов заранее известна дата и суммы выплаты. Также дивиденды приходят на счёт далеко не сразу, а с задержкой. Считаем, что нужно от 20 дней, чтобы гарантированно получить на счёт и реинвестировать дивиденды. С такими ограничениями модель выдала следующий ответ. Наиболее оптимально покупать акции за 34 дня до даты выплаты дивидендов и продавать их через 25 дней после даты выплаты дивидендов. При таком сценарии в среднем получался рост в 3,11% за этот период, что даёт 20,9% годовых. Т.е. при рассматриваемой модели инвестирования (с реинвестицией дивидендов и учётом инфляции) если покупать акцию за 34 дня до даты выплаты дивидендов и продавать её через 25 дней после даты выплаты дивидендов, то имеем 20,9% годовых свыше уровня инфляции. Это проверено усреднением по всем случаям выплаты дивидендов из нашей базы.

Например, по привилегированной акции Сбербанка такой сценарий входа-выхода давал бы 11,72% роста свыше уровня инфляции за каждый вход-выход в окрестности даты выплаты дивидендов. Это составляет аж 98,6% годовых свыше уровня инфляции. Но это, конечно, пример случайного везения.
Акция Вход Дата выплаты дивидендов Выход Коэфф. роста
Сбербанк-п 10.05.2019 13.06.2019 08.07.2019 1,112942978
Сбербанк-п 23.05.2018 26.06.2018 21.07.2018 0,936437635
Сбербанк-п 11.05.2017 14.06.2017 09.07.2017 1,017492563
Сбербанк-п 11.05.2016 14.06.2016 09.07.2016 1,101864592
Сбербанк-п 12.05.2015 15.06.2015 10.07.2015 0,995812419
Сбербанк-п 14.05.2014 17.06.2014 12.07.2014 1,042997818
Сбербанк-п 08.03.2013 11.04.2013 06.05.2013 0,997301095
Сбербанк-п 09.03.2012 12.04.2012 07.05.2012 0,924053861
Сбербанк-п 12.03.2011 15.04.2011 10.05.2011 1,010644958
Сбербанк-п 13.03.2010 16.04.2010 11.05.2010 0,796937418
Сбербанк-п 04.04.2009 08.05.2009 02.06.2009 2,893620094
Сбербанк-п 04.04.2008 08.05.2008 02.06.2008 1,073578067
Сбербанк-п 08.04.2007 12.05.2007 06.06.2007 0,877649005
Сбербанк-п 25.03.2006 28.04.2006 23.05.2006 0,958642001
Сбербанк-п 03.04.2005 07.05.2005 01.06.2005 1,059276282
Сбербанк-п 28.03.2004 01.05.2004 26.05.2004 1,049810801
Сбербанк-п 06.04.2003 10.05.2003 04.06.2003 1,161792898
Сбербанк-п 02.04.2002 06.05.2002 31.05.2002 1,099316569

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

Поставим нашей модели ещё одну задачу по анализу данных:

Задача. Найти акцию с наирегулярнейшей возможностью заработка на входе-выходе в окрестности даты выплаты дивидендов. Будем оценивать сколько случаев из случаев выплаты дивидендов дали возможность заработка более 10% в годовом исчислении выше уровня инфляции, если входить в акцию за 34 дня до и выходить через 25 дней после даты выплаты дивидендов.

Будем рассматривать акции, по которым было хотя бы 5 случаев выплаты дивидендов. Получившийся хит-парад приведён ниже. Отметим, что результат имеет ценность скорее всего только с точки зрения задачи на анализ данных, но не как практическое руководство к инвестированию.
Акция Количество
случаев выигрыша
более 10% годовых
сверх инфляции
Количество
случаев
выплаты
дивидендов
Доля
случаев
победы
Средний коэфф. роста
Лензолото 5 5 1 1,320779017
МРСК СЗ 6 7 0,8571 1,070324870
Роллман-п 6 7 0,8571 1,029644533
Россети ап 4 5 0,8 1,279877637
Кубанэнр 4 5 0,8 1,248634960
ЛСР ао 8 10 0,8 1,085474828
АЛРОСА ао 8 10 0,8 1,042920287
ФСК ЕЭС ао 6 8 0,75 1,087420610
НМТП ао 10 14 0,7143 1,166690777
КузбТК ао 5 7 0,7143 1,029743667

Из проведённого анализа рынка акций можно сделать такие выводы:
1) Проверено, что заявленная в материалах брокеров, инвестиционных компаний и прочих заинтересованных лиц доходность акций выше депозитов и инвестиционной недвижимости имеет место быть.
2) Волатильность рынка акций очень высокая, но на долгий срок с существенной диверсификацией портфеля вкладываться можно. Ради добавочных 13% налогового вычета при инвестиции на ИИС открывать для себя рынок акций вполне целесообразно и сделать это можно, в том числе, в Сбербанке.
3) Исходя из анализа результатов за прошлые периоды найдены лидеры по стабильной высокой доходности и по выгодности входа-выхода в окрестности даты выплаты дивидендов. Однако результаты не такие уж однозначные и руководствоваться только ими в своём инвестировании не стоит. Это были примеры задач на анализ данных.

Итого:
Полезно вести учет своего имущества, а также доходов и расходов. Это помогает в финансовом планировании. Если удаётся копить деньги, то есть возможности инвестировать их под ставку выше инфляции. Анализ данных из озера данных Сбербанка показал, что депозиты ежегодно приносят 2%, сдача квартир в аренду 4,5%, а российские акции около 10% свыше инфляции при наличии существенно больших рисков.

Автор: Михаил Гричик, эксперт профессионального сообщества Сбербанка SberProfi DWH/BigData.

Профессиональное сообщество SberProfi DWH/BigData отвечает за развитие компетенций в таких направлениях, как экосистема Hadoop, Teradata, Oracle DB, GreenPlum, а также BI инструментах Qlik, SAP BO, Tableau и др.
Подробнее..

Перевод Да, синдром самозванца на самом деле полезен для разработчиков

31.03.2021 14:23:38 | Автор: admin


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

  • Вы не выступаете на совещаниях и всегда занимаете роль пассивного слушателя.
  • Считаете, что все находящиеся в комнате знают больше, чем вы.
  • Всегда подвергаете сомнениям ценность своего вклада.
  • На больших совещаниях вы никогда не сидите в переднем ряду.
  • Для подтверждения своего мнения вы оглядываетесь на остальных.

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

На каком-то уровне я ощущал, что мне здесь не место и что я самозванец.

Динамика синдрома самозванца


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

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

Синдром самозванца заставляет вас испытывать такие чувства:

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

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

Давайте изучим, что же происходит на самом деле


На рисунке ниже:

  • Внешний круг это реальность. Это то, что видят другие.
  • Средний круг это имеющиеся у нас страхи.
  • Внутренний круг это истина.



Обычно мы фокусируемся на внешнем и среднем кругах. И между ними постоянно присутствует конфликт. Иногда мы думаем, что достаточно хороши и заслуживаем то, что имеем. Иногда это ощущение пропадает и мы чувствуем себя отчаявшимися и некомпетентными. Это называется эффектом Даннинга-Крюгера.


Однако почти 90% времени мы лишаем себя погружения на один уровень ниже, во внутренний круг. Круг истины. Потому что истина всегда есть, и мы знаем это. Мы намеренно решаем игнорировать её, потому что слишком заняты прыжками между кругом реальности и кругом страха.

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

Как помогает синдром самозванца?


По сути, синдром самозванца помогает выявить свои страхи.

Без синдрома самозванца эго просто съело бы вас заживо. У вас бы не было серьёзных причин обдумывать свою работу. И все мы знаем, что неудача очень важна на пути к успеху. Что вы делаете, когда терпите неудачу? Вы упорно трудитесь, чтобы изменить эту реальность. Вы превращаете её в успех. Если вы знаете свои страхи, становится проще превратить их в истину, упорно работая над ними и постепенно превращая их в новую реальность. Внутренний круг продолжает становиться больше. Круг истины берёт верх над вашими страхами, максимально приближаясь к реальности.

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


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

Мысли в заключение


Досадный аспект синдрома самозванца заключается в том, что вы никогда полностью от него не избавитесь. Даже несмотря на то, что я профессионально разрабатываю ПО в течение шести лет, у меня он всё равно есть. И я знаю других опытных разработчиков, которые постоянно с ним борются. Если начать решать проблему в нужном направлении и учиться разбираться с ней, то это поможет её обуздать.

В моём случае наибольший эффект оказали понимание и принятие синдрома самозванца, а также изменение моего образа мышления. Каждый раз, когда я начинаю думать, что недостаточно продуктивен или двигаюсь вперёд недостаточно быстро, я рефлексирую об этом и вскоре выявляю проблему. А как только я вижу проблему, начинаю с ней разбираться. Чего бы это ни стоило!



На правах рекламы


VDSina предлагает мощные и недорогие VPS с посуточной оплатой. Интернет-канал для каждого сервера 500 Мегабит, защита от DDoS-атак включена в тариф, возможность установить Windows, Linux или вообще ОС со своего образа, а ещё очень удобная панель управления серверами собственной разработки. Обязательно попробуйте!

Подробнее..

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru