シェル上からSQLを実行して簡単に集計できるライブラリ「sssummary」を作った

シェル上からSQLを実行して簡単に集計できる「sssummary」というライブラリを作った。
rubygems -> https://rubygems.org/gems/sssummary
github -> https://github.com/akira-kuriyama/sssummary

ログファイルとかCSVとかTSVとか、フォーマットされたファイルに対してシェル上から集計したいことあるよね？
その場合、だいたいgrepとかwc -l, uniq -c, sort, awk とかで頑張るんだけどもっとカジュアルに集計したいと思うよね？

そう、SQL！ SQLで集計したい！！

例えば以下のようなテキストファイルがあるとする。
1カラム目がアクセス時間、2カラム目がレスポンスタイム、3カラム目がユーザエージェント。

$ cat test.tsv
2013/07/01 23:08	0.100	Mozilla/5.0 (iPhone; CPU iPhone OS 6_0 like Mac OS X) AppleWebKit/536.26 (KHTML, like Gecko) Version/6.0 Mobile/10A403 Safari/8536.25
2013/07/01 23:08	0.160	Mozilla/5.0 (iPhone; CPU iPhone OS 5_1_1 like Mac OS X) AppleWebKit/534.46 (KHTML, like Gecko) Version/5.1 Mobile/9B206 Safari/7534.48.3
2013/07/01 23:09	0.120	Mozilla/5.0 (Linux; Android 4.1.1; Nexus 7 Build/JRO03S) AppleWebKit/535.19 (KHTML, like Gecko) Chrome/18.0.1025.166 Safari/535.19
2013/07/01 23:09	0.103	Mozilla/5.0 (Linux; U; Android 2.3.5; ja-jp; T-01D Build/F0001) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1
2013/07/01 23:09	0.140	Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.2; ARM; Trident/6.0)
2013/07/01 23:10	0.130	Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/28.0.1500.63 Safari/537.36
2013/07/01 23:10	0.190	Mozilla/5.0 (Macintosh; Intel Mac OS X 10.7; rv:9.0.1) Gecko/20100101 Firefox/9.0.1
2013/07/01 23:11	0.600	Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8) AppleWebKit/536.25 (KHTML, like Gecko) Version/6.0 Safari/536.25
2013/07/01 23:11	0.890	Mozilla/5.0 (Linux; U; Android 3.2.1; ja-jp; Transformer TF101 Build/HTK75) AppleWebKit/534.13 (KHTML, like Gecko) Version/4.0 Safari/534.13

これをアクセス時間ごとにgroup by して、レスポンスタイムの平均を出したいとする。
そうしたい場合はsssummaryを使うと簡単できる。

$ cat test.tsv | sssummary 'select c1, avg(c2) from t group by c1 order by c1'
2013/07/01 23:08	0.13
2013/07/01 23:09	0.121
2013/07/01 23:10	0.16
2013/07/01 23:11	0.745

カジュアルではないでしょうか??

インストールの仕方

MacだとSQLite3が標準でインストールされているようなので特に何もする必要がないが、
他のOSだったら以下のようにSQLite3をインストールする必要がある。
$ yum install sqlite3 sqlite-devel

$ gem install sssummary

仕組み

中の仕組みは、sqliteにデータを突っ込んでSQLの実行をして、終わったらsqliteのDBファイルを削除する、といったもの。
なのでSQLの文法はSQLiteが認識できるものに限られる。
ちなみに、
'select c1, avg(c2) from t group by c1 order by c1'
の "t"というのは、デフォルトのテーブル名(オプションで変更可能)。
c1,c2というのは、デフォルトのカラム名。c1,c2,c3... と連番で振られる(カラム名もオプションで変更可能)。

ということで、オプションを紹介していこう。

-f, --file
　上記の例は標準入力からファイルを受け取ったが-fオプションを使用することでファイルのパスを指定することもできる。
　$ sssummary -f test.tsv 'select * from t'

-p, --database-file
　デフォルトでは、SQLiteのDBファイルはsssummaryを実行したディレクトリに生成される。
　しかし権限の関係でそこに生成したくない場合もあるでしょう。その場合は-pオプションを使って生成場所を指定できる。
　$ sssummary -p ~/temp -f test.tsv 'select * from t'
　
-d, --database
　デフォルトのSQLiteのDB名はsssummary.dbである。-dオプションでそのDB名を指定することも出来る。恐らく-lオプションと併用することになるだろう。
　$ sssummary -d hoge -f test.tsv 'select * from t' #-> hoge.db というDBファイルが生成される。

-l, --leave-database
　いったんDBに入れた後、SQLiteを直接使ってSQLを実行した場合があるだろう。その場合、-lオプションが使える。
　-lオプションを使うと、sssummary実行後、DBファイルを削除しない。
　$ sssummary -l -f test.tsv 'select * from t'

-t, --table
　デフォルトのテーブル名はtだ。それも-tオプションで指定できる。
　$ sssummary -t log -f test.tsv 'select * from log'

-c, --columns
　デフォルトのカラム名はc1,c2,c3... と連番で振られる。それも-cオプションで指定できる。
　$ sssummary -c date,elapsed,ua -f test.tsv 'select data, elapsed, ua from t'
　ちなみに、カラムの型はデフォルトだとTEXT型だ。それも以下のように":"の横に型を書くと指定できる。
　$ sssummary -c date:TEXT,elapsed: INTEGER,ua:TEXT -f test.tsv 'select data, elapsed, ua from t'

-s, --import-separator
　標準入力や-fで指定したファイルの形式はデフォルトはtsvを想定している。つまりタブ区切りだ。
　-sオプションでを使うと、その区切り文字をタブではなく他の文字を指定することができる。
　$ sssummary -s , -f test.tsv 'select * from t' #->カンマを指定した。

-o, --output-separator
　SQL実行結果の出力はデフォルトだとタブ区切りで出力される。
　-oオプションでを使うと、その区切り文字をタブではなく他の文字を指定することができる。
　$ sssummary -o , -f test.tsv 'select * from t' #->カンマを指定した。

-i, --ignore-header
　標準入力や-fで指定したファイルの一行目にヘッダーがある場合は-iオプションを指定することで、
　そのヘッダーである一行目を無視することができる。
　$ sssummary -i -f test.tsv 'select * from t'

-v, --verbose
　-vオプションを指定すると詳細出力がされる。
　$ sssummary -v -f test.tsv 'select * from t'

-h, --help
　-hオプションでヘルプ表示。
　$ sssummary -h