Миграция данных в кровавом энтерпрайзе: что анализировать, чтобы не завалить проект30.11.2018 15:17

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

Для начинающих поясню, что миграция идет по такой схеме: источники → преобразование данных (отвечает ETL или шина) → приемник.

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

1. Системные интеграторы настраивают ETL-процесс.
2. ETL-процесс преобразует исходные данные и отдает мне.
3. Я изучаю выгрузку и отправляю ошибки интеграторам.
4. Интеграторы исправляют ETL-процесс и снова запускают миграцию.

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

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

Но сначала — об одном из главных мифов системной интеграции.

Документация и архитектор помогут (на самом деле нет)

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

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

Документация врет. Типичная enterprise-система работает 5–20 лет. Все эти годы изменения в ней документируют самые разные подразделения и подрядчики. Каждый со своей колокольни. Поэтому целостности в документации нет, никто до конца не понимает логику и структуру хранения данных. Не говоря о том, что сроки вечно горят и на документирование не хватает времени.

Обычная история: в таблице клиентов есть поле «СНИЛС», на бумаге очень важное. Но когда я смотрю в данные, то вижу — поле пустое. В итоге заказчик соглашается, что целевая база обойдется без поля для СНИЛС, раз данных все равно нет.

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

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

Нервы еще не пришли в порядок, и Анатолий целиком вбивает ФИО нового клиента в поле для фамилии. Про день рождения начисто забывает — в форме остается дефолтное »01.01.1900 г». Наплевать на регламенты, когда все вокруг так бесит!!! Поэтому хаос побеждает бизнес-процессы, очень стройные на бумаге.

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

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

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

Заполненность полей и null-значения

Самые простые проверки — на заполненность таблиц в целом и на заполненность отдельных полей. С них и начинаю.

Сколько всего заполненных строк в таблице. Самый простой запрос из возможных.

select count(*) from ;


Получаю первый результат.
Физические лица
Количество
Всего
99 966 324

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

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

select , count(*) as  cnt from  
where  is not null;


Первым попалось поле с днем рождения, и сразу любопытно: данные почему-то вообще не пришли.


Физические лица
Количество
Всего
99 966 324
ДР
0

Если в выгрузке все значения в поле — «NULL», первым делом смотрю в исходную систему. Возможно, там данные хранятся исправно, но их потеряли при миграции.

Вижу, что в системе-источнике дни рождения на месте. Иду к интеграторам: ребята, ошибка. Выяснилось, что в ETL-процессе неправильно отработала функция «decode». Код поправили, в следующей выгрузке проверим изменения.

Иду дальше, к полю с ИНН.


Физические лица
Количество
Всего
99 966 324
ДР
0
ИНН
65 136

В базе 100 миллионов человек, а ИНН заполнены только у 65 тысяч — это 0,07%. Такая слабая заполненность — сигнал, что поле в базе-приемнике, быть может, не нужно вовсе.

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

Добралась до флага удаления клиента.


Физические лица
Количество
Всего
99 966 324
ДР
0
ИНН
65 136
Флаг удаления
0

Флаги не заполнены. Это что же, компания не удаляет клиентов? Смотрю в исходную систему, разговариваю с заказчиком. Выходит, что да: флаг формальный, вместо удаления клиентов удаляют их счета. Нет счетов — клиента как бы удалили.

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

Дальше — табличка с адресами. Обычно в таких таблицах что-то не так, потому что адреса — штука сложная, вводят их по-разному.

Проверяю заполненность составляющих адреса.


Адреса
Количество
Всего
254 803 976
Страна
229 256 090
Индекс
46 834 777
Город
6 474 841
Улица
894 040
Дом
20 903

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

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

Во время анализа на заполненность я особняком ставлю поля, ссылающиеся на справочники. Условие «IS NOT NULL» с ними не работает: вместо «NULL» в ячейке обычно »0». Поэтому поля-справочники проверяю отдельно.

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

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

Заполненность всех полей.


Физические лица
Выгрузка 1
Выгрузка 2
Дельта
Всего
99 966 324
94 847 160
-5 119 164

Между выгрузками исчезли 5 миллионов записей. Иду к интеграторам, задаю типовые вопросы: 

«Почему потерялись записи?»; 
«Какие данные отсеяли?»; 
«Какие данные оставили?»



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

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


Физические лица
Выгрузка 1
Выгрузка 2
Дельта
Всего
99 966 324
94 847 160
-5 119 164
ДР
0
77 046 780
77 046 780

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

Что проверять, в двух словах.

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



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

Длина значений в строковых полях


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

Какие значения слишком короткие. Среди самых коротких значений полно мусорных, поэтому здесь интересно копнуть.

select * from  where length () < 3;


Таким способом я проверяю ФИО, телефоны, ИНН, ОКВЭД, адреса сайтов. Всплывает бессмыслица вроде «A*1»,»0»,»11»,»-» и »…».

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

select * from  where length() = 65;


Таким способом я нашла в поле с типом документа строку «Свидетельство о регистрации ходатайства иммигранта о признании ег». Рассказала интеграторам, длину поля поправили.

Как значения распределяются по длине. В HFLabs таблицу распределения строк по длине мы называем «частотка».

select length(), count() from  group by length();


Здесь я выискиваю аномалии в распределении по длине. Например, вот частотка для таблицы с почтовыми адресами.
Длина
Количество
122
120
123
90
124
130
125
1100
126
70

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

Что проверять, в двух словах.

Самые короткие значения в строковых полях. Часто строки меньше трех символов — это мусор.
Значения, которые «упираются» по длине в ширину поля. Часто они обрезаны.
Аномалии в распределении строк по длине.





Популярные значения


Я делю на три категории значения, попадающие в топ популярных: 

реально распространенные, как имя «Татьяна» или отчество «Владимирович». Здесь нужно помнить, что в общем случае «Татьяна» не должна быть в 100 раз популярнее, чем «Анна», а «Исмаил» едва ли может быть популярнее, чем «Егор»; 
мусорные, вроде ».»,»1»,»-» и тому подобных; 
дефолтные на форме ввода, как »01.01.1900» для дат.



Два случая из трех — маркеры проблемы, полезно их поискать.

Популярные значения я ищу в полях трех типов: 

Обычных строковых полях.
Строковых полях-справочниках. Это обычные строковые поля, но количество различных значений в них конечно и регламентировано. В таких полях хранят страны, города, месяцы, типы телефонов.
Полях-классификаторах — в них стоит ссылка на запись в сторонней таблице-классификаторе.



Поля каждого из этих типов изучаю немного по-разному.

Для строковых полей — каковы топ-100 популярных значений. Если хочется, можно взять и побольше, но в первые сто значений обычно помещаются все аномалии.

select * from (select , count(*) cnt from  group by  
order by 2 desc) where rownum <= 100;


Я проверяю таким способом поля: 

ФИО целиком, а также отдельно фамилии, имена и отчества; 
даты рождения и вообще любые даты; 
адреса́. Как полный адрес, так и отдельные его составляющие, если они хранятся в базе; 
телефоны; 
серию, номер, тип, место выдачи документов.



Почти всегда среди популярных — тестовые и дефолтные значения, какие-то заглушки.



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

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


Должность
Директор
Бухгалтер
Специалист
Секретарь
Системный администратор

Возможно, компания обслуживает только пять профессий. Не очень похоже на правду, верно? Скорее, в форме для операторов вместо строки сделали справочник и забыли отсыпать значений. Важный вопрос здесь: разумно ли вообще заполнять должности через справочник. Так через анализ данных я выхожу на возможные проблемы с операторским софтом.

Для полей-справочников и классификаторов проверяю, какова популярность всех значений. Для начала разбираюсь, какие поля — справочники. Скриптами здесь не обойтись, беру документацию и прикидываю. Обычно справочники создают для значений, число которых конечно и относительно невелико: 

страны, 
языки, 
валюты, 
месяцы, 
города.



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

select , count(*) cnt from  group by  order by 2 desc;


Обычно в строковых-полях справочниках лежит такое.


Место рождения
Количество
таджикистан
467 599
Таджикистан
410 484
Россия
292 585
ТАДЖИКИСТАН
234 465
россия
158 163
РОССИЯ
76 367

Типичные проблемы: 

опечатки; 
пробелы; 
разный регистр.



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

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


Пол
Тип телефона

Женский
Не определен


Домашний


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

Что проверять, в двух словах.

Какие строковые поля справочные, а какие — нет.
Для простых строковых полей — топ популярных значений. Обычно в топе мусор и дефолтные данные.
Для строковых полей-справочников — распределение всех значений по популярности. Выборка покажет разночтения в справочных значениях.
Для классификаторов — достаточно ли вариантов в базе.





Консистентность и кросс-сверки


От анализа данных внутри таблиц перехожу к анализу связей.

Связаны ли данные, которым положено быть связанными. Этот параметр мы называем «консистентность». Беру подчиненную таблицу, например, с телефонами. К ней в пару — родительскую таблицу клиентов. И смотрю, сколько в подчиненной таблице айдишников клиентов, которых нет в родительской.

select count(*) from ((select  from ) minus (select  from ));


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

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

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

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

Если таблиц со схожими сущностями несколько, делаю кросс-сверку: проверяю пересечение идентификаторов. Пересекаются — клеим заплатку. Например, собираем айдишники для единой таблицы по схеме «название исходной таблицы + ID».

Что проверять, в двух словах.



Сколько в связанных таблицах несвязанных данных.
Нет ли потенциальных конфликтов первичных ключей.





Что еще проверить


Нет ли латинских символов там, где им не место. Например, в фамилиях.

select  from  where regexp_like(, ’[A-Z]’, ’i’);


Так я отлавливаю замечательную латинскую букву «C», которая совпадает с кириллической. Ошибка неприятная, потому что по ФИО с латинской «C» оператор никогда не найдет клиента.

Не затесались ли посторонние символы в строковые поля, предназначенные для цифр.

select  from  where regexp_like(, ‘[^0-9]’);


Проблемы всплывают в полях с номером паспорта РФ или ИНН. Телефоны — то же самое, но там я разрешаю плюс, скобки и дефис. Запрос выявит и букву «O», которую поставили вместо нуля.
Насколько данные адекватны. Никогда не знаешь, где всплывет проблема, поэтому я всегда настороже. Встречала такие случаи: 
50 000 телефонов у клиента «Софья Владимировна» — это нормально?  Ответ: не нормально. Клиент технический, на него повесили «бесхозные» телефонные номера, чтобы делать sms-рассылки. Тянуть клиента в новую базу не нужно; 
ИНН заполнены, на поверку в столбце лежит »79853617764»,»89109462345»,»4956780966» и так далее. Что за телефоны, окуда? Где ИНН? Ответ: что за номера — неизвестно, кто положил — непонятно. Никто их не использует. Актуальный ИНН хранят в другом поле другой таблицы, забирать оттуда; 
поле «адрес одной строкой» не соответствует полям, в которых адрес хранится по частям. Почему адреса разные? Ответ: когда-то операторы заполняли адреса одной строкой, а внешняя система разобрала адреса по отдельным полям. Для сегментации. Шло время, люди меняли адреса. Операторы их исправно обновляли, но только в виде строки: адрес частями остался старый.

Все, что нужно — SQL и Excel


Чтобы анализировать данные, дорогое ПО не нужно. Хватает старого доброго Excel и знания SQL.
Excel я использую, чтобы собрать длинный запрос. Например проверяю поля на заполненность, а в таблице их 140. Писать руками буду до морковкиного заговения, поэтому собираю запрос формулами в excel-табличке.

В столбец «A» вставляю названия полей, беру их в документации или служебных таблицах. В колонке «B» — формула для склеивания запроса
Вставляю названия полей, пишу первую формулу в колонке «B», тяну за уголок — и готово.

Работает и в Excel, и в Google Docs, и в «Яндекс.Таблицах»
Анализ данных экономит вагон времени и спасает нервы менеджеров. С ним проще уложиться в дедлайн. Если проект крупный, аналитика сохранит миллионы рублей и репутацию.
Не цифры, а выводы


Сформулировала для себя правило: не показывать заказчику голые числа, эффекта все равно не добьешься. Моя задача — проанализировать данные и сделать выводы, а числа приложить как доказательство. Выводы — первичны, числа — вторичны.
Что я собираю для отчета: 
формулировки проблем в виде гипотезы или вопроса: «ИНН заполнены на 0,07%. Как вы используете эти данные, насколько они актуальны, как их трактовать? Только ли в одной таблице лежат ИНН?». Нельзя обвинять: «У вас ИНН не заполнены вообще». В ответ получишь только агрессию; 
примеры проблем. Это таблички, которых так много в статье; 
варианты, как можно сделать:  «Возможно, стоит убрать ИНН из целевой базы, чтобы не плодить пустые поля».

Я не имею права решать, что именно забирать из исходной базы и как менять данные при миграции. Поэтому с отчетом я иду к заказчику или интеграторам, и мы выясняем, как дальше быть.
Иногда заказчик, увидев проблему, отвечает: «Не парьтесь, не обращайте внимания. Закупим лишний терабайт памяти, да и все. Так дешевле, чем оптимизировать». Соглашаться на такое нельзя: если забирать все подряд, качества в приемнике не будет. Мигрируют все те же замусоренные избыточные данные.
Поэтому мы мягко, но неуклонно просим: «Расскажите, как будете использовать именно эти данные в целевой системе». Не «зачем нужны», а именно «как будете использовать». Ответы «потом придумаем» или «это на всякий случай» не годятся. Рано или поздно заказчик понимает, без каких данных можно обойтись.
Главное — найти и разрешить все вопросы, пока систему не запустили в прод. На живую менять архитектуру и модель данных — с ума сойдешь.
С базовой аналитикой на этом все, изучайте данные! 
HFLabs ищет аналитика-стажера с зарплатой от 50 000 ₽. Подходящего человека научим всему, о чем я рассказала в статье, и другим премудростям.
Вакансия подойдет технарям, которые хотят сменить профиль или еще не определились, в какой сфере хотят работать. Если интересно, присылайте отклики со страницы вакансии на hh.ru.
© Habrahabr.ru


                
                
                
                
            

                    

                    
                    
                    
                


            


            

                
О
                        проекте
                
Архив
                
                
Реклама
                    Партнёры
                    Правовая информация
                    Контакты
                    Обратная
                        связь