CSVのデータのレコード数や合計値、平均値などを求めたい時ってたまにある。
エクセルなど表計算ソフトで読み込んで求めるってのがよくやる方法だと思うが、ある列の値が同じ行の値の合計値や平均を求めたい場合では「SQLでできれば...」と思ってしまう。
そんな時にはH2DBが役立つ。
と思っていたが、2017年12月現在では Run SQL directly on CSV files | Text as Data | q の方がお手軽だと思う。
H2DBとは
Java製のRDBMS。エンジンとJDBCドライバーがjarファイル一つになっている。
上記サイトからダウンロードできるAll Platforms向けのzipファイルにはjarファイルの他、ドキュメントやソース、起動スクリプト等も含まれている。
使い方
zipファイルを展開するとbinディレクトリが含まれている。このディレクトリにjarファイルと起動スクリプトが入っている。
コンソールの起動
binディレクトリに入って次のように起動スクリプトを実行するとWebブラウザが起動し、H2DBのWebコンソールが開く。
> ./h2.sh
最初に表示されるのはログイン画面。
CSVファイルを読み込ませて検索するだけなら、設定する項目はないので、そのまま接続ボタンを押せば良い。
すると次のようなコンソールが表示される。
SQLステートメントにSQLを書き込んで実行ボタンを押せば良い。
H2DBのシェル
Webコンソールよりターミナルという方は次のコマンドでH2DBのシェルを起動できる。最初に幾つか質問されるが、CSVを検索するだけならRETERN連打でOK。
java -cp h2*.jar org.h2.tools.Shell
CSVを読み込むSQLの実行
CSVを読み込むには CSVREAD 関数をFROM句で使う。
SELECT count(*) FROM CSVREAD('ファイルパス')
CSVファイルの先頭行がヘッダ行となりカラム名として認識される。
ヘッダ行がない場合には、CSVREAD関数の第2引数で指定することもできる。詳しくは CSVREAD関数のドキュメント を参照のこと
注意点としては、数字のカラムもvarcharとして読み込まれてしまうので、AVGやSUMなど数値としての計算が必要な関数を使う場合にはCAST関数かCONVERT関数で数値に変換してから使う。
SELECT product, COUNT(*), SUM(CAST(cost as INT)) FROM CSVREAD('sample.csv')
GROUP BY product;
GUIツールから使う
Execute QueryのようなJDBCドライバーを組み込んでDBに接続できるツールでもH2DBは利用できる。慣れたツールがあるならそのつーるから使う方が便利。
ツールにより項目名は変わるかもしれないが、次のように設定すればよい。
| 設定項目 | 設定値 |
|---|---|
| JDBCドライバ | h2-1.x.xxx.jar |
| クラス名 | org.h2.Driver |
| データソース | jdbc:h2:mem:hoge |
データソースの mem はオンメモリでの動作を示し、 hoge は何でも良い。
他に同様のツールは？
これも良さそう。
そういえば昔マイクロソフトがCSVとかApacheのログとかをSQLチックな検索言語で処理できるツールを出してたけど、どうなったのかな。