本記事は、 エムスリーキャリア Advent Calendar 2019 に穴が空いてしまったとき用の予備の記事として作成されたものですが、皆様の頑張りによって穴が空くこと無く完走しました。
よって、供養のために本日26日目の記事として公開します。
TL;DR
- CSVを扱うなら、
qコマンドでSQLで宣言的にデータ操作した方が、作業が楽。- 宣言的にデータ操作ができるので、手続き型言語でやるより便利。
- qコマンドで使えるSQLはSQLiteの方言。
- SQLiteにはdate型、time型はない。CSVを読み込んだときはTEXT型などの別の型なので、適切に変換する。
はじめに
普段はRailsアプリをいじってます。ですが、なんでもシステム化するのは工数の兼ね合いなどでできませんので、本番データの抽出などの運用業務も担うことが多々あります。
その際に重宝している q コマンドを紹介します。
q is 何？
- CSVからSQLでデータ抽出するためのコマンド。
- python 2.5以上の環境で動く。3系はまだ未対応。（まだ対応作業中のようです）
- （追記）２０２１年６月現在、version
2.0.9で、Python 3.7 に対応済みとのことです。やったね。
- （追記）２０２１年６月現在、version
-
qコマンドの基本的な使い方は 公式サイトの翻訳
なんでSQLがいいの？
rubyなどの手続き型言語でCSV処理をしてしまうと、ファイル形式が複雑だったり、抽出条件が複雑になった時追いにくい。
SQLならデータについて宣言的にデータ操作ができるので、より高い抽象レベルで作業に専念できる。
データ抽出条件のレビューをしてもらうときにも、効率的にやってもらえる。
使えるSQLの方言や関数
- 使えるSQLは SQLiteの方言。
- データ型にdate型、time型、datetime型がない代わりに、関数を使って変換します。
- サポートされている命令は SELECTのみ。JOINによる内部結合・外部結合がサポートされている。
日付・時刻を扱う際の注意点
qコマンドで日付・時刻を扱う場合、CSV上ではISO8601形式の文字列、またはUNIX時刻の数値かユリウス通日の数値である必要があります。
CSVでそうなっていない場合、事前にこの形式に変換しておきます。
コマンドの実行例
使用するバージョン
q version 1.7.1 + python 2.7.15 で動作させています。
（OSは、Windows 10でWSL 1のUbuntu 18.04 LTS上で動作させています）
サンプルデータ
| id | name | birthday |
|---|---|---|
| 1 | 狡噛慎也 | 2084-08-16 |
| 2 | 常守朱 | 2092-04-01 |
| 3 | 宜野座伸元 | 2084-11-21 |
| 4 | 征陸智己 | 2058-06-27 |
| 5 | 縢秀星 | 2090-12-03 |
| 6 | 六合塚弥生 | 2090-09-28 |
| 7 | 唐之杜志恩 | 2085-05-25 |
CSV形式のデータはこちら
sample.csv
id,name,birthday
1,狡噛慎也,2084-08-16
2,常守朱,2092-04-01
3,宜野座伸元,2084-11-21
4,征陸智己,2058-06-27
5,縢秀星,2090-12-03
6,六合塚弥生,2090-09-28
7,唐之杜志恩,2085-05-25
コマンド
ここでは、2090年以降に生まれた人を抽出し、早く生まれた順に並べます。
q -d, -H "
select
t.name,
strftime('%Y/%m/%d',t.birthday)
from
sample.csv as t
where
date(t.birthday) >= date('2090-01-01')
order by date(t.birthday) asc
"
実行結果
六合塚弥生,2090/09/28
縢秀星,2090/12/03
常守朱,2092/04/01
補足： q コマンド内部の処理の流れ
q コマンドのソースコードを斜め読みすると、処理の流れは以下のようになっている。
- 入力されたSQLからFrom句を解釈し、CSVを読み込んで
- sqlite3のDBをメモリ上に動的に作成
- 入力されたSQLを処理
qコマンドで業務はどう変わった？
sed + awk より可読性が高いし、LLより短いコードで手軽にデータ処理ができるようになった！
しかもSQLなら、大抵のエンジニアは教養として知っているので、sed + awkよりも手順を引き継ぎもしやすい！