Автоматизация загрузки больших массивов данных предметной области в промышленную бд
 Скачать 0.74 Mb.
|
|
4.4 Трассируемая загрузка Задача трассируемой загрузки возникает тогда, когда необходимо отследить, как данный кортеж попал в базу данных. Если организовать загрузку так, что остается возможность определить источник каждого вставленного, измененного или удалённого кортежа, то можно определить позицию ошибки и устранить её. В зависимости от способов загрузки, можно идентифицировать источник данных по-разному. Например, данные могут попадать в базу через приложения, из средств загрузки данных или через непосредственные вставки пользователями. В любом случае, можно организовать хранение информации несколькими способами. Один из способов хранения источника данных в виде журнала, куда вносится информация о событиях, но связи между записями журнала и вставляемыми сущностями не подразумеваются. Другой способ заключается в создании дополнительных отношений в базе данных, которые содержат записи об источниках данных и связывают их с данными. Связь между таблицей данных и таблицей источников можно установить, как события в базе данных, которые хранят действие, дату, первичный ключ вставленного кортежа, первичный ключ источника данных. Контроль над наполнением данных в эти таблицы можно возложить на триггеры, которые будут вносить записи по событиям, или процедуры. Если загрузка выполняется из табличного документа, то можно добавить в источник данных наименование файла, имя листа, номер колонки и строки. В случае ручной вставки можно записывать имя пользователя базы данных, выполнившего операции над данными. Аналогично и для приложения сохраняется его название, имя пользователя, и, возможно, другие отличительные черты данного способа вставки. 31 Преимущество использования процедур заключается в том, что можно разграничить доступ посредством предоставления процедур, которые выполняют необходимые действия и заодно отмечают события в отношениях-журналах. В результате по событиям можно отследить историю изменений от создания кортежа до удаления. При больших объемах данных может возрасти объем отношения, хранящего историю событий, однако при таком же количестве данных трудоемкость поиска ошибки может существенно превысить расходы на дополнительную память. 32 5. Выгрузка данных Задача выгрузки данных заключается в том, чтобы получить необходимую информацию из базы данных. Дополнительно на неё налагается требование определённого формата. Этот формат чаще всего указывается непосредственно при выгрузке данных. Кроме того, в случае, если необходимый формат не поддерживается по умолчанию, то требуется использовать некоторый промежуточный вариант, который можно преобразовать в необходимый. Выгрузка может осуществляться различными способами в зависимости от необходимого формата. По умолчанию веб-форма СУБД Oracle позволяет использовать CSV, и XML как выходные форматы, однако этот сервис позволяет выгрузить только одну таблицу за сеанс. Другими стандартными средствами для выгрузки данных являются утилиты Export и Data Pump Export. Существенным ограничением в их использовании является то, что они выгружают данные в бинарных форматах, которые можно обработать только утилитами Import и Data Pump Import соответственно. Для выгрузки данных в текстовом виде можно применять спулинг, когда результаты запроса SQL выводятся не только на экран, но и в файл. В результате формат выходного файла, полученного таким образом, ограничен лишь возможностями SQL. Также можно использовать PL/SQL процедуры для вывода форматированных данных из базы в файл. Если стоит задача выгрузить данные в XLS формате, то для её выполнения необходимо задать отображение между таблицами базы данных и листами Excel файла. По умолчанию можно принять тождественное отображение, то есть каждой выгружаемой таблице соответствует лист Excel файла. В качестве временного решения можно использовать формат таблицы XML, которая хранит данные в виде XML, но позволяет Excel работать с ним также как с XLS файлом. Тем не менее, чтобы сгенерировать XML таблицу необходимо использовать PL/SQL. Также Excel поддерживает формат CSV, что позволяет проводить дальнейшую работу с данными, выгруженными в этом формате. В качестве альтернативы PL/SQL, можно использовать язык программирования Java в совокупности с драйвером используемой СУБД, для соединения и получения данных, и библиотекой для генерации XLS, например Apache POI, что позволяет генерировать сразу файл в XLS формате. 33 6. Возможности улучшения метода загрузки с использованием Excel + PL/SQL Учитывая недостатки метода загрузки данных с использованием Excel + PL/SQL можно предложить следующие улучшения: - добавление средства для проверки файлов; - упрощение ввода и редактирования данных; - использование плоских файлов для хранения данных; - исследование нагрузочных показателей. 6.1 Средство для проверки файлов Также как и остальные методы загрузки, способ с использованием Excel файлов и PL/SQL процедур подвержен ошибкам при модификации файла наполнения базы данных. Тот факт, что данные хранятся в табличном файле, частично упрощает ситуацию, потому что данный формат удобно читается и не требует больших усилий для освоения основных операций, доступных табличному редактору. Поэтому возникает необходимость минимизировать риск возникновения ошибки на этапе загрузки, то есть ошибки должны быть обнаружены ещё до того, как данные поступят на загрузку. Благодаря специальному формату файлов поверх табличной структуры, включающему источник данных для столбцов, можно следить, находится ли значение в определённой ячейке таблицы среди допустимых и таким образом сигнализировать о потенциальной ошибке. Соответственно, проверка файлов позволит находить ошибки ещё до загрузки в базу данных и исправлять их. Кроме проверки всего файла должна быть возможность проверять данные по мере их ввода и редактирования, что уменьшает вероятность допустить ошибку или опечатку. Также проверка должна быть возможна, если допустимые значения распределены по разным файлам вследствие того, что один файл может физически не может вместить необходимое количество записей или необходимо логическое разделение данных по файлам. 6.2 Упрощение ввода и модификации данных В связи с тем, что объемы данных могут быть велики, ввод новых значений должен быть простым. Если имеются некоторые ограничения на значения данных, то это должно отображаться по мере ввода. Чаще всего допустимые значения представляют в виде выпадающего списка с возможностью фильтрации по мере набора текста и выбора корректного значения. Фильтрация необходима, так как столбец, из которого берутся допустимые значения, может содержать тысячи значений. Более того значения могут браться из нескольких столбцов, что может существенно затруднить выбор значения. 34 При вводе данных, оператор не должен отвлекаться на системную информацию столбцов, которая располагается непосредственно над данными. Чтобы сократить количество ошибок, связанных с жестко заданным форматом системной информации, необходимо представить редакторы этих значений отдельными компонентами, которые считывают и записывают данные в определённые ячейки Excel файла. Кроме того, необходимо предоставить возможность выбирать, какие столбцы и строки данных должны присутствовать в наполнении. Соответственно желательно выделение цветом этих столбцов и строк, которые не используются при генерации файла с вызовами процедур или которые имеют фиксированные значения. Это позволит оператору проще ориентироваться по большому количеству данных. 6.3 Использование плоских файлов для хранения данных Одним из недостатков метода загрузки Excel + PL/SQL является то, что параллельная работа над одним и тем же набором данных затруднена, так как Excel файл является бинарным. Данную проблему можно решить, используя дополнения для систем контроля версий для выполнения слияния двух Excel файлов, либо храня данные в виде плоских файлов, например в формате CSV. Хранение данных в виде CSV имеет также преимущество по размеру над стандартным форматом таблиц XLS. Существенным недостатком работы с CSV файлами является то, что в каждом файле может храниться только один лист данных, в то время как XLS позволяет хранить книги с несколькими листами. Следовательно, инструмент, который будет использоваться для работы с файлами наполнения, должен предоставлять однообразный интерфейс независимо от формата данных в файлах и предоставлять механизм агрегации CSV листов в книги. 6.4 Нагрузочные показатели Для того чтобы оценить, возможны ли улучшения в процессе загрузки по скорости необходимо оценить загрузочную способность метода с использованием Excel + PL/SQL. Для проверки скорости загрузки было создано несколько таблиц со связями между собой. Были написаны типичные процедуры, которые осуществляют проверку на тот факт, есть ли кортеж с таким ключом в базе или нет и затем в случае отсутствия, вставляют этот кортеж в базу. Также было сгенерировано разное количество вызовов этих процедур с разными параметрами, которые вставляли суммарно от 8 тысяч записей в таблицы до 280 тысяч записей. Была проведена серия испытаний с изменением количества загружаемых записей и в результате, как можно увидеть на левом графике Рисунка 6, наблюдается квадратичная зависимость. 35 Рисунок 6. Зависимость общего времени загрузки и времени загрузки одной записи с использованием PL/SQL от количества вставок в базу В этом случае, также можно рассмотреть время, затрачиваемое на загрузку одной записи от общего числа загружаемых записей, график зависимости, представленный на правом графике Рисунка 6, показывает, что зависимость является линейно возрастающей. Таким образом, при возрастании объема данных, загружаемого с помощью PL/SQL процедур, общее время загрузки растёт пропорционально квадрату числа записей, а время загрузки одной записи растёт линейно. Процесс загрузки 35 тысяч записей в базу данных, которые были выполнены 20 тысячами процедурами, занял примерно 121 секунду. Из этого показателя можно получить, что в среднем одна запись при таком объёме данных загружается за 3,4 мсек. В качестве желаемого времени загрузки данного объема в 35 тысяч записей можно выбрать показатель в полторы минуты. Можно заметить, что данный метод почти удовлетворяет данному требованию. Для того чтобы достичь лучшего результата можно предложить использование групповых вставок с использованием оператора FORALL PL/SQL. Для использования этого метода необходимо изменение реализации процедур, но интерфейс остаётся тем же. При вызове процедуры вставки, кортеж помещается в буфер и как только буфер заполняется, или поступает событие о необходимости принудительного сброса в базу данных, данные загружаются в базу. Согласно [7] применение этого метода оптимизации загрузки заключается в том, что переключение контекста при выполнении вставок в базу данных между ядрами PL/SQL и SQL происходит реже и соответственно эти переключения вызывают меньше накладных расходов. С использованием данного метода оптимизации для примера в 35 тысяч записей средняя скорость загрузки данных составила 327 записей в секунду. Всего было затрачено 106 секунд, со средним временем загрузки одной записи в 3,1 мсек. Из этого можно сделать вывод, что при использовании метода загрузки с использованием Excel + PL/SQL переключения контекстов между 36 ядрами PL/SQL и SQL не столь сильно оказывают влияние на общее время выполнения загрузки. Кроме возможных технологических улучшений необходимо учитывать альтернативные факторы, влияющие на загрузку данных. Например, если есть процедура, которая выполняет некоторые операции (к примеру, вычисление наименования записи по остальным полям записи) над только что вставленными данными, то важно, чтобы эта процедура выполнялась единожды для каждой записи. Иначе, если её вызывать после каждой вставки, то будет проделано много лишней работы, и временная оценка будет пропорциональна квадрату от количества записей. В то время как, если вызывать эту процедуру для всех вставленных записей в конце загрузки, то временная оценка снизится до линейной от количества записей. Подводя итог, следует заметить, что метод оптимизации с использованием групповых инструкций вставки не подходит для данного метода загрузки, так как при увеличении объёмов данных затраты на изменение процедур превышают ускорение получаемое в результате оптимизации. Таким образом, если ключевым фактором является скорость загрузки, то необходимо предусмотреть возможность конвертации данных в другой формат с использованием другого метода загрузки, возможно с использованием утилиты SQL*Loader. 37 7. Предлагаемый вариант решения Необходимо найти или разработать редактор Excel файлов, который бы удовлетворял следующим требованиям: - возможность проверки файлов на наличие ошибок; - фильтрация допустимых значений по мере ввода; - возможность выбора из возможных значений; - простота в использовании и освоении; - предоставление графического интерфейса для взаимодействия с пользователем; - возможность ссылок на другие файлы; - гибкость при изменении формата данных внутри файла; - независимость от операционной системы; - работа с CSV файлами, как с Excel книгой; - загрузка данных из редактора. Был проведён поиск в сети Интернет по имеющимся табличным процессорам, которые поддерживают импорт данных из Excel файлов или позволяли бы напрямую редактировать данные в этих файлах. Многие из существующих редакторов не отличаются по функциональности от Excel и не поддерживают средства для задания связей между столбцами. В большинстве случаев в них есть механизм для выбора среди заданных значений, однако все допустимые значения необходимо либо задавать вручную путём ввода в область редактирования, что весьма неудобно для большого количества данных, либо указывать диапазон значений на листе. Последний подход для задания допустимых значений гораздо удобней, чем первый, однако при выходе данных за указанный ранее диапазон, можно не учесть нужных значений. Таким образом, необходимо, чтобы диапазон значений автоматически расширялся по мере ввода данных и изменения отражались в выпадающих списках для ввода данных. Табличный процессор Excel OpenOffice Calc Gnumeric KSpread Lotus 1-2-3 LibreOffice Возможность проверки файлов на наличие ошибок + 1 +1 - +1 +1 +1 Фильтрация допустимых - - - - - - 1 При использовании макросов 38 значений по мере ввода Возможность выбора из возможных значений + + + + + 1 + Простота в использовании и освоении + + + + + + Предоставление графического интерфейса + + + + + + Возможность ссылок на другие файлы + 2 +2 - - - +2 Возможность работать с другими форматами файлов +2 +2 - - - +2 Независимость от операционной системы - + + + - + Работа с CSV файлами, как с Excel книгой - - - - - - Загрузка данных из редактора +2 +2 - +2 +2 +2 Таблица 4. Сравнение существующих табличных процессоров Учитывая то, что объём данных может быть очень большим, и тот факт, что человек ответственный за наполнение должен иметь возможность выбрать допустимое значение из списка, фильтрация значений по мере ввода является одним из важнейших требований. Однако ни один из рассмотренных редакторов не поддерживает такой функции. 1 При установке дополнения для проверки 2 При использовании макросов 39 Следует отметить, что особую ценность табличным процессорам приносит функциональность для запуска макросов, так как иначе невозможно было бы выполнять большинство требуемых функций. Так, например, благодаря макросам можно осуществить обработку файлов в произвольных форматах, осуществить проверку целостности всего файла и некоторым образом проверять значения, входящие в другие файлы. Большинство табличных редакторов позволяют импортировать файлы в формате CSV, однако, ни один из них не позволяет группировать логически CSV файлы для удобной работы с ними. Открытие каждого CSV файла приводит к открытию нового окна, а не новой вкладки, что вызывает неудобство работы. Все решения, которые входят в обзор обладают примерно одинаковым графическим интерфейсом, что делает их весьма лёгкими для освоения, так как в основном люди, занимающиеся наполнением базы данных, знакомы в той или иной степени с интерфейсом и методами работы с табличными процессорами. Даже если навыки использования таблиц отсутствуют, существуют книги, документация и ресурсы в сети Интернет, содержащие основные приёмы использования. Тем не менее, решения подходящего по всем параметрам найдено не было, поэтому возникает необходимость разработки собственного решения. 40 8. Редактор и спецификация файлов Для разработки редактора использовался язык объектно-ориентированного программирования Java с использованием библиотеки Swing для создания графического интерфейса. Swing предоставляет компоненты для представления табличной информации и выпадающих списков, которые представляют наибольшую ценность для редактора. В качестве средства для программируемого взаимодействия с Excel-файлами была выбрана библиотека Apache POI. 8.1 Редактор Редактор предоставляет пользователю интерфейс для редактирования, проверки и загрузки данных из файлов. Данное приложение применяется на этапе ввода данных оператором в Excel файл и при загрузке. Редактор автоматизирует трудоемкую проверку, как существующих данных, так и вводимых данных и устраняет промежуточные этапы при загрузке, что автоматизирует процесс загрузки в целом. При этом минимизируется риск возникновения ошибок и опечаток. При открытии файла выводится список ошибок, которые содержатся в файле. Ошибки могут быть следующими: - значение не входит в диапазон допустимых значений; - ссылка не может быть разрешена (не найдены допустимые значения); Также имеются кнопки в меню для проверки, как всего файла, так и выбранного листа. Существует возможность сохранения измененного файла. Ведётся история недавно открытых файлов, для более быстрого доступа к ним. Для удобства имеется возможность автоматического расширения колонок, чтобы были видны значения в клетках таблицы. Реализовано создание новых файлов, листов, столбцов и строк. Редактор позволяет работать с XLS книгами и CSV файлами, агрегированными в книги для поддержания схожей структуры. В результате имеется возможность конвертировать файлы из XLS формата в CSV формат. Интерфейс пользователя одинаков при работе, как с XLS файлами, так и с CSV. Изменение соответствующих файлов происходит прозрачно для оператора. Редактор предоставляет возможность редактирования в боковой панели системной информации: - файла (название книги, имя файла при сохранении, ссылки на другие файлы); - листов (название процедуры, признак включения в генерацию, название листа, имя генерируемого файла); 41 |