CSV や TSV を SQL ライクに select できる q コマンドが便利すぎた。
CSV や TSV などの表形式のデータを SQL ライクに select できる q コマンド が便利すぎたのでメモしておく。
q コマンドとは
とりあえず公式ドキュメント。
公式ドキュメントには以下のように書いてある。
q is a command line tool that allows direct execution of SQL-like queries on CSVs/TSVs (and any other tabular text files).
ようするに CSV とか TSV みたいなテキストデータを SQL ぽく select できるコマンドなのだが、非常に簡単に使えてしかも驚くほど強力。
インストール
$ brew install q
使い方
公式ドキュメントが分かりやすいくてざっと公式ドキュメントに目を通すだけで十分なのだけおｄ，よく使うオプションなどを備忘録までにメモしておく。
サンプルで使う 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.csv は CSV フォーマットなので `-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 はだいたい使える。
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
