CLOVER🍀

That was when it all began.

H2 Databaseで、CSV/TSVファイルにSQLを実行する

ちょっとお手軽に、CSVやTSVに対してSQLを投げたいなーと思いまして。

さらさらと調べてみたのですが、最初に試してみたH2 Databaseがお手軽そうだったので、こちらを利用することに。

H2 Database Engine

こちらのcsvread関数を使えばよい感じみたいです。

csvread

オプションなどは、こちら。

csv_options

他の候補)
コマンドでなんとかなりそうだったのが、こちらの「q」。

Run SQL directly on CSV files | Text as Data | q

また、Apache Drillとかもちょっと興味あったり。

Apache Drill - Schema-free SQL for Hadoop, NoSQL and Cloud Storage

今回はJDBCで見れると嬉しいなぁということもあったので。Apache Drillはそのうち試してみたい…。

インストール

とりあえず、zipを落としてきて展開。

$ wget http://www.h2database.com/h2-2015-10-11.zip
$ unzip h2-2015-10-11.zip

H2をServerとして起動します。

$ sh h2/bin/h2.sh 
エラー: メイン・クラスorg.h2.tools.Consoleが見つからなかったかロードできませんでした

ん??

どうも、h2.shにCRが入っている模様…。

仕方ないなぁ…。

$ perl -wpi -e 's!\r!!' h2/bin/h2.sh

気を取り直して、起動。

$ sh h2/bin/h2.sh

今度は、無事立ち上がりました。

CSVファイルを読む

まずは、CSVファイルを読み込んでみます。

このようなCSVファイルを用意。
fruit.csv

No,名前,値段
1,Apple,300
2,"Orange",200
3,梨,200

1行目は、ヘッダーです。

これに対して、このようなSQLを投げることができます。

SELECT *
  FROM CSVREAD('/path/to/fruit.csv',
               null,
               'UTF-8',
               ',');

結果。

お手軽ー。

ヘッダ行がないCSVの場合は
fruit-header-less.csv

1,Apple,300
2,"Orange",200
3,梨,200

自分で書く、と。

SELECT *
  FROM CSVREAD('/path/to/fruit-header-less.csv',
               'No,名前,値段',
               'UTF-8',
               ',');


TSVファイルを読む

デリミタが指定できるので、TSVファイルも読めます、と。

fruit.tsv

No	名前	値段
1	Apple	300
2	"Orange"	200
3	梨	200

この場合、タブはchar(9)で表すようです…。

SELECT *
  FROM CSVREAD('/path/to/fruit.tsv',
               null,
               'UTF-8',
               char(9));

ヘッダなしの場合は
fruit-header-less.tsv

1	Apple	300
2	"Orange"	200
3	梨	200

こうなりますよっと。

SELECT *
  FROM CSVREAD('/path/to/fruit-header-less.tsv',
               'No' || char(9) || '名前' || char(9) || '値段',
               'UTF-8',
               char(9));

「\t」とかにはならないんですね、まあいいかー。

なお、スクリーンショットで使われているツールは、ExecuteQueryというJDBCが使えるGUIツールです。
Execute Query