perl - 基于Sqlite的DBI::CSV实现

Question

我经常处理文本文件，以“SQL 方式”将一个文件与另一个文件进行比较。
DBD::CSV显然是一个不错的选择，因为我可以在文本表上使用 SQL 语法的强大功能。但是，我处理巨大的文本文件，使 DBD::CSV在性能方面毫无用处。

因此，我开始编写一个将 csv 文件转换为 sqlite DB 的模块，然后返回 DBI::sqlite我可以玩的对象。问题是，将文本文件转换为 sqlite 表也不是很有效，因为我无法从 perl 运行 sqlite 命令行来快速加载 CSV 文件(使用 .load)。所以我必须创建一个巨大的Insert into基于文本表的字符串，并执行它(逐行执行插入在性能方面非常低效，所以我更喜欢执行一个大插入)。我愿意避免这种情况，寻找一种使用 perl 将 csv 加载到 sqlite 的单行器。

还有一件事，我使用以下函数很好地执行和打印 SQL 查询:

sub sql_command {
my ($self,$str) = @_;
my $s = $self->{_db}->prepare($str) or die $!;
$s->execute() or die $!;
my $table;
push @$table, [ map { defined $_ ? $_ : "undef" } @{$s->{'NAME'}}];
while(my $row = $s->fetch) {
    push @$table, [ map{ defined $_ ? $_ : "undef" }@$row ];
}
box_format($table);
return box_format($table);;
}


sub box_format {
my $table = shift;
my $n_cols = scalar @{$table->[0]};

my $tb = Text::Table->new(\'| ', '', (\' | ','')x($n_cols-1), \' |+');
$tb->load(@$table);
my $rule = $tb->rule(qw/- +/);
my @rows = $tb->body();
return $rule, shift @rows, $rule, @rows, $rule
    if @rows;
}

sql_command sub 执行大约需要 1 分钟(在 6.5 MB 文件上)，我认为这比我预期的要长得多。有没有人有更有效的解决方案？

谢谢!

Answer 1

Text::CSV_XS速度非常快，使用它来处理 CSV 应该可以解决性能问题的那一面。

不需要特殊的批量插入代码来使 DBD::SQLite 高效。带有绑定(bind)参数的插入语句非常快。主要技巧是在 DBI 中关闭 AutoCommit 并在单个事务中执行所有插入。

use v5.10;
use strict;
use warnings;
use autodie;

use Text::CSV_XS;
use DBI;

my $dbh = DBI->connect(
    "dbi:SQLite:dbname=csvtest.sqlite", "", "",
    {
        RaiseError => 1, AutoCommit => 0
    }
);

$dbh->do("DROP TABLE IF EXISTS test");

$dbh->do(<<'SQL');
CREATE TABLE test (
    name        VARCHAR,
    num1        INT,
    num2        INT,
    thing       VARCHAR,
    num3        INT,
    stuff       VARCHAR
)
SQL

# Using bind parameters avoids having to recompile the statement every time
my $sth = $dbh->prepare(<<'SQL');
INSERT INTO test
       (name, num1, num2, thing, num3, stuff)
VALUES (?,    ?,    ?,    ?,     ?,    ?    )
SQL

my $csv = Text::CSV_XS->new or die;
open my $fh, "<", "test.csv";
while(my $row = $csv->getline($fh)) {
    $sth->execute(@$row);
}
$csv->eof;
close $fh;

$sth->finish;    
$dbh->commit;

这在我的 Macbook 上运行了 1.5 秒内的 5.7M CSV 文件。该文件充满了 70,000 行...

"foo",23,42,"waelkadjflkajdlfj aldkfjal dfjl",99,"wakljdlakfjl adfkjlakdjflakjdlfkj"

使用绑定(bind)列可能会使其更快一点，但在我的测试中它减慢了速度。