Привет парни, подкину задачку которую вяло пытаюсь решить некоторое время ) ) Имеем XML файлик размером 16 гигов на 11 миллионов товаров (знаю что полный бред и неподходящий формат но другого нет (((), надо его в БД mysql. Естественно быстрее всего с этим справиться сам mysql. Не так давно появился LOAD XML Код (Text): LOAD XML INFILE '$p' INTO TABLE xml CHARACTER SET 'utf8' ROWS IDENTIFIED BY '<item>' все чудно, по 5-7 тысяч позиций в секунду залетает в БД....но оно в упор не видит то что в ХМL содержится в тегах CDATA ( пробовал так: Код (Text): LOAD XML INFILE '$p' INTO TABLE xml CHARACTER SET 'utf8' ROWS IDENTIFIED BY '<item>' (@tmp) SET name=ExtractValue(@tmp, '//name') Пишет NULL вообще во все поля... Заходим с другой стороны, LOAD DATA INFILE Код (Text): LOAD DATA INFILE '$p' INTO TABLE xml CHARACTER SET 'utf8' LINES STARTING BY '<offers>' TERMINATED BY '</offers>' (@xml) SET picture = ExtractValue(@xml, '//picture'), name = ExtractValue(@xml, '//name'), и т.д. Все чудно читает и в CDATA....но пишет в БД все в одну строку ( Перегуглил все, перепробовал как мне кажется все варианты.....но либо первая беда либо вторая....может я какой секрет не знаю? Спасибо!
а боюсь его даже открывать на локальной машине ((( но примерно так Код (Text): <item id="1348" available="true" product_key="e110c3bc0442cce72b8a87614b9c5254e3c9262d" article="2199921" category_id="1693"> <picture>c22fdfb.big.jpg</picture> <picture>445.big.jpg</picture> <picture>8d085.big.jpg</picture> <picture>87774b.big.jpg</picture> <picture>c8e3bd1.big.jpg</picture> <thumbnail>bc22fdfb.small.jpg</thumbnail> <original_picture>2199921-1.jpg</original_picture> <name><![CDATA[Зонты Airton Зонт]]></name> <description><![CDATA[Зонт чёрный в три сложения. Эта модель легко открывается с помощью кнопки автомат, закрывается вручную. Достойная альтернатива дорогим зонтам.]]></description> <vendor><![CDATA[Airton]]></vendor> <model><![CDATA[Зонт]]></model> <oldprice></oldprice> <url>20d647e2a5a/bb33779d5b54644e/</url> <currencyId>RUR</currencyId> <categoryId>1693</categoryId> <price>880.00</price> </item>
1. проверить что мускул пишет в лог, включить глубокое логирование ему. Если ругается на память и тп.: добавь нужные ресурсы ему. 2. если ни в какую, нет ресурсов и «не вставляет и все» (читай: лень глубоко разбираться), то бери XMLreader или любимый аналог, который не читает все дерево в память и вперед. Этому решению всеравно по идее будет какой у тебя файл, мне и 20 и 50 “xml” разбирать доводилось (просто распаралеливал). + сразу данные отфильтровать можно. Отпиши обязательно как решишь.
@Zuldek 1. Я для теста гружу небольшой файлик, в нем всего 800 позиций, он за секунду в БД записывается 2. В документации я не нашел решение проблемы ни с LOAD XML INFILE ни с LOAD DATA INFILE, нагуглил несколько вариантов решения, но ни один мне не помог ( XMLreader, да, рассматриваю как вариант, но будет очень долго ((( работая с CSV такой объем данных (10-15 миллионов позиций) а обновлял практически одним мускулем за час-два, хотелось бы и тут такого результата. Сколько по времени это занимало, и что имелось ввиду под "распаралелливал"? Спасибо! --- Добавлено --- я видимо не совсем точно выразился вот тут пишется данные их нескольких тегов (для примера name) но все в одну ячеку БД, т.е. если это поле варчхар 1096 запишет 1096 символов, если децемал, запишет последнее значение (или первое, уже не помню точно) поле с AI единичка и все тут (
Ну во-первых надо реалистом быть. xml это не формат данных для мускула. Обычно всегда их нужно обрабатываеть еще и фильтровать перед записью. Долго если в 1 поток, раздаешь с мастерпроцесса в воркеры задачу: по 5к записей за шаг и вреред. У меня справлялись за 30 мин. 100+ воркеров, если просто на импорт в базу, но можно быстрее. И разумеется нужно писать свой обработчик Берется sax парсер или xmlридер и вперед --- Добавлено --- штатно sax парасер+конвертер в .sql есть готовые утилитки, но мне была нужна первичная обработка этих данных, поэтому не использовал
Я об этом написал в первом сообщении, я прекрасно это понимаю ((( Просто все еще надеюсь что есть какой то секрет о котором я не знаю )
разбор xml принципиально ни на одной платформе не отличается если нужна обработка данных . Везде юзается sax парсер, хоть пых хоть ява
Нет, в "блок" <offers> заключены 11 миллионов "итемов". В этих строчках задается начальная и конечная строка в которой будет "шарить" запрос.
Привет всем, вопрос был заброшен, но проблема не решилась и я к ней вернулся... Опишу коротко свои страдания ) LOAD XML INFILE, как писал выше не видит текста внутри CDATA. решение проще некуда, из консоли Код (Text): cat /путь до файла/xml.xml| sed -e 's/<!\[CDATA\[//g' | sed -e 's/\]\]>//g' >> /путь во что сохраняем/t2.xml Файлик в 15 гигов оно обрабатывает минут за 10, далее LOAD XML INFILE влет загружает его в БД Но...не тут то было, ибо Гиг XML-ля оно загоняет в БД за 10-20 минут...в вот 15.....молотило больше суток но так и не загрузило, процесс убил, пробовал несколько раз...сутки и даже больше...толку ноль... Пошел другим путем, симбиоз XMLReader и simplexml_load_string (последнее для удобства дальнейшей обработки) Но опять же мимо...объясню почему: я пытался создать некое "смещение" для чтения, сделал так: запустил цикл, в нем функция которая читает XMLReader`ом, пропускает нужное мне число элементов и возвращает следующие 10 тысяч....но опять сутки и все навсего 8 миллионов позиций загрузили....опять не наш вариант... ( Опытным путем было выяснено, что время на чтение постоянно увеличивается, что вобщем то логично Ну и был изобретен велосипед имени @Zuldek ))) Что бы не положить БД на рабочем проекте было увеличено max_allowed_packet до 512M. Это позволило (при моем составе XML делать инсерты по 20 тысяч записей, соответственно они не сыпались по 10 штук в секунду) Далее читаем XMLReader`ом файли от начала и до конца и сбрасываем каждые 20 тысяч в БД Итог вобщем такой: Время на чтение 20 000 элементов из xml - 2-3 секунды. Время работы PHP для обработки и подготовки INSERT`а в БД - около секунды. Время на вставку в БД 20 000 строк - около 4-х секунд. Итого 11 миллионов позиций загрузились за час, размер таблицы около 12 гигов. Но надо отметить следующее: в таблице нет индексов (а они нужны, добавление строковых индексов в таблицу с 11 миллионами записей то еще удовольствие ((( ) Продолжаю наблюдение )
LOAD XML INFILE у них хреново сделана. Это не предназначено для импорта больших данных, к сожалению. Во-первых она грузит весь файл сразу. Во-вторых, она относительно медленная. Мне как-то приходилось делать импорт ФИАС. И я по глупости решил сделать средствами mysql из xml. Поначалу думал, что будет самый быстрый импорт. Там было ~60 млн.записей, ~25Г файлов, и импорт занимал около 12 часов, без индексов. Причём большой файл пришлось резать внешней утилитой на куски по 2Г. Время импорта кусков зависело от размера таблицы - вначале 20 минут (4.5М записей/кусок), в конце 1.5 часа. CDATA там не было, поэтому не знаю как обходить. (хотя там возможна примитивная обработка записей)
А чем именно резал? У меня была подобная мысля, но я не нашел какого то простого решения ( Пробовал из XML в CSV перегонять, затратно и медленно (
Divider.zip Была взята отсюда http://www.sql.ru/forum/1190752/import-bd-fias-v-mysql-prakticheskiy-opyt Доморощенная поделка для разбивки больших XML-файлов. --- Добавлено --- Я склоняюсь к использованию потокового парсера (на том же ПХП) и настройки mySQL для максимально быстрой вставки данных. Думаю, в этом случае тот же ФИАС можно импортнуть часов за 5 и без всяких "дивидоров". Только попытать не могу - времени совсем нет для экспериментов.
Подниму тему, ибо появилась доп.инфа. И так, задачка повторилась, - импортнуть ФИАС. С того времени поменялось железо и версии: Win10, Процессор 4Gh, 32G RAM, HD, MySQL 5.7 x64. LOAD XML INFILE... - Важные настройки MySQL: InnoDB естественно, буфер 4G, двойная запись отключена. - XML и DB на разных дисках. - Временные файлы и логи вынесены на другой диск (не там, где DB). - Без разбивки больших XML. - Без оптимизации и нормализации БД (т.е. обычные "плоские" таблицы, как в XML). - Файл подкачки в винде "убит"/отключен. - Нагрузка на проц ~15% (одно ядро). - Объём всех XML: ~60G - Размер получившейся БД: ~36G - Время импорта: ~4 часа. При этом MySQL сожрал все 32Г, но всё же импортнул. Самая тоскливая таблица это AS_HOUSE***.XML: ~30Гб XML, ~68М записей, импортировало ~2 часа. SQL-таблица получилась ~16G.