生CSVファイルに対してSELECTして検索する方法

CSVファイルからデータを抽出したい場合、データ量が膨大だったり、条件が複雑だったりした場合、
「あー、SQLで検索かけたいなー。でもわざわざDBにテーブル作ってINSERTするのも面倒だなー」
とか思ったりすることがあります。

そんな願望を叶える小技を見つけたので紹介します。

#概要
H2DBというDBがありますが、このJDBCドライバーを通して、例えば下記のようなSQLを実行すると、生CSVファイルに対して検索がかけられます。

select
		*
	from 
		csvread(
			-- CSVファイルパス
			'/Hoge.CSV',
			-- カラム名一覧。カンマ区切り
			'TEST1,TEST2',
			-- 文字コード
			'Shift-JIS'
		)
	where
		TEST1 like 'あいう%'

ドライバーを使う設定だけすればよく、DBをセットアップしたり、テーブルを作成する必要はありません。
LIKE文、正規表現なども使えて便利です。
あとJOINとかも出来るようです。

#やること

• H2DBドライバーをダウンロード
• お好みのDBビューアーをセットアップ
• 郵便番号一覧がのったCSVファイルをダウンロード
• SELECTしてみる
• Javaから操作してみる

#H2DBドライバーをダウンロード
下記からJDBCドライバーをダウンロードしましょう。

ちなみに、H2DBはこのJarファイル一個で、DBそのものと、ドライバーがセットになっています。
その辺りの話もいつかまとめたいですね。

#お好みのDBビューアーをセットアップ

好きなDBビューアーをセットアップしましょう。
ダウンロードしたjarファイルをを登録し、接続情報を追加します。
接続情報はこんなふうになります。

• ドライバー名：org.h2.Driver
• 接続URL：jdbc:h2:mem:hoge（hogeの部分はどんな値でも可）
• ユーザー：空でいい
• パスワード：空でいい

まあCSVファイルの操作なので、H2DB自体を使うわけではないのですが、一応接続出来るようにする必要があります。
H2DBは、上記のように「jdbc:h2:mem」などの接続URLにするとDBを起動とかしなくても接続できるモードになります。

#郵便番号一覧がのったCSVファイルをダウンロード
せっかくなので郵便番号とかのったCSVを検索してみましょう。
ここで公開しているみたいなので、「全国一括」をクリックしてダウンロードして解凍しましょう。

#SELECTしてみる
さて、これで設定は終了です。では下記のようなSQLでSELECTしてみましょう。

select
		*
	from 
		csvread(
			-- CSVファイルパス
			'/xxx/KEN_ALL.CSV',
			-- カラム名一覧。カンマ区切り。CSVの一行目にカラム名が記載されている場合は、nullにする。なぜか大文字しかダメな模様。
			'LOCAL_GOVERNMENT_CODE,OLD_ZIP_CODE,ZIP_CODE,KANA1,KANA2,KANA3,ADDRESS1,ADDRESS2,ADDRESS3,PREF_CODE',
			-- 文字コード
			'Shift-JIS'
		)
	where 
		ZIP_CODE = '9071801'

結果

LOCAL_GOVERNMENT_CODE	OLD_ZIP_CODE	ZIP_CODE	KANA1	KANA2	KANA3	ADDRESS1	ADDRESS2	ADDRESS3	PREF_CODE
"47382"	"90718"	"9071801"	"ｵｷﾅﾜｹﾝ"	"ﾔｴﾔﾏｸﾞﾝﾖﾅｸﾞﾆﾁﾖｳ"	"ﾖﾅｸﾞﾆ"	"沖縄県"	"八重山郡与那国町"	"与那国"	"0"

ポイントは、

csvread(
	-- CSVファイルパス
	'/xxx/KEN_ALL.CSV',
	-- カラム名一覧。カンマ区切り。CSVの一行目にカラム名が記載されている場合は、nullにする。なぜか大文字しかダメな模様。
	'LOCAL_GOVERNMENT_CODE,OLD_ZIP_CODE,ZIP_CODE,KANA1,KANA2,KANA3,ADDRESS1,ADDRESS2,ADDRESS3,PREF_CODE',
	-- 文字コード
	'Shift-JIS'
)

の部分です。
まあコメントで記載した通りなんですが。

他にも、こんな風に正規表現が使えたりします。

select
		*
	from 
		csvread(
			'/xxx/KEN_ALL.CSV',
			'LOCAL_GOVERNMENT_CODE,OLD_ZIP_CODE,ZIP_CODE,KANA1,KANA2,KANA3,ADDRESS1,ADDRESS2,ADDRESS3,PREF_CODE',
			'Shift-JIS'
		)
	where 
		ADDRESS3 regexp '（.*階）$'
	limit 2

結果

LOCAL_GOVERNMENT_CODE	OLD_ZIP_CODE	ZIP_CODE	KANA1	KANA2	KANA3	ADDRESS1	ADDRESS2	ADDRESS3	PREF_CODE
"04101"	"980  "	"9806101"	"ﾐﾔｷﾞｹﾝ"	"ｾﾝﾀﾞｲｼｱｵﾊﾞｸ"	"ﾁﾕｳｵｳｱｴﾙ(1ｶｲ)"	"宮城県"	"仙台市青葉区"	"中央アエル（１階）"	"0"
"04101"	"980  "	"9806102"	"ﾐﾔｷﾞｹﾝ"	"ｾﾝﾀﾞｲｼｱｵﾊﾞｸ"	"ﾁﾕｳｵｳｱｴﾙ(2ｶｲ)"	"宮城県"	"仙台市青葉区"	"中央アエル（２階）"	"0"

他のCSVと組み合わせてJOINとか、H2DBと連携して、H2DBにデータを入れたり、H2DBの内容をCSVに吐き出したりもできます。

csvread関数のオプションを変えれば、TSVとかからも検索出来るみたいです。

select
        *
    from 
        csvread(
            -- TSVファイルパス
            '/xxx/KEN_ALL.TSV',
            -- カラム名一覧。指定する区切り文字に合わせて、カラム名の区切り文字も変える
            'LOCAL_GOVERNMENT_CODE	OLD_ZIP_CODE	ZIP_CODE	KANA1	KANA2	KANA3	ADDRESS1	ADDRESS2	ADDRESS3	PREF_CODE',
            -- 文字コード
            'Shift-JIS',
            -- 区切り文字。char(9)がタブ文字とのこと
            char(9)
        )
	limit 2;

・・・なんか知らないけど文字化けましたが。
はて。

#Javaから操作してみる
JDBCドライバーなので、当然Javaから操作もできます。

下記にコミットしてみました。

参考まで。

ちなみにSQLファイルの実行には"Mirage"というORマッパーを使っています。
http://dev.classmethod.jp/server-side/java/mirage-sql-for-java/
http://amateras.sourceforge.jp/site/mirage/welcome.html

これもとても便利なライブラリなので、いずれまとめたいですねー。