2015年07月30日
Text as Data - q によるテキスト(CSV, TSV など)データの SQL クエリー操作
例えばちょっとしたサイズのログや収集データの解析をスポットで行う必要がでたらどうするか。 僕ならデータベースにするか、それともスクリプト(Bash、Ruby、今なら Go なんかも)で片付けるか迷います。 迷う理由は前者は少し大袈裟で後者はロジックとコードが強く結びつき再利用性が低いから。 大掛かりでは無く、テキスト・データに対するクエリー・ロジックを SQL で分離できたら ・・・そんな願いを叶えるツールが q なんです。
q - Text as Data のインストールと使い方
q のインストールは簡単。 プロジェクト・ページでほぼ全てのプラットフォーム向けにインストーラ(http://harelba.github.io/q/install.html)が用意されています。
僕は Ubuntu を使っていて q が採用している Python や依存ライブラリに不足はないので Single file を直接ダウンロードして以下の手順でインストール。
$ chmod +x q
$ sudo mv q /usr/local/bin/
$ q -v
q version 1.5.0
Copyright (C) 2012-2014 Harel Ben-Attia (harelba@gmail.com, @harelba on twitter)
http://harelba.github.io/q/
テキスト・ファイルを扱う前に ps コマンドの標準出力をパイプして q の使い方を学んでみましょう。
ps はプロセスのステータスを確認するコマンドで僕の Ubuntu(正確には Elementary OS)では次のような標準出力を得ることができます。
$ ps auxw | head USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND root 1 0.0 0.0 34120 4664 ? Ss 10:39 0:02 /sbin/init root 2 0.0 0.0 0 0 ? S 10:39 0:00 [kthreadd] root 3 0.0 0.0 0 0 ? S 10:39 0:00 [ksoftirqd/0] root 5 0.0 0.0 0 0 ? S< 10:39 0:00 [kworker/0:0H] root 7 0.0 0.0 0 0 ? S 10:39 0:01 [rcu_sched] root 8 0.0 0.0 0 0 ? S 10:39 0:00 [rcuos/0] root 9 0.0 0.0 0 0 ? S 10:39 0:00 [rcuos/1] root 10 0.0 0.0 0 0 ? S 10:39 0:00 [rcuos/2] root 11 0.0 0.0 0 0 ? S 10:39 0:00 [rcuos/3]
テーブル名の変わりに - を指定すると q はパイプで受け取った標準入力を read してくれ、これを COUNT すると 256 件という結果を得ることができます。
$ ps auxw | q -H "SELECT COUNT(*) FROM -"
256
-H オプションは先頭行(ヘッダ)を無視するオプション。 ただし、ヘッダ行自体は q 内部でカラム名としてマッピングされています(ここでは USER, PID, CPU ...)。
これを踏まえ USER 毎に RSS(Resident Set Size)、つまり物理メモリ消費量(KiB)の最も大きな値を求める場合はこうなります。
$ ps auxw | q -H "SELECT USER,MAX(RSS) FROM - GROUP BY 1"
avahi 2828
colord 8608
daemon 160
kernoops 2520
libvirt+ 2372
lxc-dns+ 2304
message+ 3644
netbuff+ 727856
nobody 3316
root 80912
rtkit 2512
syslog 2684
whoopsie 9588
USER 毎の RSS 合計値を集計するならこう。 この例では AS 句 も使っています。
$ ps auxw | q -H "SELECT USER,SUM(RSS) AS TOTAL FROM - GROUP BY 1 ORDER BY TOTAL DESC"
netbuff+ 3506380
root 263884
whoopsie 9588
colord 8608
message+ 3644
nobody 3316
avahi 3080
syslog 2684
kernoops 2520
rtkit 2512
libvirt+ 2372
lxc-dns+ 2304
daemon 160
RSS の単位はキビバイト。 MiB にスケールを変えるならこうなりますね。
$ ps auxw | q -H "SELECT USER,SUM(RSS)/1024 AS TOTAL FROM - GROUP BY 1 ORDER BY TOTAL DESC"
netbuff+ 3580
root 262
whoopsie 9
colord 8
avahi 3
message+ 3
nobody 3
kernoops 2
libvirt+ 2
lxc-dns+ 2
rtkit 2
syslog 2
daemon 0
「俺は一体なんで 3.6 GiB もメモリを使ってんだ?」って疑問に思い、netbuff+ ユーザで 100 MiB 以上の物理メモリを消費しているプロセス(COMMAND)を確認するならこうですね。
$ ps auxw | q -H "SELECT COMMAND,RSS/1024 AS MiB from - WHERE USER='netbuff+' AND MiB > 100 ORDER BY 2 DESC"
/usr/lib/firefox/firefox 798
python3 227
chromium-browser 204
gala 197
/usr/lib/chromium-browser/chromium-browser 122
/home/netbuffalo/.dropbox-dist/dropbox-lnx.x86_64-3.6.9/dropbox 118
/usr/lib/chromium-browser/chromium-browser 106
q による CSV ファイルの SQL 操作
日本郵便が公開している全国郵便番号 CSV データ(http://www.post.japanpost.jp/zipcode/download.html)を q してみましょうか。
このデータは MS 漢字コード(シフト JIS)でエンコードされていると説明があるのですが、Python で直接 shift-jis を UTF-8 にデコードするのはちょっと心配。 僕は先に iconv で UTF-8 に変換しておくことにします(q 自体 -e オプションでエンコーディングを直接指定することも可能)。
$ iconv -f SJIS -t UTF8 KEN_ALL_ROME.CSV > KEN_ALL_ROME_UTF8.CSV
wc でカウントするとこのデータには 12 万件強の郵便番号がリストされていることがわかります(性能を比較するため time 付き)。
$ time wc -l KEN_ALL_ROME_UTF8.CSV
123699 KEN_ALL_ROME_UTF8.CSV
real 0m0.034s
user 0m0.014s
sys 0m0.017s
勿論、q でも同じ結果。
$ time q "SELECT COUNT(*) FROM ./KEN_ALL_ROME_UTF8.CSV"
123699
real 0m1.094s
user 0m1.077s
sys 0m0.020s
中身は郵便番号、都道府県名、市区町村名、町域名の順で構成された CSV ファイル。
$ head KEN_ALL_ROME_UTF8.CSV
"0600000","北海道","札幌市 中央区","以下に掲載がない場合","HOKKAIDO","SAPPORO SHI CHUO KU","IKANIKEISAIGANAIBAAI"
"0640941","北海道","札幌市 中央区","旭ケ丘","HOKKAIDO","SAPPORO SHI CHUO KU","ASAHIGAOKA"
"0600041","北海道","札幌市 中央区","大通東","HOKKAIDO","SAPPORO SHI CHUO KU","ODORIHIGASHI"
"0600042","北海道","札幌市 中央区","大通西(1〜19丁目)","HOKKAIDO","SAPPORO SHI CHUO KU","ODORINISHI(1-19-CHOME)"
"0640820","北海道","札幌市 中央区","大通西(20〜28丁目)","HOKKAIDO","SAPPORO SHI CHUO KU","ODORINISHI(20-28-CHOME)"
"0600031","北海道","札幌市 中央区","北一条東","HOKKAIDO","SAPPORO SHI CHUO KU","KITA1-JOHIGASHI"
"0600001","北海道","札幌市 中央区","北一条西(1〜19丁目)","HOKKAIDO","SAPPORO SHI CHUO KU","KITA1-JONISHI(1-19-CHOME)"
"0640821","北海道","札幌市 中央区","北一条西(20〜28丁目)","HOKKAIDO","SAPPORO SHI CHUO KU","KITA1-JONISHI(20-28-CHOME)"
"0600032","北海道","札幌市 中央区","北二条東","HOKKAIDO","SAPPORO SHI CHUO KU","KITA2-JOHIGASHI"
"0600002","北海道","札幌市 中央区","北二条西(1〜19丁目)","HOKKAIDO","SAPPORO SHI CHUO KU","KITA2-JONISHI(1-19-CHOME)"
先頭から 3 件の市区町村名を SELECT するのであればこう。 -d はデリミタの指定で CSV 形式なのでここでは”,”になり、ヘッダーが無いのでカラムは c + [num] で指定します。
$ time q -d "," "SELECT c3 FROM ./KEN_ALL_ROME_UTF8.CSV LIMIT 3"
札幌市 中央区
札幌市 中央区
札幌市 中央区
real 0m1.283s
user 0m1.220s
sys 0m0.066s
郵便番号の多い市区町村トップ10はどうなるか?
$ time q -d "," "SELECT c3,COUNT(*) FROM ./KEN_ALL_ROME_UTF8.CSV GROUP BY 1 ORDER BY 2 DESC LIMIT 10"
富山市,1147
岐阜市,835
港区,775
上越市,753
新宿区,696
京都市 上京区,660
京都市 伏見区,630
京都市 下京区,601
長岡市,581
京都市 中京区,557
real 0m1.404s
user 0m1.362s
sys 0m0.045s
東京都に限定したら?
$ time q -d "," "SELECT c3,COUNT(*) FROM ./KEN_ALL_ROME_UTF8.CSV WHERE c2 = '東京都' GROUP BY 1 ORDER BY 2 DESC LIMIT 10"
港区,775
新宿区,696
千代田区,485
中央区,158
八王子市,120
足立区,91
江東区,85
豊島区,83
渋谷区,73
世田谷区,62
real 0m1.302s
user 0m1.260s
sys 0m0.044s
結合だって可能。 例えば郵便番号と個人名を持つ CSV ファイルがあったとして、
$ head USER.CSV
"9071435","田中太郎"
郵便番号データと結合して表示することができます。
$ time q -d "," "SELECT USER.c2,ADDR.c2,ADDR.c3,ADDR.c4 FROM ./KEN_ALL_ROME_UTF8.CSV ADDR JOIN USER.CSV USER ON (ADDR.c1 = USER.c1)"
田中太郎,沖縄県,八重山郡 竹富町,新城
real 0m1.304s
user 0m1.254s
sys 0m0.053s
ところで q は何故ここまで高いレベルで SQL ライクなのでしょうか? 実は q 内部では SQLite3 をインメモリ・モードで使っていて実体はデータベースなのです(RAM 上にテーブルとデータを作成)。
SQLite3 をテキストと組み合わせるのはとても賢いアイデアで、例えば日付型を持たず TEXT 型が書式に応じて日付データとして扱ってくれる SQLite3 であれば CSV ファイル中の日付文字列をいわゆる日付、タイムスタンプとして操作することもできるのです。
SQLite3 をテキストと組み合わせるのはとても賢いアイデアで、例えば日付型を持たず TEXT 型が書式に応じて日付データとして扱ってくれる SQLite3 であれば CSV ファイル中の日付文字列をいわゆる日付、タイムスタンプとして操作することもできるのです。
$ cat TIME.csv
"hoge","2015-07-28 20:00:00"
"huga","2015-07-28 21:00:01"
"piyo","2015-07-28 22:00:00"
$ q -d "," "SELECT * FROM ./TIME.csv where c2 > '2015-07-28 21:00:00'"
huga,2015-07-28 21:00:01
piyo,2015-07-28 22:00:00
その他サンプルはこちら(http://harelba.github.io/q/examples.html)。
もう少し頑張ったら UPDATE もできそうな気がする(終端記号を許容して SELECT(インメモリでテーブル作成、データの INSERT) > UPDATE > SELECT > 標準出力)けど・・・いらないか!
それでは、また今度。
10年戦えるデータ分析入門 SQLを武器にデータ活用時代を生き抜く (Informatics &IDEA)
posted with amazlet at 15.07.30
青木 峰郎
SBクリエイティブ
売り上げランキング: 1,912
SBクリエイティブ
売り上げランキング: 1,912