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に入れるなどしてやろう。