代码之家 › 专栏 › 技术社区 › Jason Swett

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

import csv

Jason Swett · 技术社区 · 14 年前

我的公司每月都会收到一组csv文件,其中包含我需要导入到数据库中的银行帐户信息。其中一些文件可能非常大。例如,一条大约33MB,大约65000条线路。

现在我有一个symfony/district应用程序(php),它读取这些csv文件并将它们导入数据库。我的数据库有大约35个不同的表,在导入过程中,我将这些行拆分成它们的组成对象,并将它们插入数据库中。这一切都很好用,除了 缓慢的 (每行大约需要四分之一秒)而且它使用了大量的内存。

内存使用太差了,我不得不拆分csv文件。一个20000行的文件几乎无法进入。当它接近尾声时,我的内存使用率达到了95%。导入65000行文件是不可能的。

我发现symfony是构建应用程序的一个特殊框架,通常我不会考虑使用其他任何东西,但在这种情况下,我愿意以性能的名义将我所有的先入之见抛到窗外。我不致力于任何特定的语言、DBMS或任何东西。

堆栈溢出不喜欢主观问题,所以我会尽量使其不主观:因为你们中的人不仅有一个观点,而且 有导入大型csv文件的经验 ,您有哪些工具/实践 过去用过的 成功了吗?

例如,您是否只使用了Django的ORM/OOP而没有遇到任何问题?还是把整个csv文件读到内存中,准备一些冗长的文件? INSERT 声明?

再说一次,我不仅想要一个意见,而且想要一些过去对你有用的东西。

编辑:我不仅仅是将一个85列的csv电子表格导入到一个85列的数据库表中。我正在规范化数据,并将其放入几十个不同的表中。因为这个原因,我不能只使用 LOAD DATA INFILE (我使用的是MySQL)或任何其他DBMS的功能,只读取csv文件。

此外,我不能使用任何特定于Microsoft的解决方案。

10 回复 | 直到 8 年前

kmarks2 14 年前

两周前我也遇到过同样的问题。我写了一些.NET来逐行插入和计算我拥有的数据量,这样做需要大约一周的时间。

因此,我使用了一个字符串生成器来创建一个巨大的查询,并将其一次性发送到我的关系系统。从一周到五分钟。现在我不知道您使用的是什么关系系统,但是对于大量的查询,您可能需要调整您允许的最大数据包参数或类似的参数。

Jeff Camera 14 年前

如果我不能正确理解您的问题,请原谅,但您似乎只是在试图将大量csv数据导入SQL数据库。是否有任何理由要使用Web应用程序或其他代码将csv数据处理到insert语句中?我已经成功地使用SQL Server Management Studio和大容量插入语句将大量的csv数据导入到SQL Server Express(免费版本)。简单的大容量插入如下所示:

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

candu 11 年前

第一:33MB是不大的。MySQL可以很容易地处理这种大小的数据。

正如您注意到的,逐行插入是缓慢的。在上面使用ORM甚至更慢:有构建对象、序列化等开销。使用ORM跨35个表执行此操作是更慢 . 不要这样做。

你真的可以用 LOAD DATA INFILE ;只需编写一个脚本,将您的数据转换为所需的格式,并在过程中将其分为每个表文件。那你就可以了 LOAD 将每个文件放入正确的表中。这个脚本可以用任何语言编写。

除此之外,散装 INSERT (column, ...) VALUES ... 同样有效。不要猜测你的行批量应该是多少; 按经验计时 ,因为最佳批处理大小将取决于特定的数据库设置(服务器配置、列类型、索引等)。

体积 INSERT 不会那么快 加载数据填充 ,您仍然需要编写一个脚本来将原始数据转换为可用的 插入 查询。因为这个原因,我可能会这么做 加载数据填充 如果可能的话。

Sam 9 年前

以下步骤导致我的 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';

参见文章 here

Glen P 13 年前

我不喜欢其他答案。)

我以前在工作中做过这个。

编写一个程序来创建一个包含insert语句的大型SQL脚本,每行一个。而不是运行脚本。您可以保存脚本以备将来参考(便宜的日志)。使用gzip,它将缩小90%的大小。

您不需要任何花哨的工具,而且使用什么数据库也不重要。

您可以在一个事务中为每个事务或所有事务做几百个插入,这取决于您自己。

Python是一种很好的语言,但我相信PHP也很好。

如果您有性能问题,一些数据库(如Oracle)有一个特殊的批量加载程序,它比INSERT语句更快。

您应该耗尽内存,因为一次只分析一行。你不需要把整件事都记在心里,别这样!

R T 10 年前

你可以使用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');

Paul Hadfield 14 年前

如果您使用的是SQL Server并且可以访问.NET,那么可以编写一个快速应用程序来使用 SQLBulkCopy 类。我在以前的项目中使用过这种方法,可以很快地将大量数据导入到SQL中。sqlblkcopy类利用了SQL Server的BCP,因此,如果您使用的不是.NET,那么可能需要研究该选项是否也对您开放。不确定是否使用的数据库不是SQL Server。

Mohammad Sharaf Ali 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);
}

Cyril Joudieh 12 年前

我正在读一个csv文件,它有将近100万条记录和65列。在PHP中处理的每1000条记录中,都有一条大的胖mysql语句进入数据库。写作根本用不着时间。解析就是这样。用来处理这个未压缩的600MB文件的内存大约是12MB。

Lukas 11 年前

我也需要经常这样做(导入大型的非标准化CSV,其中每一行创建十几个相关的DB对象),所以我编写了一个python脚本,在其中我可以指定与之相关的内容和方式。然后脚本只生成insert语句。

这里是: csv2db

免责声明:在数据库方面,我基本上是个笨蛋,所以可能有更好的方法来实现这一点。