В данной статье описывается «нетрадиционная», но достаточно мощная технология обработки XML, позволяющая импортировать любые XML-данные и преобразовывать их структуру эффективно и просто, при этом один и тот же процесс обработки позволяет трансформировать исходные данные любой структуры без какого-либо изменения программного кода.
XML как промежуточный формат обмена данными «оброс» экосистемой технологий и инструментов для работы с ним– специализированные редакторы, DOM-парсеры, XQUERY/XPATH, XSLT, специальные модули ETL и т. д. Все это многообразие и развитость инструментария идеологически приводят к тому, что у нас есть теперь технологии работы не просто с данными, а со специальными XML-данными. Это как отдельно «наука химия» и отдельно «наука химия для веществ, которые хранятся в синих коробках».
Описываемые далее принципы обработки позволили немножко «отмотать назад» условный прогресс технологий и перевести практически всю работу с XML данными на уровень «чистой» СУБД, без специфичного XML-инструментария. Такой подход дал возможность организовать унифицированное хранение любых XML-данных и обеспечить быстрый произвольный доступ к нужным частям информации. Кроме того, появилась возможность реализовывать ETL-функционал универсальным незатратным (практически без кодирования) способом.
Описываемый подход особенно хорошо показал себя на источниках данных большого объема и сложной структуры, с частыми изменениями схемы данных. Если вам приходится иметь дело с небольшим объемом информации, и/или структура несложная– возможно, данная технология вам (пока) не нужна.
Хорошим демо-кейсом для этой технологии могут служить открытые данные сервера госзакупок zakupki.gov.ru, доступные на соответствующем FTP: объём ежедневных обновлений – десятки и сотни тысяч XML файлов объемом в гигабайты или десятки гигабайт, в среднем раз в несколько недель выходит новая версия схемы данных.
Структура данных следует за требованиями законодательства, поэтому, например, информация об извещениях о проведении госзакупок представлена более чем десятком типов документов fcsNotification* в зависимости от типа закупки (электронный аукцион fcsNotificationEF, запрос котировок fcsNotificationZK, закупка у единственного поставщика fcsNotificationEP и т. п.)
Все эти документы основаны на одном базовом типе извещения но отличаются в деталях, поэтому для целей анализа все это многообразие при импорте надо в какой-то момент “схлопывать” и приводить к «единому знаменателю».
На данных госзакупок описываемый подход успешно применен и эффективно работает.
Кратко этапы/элементы описываемой технологии:
(1) Импорт всех XML данных в таблицу унифицированной структуры. Речь не идет о сохранении в базу документов целиком, мы импортируем данные поэлементно как пары “имя элемента” – “значение” или “имя атрибута” – “значение”. В результате данного этапа мы избавляемся от XML как формата хранения и получаем быстрый унифицированный доступ к данным всех импортированных XML-документов любой структуры (при этом нам больше не нужен XQUERY/XPATH).
(2) Вторым элементом технологии является создание спецификаций на “вытаскивание” нужных нам данных – выявление результирующих таблиц, в которые мы будем выливать данные, и маппинг полей источника и результата. Этот этап может быть проведен как на основе XSD-схем документов, так и без использования схем, через анализ закачанных на первом этапе образцов документов. Данный этап не требует никакого программирования и специальных навыков, основной инструментарий здесь – электронная таблица.
(3) Завершающие шаги – выборка нужной информации из первичного хранилища импорта (1) с помощью спецификаций (2), преобразование в “колоночное” представление (“пивотирование”) и автоматизированная трансформация в финальный “аналитический” формат – в терминах аналитических хранилищ данных это таблицы фактов структуры “звездочка” (star) со ссылками на справочники измерений (dimensions) и числовыми показателями-мерами (measures).
Мы рассматриваем XML-документ как дерево, вершинами которого являются пары “имя”-“значение”. Таким образом, описываемый подход достаточно универсален и может быть применен к любому древовидному представлению данных.
Структура таблицы для загрузки данных из XML:
Идея загрузки дерева в таблицу достаточно очевидная. В MS SQL (про другие СУБД не скажу, не смотрел) есть такая встроенная возможность –XML без указания схемы импортируется в так называемую EDGE-таблицу. Это не совсем то что нам нужно, т. к. в EDGE-формате хранятся отдельными записями имя элемента и его значение (то есть имя есть родительская запись для значения) – такой формат попросту неудобно использовать для дальнейших манипуляций. К тому же в EDGE таблице связи в дереве прописаны через указание ParentID.
Короче говоря, сделать нужное представление данных из EDGE таблицы можно, но придется немножко попотеть для “склеивания” названий и значений элементов, воссоздания XPATH до каждого элемента и создания иерархического идентификатора (о том, как мы его будем строить – чуть ниже). При большом объеме данных решение этих задач может оказаться довольно ресурсоемким, но зато можно обойтись единственным инструментом/языком.
Более правильный путь – получить дерево документа с помощью XML-парсера (какая-нибудь реализация есть практически в каждом языке и среде разработки) и заполнить нужную информацию одним проходом по документу.
Давайте посмотрим на конкретный пример. Есть у нас демо XML-файлы deliveries.xml и returns.xml. Файл deliveries.xml (доставки) содержит корневой элемент Deliveries, на верхнем уровне даты начала и окончания периода за который выгружены данные, дальше идут продукты с указанием названия и поставщика, по каждому продукту идет детализация информации доставок – дата, количество, цена.
Файл returns.xml (возвраты) абсолютно аналогичный, только корневой элемент называется Returns и в деталях элемент с датой по-другому называется.
Имена загруженных файлов хранятся в отдельной таблице, коды наших файлов там равны 2006 (deliveries) и 2007 (returns).
В нашей таблице-приемнике образ наших демо-документов будет выглядеть так:
По поводу иерархического идентификатора Record_ID: его цель — уникально пронумеровать узлы дерева документа с сохранением информации о связях со всеми предками.
В приведенном примере мы используем простую платформенно-независимую реализацию c последовательной конкатенацией счетчиков элементов на каждом уровне дерева. Мы “добиваем” счетчик каждого уровня нулями до заданной фиксированной глубины, чтобы получить легкое и быстрое выделение идентификаторов предков любого уровня через выделение подстрок фиксированной длины.
Вот собственно и все на этом этапе. Теперь мы можем простыми SQL-запросами выделить подмножества данных продуктов, деталей по доставкам или возвратам и связать их друг с другом через идентификаторы файлов и элементов.
Во многих случаях этого подхода будет вполне достаточно для эффективного хранения данных и организации доступа к ним, описанные далее “продвинутые” техники могут не понадобиться.
Действительно, мы уже загрузили все наши данные в единое хранилище, не обращая внимания на возможную разницу в структуре документов, получили достаточно эффективный способ вычленять нужную информацию из всего массива простыми SQL-запросами, можем связывать между собой “вычлененные” подмножества данных.
Производительность такого решения как системы хранения исходников XML для разовых запросов, даже без оптимизации индексов и прочих ухищрений, будет явно гораздо выше, чем если бы вы хранили XML в файлах или даже записывали его в специальные поля БД. В нашем случае не надо запускать процедуру XPATH поиска (которая подразумевает новый парсинг) к каждому документу, мы это проделали один раз и дальше спокойно пользуемся сохраненным результатом через достаточно простые запросы.
На следующих этапах мы рассмотрим трансформацию этих XML документов в единую структуру данных, содержащую 3 таблицы: MovementReports (тип движения – доставка или возврат, даты начала и окончания из корня документа), Products (название и поставщик) и MovementDetails (цена, количество, дата – поле даты в результате будет единое для обоих исходных документов, несмотря на то, что в исходных файлах поля по-разному называются)
Рассмотрим процесс создания спецификаций на маппинг исходных данных и результирующих таблиц. Для создания таких спецификаций нам потребуется еще кое-что.
Для дальнейшей обработки нужно иметь развернутую структуру всех наших XML-документов, чтобы на ее основе решать в какую структуру таблиц мы все это будем преобразовать.
Одного конкретного образца XML-документа для этой задачи нам мало, в конкретном документе может не быть каких-то необязательных элементов, которые неожиданно обнаружатся в других документах.
Если у нас нет XSD-схемы или мы не хотим с ней связываться, то нам может быть достаточно загрузить в нашу таблицу какую-то репрезентативную выборку образцов XML-документов и построить с помощью группировки по полям Path и Element_Name нужный нам список.
Однако не забывайте, что мы хотим в итоге загрузить информацию в некие целевые “финальные” таблицы, поэтому нам надо знать где в XML хранятся отношения один-ко-многим. То есть надо понять какие элементы формируют “дочернюю” таблицу, где происходит размножение.
Иногда, если схема документа не очень сложная, нам это сразу понятно эмпирически, “глазками”. Также при группировке данных нашей “репрезентативной выборки” мы можем посчитать количество элементов и увидеть по этой статистике где они начинают “размножаться”. Но в общем случае, если у нас есть нормальная XSD-схема, лучше воспользоваться ей – размножение данных один ко многим мы “поймаем”, выявив XSD конструкцию maxoccurs=unbounded.
Как видим, задача немного усложняется: хочется получить не только простую табличку, содержащую список XPATH-путей для всех элементов наших документов, но еще и с указанием того, где начинается размножение данных. (А при анализе хорошо прописанной XSD-схемы приятном бонусом могли бы получить возможность вытащить описания элементов и их типы.)
Очень хорошо было бы для получения такой таблички использовать функциональность какого-нибудь XML-редактора, однако найти такой инструмент, который бы выдавал нужную нам структуру документов по XSD-схеме, не удалось (искали и пробовали долго).
Во всех этих Oxygen, Altova, Liquid и менее навороченных нужная информация внутри, несомненно, используется – однако отдавать ее в нужном виде никто из них не умеет. Как правило, в продвинутом редакторе есть возможность генерировать Sample XML на основании схемы, но в XSD может быть конструкция choice, когда в документе может присутствовать что-то на выбор из нескольких разных элементов –тогда уж лучше реальные “боевые” образцы документов проанализировать. И еще — по образцу или образцам документов мы момент размножения информации один-ко-многим в явном виде тоже не поймаем.
В итоге пришлось изобретать велосипед и писать генератор такой таблички (фактически, парсер XSD специального вида) самостоятельно. Благо XSD это тоже XML, его можно так же загрузить в наше хранилище и реляционными операциями вытащить нужный вид. Если схема простая, без ссылок на сложные типы элементов и без наследования от базовых типов, то это достаточно просто. В случае, когда все это наследование типов в наличии (как в госзакупках, например), задача посложнее.
Получив исходные данные в виде табличной спецификации документов, переходим к дизайну трансформации данных. В результате этой трансформации мы преобразуем данные нашего исходного хранилища первичного XML в новые таблицы с новыми именами полей и уникальными кодами записей целевых таблиц. Нам не нужны какие-то специальные программные инструменты, просто добавим к этой табличке несколько новых столбцов:
Вот какие поля мы добавили:
Эта техника открывает несколько интересных возможностей манипуляции трансформацией данных. Не углубляясь в детали, просто перечислим что мы можем сделать:
После того, как наша табличка со спецификацией трансформации готова, мы загружаем ее в базу данных и джойним с нашим первичным хранилищем по полям Path и Element_Name.
Конкатенацией File_ID, “обрезанного” в соответствии с ElementDepth значения поля Record_ID и значения AdditionalInfo формируем композитный ключ нашей целевой таблицы.
Результат джойна выливаем для каждой целевой таблицы в отдельную “временную” таблицу (в зависимости от объема данных можно попробовать использовать результат запроса “ на лету ”), на следующем этапе с этими таблицами будут работать завершающие утилиты нашего “конвейера”.
Посмотрим, что получилось на выходе в нашем примере:
Обратите внимание – полученный ключ одинаков для всех полей, которые войдут в соответствующие записи наших целевых таблиц.
Получив в результате предыдущей трансформации “заготовку” целевой таблицы с исходными данными, разбитыми на тройки <ключ>-<имя поля>-<значение>, мы должны перевести ее в более привычный вид таблицы со множеством полей. Алгоритм этого преобразования очевиден – сначала группировкой значений нашего композитного ключа получаем “скелет” таблицы, потом осуществляем джойны этого “скелета” с таблицей-результатом трансформации по значению композитного ключа. (“Наращиваем мясо”, так сказать.)
То есть получится N соединений “скелета” с подмножествами таблицы- результата трансформации, выделенными по именам полей, где N- количество названий полей в целевой таблице-результате трансформации.
Мы благополучно “донесли” поле AdditionalInfo до данной стадии, закодировав его внутри композитного ключа. Теперь надо освободить наш ключ от этой “обузы” и отрезать AdditionalInfo-часть в новое поле AdditionalInfo_Dim.
Мы соединяли код файла и идентификатор записи, чтобы передать на этап пивотирования ключ одним полем. Для “финального” хранения лучше обратно разделить код файла и иерархический идентификатор на два поля, так будет проще связывать результирующие таблицы друг с другом.
В итоге получатся такие вот
Следующий этап условно можно назвать нормализацией, мы заменим все поля с суффиксом _Dim ссылками на соответствующие справочники, поля с суффиксами _Date и _Val преобразуем, соответственно, в даты и числа. Можно при необходимости и другие суффиксы типов данных использовать, это не догма.
Для каждого поля _Dim мы проверим наличие соответствующего справочника (если справочника нет, то создадим его) и дополним новыми значениями из поля пивотированной таблицы.
В конце процесса перельем данные в финальную структуру хранения, соединив пивотированную таблицу с проапдейченными справочниками по значениям полей.
В итоге мы получим наши целевые таблицы, в которых будет содержаться код файла, иерархический идентификатор, ссылки на значения справочников и преобразованные к нужным типам остальные поля.
Иерархический идентификатор в каждой целевой таблице дает нам возможность связать эти таблицы друг с другом (причем в любом порядке, при необходимости можно опускать промежуточные звенья). Как отмечалось выше, фиксированный размер элементов иерархического идентификатора дает нам возможность достаточно легко строить выражения для связывания наших целевых таблиц.
Конечно же, при желании можно дополнить структуру таблиц связями через значения привычных автоинкрементных ключей.
Отметим достаточную толерантность описанного подхода к ошибкам и упущениям разработчика (точнее, человека, конфигурирующего маппинг). Если в процессе подготовки трансформации вы упустили нужное поле, или если в схеме файлов произошли изменения типа добавления или переименования полей, которые не были отслежены вовремя – ничего страшного, даже если большой объем информации уже обработан без учета этой ошибки.
Вся информация у нас сохранена и доступна в нашем “первичном” табличном хранилище, поэтому заново парсить исходники не придется.
Можно прописать маппинг полей для «маленькой» трансформации данных – только для нужного нам пропущенного фрагмента. После того как мы “вытащим” недостающий кусок данных, мы сможем достаточно легко “подцепить” его к основному обработанному массиву через связку по иерархическому идентификатору.
Как видим, мы с самого начала поместили всю исходную информацию вне зависимости от схем документов в единообразный вид, позволяющий быстро “вытаскивать” нужные данные в режиме ad-hoc.
В процессе дальнейшей обработкини одно животное не пострадало не возникло необходимости в создании или модификации каких-либо скриптов или объектов типа ETL task, специфичных для структуры исходных XML-файлов.
Конечно, само программное окружение нашего “фреймворка” (инфраструктуру закачки и переливок данных) нужно было создать один раз, но затем интеграция данных любых структур XML сводится к редактированию таблицы маппинга исходного и целевого представлений.
XML как промежуточный формат обмена данными «оброс» экосистемой технологий и инструментов для работы с ним– специализированные редакторы, DOM-парсеры, XQUERY/XPATH, XSLT, специальные модули ETL и т. д. Все это многообразие и развитость инструментария идеологически приводят к тому, что у нас есть теперь технологии работы не просто с данными, а со специальными XML-данными. Это как отдельно «наука химия» и отдельно «наука химия для веществ, которые хранятся в синих коробках».
Описываемые далее принципы обработки позволили немножко «отмотать назад» условный прогресс технологий и перевести практически всю работу с XML данными на уровень «чистой» СУБД, без специфичного XML-инструментария. Такой подход дал возможность организовать унифицированное хранение любых XML-данных и обеспечить быстрый произвольный доступ к нужным частям информации. Кроме того, появилась возможность реализовывать ETL-функционал универсальным незатратным (практически без кодирования) способом.
Описываемый подход особенно хорошо показал себя на источниках данных большого объема и сложной структуры, с частыми изменениями схемы данных. Если вам приходится иметь дело с небольшим объемом информации, и/или структура несложная– возможно, данная технология вам (пока) не нужна.
Хорошим демо-кейсом для этой технологии могут служить открытые данные сервера госзакупок zakupki.gov.ru, доступные на соответствующем FTP: объём ежедневных обновлений – десятки и сотни тысяч XML файлов объемом в гигабайты или десятки гигабайт, в среднем раз в несколько недель выходит новая версия схемы данных.
Структура данных следует за требованиями законодательства, поэтому, например, информация об извещениях о проведении госзакупок представлена более чем десятком типов документов fcsNotification* в зависимости от типа закупки (электронный аукцион fcsNotificationEF, запрос котировок fcsNotificationZK, закупка у единственного поставщика fcsNotificationEP и т. п.)
Все эти документы основаны на одном базовом типе извещения но отличаются в деталях, поэтому для целей анализа все это многообразие при импорте надо в какой-то момент “схлопывать” и приводить к «единому знаменателю».
На данных госзакупок описываемый подход успешно применен и эффективно работает.
Кратко этапы/элементы описываемой технологии:
(1) Импорт всех XML данных в таблицу унифицированной структуры. Речь не идет о сохранении в базу документов целиком, мы импортируем данные поэлементно как пары “имя элемента” – “значение” или “имя атрибута” – “значение”. В результате данного этапа мы избавляемся от XML как формата хранения и получаем быстрый унифицированный доступ к данным всех импортированных XML-документов любой структуры (при этом нам больше не нужен XQUERY/XPATH).
(2) Вторым элементом технологии является создание спецификаций на “вытаскивание” нужных нам данных – выявление результирующих таблиц, в которые мы будем выливать данные, и маппинг полей источника и результата. Этот этап может быть проведен как на основе XSD-схем документов, так и без использования схем, через анализ закачанных на первом этапе образцов документов. Данный этап не требует никакого программирования и специальных навыков, основной инструментарий здесь – электронная таблица.
(3) Завершающие шаги – выборка нужной информации из первичного хранилища импорта (1) с помощью спецификаций (2), преобразование в “колоночное” представление (“пивотирование”) и автоматизированная трансформация в финальный “аналитический” формат – в терминах аналитических хранилищ данных это таблицы фактов структуры “звездочка” (star) со ссылками на справочники измерений (dimensions) и числовыми показателями-мерами (measures).
1. Первичный импорт.
Мы рассматриваем XML-документ как дерево, вершинами которого являются пары “имя”-“значение”. Таким образом, описываемый подход достаточно универсален и может быть применен к любому древовидному представлению данных.
Структура таблицы для загрузки данных из XML:
- Record_ID: идентификатор элемента специального иерархического вида, позволяющий связывать друг с другом разные уровни документа
- File_ID: поскольку в таблицу надо будет загружать содержимое множества XML-файлов, надо также хранить идентификатор файла
- Path: полный путь к данному элементу начиная с корня документа (фактически, это XPATH-путь до данного элемента)
- Element_Name: название элемента или атрибута
- Element _Value: значение элемента или атрибута (в виде строки – так же, как оно хранится в XML)
- Type: тип записи (элемент это или атрибут) – сохраним на всякий случай, вдруг потом надо будет из таблицы восстановить XML
Идея загрузки дерева в таблицу достаточно очевидная. В MS SQL (про другие СУБД не скажу, не смотрел) есть такая встроенная возможность –XML без указания схемы импортируется в так называемую EDGE-таблицу. Это не совсем то что нам нужно, т. к. в EDGE-формате хранятся отдельными записями имя элемента и его значение (то есть имя есть родительская запись для значения) – такой формат попросту неудобно использовать для дальнейших манипуляций. К тому же в EDGE таблице связи в дереве прописаны через указание ParentID.
Короче говоря, сделать нужное представление данных из EDGE таблицы можно, но придется немножко попотеть для “склеивания” названий и значений элементов, воссоздания XPATH до каждого элемента и создания иерархического идентификатора (о том, как мы его будем строить – чуть ниже). При большом объеме данных решение этих задач может оказаться довольно ресурсоемким, но зато можно обойтись единственным инструментом/языком.
Более правильный путь – получить дерево документа с помощью XML-парсера (какая-нибудь реализация есть практически в каждом языке и среде разработки) и заполнить нужную информацию одним проходом по документу.
Давайте посмотрим на конкретный пример. Есть у нас демо XML-файлы deliveries.xml и returns.xml. Файл deliveries.xml (доставки) содержит корневой элемент Deliveries, на верхнем уровне даты начала и окончания периода за который выгружены данные, дальше идут продукты с указанием названия и поставщика, по каждому продукту идет детализация информации доставок – дата, количество, цена.
deliveries.xml
<Deliveries>
<PeriodBegin>2017-01-01</PeriodBegin>
<PeriodEnd>2017-01-31</PeriodEnd>
<Products>
<Product>
<Supplier>Zaanse Snoepfabriek</Supplier>
<ProductName>Chocolade</ProductName>
<Details>
<Detail>
<DeliveryDate>2017-01-03</DeliveryDate>
<UnitPrice>10.2000</UnitPrice>
<Quantity>70</Quantity>
</Detail>
</Details>
</Product>
<Product>
<Supplier>Mayumi's</Supplier>
<ProductName>Tofu</ProductName>
<Details>
<Detail>
<DeliveryDate>2017-01-09</DeliveryDate>
<UnitPrice>18.6000</UnitPrice>
<Quantity>12</Quantity>
</Detail>
<Detail>
<DeliveryDate>2017-01-13</DeliveryDate>
<UnitPrice>18.7000</UnitPrice>
<Quantity>20</Quantity>
</Detail>
</Details>
</Product>
</Products>
</Deliveries>
Файл returns.xml (возвраты) абсолютно аналогичный, только корневой элемент называется Returns и в деталях элемент с датой по-другому называется.
returns.xml
<Returns>
<PeriodBegin>2017-02-01</PeriodBegin>
<PeriodEnd>2017-02-28</PeriodEnd>
<Products>
<Product>
<Supplier>Pavlova, Ltd.</Supplier>
<ProductName>Pavlova</ProductName>
<Details>
<Detail>
<ReturnDate>2017-02-21</ReturnDate>
<UnitPrice>13.9000</UnitPrice>
<Quantity>2</Quantity>
</Detail>
</Details>
</Product>
<Product>
<Supplier>Formaggi Fortini s.r.l.</Supplier>
<ProductName>Mozzarella di Giovanni</ProductName>
<Details>
<Detail>
<ReturnDate>2017-02-27</ReturnDate>
<UnitPrice>27.8000</UnitPrice>
<Quantity>4</Quantity>
</Detail>
</Details>
</Product>
</Products>
</Returns>
Имена загруженных файлов хранятся в отдельной таблице, коды наших файлов там равны 2006 (deliveries) и 2007 (returns).
В нашей таблице-приемнике образ наших демо-документов будет выглядеть так:
(Тут не все, только начало таблицы)
| Record_ID | File_ID | Path | Element_Name | Element_Value | Type |
| 001 | 2006 | Deliveries | E | ||
| 001\001 | 2006 | Deliveries\ | PeriodBegin | 2017-01-01 | E |
| 001\002 | 2006 | Deliveries\ | PeriodEnd | 2017-01-31 | E |
| 001\003 | 2006 | Deliveries\ | Products | E | |
| 001\003\001 | 2006 | Deliveries\Products\ | Product | E | |
| 001\003\001\001 | 2006 | Deliveries\Products\ Product\ |
Supplier | Zaanse Snoepfabriek | E |
| 001\003\001\002 | 2006 | Deliveries\Products\ Product\ |
ProductName | Chocolade | E |
| 001\003\001\003 | 2006 | Deliveries\Products\ Product\ |
Details | E | |
| 001\003\001\003\ 001 |
2006 | Deliveries\Products\ Product\Details\ |
Detail | E | |
| 001\003\001\003\ 001\001 |
2006 | Deliveries\Products\ Product\Details\Detail\ |
DeliveryDate | 2017-01-03 | E |
| 001\003\001\003\ 001\002 |
2006 | Deliveries\Products\ Product\Details\Detail\ |
UnitPrice | 10.2000 | E |
| 001\003\001\003\ 001\003 |
2006 | Deliveries\Products\ Product\Details\Detail\ |
Quantity | 70 | E |
По поводу иерархического идентификатора Record_ID: его цель — уникально пронумеровать узлы дерева документа с сохранением информации о связях со всеми предками.
К сведению:
В том же SQL Server есть специальный тип данных (объектное расширение) под названием hierarchyid, служащий для этих целей.
В приведенном примере мы используем простую платформенно-независимую реализацию c последовательной конкатенацией счетчиков элементов на каждом уровне дерева. Мы “добиваем” счетчик каждого уровня нулями до заданной фиксированной глубины, чтобы получить легкое и быстрое выделение идентификаторов предков любого уровня через выделение подстрок фиксированной длины.
Вот собственно и все на этом этапе. Теперь мы можем простыми SQL-запросами выделить подмножества данных продуктов, деталей по доставкам или возвратам и связать их друг с другом через идентификаторы файлов и элементов.
Во многих случаях этого подхода будет вполне достаточно для эффективного хранения данных и организации доступа к ним, описанные далее “продвинутые” техники могут не понадобиться.
Действительно, мы уже загрузили все наши данные в единое хранилище, не обращая внимания на возможную разницу в структуре документов, получили достаточно эффективный способ вычленять нужную информацию из всего массива простыми SQL-запросами, можем связывать между собой “вычлененные” подмножества данных.
Производительность такого решения как системы хранения исходников XML для разовых запросов, даже без оптимизации индексов и прочих ухищрений, будет явно гораздо выше, чем если бы вы хранили XML в файлах или даже записывали его в специальные поля БД. В нашем случае не надо запускать процедуру XPATH поиска (которая подразумевает новый парсинг) к каждому документу, мы это проделали один раз и дальше спокойно пользуемся сохраненным результатом через достаточно простые запросы.
Однако:
На этом этапе мы пока не сделали так, чтобы DeliveryDate, Quantity и UnitPrice стали полями одной таблицы, это описывается как процесс “пивотирования” в третьем разделе.
На следующих этапах мы рассмотрим трансформацию этих XML документов в единую структуру данных, содержащую 3 таблицы: MovementReports (тип движения – доставка или возврат, даты начала и окончания из корня документа), Products (название и поставщик) и MovementDetails (цена, количество, дата – поле даты в результате будет единое для обоих исходных документов, несмотря на то, что в исходных файлах поля по-разному называются)
2. Создание спецификаций трансформации в результирующие таблицы.
Рассмотрим процесс создания спецификаций на маппинг исходных данных и результирующих таблиц. Для создания таких спецификаций нам потребуется еще кое-что.
2.1. Получение таблички со структурой документов.
Для дальнейшей обработки нужно иметь развернутую структуру всех наших XML-документов, чтобы на ее основе решать в какую структуру таблиц мы все это будем преобразовать.
Одного конкретного образца XML-документа для этой задачи нам мало, в конкретном документе может не быть каких-то необязательных элементов, которые неожиданно обнаружатся в других документах.
Если у нас нет XSD-схемы или мы не хотим с ней связываться, то нам может быть достаточно загрузить в нашу таблицу какую-то репрезентативную выборку образцов XML-документов и построить с помощью группировки по полям Path и Element_Name нужный нам список.
Однако не забывайте, что мы хотим в итоге загрузить информацию в некие целевые “финальные” таблицы, поэтому нам надо знать где в XML хранятся отношения один-ко-многим. То есть надо понять какие элементы формируют “дочернюю” таблицу, где происходит размножение.
Иногда, если схема документа не очень сложная, нам это сразу понятно эмпирически, “глазками”. Также при группировке данных нашей “репрезентативной выборки” мы можем посчитать количество элементов и увидеть по этой статистике где они начинают “размножаться”. Но в общем случае, если у нас есть нормальная XSD-схема, лучше воспользоваться ей – размножение данных один ко многим мы “поймаем”, выявив XSD конструкцию maxoccurs=unbounded.
Как видим, задача немного усложняется: хочется получить не только простую табличку, содержащую список XPATH-путей для всех элементов наших документов, но еще и с указанием того, где начинается размножение данных. (А при анализе хорошо прописанной XSD-схемы приятном бонусом могли бы получить возможность вытащить описания элементов и их типы.)
Очень хорошо было бы для получения такой таблички использовать функциональность какого-нибудь XML-редактора, однако найти такой инструмент, который бы выдавал нужную нам структуру документов по XSD-схеме, не удалось (искали и пробовали долго).
Во всех этих Oxygen, Altova, Liquid и менее навороченных нужная информация внутри, несомненно, используется – однако отдавать ее в нужном виде никто из них не умеет. Как правило, в продвинутом редакторе есть возможность генерировать Sample XML на основании схемы, но в XSD может быть конструкция choice, когда в документе может присутствовать что-то на выбор из нескольких разных элементов –тогда уж лучше реальные “боевые” образцы документов проанализировать. И еще — по образцу или образцам документов мы момент размножения информации один-ко-многим в явном виде тоже не поймаем.
В итоге пришлось изобретать велосипед и писать генератор такой таблички (фактически, парсер XSD специального вида) самостоятельно. Благо XSD это тоже XML, его можно так же загрузить в наше хранилище и реляционными операциями вытащить нужный вид. Если схема простая, без ссылок на сложные типы элементов и без наследования от базовых типов, то это достаточно просто. В случае, когда все это наследование типов в наличии (как в госзакупках, например), задача посложнее.
Для нашего примера мы получим примерно вот такую таблицу структуры документов:
| Path | Element_Name | maxoccurs |
| Deliveries\ | PeriodBegin | |
| Deliveries\ | PeriodEnd | |
| Deliveries\Products\Product\ | ProductName | Products\Product |
| Deliveries\Products\Product\ | Supplier | Products\Product |
| Deliveries\Products\Product\Details\Detail\ | DeliveryDate | Products\Product\Details\Detail |
| Deliveries\Products\Product\Details\Detail\ | Quantity | Products\Product\Details\Detail |
| Deliveries\Products\Product\Details\Detail\ | UnitPrice | Products\Product\Details\Detail |
| Returns\ | PeriodBegin | |
| Returns\ | PeriodEnd | |
| Returns\Products\Product\ | ProductName | Products\Product |
| Returns\Products\Product\ | Supplier | Products\Product |
| Returns\Products\Product\Details\Detail\ | Quantity | Products\Product\Details\Detail |
| Returns\Products\Product\Details\Detail\ | ReturnDate | Products\Product\Details\Detail |
| Returns\Products\Product\Details\Detail\ | UnitPrice | Products\Product\Details\Detail |
2.2. Описание трансформации
Получив исходные данные в виде табличной спецификации документов, переходим к дизайну трансформации данных. В результате этой трансформации мы преобразуем данные нашего исходного хранилища первичного XML в новые таблицы с новыми именами полей и уникальными кодами записей целевых таблиц. Нам не нужны какие-то специальные программные инструменты, просто добавим к этой табличке несколько новых столбцов:
В приведенном примере некоторые поля представлены с сокращениями для улучшения читабельности.
| Path | Element_ Name |
maxoccurs | Target Table |
Target Field |
Element Depth |
Additional Info |
| Deliveries\ | PeriodBegin | MovementReports | PeriodBegin_Date | 1 | Deliveries | |
| Deliveries\ | PeriodEnd | MovementReports | PeriodEnd_Date | 1 | Deliveries | |
| Deliveries\...\Product\ | ProductName | …\Product | Products | ProductName_Dim | 3 | |
| Deliveries\...\Product\ | Supplier | …\Product | Products | Supplier_Dim | 3 | |
| Deliveries\...\...\...\Detail\ | DeliveryDate | …\Detail | MovementDetails | MovementDate_Date | 5 | |
| Deliveries\...\...\...\Detail\ | Quantity | …\Detail | MovementDetails | Quantity_Val | 5 | |
| Deliveries\...\...\...\Detail\ | UnitPrice | …\Detail | MovementDetails | UnitPrice_Val | 5 | |
| Returns\ | PeriodBegin | MovementReports | PeriodBegin_Date | 1 | Returns | |
| Returns\ | PeriodEnd | MovementReports | PeriodEnd_Date | 1 | Returns | |
| Returns\...\Product\ | ProductName | …\Product | Products | ProductName_Dim | 3 | |
| Returns\...\Product\ | Supplier | …\Product | Products | Supplier_Dim | 3 | |
| Returns\...\...\...\Detail\ | Quantity | …\Detail | MovementDetails | MovementDate_Date | 5 | |
| Returns\...\...\...\Detail\ | ReturnDate | …\Detail | MovementDetails | Quantity_Val | 5 | |
| Returns\...\...\...\Detail\ | UnitPrice | …\Detail | MovementDetails | UnitPrice_Val | 5 |
Вот какие поля мы добавили:
- Имя целевой таблицы TargetTable. Обратите внимание, что мы учитываем информацию о размножении один-ко-многим (столбец maxoccurs) для определения, в какую таблицу какие данные заливать.
- Имя поля целевой таблицы TargetField. Мы далее используем подход сonvention over configuration и будем присваивать суффикс _Dim для полей, которые станут справочниками-измерениями (dimensions), суффикс _Date для полей дат и суффикс _Val для числовых полей- мер (measures). На следующих этапах процесса соответствующие утилиты по суффиксу поймут что делать с данным полем – строить и обновлять нужный справочник или преобразовывать значение в соответствующий формат.
- Эффективная глубина вложенности элементов ElementDepth. Нам надо будет для последующих трансформаций сохранить единый код записи целевой таблицы на базе содержимого полей Record_ID и File_ID. В XML глубина элементов может быть разной, но попадать они должны будут в одну целевую таблицу, поэтому мы указываем, какую часть иерархического кода Record_ID нам надо сохранить, отбросив ненужный нам остаток. Благодаря фиксированной длине каждого сегмента иерархического кода, это будет достаточно “дешевая” операция выделения подстрок длины [Количество символов на сегмент кода]* ElementDepth.
- Дополнительная информация AdditionalInfo. В результате нашей трансформации мы перегрузим исходные данные в разбивке по целевым таблицам в похожую структуру с новыми названиями полей, однако в некоторых местах нам надо будет сохранить важную информацию о том, из какого именно XPATH-пути мы брали исходные данные.
Эта техника открывает несколько интересных возможностей манипуляции трансформацией данных. Не углубляясь в детали, просто перечислим что мы можем сделать:
- Если у нас есть несколько исходных схем кодирования XML-данных (например, “старая” схема госзакупок по 95 ФЗ и “новая” по 44 ФЗ), мы можем на этапе описания трансформации привести их к единой структуре данных через унификацию названий полей.
- Можем, как упоминалось ранее, “схлопнуть” разные разделы в рамках одной или нескольких схем документов в единую, более компактную структуру хранения, как мы это делаем в нашем примере.
- Можно также “схлопнуть” в единообразное представление данные разной глубины. Например, если брать схемы извещений о проведении госзакупок, то в разных схемах информация о лотах закупки может лежать “по адресу” \lot для однолотовых закупок (единственный элемент) и \lots\lot для многолотовых закупок (размножение). С помощью данного подхода можно достаточно просто весь этот зоопарк замаппить в единую таблицу информации о лотах.
После того, как наша табличка со спецификацией трансформации готова, мы загружаем ее в базу данных и джойним с нашим первичным хранилищем по полям Path и Element_Name.
Конкатенацией File_ID, “обрезанного” в соответствии с ElementDepth значения поля Record_ID и значения AdditionalInfo формируем композитный ключ нашей целевой таблицы.
Результат джойна выливаем для каждой целевой таблицы в отдельную “временную” таблицу (в зависимости от объема данных можно попробовать использовать результат запроса “ на лету ”), на следующем этапе с этими таблицами будут работать завершающие утилиты нашего “конвейера”.
Немного подробнее о композитном ключе, может быть, немного повторяясь – но это важный и тонкий момент:
- До выливки мы имеем набор данных на уровне отдельных полей (элементов). Для того, чтобы соединить поля в записи результирующей таблицы, нам нужно иметь какой-то ключ, который будет однозначно идентифицировать запись целевой таблицы, в которую попадут соответствующие поля.
- Иерархический идентификатор Record_ID может быть разной длины, в зависимости от “глубины залегания” отдельных элементов в схеме документа. Однако, если углубление уровня не сопровождается размножением элементов один-ко-многим, мы обрезаем наш Record_ID до минимальной достаточной глубины, определенной параметром ElementDepth, что обеспечит нам одинаковость идентификатора для всех полей нашей целевой таблицы. В наших демо-документах такой ситуации нет, но представьте, к примеру, что наш UnitPrice “разветвлялся” бы на 2 значения – оптовую и розничную цены UnitPrice\Retail и UnitPrice\Wholesale.
- Поскольку в нашем базовом хранилище лежит содержимое множества файлов, в нашем ключе без значения File_ID не обойтись.
- Следующие этапы преобразования данных работают только с полученными на данном шаге “трансформированными” таблицами, никакой сквозной системы настроек у нас нет. Тип поля (dimension/measure) мы передаем через суффиксы названий, но иногда нам надо передать “по цепочке” еще и информацию о том, в каком именно разделе документа мы брали информацию (помним, что мы можем трансформировать в одинаковый вид документы, закодированные разными схемами). Для передачи на следующий этап преобразования этой информации мы используем необязательный параметр нашей трансформации AdditionalInfo, “подцепив” его к нашему композитному ключу так, чтобы не нарушилась нужная нам идентификация целевых записей.
Посмотрим, что получилось на выходе в нашем примере:
Результат трансформации:
MovementReports:
Products:
MovementDetails:
| KEY | TargetField | Element_Value |
| 001;2006@Deliveries | PeriodBegin_Date | 2017-01-01 |
| 001;2006@Deliveries | PeriodEnd_Date | 2017-01-31 |
| 001;2007@Returns | PeriodBegin_Date | 2017-02-01 |
| 001;2007@Returns | PeriodEnd_Date | 2017-02-28 |
Products:
| KEY | TargetField | Element_Value |
| 001\003\001;2006 | Supplier_Dim | Zaanse Snoepfabriek |
| 001\003\001;2006 | ProductName_Dim | Chocolade |
| 001\003\002;2006 | Supplier_Dim | Mayumis |
| 001\003\002;2006 | ProductName_Dim | Tofu |
| 001\003\001;2007 | Supplier_Dim | Pavlova, Ltd. |
| 001\003\001;2007 | ProductName_Dim | Pavlova |
| 001\003\002;2007 | Supplier_Dim | Formaggi Fortini s.r.l. |
| 001\003\002;2007 | ProductName_Dim | Mozzarella di Giovanni |
MovementDetails:
| KEY | TargetField | Element_Value |
| 001\003\001\003\001;2006 | MovementDate_Date | 2017-01-03 |
| 001\003\001\003\001;2006 | UnitPrice_Val | 10.2000 |
| 001\003\001\003\001;2006 | Quantity_Val | 70 |
| 001\003\002\003\001;2006 | MovementDate_Date | 2017-01-09 |
| 001\003\002\003\001;2006 | UnitPrice_Val | 18.6000 |
| 001\003\002\003\001;2006 | Quantity_Val | 12 |
| 001\003\002\003\002;2006 | MovementDate_Date | 2017-01-13 |
| 001\003\002\003\002;2006 | UnitPrice_Val | 18.7000 |
| 001\003\002\003\002;2006 | Quantity_Val | 20 |
| 001\003\001\003\001;2007 | MovementDate_Date | 2017-02-21 |
| 001\003\001\003\001;2007 | UnitPrice_Val | 13.9000 |
| 001\003\001\003\001;2007 | Quantity_Val | 2 |
| 001\003\002\003\001;2007 | MovementDate_Date | 2017-02-27 |
| 001\003\002\003\001;2007 | UnitPrice_Val | 27.8000 |
| 001\003\002\003\001;2007 | Quantity_Val | 4 |
Обратите внимание – полученный ключ одинаков для всех полей, которые войдут в соответствующие записи наших целевых таблиц.
3. Финальная обработка.
3.1. Пивотирование
Получив в результате предыдущей трансформации “заготовку” целевой таблицы с исходными данными, разбитыми на тройки <ключ>-<имя поля>-<значение>, мы должны перевести ее в более привычный вид таблицы со множеством полей. Алгоритм этого преобразования очевиден – сначала группировкой значений нашего композитного ключа получаем “скелет” таблицы, потом осуществляем джойны этого “скелета” с таблицей-результатом трансформации по значению композитного ключа. (“Наращиваем мясо”, так сказать.)
То есть получится N соединений “скелета” с подмножествами таблицы- результата трансформации, выделенными по именам полей, где N- количество названий полей в целевой таблице-результате трансформации.
Мы благополучно “донесли” поле AdditionalInfo до данной стадии, закодировав его внутри композитного ключа. Теперь надо освободить наш ключ от этой “обузы” и отрезать AdditionalInfo-часть в новое поле AdditionalInfo_Dim.
Мы соединяли код файла и идентификатор записи, чтобы передать на этап пивотирования ключ одним полем. Для “финального” хранения лучше обратно разделить код файла и иерархический идентификатор на два поля, так будет проще связывать результирующие таблицы друг с другом.
В итоге получатся такие вот
pivot-таблички:
MovementReports:
Products:
MovementDetails:
| Record_ID | File_ID | AdditionalInfo_Dim | PeriodBegin_Date | PeriodEnd_Date |
| 001 | 2006 | Deliveries | 2017-01-01 | 2017-01-31 |
| 001 | 2007 | Returns | 2017-02-01 | 2017-02-28 |
Products:
| Record_ID | File_ID | Supplier_Dim | ProductName_Dim |
| 001\003\001 | 2006 | Zaanse Snoepfabriek | Chocolade |
| 001\003\002 | 2006 | Mayumis | Tofu |
| 001\003\001 | 2007 | Pavlova, Ltd. | Pavlova |
| 001\003\002 | 2007 | Formaggi Fortini s.r.l. | Mozzarella di Giovanni |
MovementDetails:
| Record_ID | File_ID | MovementDate_Date | UnitPrice_Val | Quantity_Val |
| 001\003\001\003\001 | 2006 | 2017-01-03 | 10.2000 | 70 |
| 001\003\001\003\001 | 2007 | 2017-02-21 | 13.9000 | 2 |
| 001\003\002\003\001 | 2006 | 2017-01-09 | 18.6000 | 12 |
| 001\003\002\003\001 | 2007 | 2017-02-27 | 27.8000 | 4 |
| 001\003\002\003\002 | 2006 | 2017-01-13 | 18.7000 | 20 |
3.2. Нормализация
Следующий этап условно можно назвать нормализацией, мы заменим все поля с суффиксом _Dim ссылками на соответствующие справочники, поля с суффиксами _Date и _Val преобразуем, соответственно, в даты и числа. Можно при необходимости и другие суффиксы типов данных использовать, это не догма.
Для каждого поля _Dim мы проверим наличие соответствующего справочника (если справочника нет, то создадим его) и дополним новыми значениями из поля пивотированной таблицы.
В конце процесса перельем данные в финальную структуру хранения, соединив пивотированную таблицу с проапдейченными справочниками по значениям полей.
В итоге мы получим наши целевые таблицы, в которых будет содержаться код файла, иерархический идентификатор, ссылки на значения справочников и преобразованные к нужным типам остальные поля.
В заключение описания процесса
Иерархический идентификатор в каждой целевой таблице дает нам возможность связать эти таблицы друг с другом (причем в любом порядке, при необходимости можно опускать промежуточные звенья). Как отмечалось выше, фиксированный размер элементов иерархического идентификатора дает нам возможность достаточно легко строить выражения для связывания наших целевых таблиц.
Конечно же, при желании можно дополнить структуру таблиц связями через значения привычных автоинкрементных ключей.
Отметим достаточную толерантность описанного подхода к ошибкам и упущениям разработчика (точнее, человека, конфигурирующего маппинг). Если в процессе подготовки трансформации вы упустили нужное поле, или если в схеме файлов произошли изменения типа добавления или переименования полей, которые не были отслежены вовремя – ничего страшного, даже если большой объем информации уже обработан без учета этой ошибки.
Вся информация у нас сохранена и доступна в нашем “первичном” табличном хранилище, поэтому заново парсить исходники не придется.
Можно прописать маппинг полей для «маленькой» трансформации данных – только для нужного нам пропущенного фрагмента. После того как мы “вытащим” недостающий кусок данных, мы сможем достаточно легко “подцепить” его к основному обработанному массиву через связку по иерархическому идентификатору.
Выводы
Как видим, мы с самого начала поместили всю исходную информацию вне зависимости от схем документов в единообразный вид, позволяющий быстро “вытаскивать” нужные данные в режиме ad-hoc.
В процессе дальнейшей обработки
Конечно, само программное окружение нашего “фреймворка” (инфраструктуру закачки и переливок данных) нужно было создать один раз, но затем интеграция данных любых структур XML сводится к редактированию таблицы маппинга исходного и целевого представлений.
Поделиться с друзьями
Комментарии (6)
RinatUsmanov
29.03.2017 20:21+1в Казахстане аналогичная ситуация. У гос. учреждений принято работать на Java и xml.
redmanmale
30.03.2017 18:28Хорошо, что хотя бы так, а не рукописное заявление и ответ "в течение семи рабочих дней в отделении, в котором писали заявление, в печатном виде".
shai_hulud
Тем временем шел 2017 год.
ABIDB
Космические корабли Илона Маска бороздили дальние уголки галактики.
Человечество покончило с проблемами ETL, полностью доверив эту работу могучему исскуственному интеллекту…