Есть огромный csv-файл с данными (over 100k строк). Как все это добро на PHP, без SSH, загнать в MySQL?

Question

[alex stephen]

Здравствуйте. Задача: есть огромный csv-файл с данными (over 100k строк).
Нужно все это добро на PHP, без SSH загнать в MySQL бд... Лимит 30 сек.
Пробовал читать файл по 1000 строк (каждый раз приходится его открывать), естественно, в 30 сек оно не укладывается.

Как такие вещи делают гуру?

Comments

[Владимир Шишминцев]

Код свой продемонстрируйте

Answer 1

[Сергей]

Почитайте про LOAD DATA INFILE ( dev.mysql.com/doc/refman/5.1/en/load-data.html )

например с его помощью 40 млн 4кб строк грузились 40 мин (причем узким местом был php, который генерил эти данные) .. в вашей ситуации это будет намного быстрее думаю.

Comments

[alex stephen]

а вот это очень интересно. Если нигде это не запрещается исполнять на уровне PHP, то вроде подходит.

[Сергей]

Да. Я не уточнил что файл должен быть локальным по отношению к mysql т.е. должен быть доступен в локальной ФС mysql-сервера.

Answer 2

[lizergil]

Алгоритм в твоем случае будет таким:
1. Удаление всех индексов из таблицы, куда планируется записывать данные.
2. Открытие файла (fopen).
3. Чтение m строк (fgets) пока не конец файла.
4. Составление запроса в виде одной пачки (batch): INSERT INTO ... VALUES ( %row1%, %row2%, ... , %rowm%);
5. Выполение запроса.
6. Переход к шагу 3.
7. Конец файла, закрытие файла, строим удаленные индексы.

Если шаги 3 и 4 выполнять параллельно, то можно сэкономить на памяти.

По поводу лимита: у тебя сложность алгоритма O(n) - т. е. линейно зависит от кол-ва строк в файле, ускорить (если недостаточно) поможет либо оптимизация (использования низкоуровневых утилит для вставки данных, но данные эти заранее должны быть подготовлены), либо использование более производительного железа (клиента, сети, сервера).

Comments

[alex stephen]

Так и делаю. С запросами хватает на 300 строк максимум, если просто выводить — не на много больше. Узкое место — чтение файла

[lizergil]

Update: закрытие файла в п.7 конечно (исправил), т. е. открываем один раз и последовательно обрабатываем.

[ZuBB]

insert’ы нужно делать в рамках 1 транзакции и в таблице должен быть отключен/отсутствовать индекс

[Егор Казанцев]

Если делать insert с параметром delayed то можно сделать много инсертов сразу... дальше с ними будет разбираться база.

Бросайте использовать мускуль переходите на надежные базы типа firebird

Answer 3

[Владимир Мерк]

Приходилось парсить файлы с огромным количеством e-mail'ов, при похожих условиях. Я использовал аякс как прокладку. С одной php забирал данные на клиент, и потом партиями отправлял в другую, где вставлял все это в базу.

Могу отправить Вам на почту этот скрипт, правда он довольно топорный, делался в спешке. Для его использования лучше разбить файл на несколько частей и запустить парсинг в несколько окон, чтоб быстрее было. Если что допилите, как Вам нужно.

Comments

[alex stephen]

Идея интересная. Спасибо, возьму на заметку на будущее. Но, сейчас слишком мало времени это писать (AJAX никогда с первого раза не настраивается))).
Выше уже предложили нужный вариант

Answer 4

[svd71]

Пробовал читать файл по 1000 строк (каждый раз приходится его открывать), естественно, в 30 сек оно не укладывается...

Таки вещи делаются через AJAX. Файл открывается на сервере и в браузер клиента пересылается инфа о количестве строк. Далее клиент позылает через AJAX короткие задания: занести в базу строки с 1 по 10. Даже при большом файле на короткие операции не такие уж длинное время требуется. После выполнения этоко маленького куска сервер через AJAX сообщает результат операции: успешно внесено, ошибка в строке NNN или что то еще.

Минусом идет то, что у клиента все время должна быть открыта сессия браузера. При ее закрытии ничего бельше непосылается серверу и оперции останавливаются.

Comments

[alex stephen]

Ответ тот же, что и для VladimirMerk

Answer 5

[lubezniy]

Я обычно на клиентской стороне бью файл на части, копирую на ftp. А там специальный скрипт с GET-параметром, в котором стоит счётчик, читает сначала первую часть, даёт редирект на вторую; вторая на третью - и так до конца заливки.

Comments

[alex stephen]

Все-равно не хватит времени. Памяти то достаточно

Answer 6

[vapmaster]

Открывайте файл не весь, а кусками по N байт. Например, с помощью CURL.

Comments

[alex stephen]

а можно ссылкой на пример или доки?

Answer 7

[Александр Зеленин]

а открыть файл один раз памяти не хватает или что?

Comments

[alex stephen]

Сейчас это 5 МБ. В перспективе, думаю, гораздо больше будет)) Так что не вариант

Answer 8

[easyman]

А что мешает читать php скриптом файл не с начала и записывать на какой строке (на каком байте в потоке) остановились?

Comments

[alex stephen]

Не хватает времени..)

Answer 9

[Фаёзжон Бердиев]

Уважаемые товарищи, такая же муть и у меня :-D спасайте

Answer 10

[Егор Казанцев]

100к - аккуратно экселем на кусочки (эксель тянет до 999 999) . потом phpmyadmin аккуратно кусочки в базу.