全力で怠けたい

怠けるために全力を尽くしたいブログ。

CSV や TSV を SQL ライクに select できる q コマンドが便利すぎた。

CSV や TSV などの表形式のデータを SQL ライクに select できる q コマンド が便利すぎたのでメモしておく。

q コマンドとは

とりあえず公式ドキュメント。

harelba.github.io

公式ドキュメントには以下のように書いてある。

q は SQL ライクな命令を表形式のテキストデータに対して実行することができます。SQL の強力な表現を Linuxコマンドラインで実行できるようにし、より簡単にテキストデータを扱えるようにするために作られました。

ようするに CSV とか TSV みたいなテキストデータを SQL ぽく select できるコマンドなのだが、非常に簡単に使えてしかも驚くほど強力。

インストール

Macbrew を使うと簡単にインストールできる。

$ brew install q

Mac 以外のインストール方法は ここ

使い方

公式ドキュメント が充実しすぎているのでざっと公式ドキュメントに目を通すだけで十分なのだがよく使うオプションなどを備忘録までにメモしておく。

サンプルで使う CSV ファイルはこんな感じ。

$ cat prefectures.csv
都道府県,読み,地方,人口
愛知県,あいちけん,中部,7484094
青森県,あおもりけん,東北,1308649
秋田県,あきたけん,東北,1022839
石川県,いしかわけん,中部,1154343
茨城県,いばらきけん,関東,2917857
岩手県,いわてけん,東北,1279814
愛媛県,えひめけん,四国,1385840
大分県,おおいたけん,九州,1166729
大阪府,おおさかふ,近畿,8838908
岡山県,おかやまけん,中国,1922181

基本的な使い方

入力データはファイル名を from に指定する。

$ q -d, -H 'select * from prefectures.csv'
愛知県,あいちけん,中部,7484094
青森県,あおもりけん,東北,1308649
秋田県,あきたけん,東北,1022839
石川県,いしかわけん,中部,1154343
茨城県,いばらきけん,関東,2917857
岩手県,いわてけん,東北,1279814
愛媛県,えひめけん,四国,1385840
大分県,おおいたけん,九州,1166729
大阪府,おおさかふ,近畿,8838908
岡山県,おかやまけん,中国,1922181

標準入力を入力データにするときは -from に指定する。

$ cat prefectures.csv | q -d, -H 'select * from -'
愛知県,あいちけん,中部,7484094
青森県,あおもりけん,東北,1308649
秋田県,あきたけん,東北,1022839
石川県,いしかわけん,中部,1154343
茨城県,いばらきけん,関東,2917857
岩手県,いわてけん,東北,1279814
愛媛県,えひめけん,四国,1385840
大分県,おおいたけん,九州,1166729
大阪府,おおさかふ,近畿,8838908
岡山県,おかやまけん,中国,1922181

データの区切り文字を指定するときは -d オプションを指定する。 サンプルの prefectures.csvCSV フォーマットなので `-d,' を指定しているが入力データのフォーマットに合わせてタブでもなんでも指定できる。

入力データの1行目がヘッダー行のときは -H オプションを指定する。 -H オプションを指定するとヘッダー行から自動的にカラム名を検出してくれてクエリのなかで使うことができる。

$ q -d, -H 'select 都道府県 from prefectures.csv'
愛知県
青森県
秋田県
石川県
茨城県
岩手県
愛媛県
大分県
大阪府
岡山県

入力データにヘッダー行がなく1行目からデータ行のときは c1, c2, ... というカラム名になる。

$ cat prefectures.csv | sed 1d | q -d, 'select c1 from -'
愛知県
青森県
秋田県
石川県
茨城県
岩手県
愛媛県
大分県
大阪府
岡山県

クエリ

標準的な SQL はだいたい使える。

SQL の文法は SQLite と同じ。

where

$ q -d, -H 'select * from prefectures.csv where 地方="東北"'
青森県,あおもりけん,東北,1308649
秋田県,あきたけん,東北,1022839
岩手県,いわてけん,東北,1279814

order by

$ q -d, -H 'select * from prefectures.csv order by 人口 desc'
大阪府,おおさかふ,近畿,8838908
愛知県,あいちけん,中部,7484094
茨城県,いばらきけん,関東,2917857
岡山県,おかやまけん,中国,1922181
愛媛県,えひめけん,四国,1385840
青森県,あおもりけん,東北,1308649
岩手県,いわてけん,東北,1279814
大分県,おおいたけん,九州,1166729
石川県,いしかわけん,中部,1154343
秋田県,あきたけん,東北,1022839

limit

$ q -d, -H 'select * from prefectures.csv order by 人口 desc limit 3'
大阪府,おおさかふ,近畿,8838908
愛知県,あいちけん,中部,7484094
茨城県,いばらきけん,関東,2917857

group by

$ q -d, -H 'select 地方, count(1) from prefectures.csv group by 地方'
中国,1
中部,2
九州,1
四国,1
東北,3
近畿,1
関東,1

having

$ q -d, -H 'select 地方, count(1) as cnt from prefectures.csv group by 地方 having cnt >= 2'
中部,2
東北,3

関数

関数も普通に使える。

sum

$ q -H -d, 'SELECT sum(人口) from prefectures.csv'
28481254

substr

$ q -H -d, 'SELECT substr(都道府県, 1, 1) from prefectures.csv'
愛
青
秋
石
茨
岩
愛
大
大
岡

その他のよく使うオプション

入力エンコーディングの指定

入力エンコーディングを指定するときは -e オプションを使う。

たとえば入力エンコーディングEUC-JP のときはエラーになってしまうが、

$ iconv -f UTF-8 -t EUC-JP prefectures.csv | q -d, -H 'select * from -'
Could not parse the input. Please make sure to set the proper -w input-wrapping parameter for your input, and that you use the proper input encoding (-e). Error: 'utf8' codec can't decode byte 0xc5 in position 0: invalid continuation byte

-e EUC-JP オプションを指定すると意図したとおりに動く。

$ iconv -f UTF-8 -t EUC-JP prefectures.csv | q -d, -H -e EUC-JP 'select * from -'
愛知県,あいちけん,中部,7484094
青森県,あおもりけん,東北,1308649
秋田県,あきたけん,東北,1022839
石川県,いしかわけん,中部,1154343
茨城県,いばらきけん,関東,2917857
岩手県,いわてけん,東北,1279814
愛媛県,えひめけん,四国,1385840
大分県,おおいたけん,九州,1166729
大阪府,おおさかふ,近畿,8838908
岡山県,おかやまけん,中国,1922181

出力エンコーディングの指定

出力エンコーディングを指定するときは -E オプションを使う。

$ q -H -d, 'select * from prefectures.csv' | nkf -g
UTF-8

$ q -H -d, -E EUC-JP 'SELECT * from prefectures.csv' | nkf -g
EUC-JP

ヘッダー行を出力

デフォルトはヘッダー行は出力されないが -O オプションを指定するとヘッダー行が出力される。

$ q -H -d, -O 'SELECT * from prefectures.csv'
都道府県,読み,地方,人口
愛知県,あいちけん,中部,7484094
青森県,あおもりけん,東北,1308649
秋田県,あきたけん,東北,1022839
石川県,いしかわけん,中部,1154343
茨城県,いばらきけん,関東,2917857
岩手県,いわてけん,東北,1279814
愛媛県,えひめけん,四国,1385840
大分県,おおいたけん,九州,1166729
大阪府,おおさかふ,近畿,8838908
岡山県,おかやまけん,中国,1922181