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

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

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

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

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

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

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

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

http://mvnrepository.com/artifact/com.h2database/h2/

ちなみに、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を検索してみましょう。
ここで公開しているみたいなので、「全国一括」をクリックしてダウンロードして解凍しましょう。

http://www.post.japanpost.jp/zipcode/dl/kogaki-zip.html

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

```sql
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"

```

ポイントは、

```sql
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'
)
```
の部分です。
まあコメントで記載した通りなんですが。

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

```sql
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
```

結果

```result
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とかからも検索出来るみたいです。

```sql
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から操作もできます。

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

https://github.com/2KB/h2db-test01

参考まで。

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

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