QA@IT

SQL ServerにCSVファイルをインポート方法

20148 PV

■環境
 DB:SQL Server 2012
 PG:Visual Studio 2010

■質問
SQL Serverに5GB前後のCSVを取り込む処理を作成しています。

取り込むCSVの仕様は以下です。
 1. 「,」区切りである
 2. 全カラム「"」で括られている
 3. カラム内に「"」が存在する場合は「"」でエスケープされている(「"」→「""」)

本当はBCPかBULK INSERTで取り込みたかったのですが
上記3がフォーマットファイルで対応できないのと
区切り文字を「","」とする必要があり
文字列内に「","」がある場合、取り込めません。

現在はC#でVisualBasic.FileIO.TextFieldParserを使用してCSVを解析し
INSERTしていくプログラムを作成して取り込んでいますが
処理に7~8時間かかってしまいます。

お客様より3~4時間で終わるようにしてほしいという要望があり
試行錯誤していますが行き詰っています。

何かいい案があればご教授お願いします。

■補足
今はSSISでどうにかできないか思案中です。

回答

まとめて1000行づつとか INSERT するだけで大分性能が改善すると思いますが、それでも不足のようなら

SqlClient.SqlBulkCopy
http://msdn.microsoft.com/ja-jp/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx

の使用も検討してみてください。

編集 履歴 (0)
  • 1000行づつというのは1回のリクエストでINSERT文を1000行投げるという事でしょうか?
    そのような事ができるのも知らなかったので試して見ます。
    -
  • SqlBulkCopyが早いのは知っているのですが、CSVパーサーで解析した結果をDataReaderで読めるという事ですか?
    ちょっと試してみます。ありがとうございました。
    -
  • >1回のリクエストでINSERT文を1000行投げる
    そーです。
    -
  • >CSVパーサーで解析した結果をDataReader

    なぜ DataReader? SQL Server に書き込むんですよね?
    -
  • SqlBulkCopyはDataTableかDataReaderを引数に渡すと思っていました。DataTableですと今回の場合は件数的に無理があるので勝手にDataReaderと判断していました。DataTableに1000行入れてWriteToServerメソッドを実行するイメージでしょうか? -
  • 1000行ごとにクエリを実行するようにしたところ2時間弱で処理が終わるようになりました。今回は時間も予算もないのでお客様にはこれで妥協して頂き、私のほうでSqlBulkCopyも試してみようと思います。ありがとうございました。 -

ボトルネックがどこかわかりませんし、実際に試したわけでもないので早くならないかもしれませんが・・・

以下の手段は検討されたでしょうか?

(1) 分散クエリを使う

分散クエリ
http://technet.microsoft.com/ja-jp/library/ms188721(v=sql.105).aspx

(2) クエリをパラメータ化する

第 4 回 アドホック クエリのパラメータ化
http://technet.microsoft.com/ja-jp/sqlserver/gg639072

上の記事「分散クエリ」に書いてある JET プロバイダは「CSVの仕様」1, 2, 3 に対応しています。

CSV パーサー
http://surferonwww.info/BlogEngine/post/2010/10/28/CSV-parser.aspx

編集 履歴 (0)
  • いろいろご助言ありがとうございます。
    教えて頂いた内容でも性能改善するか試してみます。
    -
  • (1)はあまり関係なさそうと判断して試しませんでした。(2)はアドホッククエリは基本的に書かないので実装済です。但し、JETプロバイダは後学の為にも時間があるときに試してみようと思いました。ありがとうございました。 -

もう一つ。

>本当はBCPかBULK INSERTで取り込みたかったのですが
>上記3がフォーマットファイルで対応できないのと
>区切り文字を「","」とする必要があり
>文字列内に「","」がある場合、取り込めません。

であれば、直接取り込めるようにファイルを変換するプログラムを書いて、
その後一気に取り込むのはどうでしょう。

取り込む時にあれこれ工夫するのではなく、取り込めるようにファイル変換を
するほうが作業の切り分け的にも楽かな、と思います。

編集 履歴 (0)
  • 5GB前後のテキストファイルを変換して取込むのも考えましたが
    以前に別件でテストファイルの変換プログラムをC#で作成したのですが
    性能が悪くはまった記憶が・・・
    ですが、BCPなどが使えれば間違いなく取り込みの性能は上がりますので
    候補の1つとして時間があれば試してみます。
    ありがとうございました。
    -
  • よくよく考えると今回ボトルネックになっているのはCSVの解析処理ではないので無駄かなと思い提示して頂いた案は止めました。私の技術力不足なのですがCやC++などでテキストファイルの高速変換処理が書ければいいのですが私が書いてもC#と差のないものになってしまいそうで断念します。ありがとうございました。 -

こんにちは。

おそらくCSVのパースより1行ずつINSERTするのが遅いのですよね?

例えば。。。
varchar(1000)だけの列をもつワークテーブルにBCPで取り込む。
(もちろん列サイズは十分格納できるサイズで)
insert into 本来の格納先
select CSVの分解するストアド from ワークテーブル

みたいなことはどうでしょう。

ざっくりしたアドバイスで申し訳ないです。

編集 履歴 (1)
  • 文字列処理はDBの苦手分野との思い込みがあり、考えていない方法でした。
    有効かもしれませんので試してみます。
    -
  • BCPはやはり高速で取込めました(1時間弱)が、やはりSQLで分解する処理が重くて途中で諦めました。分解する処理をSQLCLRで書くことも考えましたが今回あまり時間がないのでこの方法は諦めます。ありがとうございました。 -

やったことないので早いかどうかはわかりませんが、「MS Accessで取り込む」ってのはどうでしょうか。AccessならCSVを直接インポートすることができます。

編集 履歴 (0)
  • Accessはファイルサイズが2GBまでの制限があるようです。
    今回、取込みファイルが5GB前後なのでAccessは使えないかと思います。
    -
ウォッチ

この質問への回答やコメントをメールでお知らせします。