Определение структуры таблицы для файла данных

Задача
Вам дают файл с данными и говорят: «Вот файл, перенеси его в MySQL». Таблицы для хранения данных еще нет.

Решение
Напишите самостоятельно предложение CREATE TABLE. Или используйте утилиту, которая определяет структуру таблицы, исследуя содержимое файла данных.

Обсуждение
Иногда бывает так, что вам нужно импортировать данные в MySQL, а таблицы для них еще нет. Вы можете сами создать таблицу, основываясь на имеющейся информации о содержимом файла. Или же можете доверить часть работы утилите guess_table.pl из каталога transfer дистрибутива recipes. Утилита guess_table.pl читает файл данных, чтобы понять, какую информацию он содержит, затем пытается сформировать предложение CREATE TABLE, соответствующее содержимому файла. Конечно же, такой сценарий далек от совершенства, так как содержимое столбцов может быть неоднозначным. Например, столбец, содержащий небольшое количество различных строк, может относиться к типу как CHAR, так и ENUM. Но все же проще несколько подправить предложение, формируемое guess_table.pl, чем писать с нуля собственное. Утилиту можно использовать и для диагностики, хотя это не главное ее назначение.


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

Утилита guess_table.pl работает в предположении, что ее ввод разделен символами табуляции, а признаком конца строки является символ перевода строки. Она также считает, что вводятся разрешенные значения, так как любая попытка определения типа столбца на основе некорректных данных обречена на провал. То есть, например, для того, чтобы столбец дат был распознан как таковой, даты должны быть в формате ISO. В противном случае guess_table.pl может определить столбец как имеющий тип CHAR. Если для файла данных не выполнены описанные условия, можно предварительно переформатировать его при помощи утилит cvt_file.pl и cvt_date.pl из рецептов 10.18 и 10.31.

Утилита guess_table.pl понимает такие опции:

--labels
Интерпретировать первую строку ввода как строку заголовков столбцов и использовать их для именования столбцов таблицы. Если опция не задана, то guess_table.pl использует имена столбцов по умолчанию: c1, c2, и т. д.

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


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

--lower, --upper
Указывать имена столбцов в предложении CREATE TABLE в нижнем или верхнем регистре.

--quote-names
Заключать имена таблиц и столбцов в предложении CREATE TABLE в кавычки, используя символы ` (например, `mytbl`). Используется, если имя является зарезервированным словом. Получившееся предложение требует версии MySQL 3.23.6, так как более ранние версии не поддерживают за-
ключение имен в кавычки.

--report
Формировать отчет, а не предложение CREATE TABLE. Сценарий выводит информацию, которую он собрал о каждом столбце.

--tbl_name=имя_таблицы
Указывает имя таблицы, которое должно использоваться в предложении CREATE TABLE. По умолчанию используется t.

Рассмотрим пример работы guess_table.pl для файла managers.csv из дистрибутива бейсбольной базы данных baseball1.com. Этот файл содержит записи для руководителей команд. Он начинается со строки заголовков столбцов, за которой следуют строки значений данных:

LahmanID,Year,Team,Lg,DIV,G,W,L,Pct,Std,Half,Order,PlyrMgr,PostWins,PostLosses
cravebi01,1871,TRO,NA,,25,12,12,0.5,6,0,2,,,
deaneha01,1871,KEK,NA,,5,2,3,0.4,8,0,2,,,
hastisc01,1871,ROK,NA,,25,4,21,0.16,9,0,0,,,
paborch01,1871,CLE,NA,,29,10,19,0.345,7,0,0,,,
wrighha01,1871,BOS,NA,,31,20,10,0.667,3,0,0,,,
youngni99,1871,OLY,NA,,32,15,15,0.5,5,0,0,,,
clappjo01,1872,MAN,NA,,24,5,19,0.208,8,0,0,,,
clintji01,1872,ECK,NA,,11,0,11,0,9,0,1,,,
fergubo01,1872,BRA,NA,,37,9,28,0.243,6,0,0,,,
...

Первая строка приводит заголовки столбцов, последующие строки содержат записи данных, по одной на строку.


Сценарий guess_table.pl требует ввода, разделенного символами табуляции, с символом перевода строки в качестве признака конца строки, поэтому для обработки файла managers.csv необходимо сначала преобразовать его при помощи cvt_file.pl, записав результат во временный файл managers.txt:% cvt_file.pl --iformat=csv --ieol="\r\n" managers.csv > managers.txt

Затем для временного файла выполним guess_table.pl (используем опцию --lower, так как мне больше нравятся имена столбцов в нижнем регистре):

% guess_table.pl --table=managers --labels --lower managers.txt > managers.sql

Предложение CREATE TABLE, которое сценарий guess_table.pl записывает в managers.sql, выглядит так:

CREATE TABLE managers
(
lahmanid CHAR(9) NOT NULL,
year INT UNSIGNED NOT NULL,
team CHAR(3) NOT NULL,
lg CHAR(2) NOT NULL,
div CHAR(1) NULL,
g INT UNSIGNED NOT NULL,
w INT UNSIGNED NOT NULL,
l INT UNSIGNED NOT NULL,
pct FLOAT NOT NULL,
std INT UNSIGNED NOT NULL,
half INT UNSIGNED NOT NULL,
order INT UNSIGNED NOT NULL,
plyrmgr CHAR(1) NULL,
postwins INT UNSIGNED NULL,
postlosses INT UNSIGNED NULL
);

А получается такое предложение на основе следующих предположений:

• Если столбец содержит только целые значения, считается, что он имеет тип INT.


Если ни одно из его значений не отрицательно, вероятно, он также является столбцом без знака.

• Если столбец не содержит пустых значений, предполагается, что он NOT NULL.

• Столбцы, которые не удается отнести к числовым или датам, принимаются за столбцы CHAR, длина которых совпадает с самым длинным значением столбца.

Вы можете захотеть отредактировать предложение CREATE TABLE, построенное утилитой guess_table.pl, чтобы, например, увеличить размер символьных полей, изменить CHAR на VARCHAR или добавить индексы. Есть и другая причина редактирования предложения: если имя столбца является зарезервированным словом MySQL, можно заменить его. Например, определение таблицы managers, созданное guess_table.pl, включает столбец order, имя которого – зарезервированное ключевое слово. Столбец представляет порядок руководителей в течение сезона (если они менялись), так что в качестве разумной альтернативы названия можно предложить имя mgrorder. После редактирования предложения в файле managers.sql с изменением имени, выполните его для создания таблицы:

% mysql cookbook < managers.sql

Теперь можно загружать в таблицу данные (пропуская первую строку заголовков):

mysql> LOAD DATA LOCAL INFILE 'managers.txt' INTO TABLE managers
 IGNORE 1 LINES;

(Когда вы будете это делать, LOAD DATA будет выводить предупреждения.

База данных baseball1.com существует и в формате Access.


База данных Access содержит явную информацию о структуре таблицы managers, и эта информация доступна таким утилитам, как DBTools и MySQLFront, которые могут использовать ее для создания таблицы MySQL (программы описаны в рецепте 10.38). Так вы получаете возможность проверить, насколько хорошо guess_table.pl угадывает структуру таблицы по сведениям только из файла данных по сравнению с программами, обладающими большим объемом информации.

Проблема утилит типа DBTools и MySQLFront в том, что если имя столбца таблицы Access является зарезервированным словом, невозможно импортировать его в MySQL, не изменив таблицу Access так, чтобы она использовала другие имя. Зарезервированным словом является имя столбца Order таблицы managers. При использовании guess_table.pl никаких проблем не возникает, так как вы просто редактируете формируемое предложение CREATE TABLE так, чтобы имя было разрешенным.1 Но чтобы обрабатывать столбец Order таблицы managers при помощи DBTools или MySQLFront, необходимо изменить саму базу данных Access, переименовав ее столбец (например, в MgrOrder.) Структура таблицы managers, предлагаемая DBTools, такова:

CREATE TABLE managers (
LahmanID char(9) NOT NULL default '',
Year int(11) default NULL,
Team char(3) default NULL,
Lg char(2) default NULL,
Div char(2) default NULL,
G int(11) default NULL,
W int(11) default NULL,
L int(11) default NULL,
Pct double default NULL,
Std int(11) default NULL,
Half int(11) default NULL,
MgrOrder int(11) default NULL,
PlyrMgr char(1) default NULL,
PostWins int(11) default NULL,
PostLosses int(11) default NULL,
KEY LahmanID (LahmanID)
);

MySQLFront создает такую таблицу:

CREATE TABLE managers (
LahmanID longtext,
Year int(11) default NULL,
Team longtext,
Lg longtext,
Div longtext,
G int(11) default NULL,
W int(11) default NULL,
L int(11) default NULL,
Pct float default NULL,
Std int(11) default NULL,
Half int(11) default NULL,
MgrOrder int(11) default NULL,
PlyrMgr longtext,
PostWins int(11) default NULL,
PostLosses int(11) default NULL
);

Утилита DBTools определяет структуру таблицы MySQL более точно, чем две другие программы. Она использует индексную информацию, предоставляемую файлом Access, для вывода определения KEY и для создания строковых столбцов с соответствующими длинами. MySQLFront не определяет ключ и определяет строки как столбцы LONGTEXT, даже столбец PlyrMgr, длины значений которого никогда не превышают один символ. Качество вывода guess_table.pl находится где-то посередине. Утилита не определяет ключ, но и не сопоставляет каждому строковому столбцу наибольшую длину. (С другой стороны, с длинами столбцов надо соблюдать осторожность.) В общем, неплохо, учитывая то, что guess_table.pl не имеет доступа ко всей информации, содержащейся в исходном файле Access. К тому же этот сценарий можно использовать на разных платформах.

Результаты показывают, что если вы используете Windows и записи хранятся в файле Access, то лучше всего доверить создание таблицы MySQL утилите DBTools. В других случаях (при работе в UNIX или если ваши файлы данных поступают не из Access) утилита guess_table.pl может оказаться более эффективной.


Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Диагностическая утилита для LOAD DATA
Извлечение и перестановка столбцов файлов данных
Импорт XML в MySQL
Импорт с помощью LOAD DATA и утилиты mysqlimport
Импорт файлов в формате CSV

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