CSVファイルをSQLのクエリで集計できるqコマンドをHaskellで実装してみました！

Haskell

先日、Twitterでqコマンドが話題になっていました。 github.com スターが3000を超えていてすごいですね。2014年から開発されているツールで、Pythonで書かれています。

これはGoで実装してみたいなーと思っていたところ、mattnさんが素早く実装されていました。 mattn.kaoriya.net 一本取られたと思ったものの、よく読むとまだ標準入力しか対応していないようです。

いったいどういう仕組みなのか、何の実装が難しいところなのか、qコマンドが嬉しい場面はどういうケースなのか、自分も知りたくなったので1から実装してみました。 私が一番素早く書ける言語ということでHaskellを選びました。

qhs

qコマンドのHaskell実装、ということでqhsと名づけました。 github.com stackが入っていればインストールは簡単です。

 $ git clone https://github.com/itchyny/qhs
 $ cd qhs
 $ stack install
 $ export PATH=$PATH:$HOME/.local/bin
 $ qhs "SELECT 100+200"
300

基本的な使い方

qコマンドの基本思想は、CSVのようなファイルをSQLのテーブルとみなしてSELECTできることです。 qhsコマンドもqコマンドと同じように動きます。 SELECT * FROM [ファイル名] が基本の形です。

 $ cat basic.csv
a0,1,a2
b0,3,b2
c0,,c2
 $ qhs "SELECT * FROM basic.csv"
a0 1 a2
b0 3 b2
c0  c2

カラム名は自動的に c1, c2 ... のようになります。c2がNULLではない行だけにしてみます。

 $ qhs "SELECT * FROM basic.csv WHERE c2 IS NOT NULL"
a0 1 a2
b0 3 b2

数字の列の空文字はNULLになるようになっています。例えば、二行目の平均を取ることができます。

 $ qhs "SELECT avg(c2) FROM basic.csv"
2.0

1と3の平均なので2です。

一番上の行にカラム名がある時は、-H (--skip-header) 引数を与えてあげてください。

 $ cat basic.csv
foo,bar,baz
a0,1,a2
b0,3,b2
c0,,c2
 $ qhs -H "SELECT foo,baz FROM basic.csv"
a0 a2
b0 b2
c0 c2

普通のSQLと同じように、JOINしたりUNIONしたりすることもできます。 例えば、先月と先々月の家計簿CSVから高かった買い物トップ10を表示してみます。

 $ qhs -d , -O -H "SELECT * FROM 家計簿06.csv UNION SELECT * FROM 家計簿07.csv ORDER BY 金額 DESC LIMIT 10"
日,時刻,金額,用途,場所
27,0000,66000,家賃,銀行振込
27,0000,66000,家賃,銀行振込
30,0000,8200,端末料金,サンプルコミュニケーション
30,0000,8200,端末料金,サンプルコミュニケーション
16,2200,7948,親ビールギフト,サンプルショッピング
5,1542,5690,ポロシャツ・ベルト他,サンプル洋服店
25,2245,5300,会社飲み会,いつもの飲み屋
2,2215,5000,同期と飲み会,四条の飲み屋
25,1913,3839,食料品,サンプルマート
3,1440,3740,散髪,サンプルサロン

とても便利ですね。そんなに大きな買い物はしてないことが分かって安心です (家計簿はサンプルです・私の実際の出費とは関係ありません)。

CSVファイルをSQLのテーブルのように扱い、JOIN・UNION・サブクエリによって集計する、これがqコマンドの真髄なのだと思います。 (mattnさん頑張ってください…)

標準入力

qコマンドやqqコマンド、そしてqhsコマンドは標準入力からテーブルを読み込むことができます。

 $ cat basic.csv
foo,bar,baz
a0,1,a2
b0,3,b2
c0,,c2
 $ cat basic.csv | qhs -H "SELECT foo,baz FROM - WHERE bar IS NOT NULL"
a0 a2
b0 b2

-は標準入力から読み込むことを表しています。

他のUNIXツールとの合わせて様々な集計を行うことができます。 例えばwcコマンドの行数でソートしたり

 $ wc * | qhs "SELECT c4,c1 FROM - WHERE c4 <> 'total' ORDER BY c1 DESC"
Main.hs 118
File.hs 66
Option.hs 61
Parser.hs 51
SQL.hs 45

psコマンドと合わせたりなど、組み合わせは自由自在です。

 $ ps -ef | qhs -H -O "SELECT UID,COUNT(*) cnt FROM - GROUP BY UID ORDER BY cnt DESC LIMIT 3"
UID cnt
503 102
0 86
89 3

このpsの例はqコマンドのExampleから拾ってきたものです。 よく考えられていますね、脱帽しました。

実装基礎

qコマンドを自分の好きな言語で実装したいと考えたとします。 実装のイメージは湧きますか？ SQL相当のクエリ実行器を作らないといけないのかと嫌な予感がよぎるかもしれませんが、そこまで苦労する必要はありません。

メモリー上にデータベースを作り、ファイルを読み込んでテーブルを作り、そこにクエリを投げる、これが基本的な構成です。 これだけで理解できた人は以下は読まなくてよいでしょう。

qコマンドもmattnさんのqqコマンドもSQLite3のオンメモリデータベースを用いています。qhsもこれに倣ってSQLite3を使っています。初めて触るライブラリーでしたが、インターフェースが分かりやすいのですぐに使えました。

テーブルの構築

qコマンドの実装で最も難しいのはここだと思います。 qhsを実装するときも、かなり頭を捻りました。

例えば、次のような入力を考えます。

 $ qhs -d , -O -H "SELECT * FROM ./家計簿06.csv UNION SELECT * FROM 家計簿07.csv ORDER BY 金額 DESC LIMIT 10"

この「クエリ」自体は、SQLのクエリとして実行できるものではありません。 また、標準入力からの読み取りという簡単なクエリでさえ、通常のテーブル名としては不正なものになっています。

 $ wc * | qhs "SELECT * FROM -"

おそらくどんな言語でもSQLライブラリに突っ込んだらエラーになるでしょう。

まずは、この「クエリ」からファイル名を抽出します。 例えば ./家計簿06.csv とか 家計簿07.csv とか - のようなものです。 ファイルはテーブルに相当しますから、おそらくFROMかJOINの後にくる単語がそうでしょう。 そして、このファイル名を有効なテーブル名に置換します。

SELECT * FROM temp_table_0 UNION SELECT * FROM temp_table_1 ORDER BY 金額 DESC LIMIT 10

テーブル名はなんでもよいです。 ただ、以下のことが大事です。

  • ファイル名との対応をきちんと持つこと (後で使う)
  • あるファイル名に対しては一意に定まること
  • SQLのテーブル名として正しいものであること

また、ファイル名と同じくらいの長さが好ましいと思います。 SQLのパーサーのエラーメッセージを元に戻してユーザーにフィードバックする時に、エラー位置がずれないようにするためです。

今回の例では、

"./家計簿06.csv" => temp_table_0
"家計簿07.csv" => temp_table_1

という対応が取れました。 標準入力のみの簡単なクエリでは、例えば次のような形になるでしょう。

"-" => temp_table_0

temp_table_ の部分は何でも構いません。 qhsの実装では、ファイル名をSHA1エンコードしたものを使っています。 少しトリッキーですが、SHA1を使っておけば入力に対して安定します。

さて、次にやることは、このファイルたちを読み込んで、テーブルを作ることです。 コマンドの引数の区切り文字に従って、ファイルを読み込みます。 この段階でカラム名が決定しますので、CREATE TABLEできるようになります。 数字のカラムを自動判別して型を数字にしておくとか、gzipされたファイルを読み込むときにはdecodeするみたいな細かい芸はいろいろあります。

基本的には、一行ずつ読み込み区切り文字で分割していけばいいわけです。 ただしカラム数を超える分は分割してはいけません。 例えばps -efの結果があったとして

  UID   PID  PPID   C STIME   TTY           TIME CMD
    0     1     0   0 月10AM ??        40:25.50 /sbin/launchd
    0    45     1   0 月10AM ??         3:26.75 /usr/sbin/syslogd
    0    46     1   0 月10AM ??         1:15.17 /usr/libexec/UserEventAgent (System)
    0    48     1   0 月10AM ??         0:34.95 /usr/libexec/kextd
   55    54     1   0 月10AM ??         0:00.64 /System/Library/CoreServices/appleeventsd --server
  501  1484     1   0 月10AM ??       301:08.31 /Applications/Google Chrome.app/Contents/MacOS/Google Chrome
    0 16451     1   0 水10AM ??         0:00.08 /usr/libexec/syspolicyd
  501 98979 98977   0 土02AM ttys013    0:00.14 -zsh

スペースによって分割しないといけませんが、CMDのカラムを必要以上に分割してはいけません。 もし Google Chrome がカラムで分割されてしまったら、次のような基本的なLIKE文も動かなくなるでしょう。

 $ ps -ef | qhs -H "SELECT * FROM - WHERE CMD LIKE '%Google Chrome%'"

UNIXツールの扱いやすいように見えてなんか扱いにくい出力には、いつも頭を悩まされます。 だからこそqコマンドも一定の支持を得られてきたのだと思います。

さらに、CSVはダブルクオートでセルが複数行にまたがることがあります。ダブルクオートの中のダブルクオートはダブルのダブルクオートで表現するらしいです。

 $ cat multiline.csv
foo,bar,baz,qux,quux
a0,1,"a2
b0"",3,""b2
c0",,c2
 $ qhs -d , -H -O "SELECT foo,bar,quux FROM multiline.csv"
foo,bar,quux
a0,1,c2

barが1、bazが"a2\nb0\",3,\"b2\nc0"、quxが""、quuxがc2なので、これで合っています。 めでたしです。 律儀にもqコマンドがこのケースに対応しているのでqhsでも対応してみました。 最悪です。

なんやかんやで入力を読み込むことができれば、一行ずつテーブルに流し込んでテーブルの準備は完了です。

クエリの実行と結果の表示

テーブルの準備ができれば、後はクエリを実行するだけです。 ここで言うクエリとは、ファイル名を置換した後の、ちゃんと実行できるクエリのことです。

qコマンドでは出力の区切り文字を指定することができます。qhsも同じインターフェスにしてみました。 例えば、入力ファイルはカンマ区切りだけど、出力ファイルはタブ区切りにしたいという時は次のようにします。

 $ qhs -d , -D $'\t' -H "SELECT * FROM basic.csv"
a0  1  a2
b0  3  b2
c0     c2

dはdelimiter (区切り文字) の頭文字です。小文字 -d が入力で、大文字 -D が出力です。 いちいち $'\t' と書くのは面倒なので、 -T を使うこともできます。 -T と -D $'\t' は同じです。 -t と -d $'\t' も同じです。

テスト

qhsはHaskellで書かれていますので、テストもHaskellのテストツールを使います。 HspecはHaskellにおける標準的なテストフレームワークです。 Rspecにインスパイアを受けて作られたフレームワークです。

一行を特定のカラム数で分割する関数や、クエリのファイル名を置換する処理は難しいので丁寧にテストしています。 テストの中でファイルを読んだり書いたりすることも可能です。

コマンドラインツールですから、ビルド結果のコマンド自体が正しく動くことをテストするのも大事です。 テストのディレクトリーには、シェルスクリプトと、期待されるの出力ファイルを置いています。 これらをHspecの中で実行して、その出力と期待される出力ファイルを比較するというテストを書いています。 こうしておけばテストを追加するのも簡単ですし、最悪Hspecが滅びたとしてもシェルスクリプトを実行し出力を比較することができれば、どんなテストフレームワークでも生き残るはずです (いざとなったらシェルスクリプトでテストを回すこともできる)。 以前、Go言語で迷路コマンドを作った時も同じようなテスト構成を取りました。 itchyny.hatenablog.com

Travis CI上のstackでのテストはstackのドキュメントを参考にしています。

今回は、qコマンドという元の実装が存在します。 qコマンドのテストがかなり役に立ちました。 qコマンドのリポジトリにある112個のテストのコマンドをqhsに置き換えて、テストの実行と実装を繰り返しながらテストカバレージを上げていきました。 実装する必要があるのかよく分からないオプションがあったりするので、完全互換性を目指しているわけではありません (qコマンドの置き換えテストの中で通るのは4割ほどですが、これでもわりと通っている方だと思います)。

コマンドライン引数パーサー

Haskellにおけるコマンドラインの引数パーサーにはいろいろなライブラリーがあります。 今回は、optparse-applicativeを使ってみました。

Control.Applicativeの演算子と親和性がよくてコードが書きやすいです。 何よりも、オプションの性質を表す Mod f a という型が、Monoidのインスタンスになっていることがイケてます。 Monoidになっているということは、 (<>) で追加できますし、機能を足すのも引くのも簡単です (Haskellを書く時にMonoidのイメージはとても大事です)。 用途は思いつきませんが、オプションパーサーを動的に構築するのも簡単です。

おわりに

CSVファイルをSQLのクエリで集計できるqコマンドを、Haskellで実装してみました。 複数ファイル、標準入力、区切り文字の設定、gzip対応など、基本的な機能は揃っていると思います。 できるかぎり、オプション名などインターフェースはqコマンドに寄せています。 github.com

コメントやimport文を除くと300行程度です。 土曜日に「よし書こう」と思い立ってから丸一日で実装できるサイズでした。 やはりこの言語は楽しいなぁと感じました。 好きな言語は書いていて楽しいし、実装できたときの疲労感も心地よいものです。

すごいHaskellたのしく学ぼう!

すごいHaskellたのしく学ぼう!

  • 作者:Miran Lipovača
  • 発売日: 2012/05/23
  • メディア: 単行本（ソフトカバー）

コマンドラインツールの作り方、SQLのクエリのパースの仕方、そしてDBの触り方など、qコマンドには興味深い課題が詰まっていました。 SQLのクエリの構文木からテーブル名を抜き出す処理には、sjspコマンドでもお世話になっているData.Genericsの知識が役に立ちました (ラフにテーブル名を置換した後、さらにバリデーションで厳格なSQLパーサーを通しています)。 itchyny.hatenablog.com 構文木からの抽出や変換というタスクではData.Generics.Schemesの関数が驚異的な威力を発揮します。

DBのライブラリーを触ったり、optparse-applicativeを初めて触ってみたり、コマンドラインツールのテストについて改めて考えなおしてみたり、あるいはそのテストをTravis CI上で実行するまで持って行ったりと、いろいろと楽しいチャレンジだったなと思います。

さて、あなたの好きな言語は何ですか？ 次は、あなたがqコマンドを好きな言語で書く番ですよ。

エンジニア募集

はてなでは、好きな言語はとことん好きだ！そんな情熱と愛情あふれるエンジニアを募集しています。