Автоматизация загрузки больших массивов данных предметной области в промышленную бд
 Скачать 0.74 Mb.
|
|
Рисунок 3. Загрузка данных с помощью утилиты Import Плюсы: - простота загрузки данных из дампа; - дамп занимает небольшое количество дискового пространства; - относительно высокая скорость загрузки; - вероятность совершить ошибку при загрузке почти отсутствует. Минусы: - необходимость знания схемы базы данных при обновлении; - модификация и обработка файла дампа невозможна; - схема целевой базы данных и импортируемой базы данных должны быть совместимы или совпадать; - дамп не переносим между различными СУБД; - нельзя определить по двум файлам, какие данные в них схожи, а какие различаются; - данные из различных файлов нельзя слить воедино без загрузки в базу; Данный способ также не подходит, так как он совершенно не гибок и не позволяет никаким способом вносить изменения до загрузки. 3.3 Использование SQL*Loader Утилита SQL*Loader позволяет загружать данные в базу из текстовых файлов. Этот инструмент позволяет загружать данные из нескольких файлов с заполнением нескольких таблиц за одну сессию загрузки. Файлы с данными могут иметь разнообразный формат, 21 так как SQL*Loader позволяет задавать разделители. В приложении А представлен пример конфигурационного файла, который загружает записи в зависимости от значения в первом столбце в разные таблицы базы данных. При загрузке имеется возможность использовать последовательности для генерации первичных ключей. Однако проставление внешних ключей затруднено, так как загрузка выполняется последовательно для каждой таблицы, а не по мере встречаемости записей в файле для загрузки и таким образом не удаётся получить промежуточное значение последовательности, на которое ссылается внешний ключ. Утилита позволяет производить селективную загрузку и применять к загружаемым данным SQL функции. Кроме этого, при загрузке можно использовать два режима: обычный и прямой. При обычном способе из данных формируются вставки SQL для загрузки в базу, в то время как при прямом, SQL не используется, а блоки данных форматируются непосредственно. Также SQL*Loader может выполнять загрузку параллельно в нескольких потоках. Рисунок 4. Загрузка с использованием SQL*Loader Тем не менее, чтобы произвести загрузку данных, необходимо знать схему базы данных. Это может существенно осложнить написание конфигурационного файла, который используется для управления процессом загрузки. Текстовые файлы, которые используются для загрузки данных, могут быть изменены любым текстовым редактором, однако вероятность ошибок при таком наполнении довольно большая, так как большие блоки данных могут быть разной длины, при этом визуально разрушая форматирование. Так как данные и конфигурационный файл имеют текстовый формат, то возможна 22 одновременная работа над этими файлами и последующее слияние изменений. Также при использовании систем контроля версий можно получить историю изменений. Утилита SQL*Loader используется исключительно для загрузки в СУБД Oracle, поэтому данный способ является не переносимым между СУБД. Однако существуют аналогичные утилиты у других СУБД, позволяющие загружать данные из плоских файлов, имеющих заданный формат, например для MS SQL Server это – «bcp». Разница между ними в том, что они могут поддерживать разные режимы загрузки и допускать разные форматы данных. Кроме того надо убедится, что утилита загрузки для конкретной СУБД умеет загружать в несколько таблиц и работать с несколькими файлами, если эта функция необходима. Таким образом, если схема базы данных довольно проста, то использование формата CSV(Comma-Separated Values) будет переносимым для загрузки на различных СУБД в большинстве случаев. Плюсы: - высокая скорость загрузки; - гибкость формата файлов с данными; - возможность многократного использования файлов с данными и конфигурационного файла при неизменной схеме; - возможность одновременной работы над данными и конфигурационным файлом; Минусы: - необходимо знание схемы БД; - трудно редактировать файлы наполнения; - высока вероятность допустить ошибку или опечатку при вводе или редактировании наполнения; - трудно написать конфигурационный файл в случае с большим количеством таблиц в схеме базы данных; - изменение схемы приводит к изменению конфигурационных файлов и всех файлов с наполнением; - данный способ частично переносим между СУБД. Учитывая условие, что как схема БД, так и сами данные могут измениться, данный метод не подходит в данном случае. К тому же редактирование форматированных текстовых файлов, которые предназначены для загрузки, сопряжено с большими рисками возникновения ошибок, так как человек непосвященный в тонкости формата может, например, использовать разделитель как часть значения и тем самым, нарушив всю процедуру загрузки. 23 3.4 Использование Excel + PL/SQL Чтобы облегчить загрузку больших массивов данных, необходимо иметь возможность не только хранить файлы, но и модифицировать их. Это позволит использовать данные неоднократно и обновлять их по мере необходимости. Также появится возможность исправления ошибок наполнения, если они будут обнаружены при загрузке данных. А затем, используя наполнение этих файлов, сгенерировать файлы с вызовами процедур или SQL-запросами для загрузки в БД, которые впоследствии можно удалить, чтобы освободить место. В качестве формата файлов можно использовать любой удобный формат представления табличной информации, для которого существует возможность удобного редактирования. Наиболее используемый формат - Excel-файлы(.xls). Также аргументом в пользу этого формата является поддержка макросов. Учитывая ограничения, имеющиеся на количество записей в файле, данные могут быть распределены по нескольким файлам. Однако, большинство форматов файлов, в которых хранятся табличные данные, имеют бинарный вид, что препятствует как одновременной работе с ними, так и определению различий и сходств данных в файле потому, что системы контроля версий по умолчанию не могут сравнить файлы в бинарном формате. Тем не менее, существуют различные дополнения к программам сравнения файлов и программы для нахождения сходств, различий и слияния изменений некоторых наиболее популярных форматов. Кроме того существуют API для работы с xls файлами, например, для языка Java это Apache POI и JExcel API. Это позволяет писать сложные приложения для работы с Excel файлами. С помощью макросов или программы можно сгенерировать вызовы PL/SQL процедур для корректных вставок в базу данных. Процедуры позволяют производить сложные вставки, когда наполнение происходит сразу в несколько таблиц со связями между ними. Однако макрос для генерации файла наполнения и сами PL/SQL процедуры, осуществляющие загрузку, необходимо написать. Так как макрос генерации можно сделать довольно гибким, то его можно написать единожды. Это верно и для PL/SQL процедур, их изменение необходимо только в случае коренной смены схемы БД. Данный метод частично позволяет менять СУБД с тем ограничением, что для этой СУБД должна проводиться генерация файлов для наполнения специфичным образом. Это необходимо, так как синтаксис создания процедур выполняющих загрузку данных может различаться при переходе на другую СУБД. Таким образом, для каждой СУБД, на которую требуется иметь возможность загрузить данные, необходимо создать свой VBA- макрос для генерации вызовов процедур и написать эти процедуры. 24 Рисунок 5. Загрузка данных с использованием Excel и PL/SQL. Для выполнения такого рода загрузки оператору достаточно уметь работать с табличным редактором, в котором осуществляется редактирование данных. Однако остается не разрешенной проблема человеческого фактора: возможны ошибки и опечатки во время наполнения и модификации файла. Кроме того, данный метод обладает более высокой скоростью загрузки по сравнению с простыми SQL вставками. Для того чтобы вставить 35 тысяч записей в несколько таблиц баз данных со связями при использовании SQL запросов потребовалось 475 секунд, в то время как при использовании процедур PL/SQL на этот процесс потребовалось 121 секунда. Это объясняется тем, что процедура компилируется один раз и запрос в ней разбирается единожды, в то время как для каждой SQL вставки приходится проводить разбор. Плюсы: - простота обработки и модификации данных; - возможность многократного использования; - нет необходимости знать схему БД при наполнении файла данными; - файл хранит только необходимую информацию, операторы для вставки генерируются автоматически и после загрузки их можно удалить; - простота использования и освоения; - гибкость: при смене схемы БД необходимо переписать только процедуры для вставки; Минусы: - низкая скорость загрузки больших массивов данных - возможны ошибки во время наполнения или модификации файла, обнаружение которых отложено на момент загрузки; - дополнительные трудозатраты для возможности загрузки в базы под управлением дополнительных СУБД; 25 - необходимо написать макрос генерации вызовов процедур и сами процедуры. - Excel файлы по умолчанию нельзя сравнивать и находить отличия между несколькими версиями одного файла. 3.5 Сравнение методов загрузки данных В таблице 3 приведено сравнение вышеописанных методов загрузки данных. Критерий SQL вставки Import SQL*Loader Excel + PL/SQL Возможность модификации данных Да Нет Да Да Простота модификации данных Модификация затруднительна Невозможно, модификация только в базе данных Модификация затруднительна Модификация проста Знания о схеме базы данных Необходимы Необходимы Необходимы Не требуются (Инкапсулируются в процедурах) Наличие избыточной информации в файле Конструкции языка SQL Нет избыточной информации (бинарный формат) Нет избыточной информации Нет Простота использования Требует специальных знаний - знания SQL Необходима работа с командной строкой и утилитой Import Необходима работа с командной строкой и утилитой SQL*Loader Необходима работа с табличным редактором, консолью Действия при смене схемы БД Переписать все SQL запросы Заново генерировать дамп файл Переписать конфигурационн ый файл Переписать процедуры Допускается ли автоматизация Возможна автоматизация генерации запросов и т.д. Автоматизац ия запуска утилит Возможна генерация данных для файла Возможна автоматизация на этапах наполнения и загрузки Скорость загрузки Низкая Наивысшая Высокая Средняя Возможность допустить ошибку при загрузке Высокая вероятность на многих этапах загрузки Отсутствует Высокая вероятность на этапе наполнения файлов для загрузки Средняя вероятность на этапе наполнения файлов для загрузки (Из-за более удобного редактирования) Этап обнаружения ошибки При загрузке При загрузке При загрузке При загрузке Переносимость между СУБД Переносимо Непереносим о Частично переносимо (При условии, Частично переносимо (Необходимо 26 что формат данных для загрузки совпадает) написать процедуры для других СУБД) Дополнительные трудозатраты помимо наполнения данными Нет Нет Написание конфигурационн ого файла Написание процедур, макроса или программы генерации их вызовов Возможность параллельной модификации Да Нет Да Да (При использовании дополнений для слияния Excel файлов) Возможность инкрементально й загрузки Да Да Да Нет Таблица 3. Сравнение методов загрузки Таким образом, метод с использованием Excel файлов для представления табличной информации и PL/SQL процедур для наполнения базы данных является наиболее удобным и подходящим для автоматизации способом загрузки. 27 4. Виды загрузки В зависимости от задачи можно разделить загрузку данных на несколько видов. 4.1 Частичная загрузка Задачу частичной загрузки можно сформулировать следующим образом. Имеется набор данных, в кортежах которого могут быть заполнены не все атрибуты. Предполагается, что незаполненные атрибуты могут быть заполнены в дальнейшем или если информации не хватает, оставлены в таком виде, в котором они были на момент загрузки. Необходимо осуществить загрузку имеющихся данных в базу. Способы решения данной задачи зависят от того, как отображается кортеж из массива данных на схему БД, в которую ведется загрузка данных. В первом случае, если незаполненный атрибут определён в той же таблице, что и загружаемый кортеж, то при вставке кортежа из массива данных можно явно загрузить значение NULL (при условии, что нет ограничения NOT NULL) или некоторое значение по умолчанию. Кроме того, если в самой схеме отношения для незаполненного атрибута указано значение по умолчанию, то при вставке можно пропустить значение атрибута. Во втором случае, если значения атрибутов представлены отдельной таблицей, связанной с отношением, где хранятся основные данные загружаемого кортежа, внешним ключом или другим отношением, то необходимо иметь возможность настраивать поведение загрузки. Такую гибкость могут обеспечить процедуры, которые обрабатывают входные данные и учитывают схему базы данных для расположения значений атрибутов и проставления связей между объектом и его атрибутами. В результате частичная загрузка данных таким способом сводится к первому случаю с тем отличием, что отсутствие значения или значение по умолчанию, указывается в принимаемом параметре процедуры. Средство, которое позволит выполнять данную задачу должно иметь возможность исключения как определённых атрибутов из загружаемых кортежей, так и определённых значений атрибутов. Это реализуется, например, так, что для каждой колонки и для каждой ячейки добавляется переключатель, который показывает, будет ли участвовать этот параметр или конкретно это значение при генерации вызовов процедур. Однако, необходимо продумать вопрос с хранением состояния переключателей, если оно должно сохраняться между запусками. В качестве альтернативы, можно заменять имеющиеся значения, отключенного параметра или значения, константой NULL или некоторым значением по умолчанию, что позволит подстроиться под интерфейс процедуры. 4.2 Инкрементная загрузка 28 Задача инкрементной загрузки заключается в том, чтобы выполнить загрузку в базу данных, в которой уже есть наполнение. Для того чтобы корректно решить эту задачу необходимо контролировать, чтобы данные, которые уже имеются в базе не повторялись и чтобы те данные, которых на данный момент нет в базе, успешно загрузились. Учёт дубликатов при инкрементной загрузке можно производить на разных уровнях. Контроль, осуществляемый на уровне БД, заключается в том, что проверка того, имеется ли сущность в базе, выполняется при помощи процедур или других встроенных механизмов СУБД. Например, загрузка может выполняться через процедуру, которая проверяет, имеется ли объект, идентифицируемый ключом кортежа, в базе и, если нет, то выполняет загрузку нового объекта, иначе объект считается уже загруженным. В результате пользователь указывает лишь состояние, к которому он хочет привести данные в базе, а процедуры выполняют необходимые проверки и вставки. Также данный способ удобен тем, что процедура инкапсулирует в себе знание о схеме базы данных. Также, контроль можно проводить на уровне средства загрузки данных. Можно привести несколько способов реализации контроля над корректностью инкрементной загрузки. Первый способ заключается в хранении сессий загрузки в качестве метаданных для каждого кортежа. То есть каждой строке приписывается номер сессии, когда она была загружена в базу. Средство, догружающее данные должно предоставлять выбор: загружать все данные со всех сессий, выполнить загрузку определённой сессии, выполнить загрузку ещё не загруженных данных. Однако чтобы реализовать такой способ загрузки необходимо решить, где хранить метаданные для каждого загружаемого кортежа. В результате ответственность за контролем над данными возлагается на пользователя, что может привести к ошибкам загрузки. Второй способ подразумевает соединение с базой данных, куда осуществляется загрузка, и возможность проверки, имеются ли данные с таким ключом. Этот метод является аналогом подхода, когда проверки производятся на уровне БД, описанном выше, с тем лишь отличием, что контроль над корректностью загрузки производится из средства загрузки. Одним из недостатков такой конструкции является то, что приложению необходимо знать схему базы данных. Третий способ возлагает ответственность за контролем над корректностью инкрементной загрузки пользователю. То есть пользователь сам выбирает, какие кортежи он хочет загрузить в базу данных. Такой подход может привести к ошибкам, если он применяется в чистом виде, однако он наиболее прост в реализации. 29 Для того чтобы найти компромисс между удобством, скоростью и надежностью, можно объединить методы контроля с разных уровней. То есть, например, использовать подход с выбором пользователя в совокупности с методом контроля, осуществляемого процедурой в базе данных. Пользователь указывает, какие кортежи он хочет, чтобы были в базе данных. Система загрузки передает эти данные процедурам загрузки и они, осуществляя необходимые проверки, производят вставку кортежей, которые не были найдены, в базу. 4.3 Корректирующая загрузка Задача корректирующей загрузки заключается в том, чтобы иметь возможность обновлять или удалять данные из базы данных. Целью корректирующей загрузки является изменение содержимого базы данных таким образом, чтобы оно стало корректно на текущий момент. Эта задача похожа на предыдущие две: как и в задаче частичной загрузки, должна быть возможность выбирать, какие атрибуты необходимо обновить или удалить, и также как и в задаче инкрементной загрузки, необходимо модифицировать только указанные строки, не изменяя остального содержимого. Соответственно решение этой задачи во многом похоже на предлагаемые варианты для вышеописанных задач. Это может использоваться для реализации средства загрузки, которое будет одновременно справляться со всеми задачами. Аналогично, логика загрузки данных может располагаться на разных уровнях. При выполнении непосредственных запросов на изменение и удаление данных на уровне базы необходимо иметь три механизма, которые отвечают за вставку, изменение и удаление данных. Вставка данных реализуется процедурой, которая проверяет, нет ли кортежа с таким первичным ключом в базе данных, и выполняет запрос на вставку в случае его отсутствия. Процедура удаления выполняет аналогичные действия: поиск и выполнение запроса на удаление, если такой объект имеется. Однако удаление из базы данных со сложной схемой может потребовать более сложной логики. Например, если на удаляемый кортеж ссылаются кортежи других отношений, то необходимо выполнить каскадное удаление или изменения ссылок на NULL или некоторое другое значение. Процедура обновления также выполняет поиск кортежа, проверяет введённые значение и обновляет изменившийся кортеж. В качестве более удобной альтернативы можно предложить использовать одну процедуру, которая выполняет все вышеперечисленные функции в зависимости от опционального параметра, значение по умолчанию которого, установлено на вставку или обновление. Данная процедура выполняет проверку, имеется ли кортеж в базе данных, 30 если да, то выполняется модификация, если нет, то вставка. Если же указан режим удаления, то выполняется поиск и удаление кортежа. Для выполнения корректирующей загрузки на уровне редактора необходимо следить за историей создания записей в загружаемых данных. При создании записей добавлять им метаданные, показывающие, что этот кортеж новый и его необходимо вставить. При генерации событий загрузки, например, вызовов процедур для вставки, все данные помечаются, как загруженные. При модификации записей, которые уже были загружены, в метаданные вносится флаг того, что запись была изменена и её необходимо обновить в БД. Кроме того, необходимо учесть возможность помечать запись данных для удаления из базы. При загрузке все метаданные учитываются, и в зависимости от их значений происходит вызов процедур или методов вставки, обновления или удаления. |