Рекомендации по импорту больших файлов CSV

Ответ 1

У меня была такая же проблема примерно 2 недели назад. Я написал некоторые .NET, чтобы делать вставки ROW BY ROW, и по моим расчетам с объемом данных, которые у меня были, потребуется около недели для этого таким образом.

Поэтому вместо этого я использовал построитель строк для создания одного ОГРОМНОГО запроса и сразу же отправил его в свою реляционную систему. Это продолжалось от недели до 5 минут. Теперь я не знаю, какую реляционную систему вы используете, но с огромными запросами вам, вероятно, придется настроить ваш параметр max_allowed_packet или аналогичный.

Ответ 2

Простите меня, если я точно не понимаю вашу проблему правильно, но похоже, что вы просто пытаетесь получить большое количество данных CSV в базе данных SQL. Есть ли причина, по которой вы хотите использовать веб-приложение или другой код для обработки CSV-данных в операторы INSERT? Я успешно импортировал большое количество CSV-данных в SQL Server Express (бесплатную версию), используя SQL Server Management Studio и используя инструкции BULK INSERT. Простая объемная вставка будет выглядеть так:

BULK INSERT [Company].[Transactions]
    FROM "C:\Bank Files\TransactionLog.csv"
    WITH
    (
        FIELDTERMINATOR = '|',
        ROWTERMINATOR = '\n',
        MAXERRORS = 0,
        DATAFILETYPE = 'widechar',
        KEEPIDENTITY
    )
GO

Ответ 3

Во-первых: 33MB не большой. MySQL может легко обрабатывать данные такого размера.

Как вы заметили, вставка строки за строкой медленная. Использование ORM поверх этого еще медленнее: накладные расходы для создания объектов, сериализации и т.д. Использование ORM для этого через 35 таблиц еще медленнее. Не делайте этого.

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

Кроме того, также работает массив INSERT (column, ...) VALUES .... Не угадайте, какой должен быть размер вашей партии; как оптимальный размер партии будет зависеть от конкретной настройки базы данных (конфигурация сервера, типы столбцов, индексы и т.д.).

Массовая INSERT не будет такой же быстрой, как LOAD DATA INFILE, и вам все равно придется написать script, чтобы преобразовать необработанные данные в используемые INSERT запросы. По этой причине я, вероятно, сделаю LOAD DATA INFILE, если это вообще возможно.

Ответ 4

FWIW следующие шаги привели к огромному ускорению моего LOAD DATA INFILE:

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET SESSION tx_isolation='READ-UNCOMMITTED';
SET sql_log_bin = 0;
#LOAD DATA LOCAL INFILE....
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
SET SESSION tx_isolation='READ-REPEATABLE';

См. статью здесь

Ответ 5

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

LOAD DATA INFILE '/opt/lampp/htdocs/sample.csv' INTO TABLE discounts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (title,@expired_date,discount) SET expired_date = STR_TO_DATE(@expired_date, '%m/%d/%Y');

для получения дополнительной информации: http://dev.mysql.com/doc/refman/5.5/en/load-data.html и http://www.mysqltutorial.org/import-csv-file-mysql-table/

Ответ 6

Если вы используете Sql Server и имеете доступ к .NET, вы можете написать быстрое приложение для использования класса SQLBulkCopy. Я использовал это в предыдущих проектах, чтобы получить очень много данных в SQL очень быстро. Класс SQLBulkCopy использует SQL Server BCP, поэтому, если вы используете что-то другое, кроме .NET, возможно, стоит посмотреть, открыт ли этот параметр для вас. Не уверен, что вы используете БД, кроме SQL Server.

Ответ 7

Мне не нравятся некоторые другие ответы:)

Я делал это на работе.

Вы пишете программу для создания большого SQL script, полного инструкций INSERT, по одному на строку. Затем вы запустите script. Вы можете сохранить script для справок в будущем (дешевый журнал). Используйте gzip, и он уменьшит размер, равный 90%.

Вам не нужны какие-либо причудливые инструменты, и на самом деле неважно, какую базу данных вы используете.

Вы можете сделать несколько сотен вложений за транзакцию или все из них в одной транзакции, это зависит от вас.

Python - хороший язык для этого, но я уверен, что php тоже прекрасен.

Если у вас есть проблемы с производительностью, некоторые базы данных, такие как Oracle, имеют специальную программу массовой загрузки, которая быстрее, чем инструкции INSERT.

У вас должно закончиться нехватка памяти, так как вы должны анализировать только одну строку за раз. Вам не нужно держать все в памяти, не делайте этого!

Ответ 8

Я читаю CSV файл, который имеет около 1M записей и 65 столбцов. Каждая 1000 записей, обработанных в PHP, есть одна большая толстая инструкция MySQL, которая входит в базу данных. Письмо не требует времени. Это разбор делает. Память, используемая для обработки этого несжатого файла 600 МБ, составляет около 12 МБ.

Ответ 9

Мне тоже нужно делать это время от времени (импортируйте большие нестандартизированные CSV, где каждая строка создает дюжину связанных объектов БД), поэтому я написал python script, где я могу указать, что происходит там, где и как это делается все связанные. script затем просто генерирует инструкции INSERT.

Вот он: csv2db

Отказ от ответственности: в основном я использую noob, когда дело доходит до баз данных, поэтому могут быть лучшие способы для этого.

Ответ 10

Вы можете использовать генератор для эффективного хранения файлов. Ниже приведен небольшой фрагмент ниже.

#Method
public function getFileRecords($params)
{
    $fp = fopen('../' . $params['file'] . '.csv', 'r');
    //$header = fgetcsv($fp, 1000, ','); // skip header

    while (($line = fgetcsv($fp, 1000, ',')) != FALSE) {
        $line = array_map(function($str) {
            return str_replace('\N', '', $str);
        }, $line);

        yield $line;
    }

    fclose($fp);

    return;
}

#Implementation
foreach ($yourModel->getFileRecords($params) as $row) {
    // you get row as an assoc array;
    $yourModel->save($row);
}