Top
1GB超のCSVファイルを編集する
2013年 03月 28日 *
NHKでビッグデータを連呼しているから、容量の大きいデータをどのように処理するか考えてみた。

その前に、比較的容量の少ないデータを扱う場合に普段どうしているだろう。

エディタで加工したり、エクセルを用いたり。
プログラムの得意な人はエクセルマクロを使うかもしれない。
データを結合するのにアクセスを使うかもしれない。
普段のオフィスワークに関しては、それで事足りる場合がほとんどである。

しかし、問題はファイルサイズが大きい場合だ。

そもそもエクセルではファイルサイズの大きいデータを開くことすらできない。
昔のエクセルでは65,536行、最近のエクセルでも1,048,576行が限界である。
エクセルマクロもその制限の影響を受けるので、正常終了するマクロであっても大容量のデータを扱おうとすると異常終了で終わる。
また、100万行に近いデータは開くだけでも時間がかかり、ちょっと編集するだけでも動作が重くなる。


この重い動作を何とかできないか考えるのが今回のテーマだ。


ならばアクセスはどうか。
アクセスなら2Gまでのデータは扱えることになっている。
が、やはり1G超のデータを扱おうとすると処理が重くなる。
インポートだけでも数分かかり、クエリの実行だけでも数分かかり、エクスポートにも数分かかる。
その間もアプリが固まっているのか不安になり、他の操作を受け付けないので、椅子に座ってじっとしている以外にない状態。
結論として、アクセスで1G超のデータを扱うのはあまり得策ではない気がする。


ではエディタならどうか。
フリーのエディタとして有名なのがTeraPadとサクラエディタである。
しかし、TeraPadで1G超のテキストファイルを開こうとするとメモリ不足のエラーで開くことができない。
なんだよと思いながら、試しにサクラエディタで開こうとするとPCが固まった状態になる。
これでは最初からファイルを開かないTeraPadの方が動作としては良心的なのかもしれない…。

大容量データを開けるエディタとしてはEmEditorがある。
有料版はWindows7 64bit対応しており、1G超のファイルを軽快に開くことができる。
フリー版でも1G超ファイルを開くことができ、正規表現にも対応しているので、ちょっとした編集ならこのソフトが使えるかもしれない。
でも100万行全てを置換しようとするとやはり何分も時間がかかってしまうところが少し残念。
またエクセルと違って自由に並び替えしたりできないので、これだけで編集を完結させるのは無理そうだ。


という訳で、PCにRDBMSというものをインストールし、1G超のデータの加工にどれくらいの時間がかかるのか測ってみようと思った。


■MySQL

まずはオープンソース系で世界シェアNo1と言われているMySQLをインストールしてみる。
実際にはXAMPPをインストールすると色々なソフトと一緒にMySQLも簡単に使えるようになるので、XAMPPを入れてみた。

今回、データの加工用に用意したのは、約1.2GBのCSVデータ。
行数だと500万行ちょっとくらい。
当然エクセルでは開くことすらできない。
またアクセスを用いてもかなり動作が重くてイライラするデータ容量と言えるだろう。

データをインポートする前に、まずテーブルを用意しなければならない。
いちいちコマンドを打つのが面倒な人はphpMyAdminを使うとGUIベースでテーブルを作成することができる。

また作成したテーブルの型だけを複製したいといった場合も

CREATE TABLE 新規テーブル LIKE 既存テーブル;

という風にLIKE~を使うと便利。

では早速データをインポートしよう。

コマンドは、

load data local infile "ファイルの場所" into table テーブル名 fields terminated by ',';

のような感じで入力。
結果は

Query OK, 5293104 rows affected (1 min 43.75 sec)

2分弱か…。
決して速い訳ではないが、遅いとも言えない。

ではテーブルの中身を5行ばかり表示してみよう。

コマンドは、

select * from テーブル名 limit 5;

のように入力。
するとエンターキーを叩いたと同時に結果が表示された。

5 rows in set (0.00 sec)

おお、これはすごい速いぞ!
ちなみに列が沢山あるテーブルが変に改行されて見づらくなってしまう現象が起こる場合は、コマンドプロンプトのプロパティで幅の設定を変更できる。

今度は並び替えをしてみよう。

コマンドは、

select * from テーブル名 order by 列名 limit 5;

のように入力。
結果は、

5 rows in set (17.89 sec)

まあこれもそれなりに速いじゃないか!

と、この時点では興奮気味に色々コマンドを叩いていたのだが…。

テーブルを並び替えて連番を振ってみようと思ったが、そう単純にはいかない。
そもそもデータベースにはオートナンバーのようなレコードを一意に特定するための数字を振る概念はあるが、並び替えた順序で連番を振るといった考えはあまり存在しないようだ。
ただネットで検索すると、ユーザー定義関数を使用すれば実現できるらしい。

SET @i := 0;
UPDATE テーブル名 SET id = (@i := @i +1) ORDER BY 列名1,列名2,…;

実際にやると確かに連番が振れた。
が、6分近く時間がかかった。
単純に100万行の連番を振るのに1分近くかかる計算。
やはりあまりメジャーな操作でない分、実行は速くできないようだ。

次に重複の削除をしてみよう。
ちなみにエクセルにも2007から重複の削除機能が追加されている。
データベース的にはデータを削除するというより、重複データを表示しないようにするという感覚に近い。

コマンドとしては、以下のように先ほど連番を振ったid列とサブクエリとgroup by句を使用することで実現できる。

select * from テーブル名 where id in (
select min(id) from テーブル名 group by 列名
)

だがしかしここで問題が生じる。
数百行のテーブルならすぐに実行できるが、数百万行のテーブルだと実行に時間がかかりすぎる様子。
30分近く経っても結果が返ってこない…。
ネットで調べてみるとMySQLのサブクエリは遅いらしく。
自分はこの時点をもって、MySQLを使用していくことを断念した。orz
そもそも使うコマンドが間違っている可能性もあるが、ここで心が折れました…。

(PostgreSQL編に続く)
[PR]
by nochoice1 | 2013-03-28 00:22 | Comments(0) *
<< 1GB超のCSVファイルを編集... ページトップ 震災ビッグデータ(追記) >>
The Original by Sun&Moon