CSV/TSVに対してSQL発行できるツール「q」
今日はCSV/TSVなどのテキストファイルをSQLで操作できるツール「q」の紹介です。
使いどころ
データ処理を実施する時にはDBに保存されているデータやテキストファイルなどをCSVやTSV等の一定のフォーマットに変換して処理することが多いと思います。そのデータ内容を確認するときには、例えばExcel等に貼り付けることもあると思います。ただ、Excelではデータ量が数百万件規模になってくるとソフトウェアの制約上、処理が行えません。
データベースに保存されている大量のデータを処理・取得する際によく用いられるのはSQLです。SQLはデータ操作のための非常によくできたインターフェースであり、かなりの数のエンジニアが利用できるのではないでしょうか。
単純なCSVファイルに対してもSQLを利用して検索が実施できれば楽だと思って探していたところ、「q」にたどり着きました。
インストール
qには様々なインストール方法が提供されているため、ほぼすべての環境で問題なく動作させることができるでしょう。プロダクトページから直接ダウンロードすることもできます。
筆者の環境はMac OS Xのため、Homebrewを利用してインストールしました。
$ brew install q $ q --version q version 1.5.0 Copyright (C) 2012-2014 Harel Ben-Attia ([email protected], @harelba on twitter) http://harelba.github.io/q/ $
動作確認
今回は検証用のデータに、Amazon Machine Learningのチュートリアル教材として利用されている約4万件のCSVファイルを利用しました。
qコマンドの実行名はqです。必要なオプションは２つです。-Hオプションは、そのテキストファイルがヘッダ行を含んでいる際に付与します。-dオプションでは、テキストファイルの区切り文字を指定します。今回はCSVファイルなのでカンマを指定します。
なお、以下のコマンドの実行にはAWS CLIのインストールがあらかじめ必要です。
~/tmp/q$ aws s3 cp s3://aml-sample-data/banking.csv . download: s3://aml-sample-data/banking.csv to ./banking.csv $ q -H -d',' "select count(*) from ./banking.csv" 41188 $ wc -l banking.csv 41189 banking.csv
ヘッダ行を抜くと、行数がしっかり一致していることがわかりますね。
今度はwhere句をつけて実行してみます。速度の面と文法の面が気になりますね。 まずは年齢が30歳以上の行数を調べてみます。
$ q -H -d',' "select count(*) from ./banking.csv where age >= 30" 35519
この程度の検索であれば、かなりすぐに結果が返ってきました。
今度はwhere句をandでつなげてもう少し複雑にしてみます。30歳以上で、ローンを借りている人物(loanカラムの値が'yes')のカウントを調べてみましょう。
$ q -H -d',' "select count(*) from ./banking.csv where age >= 30 and loan == 'yes'" 5374
こちらもすぐに結果が返ってきました。書き慣れたSQLでCSVの内容が検索できるのはかなり便利ですね！
まとめ
今回の例で利用した4万件程度のCSVであれば、ほとんど待たされることもなくすぐに結果が返ってきました。一方で、手元にあった数百万件レベルのCSVで試すと実行に数十秒かかりましたが、手軽にテキストファイルの内容を探索できるのは非常に有用だと感じました。
これまでCSVの内容把握をどうしたらいいか悩んでいましたが、これからはしばらくこのツールを使ってみようと思っています。
