2015年07月30日

Text as Data - q によるテキスト(CSV, TSV など)データの SQL クエリー操作

例えばちょっとしたサイズのログや収集データの解析をスポットで行う必要がでたらどうするか。 僕ならデータベースにするか、それともスクリプト(Bash、Ruby、今なら Go なんかも)で片付けるか迷います。 迷う理由は前者は少し大袈裟で後者はロジックとコードが強く結びつき再利用性が低いから。 大掛かりでは無く、テキスト・データに対するクエリー・ロジックを SQL で分離できたら ・・・そんな願いを叶えるツールが q なんです。

q_qeury_text_as_data 

q - Text as Data のインストールと使い方


q のインストールは簡単。 プロジェクト・ページでほぼ全てのプラットフォーム向けにインストーラ(http://harelba.github.io/q/install.html)が用意されています。

download_q


僕は 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 ファイル中の日付文字列をいわゆる日付、タイムスタンプとして操作することもできるのです。

$ 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 > 標準出力)けど・・・いらないか!

それでは、また今度。

統計学が最強の学問である
西内 啓
ダイヤモンド社
売り上げランキング: 2,145

10年戦えるデータ分析入門 SQLを武器にデータ活用時代を生き抜く (Informatics &IDEA)
青木 峰郎
SBクリエイティブ
売り上げランキング: 1,912

Posted by netbuffalo at 21:00│Comments(0)TrackBack(0) ユーティリティ | Python


この記事へのトラックバックURL

コメントする

名前
 
  絵文字