SQLを使ってCSVファイルからデータを抽出する方法 - Groovy + H2 データベース

Java Groovy

CSV ファイルからデータを抽出するのに SQL を使いたいケースがあると思いますが、RDB をセットアップして CSV ファイルをインポートしたりするのは非常に面倒です。

そこで、Groovy と H2 データベースを使って手軽に実施する方法をご紹介します。

必要な環境は以下の通りです。

  • Groovy 1.8.0

サンプルソースは http://github.com/fits/try_samples/tree/master/blog/20110702/

CSV ファイルの用意

今回は 駅データ.jp の駅情報 CSV ファイルからデータを抽出する事にします。

駅データ.jp のサイトから駅データの CSV ファイルをダウンロードし、都道府県マスターをマスターデータページの内容を元に作成しました。

  • m_station.csv（駅データ）
  • m_pref.csv（都道府県マスター）

なお、文字コードは Shift_JIS に変換しています。（環境に応じて変更する）

例 m_station.csv（駅データ）
rr_cd,line_cd,station_cd,line_sort,station_sort,station_g_cd,r_type,rr_name,line_name,station_name,pref_cd,lon,lat,f_flag
11,11101,1110101,11101,1110101,1110101,1,JR,JR函館本線(函館〜長万部),函館,1,140.726413,41.773709,1
11,11101,1110102,11101,1110102,1110102,1,JR,JR函館本線(函館〜長万部),五稜郭,1,140.733539,41.803557,1
・・・
例 m_pref.csv（都道府県マスター）
pref_cd,pref_name
1,北海道
2,青森県
・・・

駅データの仕様は 駅データ.jp のページをご参照ください。

SQL を使って CSV ファイルからデータを抽出

Groovy と H2 データベースを使って、駅データの CSV ファイルから路線数が多い上位 10 駅を抽出するスクリプトは次のようになります。

complex_lines_listup.groovy
//H2 のモジュール定義
//JDBC ドライバーとして使うために @GrabConfig の設定は必須
@Grapes([
    @Grab(group = 'com.h2database', module = 'h2', version = '1.3.157'),
    @GrabConfig(systemClassLoader = true)
])
import groovy.sql.Sql

//インメモリDBとして H2 を使用
def db = Sql.newInstance("jdbc:h2:mem:", "org.h2.Driver")

//駅グループ（= station_g_cd）と駅名（= station_name）でグルーピングして
//路線数の合計（= lines）が多い上位 10 駅を抽出
def sql = """
SELECT *
FROM (
    SELECT
        pref_name,
        station_g_cd,
        station_name,
        count(*) as lines
    FROM
      CSVREAD('m_station.csv') S
      JOIN CSVREAD('m_pref.csv') P ON S.pref_cd = P.pref_cd
    GROUP BY station_g_cd, station_name
    ORDER BY lines DESC, station_g_cd
)
WHERE ROWNUM <= 10
"""

db.eachRow(sql) {r ->
    println "${r.station_name}駅（${r.pref_name}）: ${r.lines}"
}

H2 では CSVREAD 関数という CSV の内容をテーブルのように使える関数が用意されているので、Groovy と組み合わせれば非常に簡潔なスクリプトで CSV に対して SQL を実行できます。（JOIN にも使えますし、CSV ファイルの1行目を列名に使えます）

ちなみに、以下のように CSV ファイルからテーブルを作成する事も可能です。

m_station.csv ファイルから m_station テーブルを作る例
db.execute("create table m_station as select * from CSVREAD('m_station.csv')")

H2 は CSVREAD や ROWNUM 関数以外にも SQL 用の関数が一通り用意されているので（参照 Functions）、中々使い勝手も良いと思います。

@Grab アノテーションを使ってモジュールの依存関係を設定すれば、H2 の JAR ファイルを事前に用意する必要も無く、Groovy スクリプトと CSV ファイルさえ用意すればすぐに実行できる点が非常に有用だと思います。

実行例
> groovy complex_lines_listup.groovy
新宿駅（東京都）: 12
横浜駅（神奈川県）: 11
東京駅（東京都）: 10
渋谷駅（東京都）: 10
池袋駅（東京都）: 9
大宮駅（埼玉県）: 9
新橋駅（東京都）: 7
大船駅（神奈川県）: 7
上野駅（東京都）: 7
千葉駅（千葉県）: 7