CSV・TSVファイルでSQLを利用する「q」コマンド
CSVやTSVのデータを扱っていると、大容量になるとcatなんかでは抽出するのは辛くなってくる。
かと言って、grepで行抽出、awkで列指定しての行抽出となると毎回はちょっとつらいし、複数人にそれをやらせるとブーイングが来そうだ。DBにインポートすればよいのだろうが、それも面倒くさい。。。jsonであればjqコマンドを使えば良いのだけど、そうも言ってられないし、わざわざjson形式に切り替えるのも面倒くさい。
そんな時に使えそうなのが、csvファイルに対してSELECT文などを利用できるコマンド「q」コマンドだ。
これならSQL使えれば簡単に扱えるし、DBも必要なさそう。
1.インストール
インストールは簡単で、こちらを参考に各OSに合わせてバイナリをインストールするだけだ。
今回はCentOS 7でインストールしてみる。
curl -s http://harelba.github.io/q/install.html | grep rpm_download_link | awk -F\" '{print $4}' | xargs rpm -ihv
[root@BS-PUB-CENT7-01 ~]# curl -s http://harelba.github.io/q/install.html | grep rpm_download_link | awk -F\" '{print $4}' | xargs rpm -ihv
https://github.com/harelba/packages-for-q/raw/master/rpms/q-text-as-data-1.5.0-1.noarch.rpm を 取得中
準備しています... ################################# [100%]
更新中 / インストール中...
1:q-text-as-data-1.5.0-1 ################################# [100%]
これでインストールができた。
2.実際に使ってみる
さて、それでは実際に使ってみよう。
2-1.普通にselect文を実行する
基本的には、以下のようにコマンドを実行してやれば良い。(カラムは、c○で表現する)
q -d"区切り文字" "SELECT ～ FROM ファイルPATH WHERE ～"
例として、以下のようなCSVファイルを使ってみる。
●test1.csv
1,aaa,1,123 2,bbb,2,544 3,ccc,1,65745 4,ddd,1,122 5,eee,2,222 6,fff,1,444 7,ggg,3,566 8,hhh,4,111 9,iii,3,12 10,bbb,2,1243 11,ddd,1,4423
[root@BS-PUB-CENT7-01 ~]# cat /tmp/test1.csv 1,aaa,1,123 2,bbb,2,544 3,ccc,1,65745 4,ddd,1,122 5,eee,2,222 6,fff,1,444 7,ggg,3,566 8,hhh,4,111 9,iii,3,12 10,bbb,2,1243 11,ddd,1,4423 [root@BS-PUB-CENT7-01 ~]# q -d"," "select * from /tmp/test1.csv where c3='2'" 2,bbb,2,544 5,eee,2,222 10,bbb,2,1243 [root@BS-PUB-CENT7-01 ~]# q -d"," "select * from /tmp/test1.csv where c3='2' or c3='4'" 2,bbb,2,544 5,eee,2,222 8,hhh,4,111 10,bbb,2,1243 [root@BS-PUB-CENT7-01 ~]# q -d"," "select count(*) from /tmp/test1.csv where c3='2'" 3
2-2.ヘッダーについてるカラム名を利用する
csvのヘッダーにカラム名がついてる場合、「-H」オプションをつけることでそれをSQL内で利用することができる。
[root@BS-PUB-CENT7-01 ~]# cat /tmp/test1.csv id,name,prefecture,value 1,aaa,1,123 2,bbb,2,544 3,ccc,1,65745 4,ddd,1,122 5,eee,2,222 6,fff,1,444 7,ggg,3,566 8,hhh,4,111 9,iii,3,12 10,bbb,2,1243 11,ddd,1,4423 [root@BS-PUB-CENT7-01 ~]# q -H -d"," "select * from /tmp/test1.csv where prefecture='2'" 2,bbb,2,544 5,eee,2,222 10,bbb,2,1243
2-3.グループ化して集計を行う
「group by」でsumやavgで集計することも可能だ。
[root@BS-PUB-CENT7-01 ~]# cat /tmp/test1.csv id,name,prefecture,value 1,aaa,1,123 2,bbb,2,544 3,ccc,1,65745 4,ddd,1,122 5,eee,2,222 6,fff,1,444 7,ggg,3,566 8,hhh,4,111 9,iii,3,12 10,bbb,2,1243 11,ddd,1,4423 [root@BS-PUB-CENT7-01 ~]# q -H -d"," "select prefecture,sum(value) from /tmp/test1.csv group by prefecture" 1,70857 2,2009 3,578 4,111 [root@BS-PUB-CENT7-01 ~]# q -H -d"," "select prefecture,avg(value) from /tmp/test1.csv group by prefecture" 1,14171.4 2,669.666666667 3,289.0 4,111.0 [root@BS-PUB-CENT7-01 ~]# q -H -d"," "select prefecture,sum(value),avg(value) from /tmp/test1.c sv group by prefecture" 1,70857,14171.4 2,2009,669.666666667 3,578,289.0 4,111,111.0 [root@BS-PUB-CENT7-01 ~]# q -H -d"," "select prefecture,count(prefecture),sum(value),avg(value) from /tmp/test1.csv group by prefecture" 1,5,70857,14171.4 2,3,2009,669.666666667 3,2,578,289.0 4,1,111,111.0
2-4.csvの結合を行う
「join」も使えるので、CSVを結合して利用することも可能だ。
[root@BS-PUB-CENT7-01 ~]# cat /tmp/test1.csv id,name,prefecture,value 1,aaa,1,123 2,bbb,2,544 3,ccc,1,65745 4,ddd,1,122 5,eee,2,222 6,fff,1,444 7,ggg,3,566 8,hhh,4,111 9,iii,3,12 10,bbb,2,1243 11,ddd,1,4423 [root@BS-PUB-CENT7-01 ~]# cat /tmp/test2.csv prefecture,ja_prefecture 1,tokyo 2,kanagawa 3,kyoto 4,osaka [root@BS-PUB-CENT7-01 ~]# q -H -d "," " > SELECT test1.name > ,test2.ja_prefecture > ,test1.value > FROM /tmp/test1.csv as test1 left join /tmp/test2.csv as test2 on test1.prefecture=test2.prefecture" aaa,tokyo,123 bbb,kanagawa,544 ccc,tokyo,65745 ddd,tokyo,122 eee,kanagawa,222 fff,tokyo,444 ggg,kyoto,566 hhh,osaka,111 iii,kyoto,12 bbb,kanagawa,1243 ddd,tokyo,4423
これだけ使えれば、CSVでのデータ処理もだいぶ楽になるのではなかろうか。
ただまぁ、あまり大きいCSVファイルだと処理に時間がかかるようなので、その場合は素直にDBに入れるなどしてやろう。