导入大型CSV文件的最佳实践

Question

每个月，我的公司会得到一组CSV文件，其中包含银行账户信息，需要将其导入数据库。其中一些文件可能非常大。例如，一个文件大小约为33MB，有大约65,000行。

目前，我有一个symfony / Doctrine应用程序（PHP），可以读取这些CSV文件并将它们导入数据库。我的数据库有大约35个不同的表格，在导入过程中，我将这些行拆分成各自的对象并将其插入到数据库中。这一切都很完美，只是速度较慢（每行大约需要0.25秒）并且占用大量的内存。

内存使用情况非常糟糕，以至于我必须拆分CSV文件。一个有20,000行的文件勉强能够导入完成。当接近结束时，我使用了95％的内存。无法导入65,000行文件。

我发现Symfony是构建应用程序的卓越框架，通常我不会考虑使用其他任何东西，但在这种情况下，我愿意抛开所有预设，以追求更好的性能。我没有承诺任何特定的语言，DBMS或任何其他内容。

Stack Overflow不喜欢主观问题，因此我将尝试尽可能使其客观：对于那些不仅具有 导入大型CSV文件的经验，还使用过哪些工具/实践，这些工具/实践在过去是成功的？

例如，您是否只使用Django的ORM / OOP，并且没有遇到任何问题？还是将整个CSV文件读入内存并准备了一些巨大的INSERT语句？

再次强调，我需要的不仅是意见，而是在过去实际有效的方法。

编辑：我不仅要将一个具有85列的CSV电子表格导入到一个具有85列的数据库表中。我正在对数据进行规范化并将其放入数十个不同的表格中。因此，我不能只使用LOAD DATA INFILE（我使用的是MySQL）或其他DBMS的只读取CSV文件的功能。

此外，我不能使用任何微软特定的解决方案。

- Jason Swett

---

你在数据库端针对事务的创建/提交进行过性能分析吗？ - NG.

不，我的整个导入过程都包含在一个大事务中。就单独的INSERT语句本身而言，我还没有进行任何性能分析。如果有任何建议，将不胜感激。（然而光凭这一点并不能解决我的内存使用问题。） - Jason Swett

Answer 1

如果我没有完全理解你的问题，请原谅。看起来你只是想把大量的CSV数据导入到SQL数据库中。你使用Web应用程序或其他代码将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

- Jeff Camera

---

+1 很好的回答。这也使用了BCP（就像我的回答一样），但是你的方法不需要编码。@Jason：如果一个文件填充多个表格（我认为是这样的），那么可以将其BCP到单个表格中，并使用SQL批处理语句将其拆分成相关表格 - 这仍然比您当前的解决方案更快。 - Paul Hadfield

原因是我不仅仅是把一个85列的CSV电子表格导入到一个85列的数据库表中，而是对数据进行了归一化处理并将其放入不同的表中。 - Jason Swett

Jason：感谢更新，这确实会改变一些情况，但实际答案仍然可能是有效的。您可以使用可用的更快方法将数据导入MySQL，然后在MySQL中使用批处理语句进行规范化/拆分。 - Paul Hadfield

@Jason：你可能想要查看使用批量插入格式文件的方法- http://msdn.microsoft.com/en-us/library/ms191516.aspx你还可以考虑将CSV中的所有85列插入到一个临时的85列表中，然后使用SQL脚本处理数据，以便将其规范化到其他表中。当你处理这么多数据时，在数据库中处理它比其他任何方式都要快得多。 - Jeff Camera

@Paul：你说得对，我确实可以这样做。出于某种原因，这个想法并不完全吸引我，但它可能是最快的方法。 - Jason Swett

@Jason：我知道你的意思——最快的方式并不总是最好或最易维护的。如果可能的话，也许可以将你的PHP代码添加到这个问题中，看看其他人是否能够找出巨大内存消耗的原因（我猜测可能是变量没有被释放）。然后你的解决方案可能已经足够满足你的需求了。 - Paul Hadfield

Answer 2

两周前我遇到了完全相同的问题。我编写了一些.NET代码逐行插入数据，根据我的计算，对于我所拥有的数据量，这种方式需要大约一周的时间。

所以我使用了一个字符串构建器来创建一个巨大的查询，并一次性将其发送到我的关系数据库系统中。这样做只花费了5分钟，而不是一周。现在我不知道你使用的是什么关系数据库系统，但使用庞大的查询时，你可能需要微调max_allowed_packet参数或类似参数。

- kmarks2

---

@Jason，大约有150万。 - kmarks2

哇，每个INSERT语句都要这样吗？而且每个语句大约需要5分钟？你还在使用MySQL？有多少列呢？（对不起问了这么多问题。） - Jason Swett

@Paul，我们的案例涉及大量的DateTime数据，在应用程序连接到我们的rdbms时，它们严格要求每5分钟一个数据点。给我们提供数据的客户实际上给了我们Excel文件...所以我不得不对其进行清理，将其转换为CSV，然后编写一个智能解析器，以考虑客户的迟缓（每15分钟一个数据点，巨大的间隔等）和不正确的数据。如果您的CSV在开始时已知为格式良好且正确，则Bulk绝对是一个很好的起点。 - kmarks2

@Jason，忘了提到，这个CSV文件分散在38个文本文件中，格式不一致。有时候列会从一个文本文件切换到下一个文本文件。必须在CSV文件的顶部编写一些标记，以便解析器知道实际最终插入需要放置哪些列。解析器需要大量干预。客户基本上是把他们的Excel垃圾桶清空，把它弄皱并扔给我。 - kmarks2

哦，顺便说一下，每个INSERT并不是5分钟。那是作为单个查询发送的150万个分号分隔插入的5分钟。谢谢@Paul，我会检查这个的。 - kmarks2

Answer 3

首先，33MB不算大。MySQL可以轻松处理这种大小的数据。

正如您所注意到的，逐行插入是很慢的。在此基础上使用ORM甚至更慢：构建对象、序列化等都会有开销。在35个表之间使用ORM进行操作会更慢。不要这样做。

您确实可以使用LOAD DATA INFILE。只需编写一个脚本将数据转换为所需格式，并在此过程中将其分成每个表文件。然后可以将每个文件加载到正确的表中。这个脚本可以用任何语言编写。

除此之外，批量INSERT (column, ...) VALUES ...也可以工作。不要猜测您的行批量大小应该是多少；根据经验来计时，因为最佳批量大小取决于您特定的数据库设置（服务器配置、列类型、索引等）。

批量INSERT不会像LOAD DATA INFILE那么快，而且您仍然需要编写一个脚本将原始数据转换为可用的INSERT查询。出于这个原因，如果可能的话，我会选择LOAD DATA INFILE。

- candu

Answer 4

以下步骤可大幅优化我的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';

See article here

- Sam

---

这将我的数据插入负载从20分钟减少到11分钟，非常有帮助！ - Alex Beals

Answer 5

您可以使用Mysql的LOAD DATA INFILE语句，它允许您从文本文件中读取数据并快速将文件的数据导入数据库表中。 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/。

- R T

Answer 6

我不喜欢其他答案 :)

我曾经在一份工作中这么做。

你可以编写一个程序来创建一个大的SQL脚本，每行一个INSERT语句。然后运行此脚本。你可以保存此脚本以备将来参考（廉价日志）。使用gzip可以将其大小缩小90%。

你不需要任何高级工具，而且使用什么数据库并不重要。

你可以每个事务执行几百个插入操作，也可以在一个事务中执行所有插入操作，由你决定。

Python是一个很好的语言，但我相信php也可以。

如果你遇到性能问题，一些数据库（如Oracle）有一个特殊的批量加载程序，比INSERT语句更快。

你不应该耗尽内存，因为你只需要逐行解析。你没有必要将整个内容保存在内存中，请不要这样做！

- Glen P

---

纯天才，解决了我的问题。 简化版：不要立即导入，创建 SQL 文件并稍后导入（最好使用 SQL 导入工具，如 http://www.mysqldumper.net/ 来处理实际的大规模导入）。 转换后再导入。 - iGNEOS

Answer 7

如果您正在使用Sql Server并且可以访问.NET，则可以编写一个快速应用程序来使用SQLBulkCopy类。我以前在项目中使用过它，可以快速将大量数据导入SQL。 SQLBulkCopy类利用了SQL Server的BCP，因此，如果您使用的不是.NET，则可能值得研究一下是否也可以使用该选项。不确定您是否使用的是除SQL Server之外的其他DB。

- Paul Hadfield

Answer 8

你可以使用生成器来处理内存高效的文件读取。下面的代码片段可能会对你有所帮助。

#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);
}

- Mohammad Sharaf Ali

Answer 9

我正在读取一个包含近100万条记录和65个列的CSV文件。每处理1000条记录，就会有一个大而臃肿的MySQL语句进入数据库。写入所需的时间非常短暂，而解析则需要更多时间。处理这个未压缩的600MB文件所使用的内存约为12 MB。

- Cyril Joudieh

Answer 10

我有时也需要这样做（导入大型非标准化的CSV文件，其中每行创建十几个相关的数据库对象），因此我编写了一个Python脚本，可以指定数据放在哪里以及它们之间的关系。然后脚本会生成INSERT语句。

这是脚本链接：csv2db 免责声明：当涉及到数据库时，我基本上是个新手，所以可能有更好的方法来完成这个任务。

- Lukas