Поиск по блогу

понедельник, 31 мая 2010 г.

PHP: построчное чтение и обработка больших CSV-файлов

С проблемой обработки больших CSV-файлов на PHP в первый раз я столкнулась недавно. На PHP я вообще мало программирую, только если возникают задачи написать что-либо конкретно на этом языке.

В предыдущей статье были рассмотрены разные варианты импорта CSV-файла в базу данных MySQL. Там же я отметила, что работа с большими файлами требует особого подхода. Основным ограничением для импорта большого объема данных является время выполнения скрипта, которое задается хостером (как правило 30 секунд).

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

Когда я прочитала в описании утилиты BigDump (в предыдущей статье я на нее ссылалась) о принципе работы:

The script executes only a small part of the huge dump and restarts itself. The next session starts where the last was stopped. (Перевод: Скрипт выполняет лишь небольшую часть SQL-команд из файла и перезапускает сам себя. В следующий раз импорт начинается с того места, в котором скрипт прервал свою работу.)


я поняла, что мне обязательно нужно попробовать такое решение. Поиски в инете чего-то похожего окончились успешно.

$file_name = $_GET['path'];

$conn = mysql_connect ("localhost", "username", "pass")
or die ("Соединение не установлено!");
@mysql_select_db("db_name") or die ("Соединение не установлено!");

if (($handle_f = fopen($file_name, "r")) !== FALSE)
{
// проверяется, надо ли продолжать импорт с определенного места
// если да, то указатель перемещается на это место
if(isset($_GET['ftell'])){
fseek($handle_f,$_GET['ftell']);
}
$i=0;
if(isset($_GET['x'])){
$x=$_GET['x'];
} else {
$x = 0;
}

// построчное считывание и анализ строк из файла
while ( ($data_f = fgetcsv($handle_f, 1000, ";"))!== FALSE) {
$insert_q = 'insert into temp1 (code,contract,price,amount,dat_time,is_op) values '.
' (\''.$data_f[0].'\',\''.$data_f[1].'\',\''.$data_f[2].'\',\''.$data_f[3].'\',\''.$data_f[4].'\',\'0\')';
@mysql_query($insert_q);

if(!strstr($i/5000,'.')){
print 'Importing record #: '.$x.'<br />';
flush();
ob_flush();
}

if($i==20000){
print '<meta http-equiv="Refresh" content="0; url='.$_SERVER['PHP_SELF'].'?x='.$x.'&amp;ftell='.ftell($handle_f).'&amp;path='.$_GET['path'].'">';
exit;
}
$x++;
$i++;

}

fclose($handle_f);
} else {$err = 1; echo "Не получилось открыть файл";}


В параметре path при вызове скрипта передается путь к файлу, из которого надо производить импорт. В скрипте происходит импорт определенного количества строк (в примере - 20000), после чего он перезапускает сам себя с параметрами, среди которых кроме названия файла передается указатель на то место, с которого продолжать импорт (ftell).

Я протестировала этот скрипт на файле размером 60 Mb. Отработал он правильно, все проимпортировал. Но время работы, все-таки, хотелось бы уменьшить.

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

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

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

Несправедливо было бы обойти вниманием комментарий maxnag-а к предыдущему посту и не упомянуть о возможности импорта данных из CSV средствами MySQL. Почитала документацию по LOAD DATA INFILE, осталось потестировать на больших файлах :) Сначала я отмела для своего случая такой вариант, но потом решила, что, если он будет достаточно производительным, можно будет проимпортировать данные во временную таблицу, а затем произвести обработку и записать, куда надо. Но о результатах теста как-нибудь в следующий раз.

Всем удачных решений! :)


Чтобы быть в курсе обновлений блога, можно подписаться на RSS.

Статьи схожей тематики:



7 комментариев:

  1. всё же вставка средствами самой бд должна быть наиболее быстрой.

    А если надо делать проверки и обработку, то проще и быстрее вставить данные во временную таблицу и уже там их обрабатывать. Причем желательно это так же делать не по строчечно, а общей командой sql ко всем строкам. Это даст макс производительность. По возможности, если файл очень большой, то стоит его разбить на части.

    ИМХО

    ОтветитьУдалить
  2. Есть специально для целей быстрого импорта встроенное решение.
    LOAD DATA INFILE `./file.csv`
    INTO TABLE table3 (pole1, pole2)
    FIELDS TERMINATED BY `,` LINES TERMINATED BY `\n`
    SET pole4 = 12345678

    Которое и есть самое быстрое.

    ОтветитьУдалить
  3. Владимир, да, если обрабатывать не надо, а надо просто проимпортировать, то удобно воспользоваться напрямую SQL-запросом. Об этом, вроде, говорилось в первой статье серии: http://parsing-and-i.blogspot.com/2010/05/csv-mysql.html

    ОтветитьУдалить
  4. Когда мне надо было распарсить CSV файл в 3 гига с 150 полями, то самым простым и быстрым решением оказалось использовать старый и добрый Perl, PHP не потянул

    ОтветитьУдалить
  5. Спасибо, Маша! Бастро решил свою задачу :)

    ОтветитьУдалить
  6. Отличный вариант. Спасибо за статью!
    Использовал для импорта 50к строк. Перед импортом очищаю таблицу "DELETE `table_name`", чтобы не делать проверку на существование записи.
    Поставил лимит в 500 строк. Выполняется запрос около минуты, размер файла 100Мб

    ОтветитьУдалить
  7. Большое спасибо! А как такое провернуть через Cron?

    ОтветитьУдалить

Комментарии модерируются, вопросы не по теме удаляются, троллинг тоже.

К сожалению, у меня нет столько свободного времени, чтобы отвечать на все частные вопросы, так что, может, свой вопрос лучше задать на каком-нибудь форуме?

Поделиться