MySQL / 10. Импорт и экспорт данных

Диагностическая утилита для LOAD DATA

Задача
LOAD DATA или mysqlimport при загрузке файла данных в MySQL выводит ненулевой счетчик предупреждений, но вы не представляете, какие строки или столбцы вызвали проблемы.

Решение
Запустите для файла утилиту, которая определяет, какие значения данных привели к выводу сообщений.

Обсуждение
Предложение LOAD DATA очень эффективно для массовой загрузки, оно работает во много раз быстрее, чем набор предложений INSERT, добавляющих те же самые строки. Однако это предложение не очень информативно. Оно возвращает только сообщение, указывающее количество обработанных записей и несколько других счетчиков. Например, в предыдущем разделе был создан
файл данных managers.txt, используемый утилитой guess_table.pl для определения структуры таблицы managers базы данных baseball1.com. Если создать эту таблицу, используя результирующее предложение CREATE TABLE, и загрузить в нее файл данных, то результат будет таким:

mysql> LOAD DATA LOCAL INFILE 'managers.txt' INTO TABLE managers
-> IGNORE 1 LINES;
Query OK, 2841 rows affected (0.06 sec)
Records: 2841 Deleted: 0 Skipped: 0 Warnings: 5082

Очевидно, что с файлом что-то не так. К сожалению, выводимое LOAD DATA сообщение ничего не говорит о том, в каких строках и столбцах возникли проблемы. Программа mysqlimport так же немногословна, ее сообщение совпадает с возвращаемым LOAD DATA.

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

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

Пока же вы можете использовать утилиту load_diag.pl из каталога transfer дистрибутива recipes. Утилита load_diag.pl удобна для предварительного просмотра файла данных с тем, чтобы понять, хорошо ли файл будет загружен в предназначенную для него таблицу и чтобы выявить проблемы, дабы разобраться с ними до выполнения «настоящей» загрузки в MySQL. Утилита load_diag.pl также может помочь в определении типов проблем для ситуаций, в которых может оказаться полезным применение фильтра. Предположим, что вы периодически получаете файлы с данными для загрузки в определенную таблицу MySQL. Чем чаще это происходит, тем сильнее вы хотите автоматизировать (насколько это возможно) процесс переноса данных.

Может потребоваться создать фильтр для преобразования значений данных из формата, в котором они поступают, в формат, более подходящий для MySQL. Обработка файлов данных утилитой load_diag.pl может помочь в оценке того, какие столбцы могут вызывать проблемы, и тем самым способствовать определению того, на чем стоит сосредоточить свои усилия по созданию программы преобразования файлов для их корректной загрузки в MySQL.

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

% load_diag.pl имя_БД имя_таблицы имя_файла

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

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

• Строка записывается во временный файл, выдается предложение LOAD DATA для загрузки файла в таблицу. Если счетчик предупреждений равен нулю, то считается, что со строкой все хорошо.

• Если счетчик предупреждений не равен нулю, load_diag.pl по очереди исследует каждый из столбцов, используя серию одностолбцовых предложений LOAD DATA для выявления столбцов, генерирующих предупреждения.

• Если для столбца выводится сообщение, а значение данных пусто, то load_ diag.pl определяет, исчезнет ли сообщение при загрузке значения NULL вместо пустого значения. Если файл данных содержит пустые значения, то часто можно добиться лучших результатов, загружая вместо пустых
строк значения NULL. (Например, если вы загружаете пустую строку в столбец INT, MySQL преобразует ее в 0 и выдает сообщение.) Если окажется, что количество сообщений для файла значительно уменьшится при загрузке NULL вместо пустых строк, то, возможно, следует перед загрузкой файла выполнить для него to_null.pl.

• Предупреждения также могут появляться, если строка содержит больше или меньше столбцов, чем количество столбцов таблицы, поэтому load_ diag.pl проверяет и это.

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

Как вы понимаете, вся эта активность означает, что load_diag.pl далеко не так эффективна, как LOAD DATA. На самом деле она может очень сильно загрузить сервер! Но ее цель – это предоставление максимума информации, а не обеспечение минимального времени исполнения. (Отметьте, что если ваш сервер MySQL разрешает протоколирование, то использование load_diag.pl для больших файлов данных может привести к быстрому росту объема журнала.)

Чтобы посмотреть, как работает load_diag.pl, предположим, что у вас есть простая таблица diag_test, которая содержит строковый столбец, столбец дат и числовой столбец:ег

CREATE TABLE diag_test
(
str CHAR(10),
date DATE,
num INT
);

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

str1 01-20-2001 97
str2 02-28-2002
03-01-2002 64 extra junk

Чтобы посмотреть, возникнут ли у файла проблемы при загрузке, проверим его так:

% load_diag.pl cookbook diag_test diag_sample.dat
line 1: 1 warning
column 2 (date): bad value = (01-20-2001)
line 2: 2 warnings
too few columns
column 2 (date): bad value = (02-28-2002)
column 3 (num): missing from input line
column 3 (num): bad value = () (inserting NULL worked better)
line 3: 1 warning
excess number of columns
Number of lines in file: 3
Warnings found when loading entire file: 4
Lines containing too few column values: 1
Lines containing excess column values: 1
Warnings per column:
Column Times Total Warnings for Improved
missing warnings empty columns with NULL
str 0 0 0 0
date 0 2 0 0
num 1 1 1 1

Похоже на то, что даты загружаются не очень хорошо. Это и неудивительно, едь они имеют формат США и должны быть преобразованы в формат ISO.

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

% yank_col.pl --columns=1-3 diag_sample.dat \
| cvt_date.pl --iformat=us --oformat=iso \
| to_null.pl > tmp

Команда формирует такой файл tmp:

str1 2001-01-20 97
str2 2002-02-28 \N
\N 2002-03-01 64

Используем load_diag.pl для проверки нового файла:

% load_diag.pl cookbook diag_test tmp
File loaded with no warnings, no per-record tests performed

То есть если загрузить tmp в таблицу diag_test, результат должен быть хорошим, и так это и есть:

mysql> LOAD DATA LOCAL INFILE 'tmp' INTO TABLE diag_test;
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

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

В дополнение к необходимым аргументам имен базы данных, таблицы и файла данных, load_diag.pl понимает ряд опций:

--columns=имя1, имя2, имя3,...

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

--labels
Опция указывает на то, что файл данных содержит начальную строку заголовков, которую следует пропустить. (Загрузка заголовков в таблицу обычно приводит к выводу ложных предупреждений.)

--skip-full-load
Пропустить начальный тест, загружающий весь файл данных.

--tmp-table=имя_таблицы
Указывает имя для временной таблицы. По умолчанию _load_diag_n, где n – это идентификатор процесса load_diag.pl.

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

На использование load_diag.pl налагаются такие ограничения:

• Ввод должен быть разделен символами табуляции, признак конца строки – символ перевода строки.

• Загрузка записи выполняется при помощи предложения LOAD DATA с опцией LOCAL. Применение LOCAL возможно начиная с версии MySQL 3.22.15 и выше (и начиная с версии 3.23.49 требуется, чтобы эта возможность не отключалась при сборке).

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

С другой стороны, неиспользование индексов означает, что load_diag.pl не обнаружит предупреждений о повторяющихся значениях уникальных ключей.

Вернемся к примеру из начала раздела: так чем же объясняются все эти предупреждения, появившиеся при загрузке файла managers.txt в таблицу managers? Утилита load_diag.pl идентифицировала все их как результат отсутствующих или пустых столбцов в конце некоторых строк:

% load_diag.pl --labels cookbook managers managers.txt
line 2: 2 warnings
column 14 (postwins): bad value = () (inserting NULL worked better)
column 15 (postlosses): bad value = () (inserting NULL worked better)
line 3: 2 warnings
column 14 (postwins): bad value = () (inserting NULL worked better)
column 15 (postlosses): bad value = () (inserting NULL worked better)
...
line 2839: 2 warnings
column 14 (postwins): bad value = () (inserting NULL worked better)
column 15 (postlosses): bad value = () (inserting NULL worked better)
line 2842: 2 warnings
column 14 (postwins): bad value = () (inserting NULL worked better)
column 15 (postlosses): bad value = () (inserting NULL worked better)
Number of lines in file: 2842
Warnings found when loading entire file: 5082
Lines containing too few column values: 416
Lines containing excess column values: 0
Warnings per column:
Column Times Total Warnings for Improved
missing warnings empty columns with NULL
lahmanid 0 0 0 0
year 0 0 0 0
team 0 0 0 0
lg 0 0 0 0
div 0 0 0 0
g 0 0 0 0
w 0 0 0 0
l 0 0 0 0
pct 0 0 0 0
std 0 0 0 0
half 0 0 0 0
mgrorder 0 0 0 0
plyrmgr 16 0 0 0
postwins 416 2533 2533 2533
postlosses 416 2533 2533 2533

Из результата видно, что в 416 строках отсутствуют столбцы postwins и postlosses (а в 16 строках – и столбец plyrmgr). Остальные ошибки возникли в строках, в которых столбцы postwins и postlosses присутствуют, но содержат пустые значения. Общее количество предупреждений для файла равно 5082 и может рассматриваться как количество отсутствующих значений plyrmgr плюс общее количество предупреждений о столбцах postwins и postlosses (16 + 2533 + 2533 = 5082).

Значение Total warnings для столбца plyrmgr равно нулю, так как это столбец типа CHAR, и в него разрешено загружать пустые значения. Значение Total warnings для postwins и postlosses не равно нулю, так как они относятся к типу INT, и загрузка в них пустых значений инициирует операции преобразования в ноль. Все проблемы могут быть решены за счет преобразования пустых и
отсутствующих значений в \N. Обработайте файл утилитой yank_col.pl, тогда в каждой строке будет по 15 столбцов, затем примените к результату сценарий to_null.pl для преобразования пустых значений в \N:

% yank_col.pl --columns=1-15 managers.txt | to_null.pl > tmp

Посмотрим, что load_diag.pl скажет о получившемся файле:

% load_diag.pl --labels cookbook managers tmp
File loaded with no warnings, no per-record tests performed

Если загрузить tmp в таблицу managers, никаких проблем не возникнет:

mysql> LOAD DATA LOCAL INFILE 'tmp' INTO TABLE managers IGNORE 1 LINES;
Query OK, 2841 rows affected (0.13 sec)
Records: 2841 Deleted: 0 Skipped: 0 Warnings: 0

Статьи по MySQL на эту тему:

Импорт XML в MySQL
Обмен данными между MySQL и FileMaker Pro
Обмен данными между MySQL и Microsoft Access
Обмен данными между MySQL и Microsoft Excel
Экспорт результатов запроса в XML

Вернуться в раздел: MySQL / 10. Импорт и экспорт данных